02 May 2022

38298 views

15 0

Optimizing my.cnf for MySQL performance

Database systems often need tuning for best performance. Lukas Vileikis explains to how to optimize my.cnf for MySQL performance.

Since the dawn of software engineering, developers turned to databases to store their data – as time went on, some developers were able to choose their path more clearly: some started using PostgreSQL, some chose MySQL or its flavors (MariaDB or Percona Server), some went for NoSQL-based solutions such as MongoDB.

Those developers that elected to use MySQL, however, quickly noticed that the database management system is not that powerful by default and started searching for ways to improve its performance, security, and high availability beyond its default capabilities; as they found themselves working towards this goal, they found that the answers to the majority of their questions lie in one file – that file was called my.cnf.

What is my.cnf and what does it do?

In order to answer the question how should you optimize your my.cnf file for performance, I’ll start with the basics. The my.cnf file is the main configuration file available within MySQL and all of its flavors. The file contains various parameters that can be configured according to requirements. These parameters will then be loaded onto MySQL as the database management system starts up. There are multiple locations where the my.cnf file could be located (the locations differ depending on what flavor of Linux is used). MySQL usually loads the settings from the file after finding it in one of the following locations:

/etc/my.cnf
/etc/mysql/my.cnf
/var/lib/mysql/my.cnf
~/my.cnf

The path to the directory containing my.cnf can also be defined by changing the value of the MYSQL_HOME variable. If running MySQL on Windows, the directory where the my.ini (note the extension change – my.cnf is called my.ini on Windows) file will be located will correspond to the directory where the MySQL data directory is located. On Linux, it will probably be /var/lib/mysql/my.cnf or in one of the locations specified above. On Windows, the locations will depend on what version of MySQL you’re running – for MySQL 5.7 on Windows 10, for example, the location could be /bin/mysql/mysql*.*.** where *.*.** is the version of the MySQL server or even in the ProgramData directory which is hidden by default. In that case, search for the MySQL folder inside of it.

Once opening the my.cnf file, you should see something similar to the following:

Image showing the my.cnf file partial contents.

Image 1 – some of the settings available within my.cnf

As you can see, the file houses a whole lot of parameters – some of those are more crucial for performance, others less. The Windows version of my.cnf (my.ini) is also widely known for the fact that the file includes a bunch of comments (see image above). The Windows version explains pretty much everything, while Linux isn‘t so generous; in many cases, Linux only provides the ‘skeleton’ settings that are most frequently changed and that are related to InnoDB (one of MySQL‘s primary storage engines). One statement is true regardless: to optimize the performance of MySQL, optimize the settings within my.cnf.

Some of you might remember the earlier article about the nuances of indexes in MySQL. That article mentioned that one of the primary storage engines available within MySQL, InnoDB. There are a couple of parameters in the my.cnf file that are crucial for InnoDB’s performance and that need to be optimized to get the best out of MySQL, and the statement rings true for all storage engines available within the database management system. Engineers working with MySQL usually choose the storage engine they want to use according to their needs, modify cnf.ini file to get the best out of the engine, and restart MySQL. Once MySQL is restarted, engineers can enjoy better performance, availability, and, in some cases, security benefits.

How to optimize my.cnf for performance?

When looking at everything from a broader perspective, optimizing my.cnf for performance isn’t that hard. Everything comes down to two things:

Decide what kind of storage engine to use within MySQL.
Optimize the parameters relevant to that storage engine and restart MySQL.

That’s it, really – nothing that complex!

Begin by choosing a storage engine with this table to simplify your choices:

