The top 5 MySQL performance variables
There are numerous performance variables that dictate how the system will work. Each one defines some functionality for a specific workload that could help you achieve better performance, but I know your time is valuable, so I will be quick and precise. In this article I’ll go over five of the most important variables (if not, the most) that you can adjust to achieve the best performance on your system. These variables are part of the InnoDB storage system on MySQL, and are an essential tool when managing a database.
This is the first article in a series of useful tips and tricks for any MySQL database administrator. Stay around for valuable content that, sooner or later, will come in handy for you, your co-workers, or anyone with a hunger for MySQL knowledge!
This first variable, which indicates the size of the innodb_buffer_pool_size, is the most important selection for an optimal performance. Make sure to give it enough memory to avoid unnecessary pain when dealing with your database.
But, how much memory is enough for the innodb_buffer_pool_size?
Well, a buffer pool should be a little bit larger than your data size, mainly because it will not only contain said data, but also adaptive hash indexes, locks and insert buffers, which at the same time take up memory. An advice to keep in mind is to make your Buffer Pool at least 10% larger than the size of your data.
By giving the Buffer Pool 10% more memory than the size of your data you will be prepared for any eventualities, but if you have extra memory that is not being used, it could be a good practice to give it also to the InnoDB Buffer Pool. But before doing so, make sure there are no restrictions on the Buffer size you can use, and also keep in mind how much memory you would use on other needs, such as page tables, socket buffers, system processes, etc.
It is a good starting point to calculate the size of the Buffer Pool by using 50% to 70% of all RAM memory, since, as mentioned before, it is the most important variable.
Then you could have a better idea of how much memory you need by determining the size of your database. You can get that information by using the following MySQL query:
select engine, round(sum(data_length+index_length)/1024/1024/1024, 2) as total_gb, round(sum(data_length)/1024/1024/1024,2) as data_gb, round(sum(index_length)/1024/1024/1024,2) as index_gb from information_schema.tables where table_schema not in ('information_schema', 'mysql', 'performance_schema') and TABLE_TYPE <> 'VIEW' and engine="Innodb" group by 1 order by 2 desc;
The second performance variable, and almost as important as the first one, indicates the InnoDB Log File size. InnoDB is constantly logging changes into its transaction logs, this is in order to recover from a crash or a system failure. This variable designates the number of changed blocks that will be kept in memory for a given period of time.
What is the optimal size for the innodb_log_file_size?
Well, a larger-sized Log File means that more data will be hold in case of a crash, so it wouldn’t be lost, but a significant drawback from a large Log File is that, if the system fails, there will be a larger recovery time.
Background flushing is related to the innodb_log_file_size variable. By using checkpoint_age, an indicator between 0 and innodb_log_file_size, you can keep count of the current state of your system. If queries such as INSERT, UPDATE or DELETE are executed, checkpoint_age goes up, if pages are flushed (removing dirty pages from the buffer pool), the checkpoint_age goes down.
Before the checkpoint_age arrives at the innodb_log_file_size, creating a crash from which you could not recover, it arrives at two protection points, “async” and “sync”. Once checkpoint_age gets to “async”, InnoDB starts flushing as many pages as possible, and when it gets to “sync” it accelerates the flushing while blocking other queries, as an attempt to lower the checkpoint_age.
The behavior of checkpoint_age in your database should be kept in mind when deciding innodb_log_file_size.
Now, since every environment is different, it is quite a challenge to evaluate the right size for the Log File. But don’t worry, here is a useful trick that will help you figure out how much of a Log File size you really need. Run this MySQL query on the busiest workload on your server:
1. mysql> pager grep sequence 2. show engine innodb status; select sleep(60); show engine innodb status; 3. select (OUTPUT 1 - OUTPUT 2) / 1024 / 1024 as MB_per_min;
As a rough rule of thumb, you can make the innodb_log_file_size variable large enough to hold logs for an hour.
This third variable controls InnoDB flushing behavior, and it is responsible for writing the data into the ib_logfile. This is done every second, and the value given to this variable dictates how the system will carry out this task.
innodb_flush_log_at_trx_commit = 1
This is the default setting. After each commit, the data is physically written from the memory to the hard disk or the ib_logfile. After the hard disk confirms the writing process, then the commis is considered successful. This is the safest, but also the slowest setting. On one hand, in case of a system failure, you can rest assured that all commits are saved into the ib_logfile. On the other hand , MySQL has to wait for the confirmation from the hard disk for each commit.
innodb_flush_log_at_trx_commit = 0
This setting is the fastest, but not the most reliable one. Same as with the previous setting, the process is done every second, but here the MySQL process does not have to wait for the hard drive to be written when committing data. It trusts that the data will be actually written in time, making the process quicker. But with a setting of “0”, if there is any system failure, you woud lose a maximum of one second of data.
The data loss described above can be avoided by using a BBU (Battery Backup Unit). In the event of a system failure, the BBU supplies the hard disk with enough power so that the disk cache can be completely (physically) written to the hard disk.
innodb_flush_log_at_trx_commit = 2
With a setting of “2” you can be sure that the data will be committed successfully, and that a second’s worth of data lost will only be possible if the entire server crashes. The MySQL process has no influence on the writing process, instead this variant writes to the “File System Buffer”, which is controlled by the Operating System and writes to the hard disk. The difference between this setting and the “0” is that in the case of a manual commit or a commit generated in a script, the data is also written to disk. In the event of a system failure, we may therefore have less data loss than with variant “0”.
Typically, the proper setting of this variable is more dependent upon the needs of the system rather than any specific technical concern. Basically, what works for your system is the best setting for you.
4. innodb_flush_method = O_DIRECT
This variable defines the method used to flush data to InnoDB data and log files. There are different valid values for it, with distinctions between Windows and Unix’s values.
The O_DIRECT value is available on some GNU/Linux versions, FreeBSD, and Solaris. This, as other of the options, is used by InnoDB to open the data files, and uses fsync() to flush both the data and log files, but avoids Operating System’s double-buffering on the InnoDB log files. All reads and writes go directly to the hard drive. It is the recommended setting here for the XFS filesystem with log files that are smaller than 8GB.
The sync_binlog is responsible for the write access to the Binlog. It defines whether or how often it should be written to.
An executed transaction is always successful. MySQL does not check whether the data has been written from the RAM memory to the hard disk or to the Binlog. It is a fast method, but, in the event of a crash or system failure, it is possible that not all transactions were actually written to the Binlog.
Sync_binlog = 0
An executed transaction is only successful if it has been written to the hard disk or to the Binlog. This makes this method slow because every transaction has to wait for a successful write to the hard disk. However, in the event of a system failure, the data is completely available in the Binlog, making it a safer option.
Sync_binlog = 5
Every fifth transaction is written to the hard disk or to the Binlog.
MySQL has been updated and upgraded constantly, and new versions mean better handling of resources for developers, but even if you have the latest MySQL version installed with the newest features, you will still take advantage of it by configuring InnoDB variables to obtain the best system performance. So, if you are lost or do not know where to begin, follow these steps and you should be fine!
If you need any further assistance on your MySQL environment or have a performance question feel free to contact me.