Historical - design doc for semisync replication

This can be read along with the initial semisync post. This post was shared on code.google.com many years ago but code.google has been shutdown. It describes work done by my team at Google. I am interested in the history of technology and with some spare time have been enable to republish it.

Semisync replication was designed and implemented by Wei Li. He did a lot of work to make replication better for web-scale and then moved away from MySQL. Upstream reimplemented the feature which was a good decision given the constraints on our implementation time.

Introduction

Semi-sync replication blocks return from commit on a master until at least one slave acknowledges receipt of all replication events for that transaction. Note that the transaction is committed on the master first.

Background

MySQL replication is asynchronous. If a master fails after committing a transaction but before a slave copied replication events for that transaction, the transaction might be lost forever. For some deployments, we prefer to reduce the chance of this.

The asynchronous replication model might lose user-visible transactions during a unplanned failover. If the master crashes and we let a slave take over, then the application must be prepared to check which transactions actually made it to the slave, and rerun the ones that did not.

Overview

To solve the asynchronous problem, we can add different degrees of synchronicity: fully synchronous replication would wait for the slave to process the transaction first, before telling the client that it has been committed. The downside: delays in commits.

We propose to do semi-synchronous replication: before telling the client that a transaction has been committed, make sure that the slave receives its replication events first. This is also called 2-safe replication. Below is the picture of a semi-synchronous replication:

MySQL commit protocol

The commit protocol is different between MySQL-4.x and MySQL-5.0. The main reason is because MySQL-5.0 uses two phase commit to make sure binlog status conforms to transactional storage engines' internal status.

• MySQL-4.x
• write the transaction in the binlog file
• commit the transaction in InnoDB or other storage engine
• MySQL-5.0:
• prepare the transaction in InnoDB or other storage engines
• write the transaction in the binlog file - this is considered as the commit point
• commit the transaction in InnoDB or other storage engines

Semi-synchronous commit protocol

Our proposed semi-synchronous replication works in the following way:

• commit the transaction
• wait for the replica databases acknowledge that they already received the transaction - this step has a timeout
• tell the client that the commit has been processed

The committed transaction would not wait indefinitely for the replication thread to send the binlog event. If so, the transaction would never commit if there are network issues or the slave database is down. In step-2, the committed transaction will timeout after a predefined waiting time.

After the timeout, the semi-synchronous replication will be disabled. A new replication thread can catch up in replication and enables it again.

During the wait for network acknowledgment, other transaction would not be blocked and can still continue.

The following global counters will be added:

• transaction failure timeouts
• transactions without going through semi-synchronous replication
• network timeouts

TCP/IP is not enough for acknowledgment

The tricky thing is that replication thread calls TCP/IP to send the replication events. Note that TCP/IP, even with the TCP_NODELAY option does not guarantee that the slave has received the data. Thus, to make sure that the slave database has got the transaction, the slave database must give us an reply to indicate that. This means a transaction commit requires at least a TCP round-trip time. Considering that the round-trip time in one data center is 0.5ms, this should not prevent MySQL from achieving above hundreds of transaction per second.

We will also provide the option of sending the transaction without waiting for the confirmation. We can measure the performance difference to understand the network overhead in the synchronous replication. A parameter will be provided to dynamically change the timeout.

Replication protocol changes

To guarantee that a slave database has got the transaction, the slave database must send one reply message back. This is the situation:

• the master database need to know when to wait for the reply from the slave database; right now, the master database never waits
• the slave database need to know when it should send reply message to the master master database
• we can not do the ping-pong process for every replication event; it can only work for one transaction to minimize the network overhead

In this way, we must have a way for both the master and the slave know when to start this confirmation process. So, any design without replication event changes or replication protocol changes is not possible because the slave database can only figure out the information from the received message. Initially, we wanted to make the replication event change that one special event is appended after a transaction to indicate the slave waiting. However, since replication logs will be served at least once for each replica, this turns out to be a bad idea because we wait only once during transaction commit time.

The only solution after this is to make replication protocol changes. This is the current MySQL replication login process:

• on the slave database side:
• a slave database calls safe_connect() to login to the master database
• COM_BINLOG_DUMP command is sent to the master database to request for binlogs with the following information: binlog_filename, binlog_pos, binlog_flag, server_id
• on the master database side:
• COM_BINLOG_DUMP is handled to recognize the requested dump information
• mysql_binlog_send() is called to send the requested binlog events