Storage Engine	When to Use?
InnoDB / Percona XtraDB	InnoDB should be chosen when the database supports a general use case software product or if you’re not sure what to use to get the best out of MySQL.
MyISAM	Obsolete storage engine, should only be used if you need the exact row count in a specific table. If used as the main storage engine, MyISAM can become the cause of crashes and lost data.
MEMORY	Such a storage engine stores all data in memory.
CSV	Such a storage engine stores data as CSV files.
MERGE (formerly MRG_MyISAM)	A collection of two or more identical MyISAM-based tables that are supposed to be used as one table.
ARCHIVE	The storage engine is supposed to let us archive data.
FEDERATED	The storage engine lets us access data from a database without using replication or clustering.
BLACKHOLE	The storage engine should be used for testing purposes – all data contained inside of it will be gone in an instant.
EXAMPLE	Should be used as an example of how to build new storage engines.

Despite the plethora of storage engines available for use, most developers and MySQL engineers use InnoDB as it’s widely considered the best for most use cases. Earlier, MyISAM was also a pretty reliable option, but as the storage engine became obsolete due to stability issues and other things, it’s now effectively only suitable for one purpose – knowing the exact count of rows in a table (InnoDB doesn’t provide that information, but MyISAM stores the row count inside of itself.)

Optimizing InnoDB for performance

The InnoDB storage engine has these options, among others, available within my.cnf:

Image showing the innodb section of the my.cnf file. Important settings are innodb_data_file_path, innodb_buffer_pool_size, innodb_log_file_size, innodb_log_buffer_size, innodb_flush_log_at_trx_commit, innodb_lock_wait_timeout, innodb_flush_method

Image 2 – InnoDB parameters within my.cnf

Here’s what the parameters do, one by one:

Parameter	Function
`innodb_data_file_path`	This parameter depicts the location of the data file (ibdata1) relevant to InnoDB – ibdata1 holds all of the data required for InnoDB to function correctly.
`innodb_buffer_pool_size`	This parameter outlines the buffer pool size to be used within InnoDB. The buffer pool is used to cache data and indexes of the tables within InnoDB.
`innodb_log_file_size`	Sets the size of InnoDB log files. The bigger this value is, the less recovery time is needed in case of a crash.
`innodb_log_buffer_size`	Used to write to the log files.
`innodb_flush_log_at_trx_commit`	Ways to flush the log when a transaction commits. MySQL offers three values – the default (1) makes InnoDB ACID compliant, while 0 or 2 exchanges ACID for faster write speed.
`innodb_lock_wait_timeout`	The length of time in seconds a transaction waits for a row lock.
`innodb_flush_method`	Defines the method used to flush data. The flush method is very important because it can affect I/O throughput. I won’t get into all of your choices here, but keep in mind that MySQL offers a couple of flush methods for you to choose from including, but not limited to: `normal` which is the default flushing method on Windows. The other option for Windows is `unbuffered`. `fsync` which is the default flushing method on Linux – MySQL will use the fsync() function to flush data. `O_DIRECT` which instructs the operating system to bypass the page cache and perform I/O operations against the disk in Linux. `O_DSYNC` exchanges speed for consistency – this option is generally faster than `O_DIRECT`, but data may not be consistent after a crash in Linux.

And here’s how to optimize each of them to get the max out of MySQL when using InnoDB:

Set the innodb_data_file_path to extend automatically by specifying autoextend. Some developers might also benefit from the fact that the data file path can also have a maximum size by specifying max after “autoextend” so the parameter looks like this (the maximum size of InnoDB, in this case, is 10GB.):

1

innodb_data_file_path = ibdata1:12M:autoextend:max:10G

If the maximum size is reached, MySQL will return the error:

ERROR 1114 (HY000): The table is full
Set the InnoDB buffer pool size to 50 – 75% of available operating memory on your system. The bigger this parameter is, the faster operations regarding InnoDB (inserts, etc.) will complete.
Set the InnoDB log file size to around a quarter (25%) of the InnoDB buffer pool size – the larger the log file size is, the less recovery time you need when restoring MySQL from a crash.
It’s advisable to leave the InnoDB log buffer size at the default value of 8MB (8M.)
Leave the innodb_flush_log_at_trx_commit at 1 if you want ACID compliance (ACID will ensure that the data remains intact despite any kind of power outages or other failures that could impact data integrity), but switch it to 0 or 2 if you want to exchange ACID for more writing speed. The former will be a fit for most data-related operations, though some infrastructures, especially those dealing with big data sets, might benefit from the exchange.
Changing the innodb_lock_wait_timeout value will change how InnoDB responds to a row lock after a specified amount of seconds – the default value for this parameter is 50, meaning that InnoDB will issue an error if a lock is not released after 50 seconds, though this parameter can be modified according to your needs.
Most engineers would advise turning the innodb_flush_method to O_DIRECT for MySQL to be able to perform all I/O operations against the disk and bypass the page cache – that way I/O operations should be a lot faster than normal.

