How to speed up MySQL and take the load off the disk subsystem
Any successful project sooner or later faces a growth problem. The number of website visitors is increasing, the web server is handling more connections, and the number of queries to the database is growing. At a certain point in time, the responsiveness of the site decreases: pages load more slowly, which, according to numerous studies, affects the conversion.
The reasons for the increased page load time can be very different. In this article, we will look at one of the most typical situations, namely, queries to the MySQL database take a long time, and there is a high load on the disk subsystem.
First of all, you should find out the nature of the load on the disks. The iostat utility will help you with this. On Ubuntu, it is installed with the sysstat package:
$ sudo apt-get install sysstat
iostat is a very powerful and handy tool for viewing I/O statistics and block device utilization rates. Its use is a topic for a separate article. In our situation, with its help, it is necessary to determine the read/write ratio in order to find out the further direction of work.
How to speed up reading
Let's say the disks are loaded with read requests. What can you do to speed up data delivery? Cache data in memory. MySQL provides the ability to use different storage engines for accessing data, so the approach to caching is different. Let's take a look at the two most popular engines: MyISAM and InnoDB.
The InnoDB engine has a built-in data and index cache called Buffer Pool. Its size is controlled by the innodb_buffer_pool_size variable. Ideally, the Buffer Pool should be at least large enough to accommodate all data and indexes, plus 30% -60% of their size. The extra memory is used for service needs and Insert Buffer, as well as to provide a headroom for the future. The innodb_buffer_pool_size variable is not dynamic, so after changing it in the configuration file, you will need to restart MySQL.
The MyISAM engine does not have a data cache. But we can still speed up reads from MyISAM tables. The fact is that the Linux kernel caches all read files in an area of RAM called pagecache. Of course, files with MyISAM tables also end up in this cache. The pagecache size can be found from the output of the free command:
$ free -m
total used free shared buffers cached
Mem: 257934 255969 1964 0 4354 157772
-/+ buffers/cache: 93841 164092
Swap: 0 0 0
The maximum read performance can be achieved if the pagecache size is equal to the size of the MyISAM data.
By default, almost all the memory unused by processes is allocated for pagecache, so you can increase its size only by installing additional RAM strips. However, memory is inexpensive compared to CPUs and disks, and the effect of increasing cache can lead to significant performance gains. Below is a graph of% iowait - the percentage of time the CPU is waiting for I/O. The graph was taken from a working, loaded server. I think comments are superfluous here.
How to speed up writing
You can increase MySQL performance with a large write volume by fine-tuning the server parameters.
By default, InnoDB flushes modified data to disk using the fsync () system call. At the same time, the operating system does not guarantee that the data will get into the storages this very second, because the data first passes through a buffer maintained by the kernel. Buffering is needed to speed up I/O.
However, if the MySQL datadir is located on a hardware RAID array, then it is possible to use the NVRAM cache of the RAID controller for such buffering, which is much more efficient. You just need to make sure that the controller is equipped with a BBU (Battery Backup Unit) - a separate power source for the cache. In the event of a sudden power outage, the controller must have time to flush the contents of the cache to disks, otherwise the data in the array will remain in an inconsistent state.
When enabling the cache of the RAID controller, you can improve the performance of database writes by disabling unnecessary buffering at the operating system level. To do this, set the MySQL variable innodb_flush_method to O_DIRECT, and then restart the database management system. Changing the innodb_flush_log_at_trx_commit variable can also reduce the load on the disks. To comply with ACID requirements, the InnoDB engine stores transaction logs, or redo-logs, in which all requests to change data are recorded. These logs are used during crash recovery of the database management system.
The default (1) assumes that the redo-log buffer located in InnoDB memory is written to disk after each commit of a transaction. This is the most secure mode of operation, ensuring the safety of each transaction even in the event of a server crash. You can set innodb_flush_log_at_trx_commit to 2, then logs will also be written after each commit, but fsync () - flushing data to disk - will be performed only once a second (since MySQL 5.6.6, this interval is determined by the innodb_flush_log_at_timeout variable). Abnormal termination of the DBMS will not lead to the loss of transactions, however, shutting down the server itself can lead to the loss of the last second of transactions. A value of 0 implies an even faster write mode - data is both written and synchronized once per second, regardless of transaction commits. However, innodb_flush_log_at_trx_commit = 0 can lead to lost transactions even if the process crashes. The database administrator needs to make choices based on current workload and business requirements.
Choosing the right size of redo-logs helps to optimize disk write operations. There is a simple rule for this. It is enough to measure the amount of data that is written to the log in one minute. This operation must be performed at the time of the day's peak load:
mysql> show global status like "Innodb_os_log_written"; select sleep(60); show global status like "Innodb_os_log_written";
| Variable_name | Value |
| Innodb_os_log_written | 337936892416 |
1 row in set (0.00 sec)
| sleep(60) |
| 0 |
1 row in set (1 min 0.01 sec)
| Variable_name | Value |
| Innodb_os_log_written | 337939448320 |
1 row in set (0.00 sec)
mysql> select (337939448320 - 337936892416) / 1024 / 1024 as innodb_log_written_per_min;
| innodb_log_written_per_min |
| 2.43750000 |
1 row in set (0.00 sec)
The example shows that 2.44 MB of data is written to the InnoDB log per minute. The volume of the log should be selected in such a way that the volume of data per hour fits into it. In such a case, InnoDB will have enough time to reorder the I/O requests to achieve a sequential write. In our example, 150 MB of data passes through redo-logs in one hour, so the innodb_log_file_size variable should be set to a value of at least 75M. If the log size is set too large, then the InnoDB Crash Recovery time will increase, which will increase the downtime during an emergency restart (it should be noted that in MySQL 5.5, the Crash Recovery time depends on the size of the InnoDB log to a lesser extent).
Of course, all of these tips are not exhaustive. The key to a fast database is understanding your data, a well-designed schema, and well-crafted queries. However, a number of effective optimizations can be made at the server level.