Because binlog_flag is sent from the slave database and processed in the master database, semi-synchronous replication will be initiated by the slave and the replication thread will trigger the synchronous operation in the master database. We add one bit in binlog_flag so that the slave database can register itself as synchronous replication target.

    * #define BINLOG_SEMI_SYNC 0x02

If BINLOG_SEMI_SYNC is set for the replication thread, then every event sent from the master database to the slave database will always have one byte extra header. The one byte indicates whether the replication thread is expecting the reply from the slave database. In this way, the new replication protocol's usage is session-based.

Work on the master database side

We will create a search tree that records all waiting transactions. The tree will be keyed on (binlog_filename, binlog_pos). At transaction commit time, after all transaction events have been written into the binlog file, we insert the (binlog_filename, binlog_pos) into the search tree. The purpose of the search tree is for the replication thread to recognize the current waiting transactions. When a transaction stops waiting for the reacknowledgment of the binlog events, the transaction's position should be removed from the tree.

The replication thread reads a binlog event from the file and probe the binlog position into the search tree. Depending on whether the position in the search tree, the replication thread will set the one byte extra header before sending the event.

Work on the slave database side

If a slave database is connecting with the semi-synchronous replication mode, it will check the first byte header to decide whether to reply the replication event. Otherwise, it work as original.

Currently, the master database uses one mutex LOCK_log to synchronize all operations on the binlog:

• a transaction acquires LOCK_log before writing transaction events to a binlog
• the transaction releases LOCK_log after committing and flushing the binlog to the file system
• replication thread acquires LOCK_log before reading each event and release the lock afterwards

In semi-synchronous replication, we are planning to add one mutex and one condition variable:

• innobase_repl_semi_cond: this variable is signaled when enough binlog has been sent to slave, so that a waiting transaction can return the 'ok' message to the client for a commit
• innobase_repl_semi_cond_mutex: the mutex that is associated with the above condition variable

Code flow for each MySQL session during transaction commit

• write all binlog events, append the transaction-end event and flush the file to the filesystem
• commit the transaction inside InnoDB
• acquire innobase_repl_semi_cond_mutex
• while true:
• if semi-synchronous replication has been disabled by timeout:
• update the asynchronous transaction counter
• release innobase_repl_semi_cond_mutex and return from the commit
• check the current binlog sending status
• if the binlog sending status is ahead of my transaction's waiting position
• release innobase_repl_semi_cond_mutex and return from the commit
• set my binlog waiting position to my commited transaction position
• wait for innobase_repl_semi_cond with a timeout
• if timeout occurs with waiting innobase_repl_semi_cond or if semi-synchronous replication is disabled after wake-up
• print the error message
• update failed timeout counter
• disable the semi-synchronous replication until the replication thread enables it again
• release innobase_repl_semi_cond_mutex and return from the commit

Code flow for replication thread

This is the work done by the replication thread when sending binlog events to support the semi-synchronous protocol.

• if the replication is not the semi-synchronous target, then do nothing and simply return
• if the most recent sent event is NOT an transaction-end event, then do nothing and simply return
• wait for the confirmation from the slave database with a network timeout
• remember whether network timeout occurs
• acquire innobase_repl_semi_cond_mutex
• if the network timeout occurs:
• update failed timeout counter
• disable the semi-synchronous replication until the replication thread enables it again
• release innobase_repl_semi_cond_mutex and return
• if the semi-synchronous replication is disabled, then enable the semi-synchronous replication again
• check whether any session is waiting for the current sending position
• if there exist such sessions, wake them up through innobase_repl_semi_cond
• release innobase_repl_semi_cond_mutex and return

The only one mutex/condition variable create one synchronize point because every committed transaction needs to wait for innobase_repl_semi_cond. When the replication thread wakes up innobase_repl_semi_cond, it has to use broadcast. This might be changed in the future if there are performance issues around the single mutex wait.

Codeflow for replication I/O thread connection to the primary database

When a replica connects to the primary database, it is an opportunity for the primary database to understand the replica's progress. Based on the progress, the primary database will adjust semi-synchronous replication's progress. If the replica's status is too behind, semi-synchronous replication might be suspened until the replica is fully caught up.

