Edit

MySQL configuration tuning

Recommendations

1. Only change one setting at a time if you are unsure of the performance issue root cause. For instance, if you have a slow MySQL database and you don't know the exact root cause, change one setting at a time. This way you avoid making changes that can do more harm than good. It also lets you identify exactly where the problem occurred.

2. You must make changes in the right section. Configuration files are divided into different sections. These sections determine each variable allowed. If you put a wrong configuration and tune variables in a wrong section, you won't see any changes take effect.

3. If your MySQL database does not start, review the file for any inaccuracies. For instance, if you use megabytes for a variable that requires bytes, it could cause issues when you restart the service. This also happens if you don't put the right settings in a proper section.

Properties

Property	Description
innodb_buffer_pool_size	This setting is the first performance tuning option for database administrators who use the InnoDB storage engine. As highlighted in the previous section, you can cache common queries, tables, and other file structures. We covered some SELECT query caching, but we didn't review any of the storage engine-specific caching. The innodb_buffer_pool_size setting is the first one for performance tuning with InnoDB. This is the buffer pool size for data and indexes. When you cache these properties, you improve the speed at which the MySQL database can access and display data for users. Caching stores data and indexing in memory, which is far faster than disk reads. The value you use depends on the size of your database. You can use a value as small as 5GB and as high as 128GB.
innodb_log_file_size	This configuration controls the size of the log file, which is also called the redo file. As the name suggests, the log file can be used to redo transactions when the MySQL server crashes or becomes corrupted. Log files record each writable instance to the database, which include UPDATE, DELETE, and INSERT commands. MySQL continues to improve on the log file performance. Usually, more crash recovery options cause slower performance, but in the latest MySQL version, the limitation was improved. If you have a small database with few write transactions, you can start the value at 512M. If you have a large enterprise-level MySQL application, you can set this value to 4G.
innodb_file_per_table	This setting tells InnoDB if it should store data and indexes in the shared tablespace (innodb_file_per_table = OFF) or in a separate IDB file for each table (innodb_file_per_table= ON). Having a file per table allows you to reclaim space when dropping, truncating, or rebuilding a table. It is also needed for some advanced features, such as compression. However, it does not provide any performance benefits. The main scenario when you do NOT want a file per table is when you have a very high number of tables (say 10k+). With MySQL 5.6, the default value is ON, so you have nothing to do in most cases. For previous versions, set it to ON prior to loading data as it affects newly created tables only.
max_connections	The default value for this variable is 151. For most small companies, this value is the right setting. Many application developers fail to close the MySQL connection when they no longer need it. This means that the MySQL server keeps a connection open even when the application isn't using it. If the application opens too many connections, the MySQL engine returns a "too many connections" error to the application. This means that any current active connection fails, whether it's for customers or employees. You can increase this value to avoid these errors. However, too many open concurrent connections can lead to performance issues. When too many connections are open, the MySQL database becomes unresponsive and must be restarted. You should work with this value to find the right speed and performance for your queries without crashing the server or cutting off applications that open too many connections at once. These variables are global values you can set for the MySQL server. Let's take a look at InnoDB-specific settings. If you remember, InnoDB is an ACID-compliant database storage engine that also supports rollbacks, commits, and foreign key constraints. If you rely on data integrity and normalization within your database, you probably use the InnoDB storage engine.
innodb_flush_log_at_trx_commit	Being fully ACID-compliant, it has advantages if you solely rely on data integrity and procedures. However, to have this type of security and rollback system comes at the cost of performance. Setting this value to 1 makes your MySQL database fully ACID-compliant. You can usually rollback any time and recover from any crashes. You can also use a value of 2. This value makes the MySQL server a little less reliable, but it improves performance if you notice that you don't have the best performance for your application. In most cases, this is a safe option. The final option is 0. Setting a value to 0 improves performance, but you also have a chance to lose some data if you need to roll back transactions. This option is the least recommended, but it can help performance for servers that need extensive performance tuning.
innodb_flush_method	This setting controls how data and logs are flushed to the disk. Popular values are O_DIRECT when you have a hardware RAID controller with a battery-protected write-back cache and fdatasync (default value) for most other scenarios. sysbench is a good tool to help you choose between the two values.
innodb_log_buffer_size	As the name suggest, this variable controls the buffer size for log files. For regular databases with little traffic, the default value of 1MB is sufficient. However, this value is small when you have several large field data types, such as blobs. The buffer fills up quickly, and you no longer have the performance that you need. If you have several write transactions with large data type values, increase the value of the variable to help with performance.
query_cache_size	This variable is beneficial when you have the same query that's run several times an hour. However, many database administrators suggest that this variable is a known bottleneck and should be changed. Some database administrators suggest that it should be disabled altogether. Current MySQL versions disable this option by default. This variable option is used when you don't have good indexing and optimized queries and need a way to remedy the situation.
innodb_autoinc_lock_mode	Setting innodb_autoinc_lock_mode =2 (interleaved mode) can remove the need for the table-level AUTO-INC lock and increase performance when multi-row insert statements are used to insert values into tables with the auto_increment primary key. This requires binlog_format=ROW or MIXED (and ROW is the default value in MySQL 5.7).
innodb_io_capacity and innodb_io_capacity_max	This is a more advanced tuning and only makes sense when you are performing a lot of writes all the time (it does not apply to reads, such as SELECT). If you really need to tune it, the best method is knowing how many IOPS the system can do. For example, if the server has one SSD drive, you can set innodb_io_capacity_max=6000 and innodb_io_capacity=3000 (50% of the max). It is a good idea to run the sysbench or any other benchmark tool to benchmark the disk throughput.