These are most of the parameters you need to optimize when using InnoDB. Now, restart MySQL, and the settings will take effect. However, if you elect to use MyISAM instead (perhaps you’re trying out some of its features or using it on an older, not so important server), there are other parameters you should review.

Optimizing MyISAM for performance

If you’re using Windows, scroll down from InnoDB parameters, and you should see four parameters relevant to MyISAM. If you’re a Linux user and you want to use MyISAM for any reason, though, bear in mind that you would probably need to add these parameters in manually:

Parameter	What Does It Do?
`key_buffer_size`	The equivalent of the InnoDB buffer pool size – it’s recommended to set this value to approximately 40% of the available memory on the server.
`myisam_sort_buffer_size`	Defines the size of the buffer that is allocated when sorting operations are performed. Raising this parameter from its default value might help with improving the performance of ORDER BY and GROUP BY operations – other than that, this parameter is important when creating indexes or repairing tables.
`read_buffer_size`	Defines the size of the buffer that is allocated for each thread that does a full table scan (when a database is finding a value by reading the table in sequential order.)
`read_rnd_buffer_size`	This parameter is used for some sorting operations in order to read rows after sorting is accomplished. The parameter can be used by all tables, not only MyISAM – head over to the documentation for more information.

Other parameters within my.cnf

Once you have optimized either (or both) InnoDB and MyISAM for performance, you can also look a little into other parameters available to be configured within my.cnf.

Parameter	Importance
`skip-federated`	Leaving this parameter inside of my.cnf without any value (or including this parameter inside of the file) disables the FEDERATED storage engine within MySQL.
`secure_file_priv`	This parameter defines the directory from where files can be loaded into MySQL when using the LOAD DATA INFILE command – if the wrong directory is specified, MySQL will respond with an error.
`sql_mode`	This parameter can be used to set the SQL mode to use when MySQL is running. For example, the “ANSI” parameter changes MySQL’s behavior to behave like standard SQL, including “ALLOW_INVALID_DATES” will disable the checking of dates while specifying no value will set the SQL mode to strict. Refer to the documentation for further explanation.
`skip-ssl` or `ssl`	The “skip-ssl” option enables MySQL to use an unencrypted connection. The “ssl” option, on the other hand, will specify the fact that MySQL will attempt to connect via SSL and fail if a secure connection cannot be established.
`datadir`	Defines the location of the data directory of MySQL. Can be redefined – redefining this parameter can be useful for security reasons, though not much apart from that.
`log-error`	Defines the location of the error log file within MySQL. The location of the error log file can be changed using this parameter, though doing so could be considered “security through obscurity.”
`default-storage-engine`	Defines the default storage engine used by MySQL.
`lc-messages-dir and lc-messages`	Some of the more interesting parameters dealing with error messages. The former defines the location of the directory containing language files to be used within MySQL when errors are encountered, while the latter defines the language of the error messages. Redefining these parameters might be very useful for MySQL installation is used by those who cannot speak English or for other reasons.

The given table should give you a rough idea of just how powerful my.cnf really is. Not only can it be used to improve performance for various storage engines, including InnoDB and MyISAM, but the parameters contained within the file are also very useful to complete various kinds of operations. One thing shouldn’t be forgotten, though – whenever you change the values of any parameter contained inside of my.cnf, you should restart the server to be sure that your changes have taken effect.