If there is only one semi-synchronous target, meaning just one thread is sending the binlog to the slave for which we want synchronous replication, then the replication position should increase monotonically. However, we want to have more than one semi-synchronous replica target to increase the primary database's transaction availability. In that sense, a falling behind replica should not affect the status on the primary if others are caught up.

Network group commit

Replication threads can do group commit to minimize network overhead. When the thread finds the current sending event is a end of transaction event, it would request for a reply from the slave database immediately. Instead, it look for the tail of the binlog file to check whether there are more transaction. Or, it can wait for a while to make the check. If there are more transactions in the file, the replication thread can send all waiting transactions and only waits for one reply. This looks like that we are doing group commit on the network.

The benefit is that we can reduce network round trip by batching transaction replies. However, it also reduces the reliability of semi-synchronous replication. If we acknowledge each transaction, we can only lose at most one transaction during failure. If we do group commit, we might lose all transactions in the batch. We need to trade off between performance and reliability.

Historical - SemiSync replication

This post was shared on code.google.com many years ago but code.google has been shutdown. It describes work done by my team at Google. I am interested in the history of technology and with some spare time have been enable to republish it.

Semisync was useful to but misunderstood. Lossless semisync was awesome but perhaps arrived too late as Group Replication has a brighter future. I like Lossless semisync because it provides similar durability guarantees to GR without the overhead of running extra instances locally. Not running extra instances locally for GR means that commit will be slow courtesy of the speed of light. I hope that GR adds support for log-only voters (witnesses).

Regular semisync was misunderstood because people thought it provided extra durability. It didn't do that. It rate limited busy writers to reduce replication lag. It also limited a connection to at most one transaction that wasn't on at least one slave to reduce the amount of data that can be lost when a primary disappears.

Wei Li implemented semisync during his amazing year of work on replication. Then it was improved to lossless semisync by Zhou Zhenzing (see the first and second post and feature request) and work done upstream. Lossless semisync was widely deployed at FB courtesy of Yoshinori Matsunobu.

Introduction

Heikki Tuuri had the idea and perhaps a PoC but there wasn't much demand for it beyond me. Solid will offer their version of this later in 2007. We couldn't wait and implemented it.

The MySQL replication protocol is asynchronous. The master does not know when or whether a slave gets replication events. It is also efficient. A slave requests all replication events from an offset in a file. The master pushes events to the slave when they are ready.

Usage

We have extended the replication protocol to be semi-synchronous on demand. It is on demand because each slave registers as async or semi-sync. When semi-sync is enabled on the master, it blocks return from commit until either at least one semi-sync slave acknowledges receipt of all replication events for the transaction or until a configurable timeout expires.

Semi-synchronous replication is disabled when the timeout expires. It is automatically reenabled when slaves catch up on replication.

Configuration

The following parameters control this:

• rpl_semi_sync_enabled configures a master to use semi-sync replication
• rpl_semi_sync_slave_enabled configures a slave to use semi-sync replication. The IO thread must be restarted for this to take effect
• rpl_semi_sync_timeout is the timeout in milliseconds for the master

Monitoring

The following variables are exported from SHOW STATUS:

• Rpl_semi_sync_clients - number of semi-sync replication slaves
• Rpl_semi_sync_status - whether semi-sync is currently ON/OFF
• Rpl_semi_sync_slave_status - TBD
• Rpl_semi_sync_yes_tx - how many transaction got semi-sync reply
• Rpl_semi_sync_no_tx - how many transaction do not get semi-sync reply
• Rpl_semi_sync_no_times - TBD
• Rpl_semi_sync_timefunc_failures - how many gettimeofday() function fails
• Rpl_semi_sync_wait_sessions - how many sessions are waiting for replies
• Rpl_semi_sync_wait_pos_backtraverse - how many time we move waiting position back
• Rpl_semi_sync_net_avg_wait_time(us) - the average network waiting time per tx
• Rpl_semI_sync_net_wait_time - total time in us waiting for ACKs
• Rpl_semi_sync_net_waits - how many times the replication thread waits on the network
• Rpl_semi_sync_tx_avg_wait_time(us) - the average transaction waiting time
• Rpl_semi_sync_tx_wait_time - TBD
• Rpl_semi_sync_tx_waits - how many times transactions wait
• Rpl_semi_sync_timefunc_failures - #times gettimeofday calls fail

Design Overview

