Improving The Performance Of Concurrent Inserts With MySQL

MySQL and concurrency can be an exhaustive subject which requires reading many pages of documentation, sleepless nights of MySQL configuration and a lot of coffee/tea/energy drink of your preference. The two main topics of discussion which you will find numerous articles, blogs and documentation on include: 

1.    Setting up MySQL for concurrent inserts
2.    Tuning MySQL for concurrency

Bear in mind, these two have no relation to each other in terms of set-up, but the area of implementation is the same (ex. system variables). But for someone who is looking to indulge into the more intricate workings of MySQL, it is important to understand both subjects and identify some areas of MySQL one should familiarize themselves with.  

Setting up MySQL for concurrent inserts

Inserts in MySQL are always sequential, what concurrent inserts means here is depending on the storage engine you are using, since you will be able to SELECT and INSERT at the same time. With MyISAM and InnoDB being the default storage engines, here is an explanation of how each one works: 

MyISAM

By default, this storage engine allows concurrent inserts (with selects) to reduce contention between readers and writers. If there are no gaps in the datafile (deleted rows in the table), the writer will be able to write at the end and a select statement will be able to read any row from the same table. If there are gaps (deleted rows), a select statement will happen first and concurrent inserts will be disabled. This also depends on the system variable, which in this case is the concurrent insert. 

This variable can be tweaked with the values of 0 or NEVER, 1 or AUTO and 2 or ALWAYS. By default, it is set to 1 which means it will work as described above. 0 is rather obvious (concurrent inserts are disabled) and 2 means that the concurrent inserts will work even when there are gaps in the datafile (deleted rows).

You can learn more about MyISAM concurrent inserts here. Please do take time to read and understand this as there are some caveats and explanations about how they work when using LOAD DATA to do inserts.  

InnoDB

InnoDB uses row level locking mechanisms by default, so there is no need to set up any system variable or worry about deleted rows when doing concurrent inserts. Tuning MySQL for concurrency comes into play here as you can improve on setting up your MySQL server to better handle those concurrent writes using all the CPUs available. This enables concurrent threads in the InnoDB storage engine to better handle the OS resources.

All these variables are system variables, which are described in the MySQL documentation.

innodb_file_per_table:

This creates a separate file for storing index and data per table. Allows the system to reclaim that space when the table is dropped. In versions later than 5.5x, it allows you to create tables in specific directories with CREATE TABLE … DATA DIRECTORY = “absolute_path_to_directory”.

innodb_buffer_pool_size:

The size of the memory which your system will use to cache table data and indexes. This depends on the CPU architecture and the amount of memory available. On a busy system holding other applications, assigning a large percentage of memory to this variable is not recommended.

innodb_write_io_threads:

InnoDB uses background threads to service various types of I/O requests, in this case the writes to disk threads. By default, it is set to use 4 threads internally. It can be set to use up to 64 threads. Each thread handles up to 256 pending I/O requests as its counterpart.

innodb_read_io_threads:

InnoDB uses background threads to service various types of I/O requests, in this case the read from disk threads. By default, it is set to use 4 threads internally. It can be set to use up to 64. Each thread handles up to 256 pending I/O requests.

innodb_thread_concurrency:

Consider setting this value if your system is going to share CPU load. If set to 0, it is interpreted as infinite concurrency, allowing InnoDB to create as many threads as possible. The max value you can set it to is 1000. Depending upon the number of users and connections to the server, you’ll have to start from a higher number of threads (ex. 128) and go down until you can find a thread setting which will give you the highest performance. 

innodb_log_buffer_size:

If you have transactions that update, insert, and delete rows, setting this value to a larger amount saves disk I/O. Like any other value, you’ll have to calculate these carefully. By default, the value before 5.7.6 is 8MB, but in 5.7.6 it is set to 16MB. 

Conclusion

While we have mentioned some basic settings you should be looking at for optimizing a database running InnoDB as its storage engine, please do not set these variables in your configuration unless you have thoroughly read and understood the documentation for MySQL. Increasing one of these values without correctly calculating the resources you have available may lead to a slow database.

There are a lot of other system variables which can be set and tweaked per your needs, but these are the basic ones which a user can utilize to increase performance. Before changing these values, be sure to have some benchmarking tools available and set up (such as MySQLslap). Also, try and run mysqltuner.pl, which will identify other ways to increase your performance.

Be sure to head over to the Opspack Marketplace to see how our MySQL Opspack helps you make sure your database is running at full speed.