Semi-sync replication blocks any COMMIT until at least one replica has acknowledged receipt of the replication events for the transaction. This ensures that at least one replica has all transactions from the master. The protocol blocks return from commit. That is, it blocks after commit is complete in InnoDB and before commit returns to the user.

This option must be enabled on a master and slaves that are close to the master. Only slaves that have this feature enabled participate in the protocol. Otherwise, slaves use the standard replication protocol.

Deployment

Semi-sync replication can be enabled/disabled on a master or slave without shutting down the database.

Semi-sync replication is enabled on demand. If there are no semi-sync replicas or they are all behind in replication, semi-sync replication will be disabled after the first transaction wait timeout. When the semi-sync replicas catch up, transaction commits will wait again if the feature is not disabled.

Implementation

The design doc is here.

Each replication event sent to a semi-sync slave has two extra bytes at the start that indicate whether the event requires acknowledgement. The bytes are stripped by the slave IO thread and the rest of the event is processed as normal. When acknowledgement is requested, the slave IO thread responds using the existing connection to the master. Acknowledgement is requested for events that indicate the end of a transaction, such as commit or an insert with autocommit enabled.

Historical - SQL changes for MySQL

This is a post from circa 2008 that describes many of the changes we made at Google to the parser. It was first shared at code.google.com which has since shutdown. It describes work done by my team at Google.

Introduction

This describes changes to SQL parsed by MySQL.

New tokens:

• CLIENT_STATISTICS
• TABLE_STATISTICS
• USER_STATISTICS
• INDEX_STATISTICS
• IF_IDLE
• MAKE
• MAPPED
• MAX_QUERIES_PER_MINUTE
• NEW_PASSWORD
• ROLE
• SLOW
• TCMALLOC
• IEEE754_TO_STRING
• LAST_VALUE
• ORDERED_CHECKSUM
• UNORDERED_CHECKSUM

New SQL functions

New SQL functions include:

• ORDERED_CHECKSUM - This is a SQL aggregate function that accepts one or more arguments. It returns the hash of its input arguments per group. The function is order dependent. The output of this from the first row in a group is used as the seed for the hash on the next row.
• UNORDERED_CHECKSUM - This is a SQL aggregate function that accepts one or more arguments. It returns the hash of its input arguments per group. The function is order independent. The result from each row in a group is combined by XOR.
• LAST_VALUE - This is a SQL aggregate function. It returns the last value read per group. Thus this depends on the input order to aggregation. See OnlineDataDrift for a use case (TODO - repost that)
• HASH - This is a SQL function. It returns the hash of its input argument. It is not an aggregate function and produces one value per row.
• IEEE754_TO_STRING - Converts a float or double decimal value with type string. This generates 17 digits of precision so that conversion of the string back to a double does not lose precision (the original double should be equal to the final double for all but a few special cases.
• NEW_PASSWORD - Computes the new-style password hash regardless of the value for the my.cnf parameter old_passwords.

An example for UNORDERED_CHECKSUM is:

select unordered_checksum(c1) from foo group by c2;
select unordered_checksum(c1, c2) from foo group by c3;

An example for ORDERED_CHECKSUM is:

select ordered_checksum(c1) from foo group by c2;
select ordered_checksum(c1, c2) from foo group by c3;

An example for HASH is:

select hash(column) from foo

New options for existing statements

KILL <id> IF_IDLE can be used to kill a connection but only if it is idle.

MAX_QUERIES_PER_MINUTE can be used in place of MAX_QUERIES_PER_HOUR. This version of MySQL enforces query limits per minute rather than per hour and the value stored in the MySQL privilege table is the rate per minute.

CREATE MAPPED USER '' ROLE 'bar' and DROP MAPPED USER 'foo' support mapped users. See MysqlRoles for more details (TODO - repost Roles)

SHOW PROCESSLIST WITH ROLES and SHOW USER_STATISTICS WITH ROLES use the role name rather than the user name in results.

New statements

See the monitoring post for more details:

• SHOW USER_STATISTICS
• SHOW TABLE_STATISTICS
• SHOW INDEX_STATISTICS
• SHOW CLIENT_STATISTICS
• FLUSH TABLE_STATISTICS
• FLUSH INDEX_STATISTICS
• FLUSH CLIENT_STATISTICS*

See the post on delayed users for more details:

• MAKE USER 'foo' DELAYED 1000
• MAKE CLIENT '10.0.0.1' DELAYED 2000
• SHOW DELAYED USER
• SHOW DELAYED CLIENT
• SHOW TCMALLOC STATUS displays the status of tcmalloc when MySQL hash been linked with it and compiled with -DUSE_TCMALLOC. This displays the output from MallocExtension::GetStats.
• CAST supports cast to DOUBLE
• SHOW INNODB LOCKS provides more details on InnoDB lock holders and waiters
• FLUSH SLOW QUERY LOGS rotates the slow query log.
• MAKE MASTER REVOKE SESSION disconnects all sessions but the current one and prevents future connections from all users unless they have SUPER, REPL_CLIENT or REPL_SLAVE privileges. MAKE MASTER GRANT SESSION undoes this.

Historical - Transactional Replication

This is a post about work done by Wei Li at Google to make MySQL replication state crash safe. Before this patch it was easy for a MySQL storage engine and replication state to disagree after a crash. Maybe it didn't matter as much for people running MyISAM because that too wasn't crash safe. But so many people had to wake up late at night to recover from this problem which would manifest as either a duplicate key error or silent corruption. The safe thing to do was to not restart a replica after a crash and instead restore a new replica from a backup.

Wei Li spent about 12 months fixing MySQL replication adding crash safety and several other features. That was an amazing year from him. I did the code reviews. My reviews were weak. MySQL replication code was difficult back then.

I got to know Domas Mituzas after he extracted this feature from the Google patch to use for Wikipedia. I was amazed he did this and he continued to make my life with MySQL so much better at Google and then Facebook. When I moved to Google I took too long to port this patch for them. My excuse is that Domas wasn't yelling enough -- there were many problems and my priority list was frequently changing.

This post was first shared at code.google.com which has since shutdown. This feature was in production around 2007, many years before something similar was provided by upstream. I can't imagine doing-web-scale MySQL without it. The big-3 problems for me back then were: lack of monitoring, replication and InnoDB on many-core.

Introduction

Replication state on the slave is stored in two files: relay-log.info and master.info. The slave SQL thread commits transactions to a storage engine and then updates these files to indicate the next event from the relay log to be executed. When the slave mysqld process is stopped between the commit and the file update, replication state is inconsistent and the slave SQL thread will duplicate the last transaction when the slave mysqld process is restarted.

Details

This feature prevents that failure for the InnoDB storage engine by storing replication state in the InnoDB transaction log. On restart, this state is used to make the replication state files consistent with InnoDB.

The feature is enabled by the configuration parameter rpl_transaction_enabled=1. Normally, this is added to the mysqld section in /etc/my.cnf. The state stored in the InnoDB transaction log can be cleared setting a parameter and then committing a transaction in InnoDB. For example:

set session innodb_clear_replication_status=1;
create table foo(i int) type=InnoDB;
insert into foo values (1);
commit;
drop table foo;

Replication state is updated in the InnoDB transaction log for every transaction that includes InnoDB. It is updated for some transactions that don't include InnoDB. When the replication SQL thread stops, it stores its offset in InnoDB.

The Dream

We would love to be able to kill the slave (kill -9) and have it always recover correctly. We are not there yet for a few reasons:

• We don't update the state in InnoDB for some transactions that do not use InnoDB
• DDL is not atomic in MySQL. For *drop table* and *create table* there are two steps: create or drop the table in the storage engine and create or drop the frm file that describes the table. A crash between these steps leaves the storage engine out of sync with the MySQL dictionary.
• Other replication state is not updated atomically. When relay logs are purged, the files are removed and then the index file is updated. A crash before the index file update leaves references to files that don't exist. Replication cannot not be started in that case. Also, the index file is not updated in place rather than atomically (write temp file, sync, rename). 

Historical - UserTable Monitoring

This is a post about user, table and index monitoring added by my team at Google. It was shared at code.google.com which has since shutdown. Eventually I wrote a better guide here. This was in production around 2007, several years before something similar was provided by upstream. This monitoring allowed us to keep MySQL from collapsing. I can't imagine how anyone did web-scale MySQL without it. The big-3 problems for me back then were: lack of monitoring, replication and InnoDB on many-core.

A common problem was a server collapsing from short-running queries. This was hard to see in SHOW PROCESSLIST but easy to spot with USER_STATISTICS.

We also had a framework to sample, archive and aggregate SHOW PROCESSLIST output from production servers.

Introduction

We have added code to measure database activity and aggregate the results per account, table and index. We have also added SQL statements to display these values.

One of these days we will integrate this with the information schema.

Details

Note that *rows changed* includes rows from insert, update, delete and replace statements.

The commands are:

• SHOW USER_STATISTICS
• SHOW TABLE_STATISTICS
• SHOW INDEX_STATISTICS
• SHOW CLIENT_STATISTICS
• FLUSH TABLE_STATISTICS
• FLUSH INDEX_STATISTICS
• FLUSH CLIENT_STATISTICS

SHOW USER_STATISTICS

This displays resource consumption for all sessions per database account:

• number of seconds executing SQL commands (wall time and CPU time)
• number of concurrent connections (the current value)
• number of connections created
• number of rollbacks
• number of commits
• number of select statements
• number of row change statements
• number of other statements and internal commands
• number of rows fetched
• number of rows changed
• number of bytes written to the binlog
• number of network bytes sent and received
• number of rows read from any table
• number of failed attempts to create a connection
• number of connections closed because of an error or timeout
• number of access denied errors
• number of queries that return no rows

SHOW CLIENT_STATISTICS

This has the same values as SHOW USER_STATISTICS but they are aggregated by client IP address rather than by database account name.

SHOW TABLE_STATISTICS

This displays the number of rows fetched and changed per table. It also displays the number of rows changed multiplied by the number of indexes on the table.

SHOW INDEX_STATISTICS

This displays the number of rows fetched per index. It can be used to find unused indexes.

Flush commands

Each of the flush commands clears the counters used for the given SHOW command.

Historical - InnoDB SMP performance

This is a post about InnoDB SMP performance improvements in 200X written around 2008. It was shared at code.google.com which has since shutdown. It describes work done by my team at Google. The big-3 problems for me back then were: lack of monitoring, replication and InnoDB on many-core.

Introduction

This describes performance improvements from changes in the v2 Google patch. While the changes improve performance in many cases, a lot of work remains to be done. It improves performance on SMP servers by:

• disabling the InnoDB memory heap and associated mutex
• replacing the InnoDB rw-mutex and mutex on x86 platforms
• linking with tcmalloc

While tcmalloc makes some of the workloads much faster, we don't recommend its use yet with MySQL as we are still investigating its behavior.

Database reload

This displays the time to reload a large database shard on a variety of servers (HW + SW). Unless otherwise stated, my.cnf was optimized for a fast (but unsafe) reload with the following values. Note that

• innodb_flush_method=nosync is only in the Google patch and is NOT crash safe (kind of like MyISAM). This uses a real data set that produces a 100GB+ database.
• innodb_log_file_size=1300M
• innodb_flush_method=nosync
• innodb_buffer_pool_size=8000M
• innodb_read_io_threads=4
• innodb_write_io_threads=2
• innodb_thread_concurrency=20

The data to be reloaded was in one file per table on the db server. Each file was compressed and reloaded by a separate client. Each table was loaded by a separate connection except for the largest tables when there was no other work to be done. 8 concurrent connections were used.

The smpfix RPM is MySQL 5.0.37 plus the v1 Google patch and the SMP fixes that include:

• InnoDB mutex uses atomic ops
• InnoDB rw-mutex uses lock free methods to get and set internal lock state
• tcmalloc is used in place of glibc malloc
• the InnoDB malloc heap is disabled

The base RPM is MySQL 5.0.37 and the v1 Google patch. It does not have the SMP fixes.

The servers are:

• 8core - the base RPM on an 8-core x86 server
• 4core-128M - the base RPM on a 4-core x86 server with innodb_log_file_size=128M
• 8core-tc4 - the base RPM on an 8-core x86 server with innodb_thread_concurrency=4
• smpfix-128M - the smpfix RPM with innodb_log_file_size=128M
• 4core - the base RPM on a 4-core x86 server
• smpfix-4core - the smpfix RPM on a 4-core x86 server
• smpfix-512M - the smpfix RPM on an 8-core x86 server with innodb_log_file_size=512M
• smpfix - the smpfix RPM on an 8-core x86 server
• onlymalloc - the base RPM on an 8-core x86 server with the InnoDB malloc heap disabled
• smpfix-notcmalloc - the smpfix RPM on an 8-core x86 server without tcmalloc

The x-axis is time so larger is slower.

Sysbench readwrite

Sysbench includes a transaction processing benchmark. The readwrite version of the sysbench OLTP test is measured here using 1, 2, 4, 8, 16, 32 and 64 threads.

The sysbench command line options are:

# N is 1, 2, 4, 8, 16, 32 and 64
 --test=oltp --oltp-table-size=1000000 --max-time=600 --max-requests=0 --mysql-table-engine=innodb --db-ps-mode=disable --mysql-engine-trx=yes --num-threads=N

The my.cnf options are:

innodb_buffer_pool_size=8192M
innodb_log_file_size=1300M
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size = 200m
innodb_thread_concurrency=0
log_bin
key_buffer_size = 50m
max_heap_table_size=1000M
max_heap_table_size=1000M
tmp_table_size=1000M
max_tmp_tables=100

The servers are:

• base - MySQL 5.0.37 without the smp fix
• tc4 - MySQL 5.0.37 without the smp fix, innodb_thread_concurrency=4
• smpfix - MySQL 5.0.37 with the smp fix and tcmalloc
• notcmalloc - MySQL 5.0.37 with the smp fix, not linked with tcmalloc
• onlymalloc - MySQL 5.0.37 with the InnoDB malloc heap disabled
• my4026 - unmodified MySQL 4.0.26
• my4122 - unmodified MySQL 4.1.22
• my5067 - unmodified MySQL 5.0.67
• my5126 - unmodified MySQL 5.1.26
• goog5037 - the same as base, MySQL 5.0.37 without the smp fix

Results for sysbench readonly

Sysbench includes a transaction processing benchmark. The readonly version of the sysbench OLTP test is measured here using 1, 2, 4, 8, 16, 32 and 64 threads.

The sysbench command line options are:

# N is 1, 2, 4, 8, 16, 32 and 64
 --test=oltp --oltp-read-only --oltp-table-size=1000000 --max-time=600 --max-requests=0 --mysql-table-engine=innodb --db-ps-mode=disable --mysql-engine-trx=yes --num-threads=N

The my.cnf options are:

innodb_buffer_pool_size=8192M
innodb_log_file_size=1300M
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size = 200m
innodb_thread_concurrency=0
log_bin
key_buffer_size = 50m
max_heap_table_size=1000M
max_heap_table_size=1000M
tmp_table_size=1000M
max_tmp_tables=100

The servers are:

• base - MySQL 5.0.37 without the smp fix
• tc4 - MySQL 5.0.37 without the smp fix, innodb_thread_concurrency=4
• smpfix - MySQL 5.0.37 with the smp fix and tcmalloc
• notcmalloc - MySQL 5.0.37 with the smp fix, not linked with tcmalloc
• onlymalloc - MySQL 5.0.37 with the InnoDB malloc heap disabled
• my4026 - unmodified MySQL 4.0.26
• my4122 - unmodified MySQL 4.1.22
• my5067 - unmodified MySQL 5.0.67
• my5126 - unmodified MySQL 5.1.26
• goog5037 - the same as base, MySQL 5.0.37 without the smp fix

Concurrent joins

This test runs a query with a join. It is run using concurrent sessions. The data fits in the InnoDB buffer cache. The query is:

select count(*) from T1, T2 where T1.j > 0 and T1.i = T2.i

The data for T1 and T2 matches that used for the sbtest table by sysbench. This query does a full scan of T1 and joins to T2 by primary key.

The servers are:

• base - MySQL 5.0.37 without the smp fix
• tc4 - MySQL 5.0.37 without the smp fix, innodb_thread_concurrency=4
• smpfix - MySQL 5.0.37 with the smp fix and tcmalloc
• notcmalloc - MySQL 5.0.37 with the smp fix, not linked with tcmalloc
• onlymalloc - MySQL 5.0.37 with the InnoDB malloc heap disabled
• my4026 - unmodified MySQL 4.0.26
• my4122 - unmodified MySQL 4.1.22
• my5067 - unmodified MySQL 5.0.67
• my5126 - unmodified MySQL 5.1.26
• goog5037 - the same as base, MySQL 5.0.37 without the smp fix

I only have results for 8 and 16 core servers here. Lower times are better.

With data from the worst case:

Without data from the worst case:

With data from the worst case:

Without data from the worst case:

Concurrent inserts

This test reloads tables in parallel. Each connection inserts data for a different table. Tests were run using 1, 2, 4, 8 and 16 concurrent sessions. The regression for 5.0.37 is in the parser and was fixed by 5.0.54. A separate table is used for each connection. DDL for the tables is:

create table T$i (i int primary key, j int, index jx(j)) engine=innodb

Multi-row insert statements are used that insert 1000 rows per insert statement. Auto-commit is used. The insert statements look like:

INSERT INTO T1 VALUES (0, 0), (1, 1), (2, 2), ..., (999,999);

The servers are:

• base - MySQL 5.0.37 without the smp fix
• tc4 - MySQL 5.0.37 without the smp fix, innodb_thread_concurrency=4
• smpfix - MySQL 5.0.37 with the smp fix and tcmalloc
• notcmalloc - MySQL 5.0.37 with the smp fix, not linked with tcmalloc
• onlymalloc - MySQL 5.0.37 with the InnoDB malloc heap disabled
• my4026 - unmodified MySQL 4.0.26
• my4122 - unmodified MySQL 4.1.22
• my5067 - unmodified MySQL 5.0.67
• my5126 - unmodified MySQL 5.1.26
• goog5037 - the same as base, MySQL 5.0.37 without the smp fix

MySQL 5.0.37 has a performance regression in the parser. This was fixed in 5.0.54. See bug 29921.

Note, lower values for Time are better.

With data from the worst case:

Without data from the worst case:

With data from the worst case:

Without data from the worst case:

Fixing the InnoDB RW-lock

I hope that someone updates the InnoDB Wikipedia page to explain how it came to be. I know it started with Heikki Tuuri. At what point did he get help? Regardless, early InnoDB was an amazing accomplishment and fortunately MySQL saw the potential for it after other large DBMS vendors were not interested.

I enjoyed reading the source code -- it is well written with useful comments. Some of the comments are amusing at this point with references to HW assumptions that were valid in the early and mid 90s. InnoDB had, and might still have, a SQL parser that is used to maintain dictionary tables. Fortunately the code has aged well and been significantly improved by the InnoDB team.

InnoDB and SMP

InnoDB had a big problem in 2005. Commodity SMP hardware was changing from 4-core/1-socket to 8-core/2-socket and InnoDB wasn't ready for it. My memory is that MySQL in 2005 saturated at 10k QPS with sysbench and moving from 1 to 2 sockets hurt QPS.

InnoDB had many problems. A lot of work was done to fix them by the InnoDB team, Percona, Google, Facebook and others. I have forgotten a lot of the details but much is recorded in blog posts and slide decks. The InnoDB rw-lock was a big part of the problem. InnoDB implements a rw-lock and mutex. Both add value -- by adding monitoring for performance and debugging. The rw-lock is also required to support special use cases (I forgot the reasons, but they are valid). Unfortunately both the mutex and rw-lock were lousy on 2-socket servers under contention.

The problem was too much spinning. The rw-lock used the InnoDB mutex to protect its internal state. Both the rw-lock and mutex did some spinning before going to sleep when the lock could not be acquired. This is similar to PTHREAD_MUTEX_ADAPTIVE_NP but the spinning is configurable. The InnoDB rw-lock did spinning on its own and then could do even more spinning when using the InnoDB mutex that guarded its state. There was so much spinning.

The performance impact from this work is explained here.

A Solution

My memory is that Yasufumi Kinoshita implemented the solution first and I learned of that from his presentation at Percona Live. He might have been working at NTT at the time. Inspired by him, my team at Google implemented a similar solution, wrote a Spin model to validate the correctness and then contributed the fix to the InnoDB team who were part of Oracle. The Spin model helped convince the InnoDB team that the code might be correct. I am a huge fan of Spin.

I am proud of the work my team at Google did to fix the rw-lock and it was easy to work with the InnoDB team -- they have always been a great partner for external InnoDB contributors. But I also want to give credit for the person who first showed how to fix the InnoDB rw-lock as he has a long history of making InnoDB better.

Other fixes

Much more has been done to make InnoDB great for many-core servers. Back when the rw-lock was fixed the mutex was also changed to use atomic operations rather than pthread mutex (at least for x86, hopefully for ARM now). My early blog posts mention that and changes to the InnoDB memory heap mutex. I remember nothing about the InnoDB heap mutex. Some of my early docs were published on code.google.com (since shutdown by Google). I archived some of these before the shutdown and will try to republish them.