When you develop a large-scale service, in many cases, the actual bottleneck is strain on the database. These instructions explain how to tune up your MySQL database for better performance, assuming you are using MySQL version 5.1.58.
0. Workflow
Below is the following workflow:
- Specify the bottleneck by logging slow queries
- Tune up
- Benchmark
1. How to enable slow query logging
To enable the mysql slow query log, add the following description to your “my.cnf” file.
long_query_time=3 log-slow-queries=varlogslow.log
The above settings log all queries that take three seconds or more (the time is specified by the “long_query_time” option) into a file named “varlogslow.log”. Based on the log data, you can tune up your MySQL settings.
2. Tune-up
The tune-up can be categorized into the following three methods.
- Query optimization
- Optimize system variables
- Add peripheral devices
2-1. Query optimization
Let’s review your queries first. The basic principle to optimize queries is “Never get more than you need”. Although the definition of optimized queries will vary depending on different systems, you will need to keep in mind for better performance:
- Limit queries properly
- Not to use “select”
2-2. Optimize system variables
You can get information on the values of MySQL system variables by
entering the following command in the MySQL console:
mysql > SHOW VARIABLES;
You can also change the value of a particular variable by entering the following command:
mysql > SET GLOBAL (variable) = (value);
Here is a list of variables and their recommended values that you might want to review when tuning up your system.
■key_buffer_size
The buffer memory size to store indexes. Approximately 256MB will be appropriate since MyISAM uses the OS cache as well.
Example) mysqlset global key_buffer_size = 268435456
■innodb_buffer_pool_size
The buffer pool size used for InnnoDB.
InnnoDB does not use the OS cache, so you may set this to 70-80% of the entire memory space.
■table_open_cache
The size of the table cache. Adjustment of this parameter is effective in reducing I/O transaction time. I would recommend 1024 for a table of hundreds of lines.
■thread_cache_size
The cache size of server threads. You might as well set it to at least 16 because creating or deleting a thread places a lot of burden on the system.
■innodb_log_file_size
The size of each log file. Increasing the value improves performance, however it also slows down recovery. I suggest the value should be set somewhere between 64 and 512MB.
■innodb_log_buffer_size
The buffer size of each log. The default will be sufficient and should not be increased too much.
■innodb_flush_log_at_trx_commit
The system gets extremely slow unless this setting is adjusted. This option determines where log files will be flushed – 1: to disk, 2: to memory, 0: not to be flushed anywhere. Set it to 2 to improve performance.
■max_allowed_packet
The largest possible buffer size to retain input data. Change this setting if the server needs to handle big data. 1MB will be sufficient.
■sort_buffer_size
This increases the query speed of “ORDER BY” and “GROUP BY”. You will not need to change the setting unless the system has large memory.
■long_query_time
Queries that take more than the time set by this parameter are considered as slow query. It is common to set the value to 1.
■join_buffer_size
The buffer for joins. 1MB will be sufficient.
■query_cache
The cache size for queries. A too large query cache will waste resources, slowing down updates. Set the parameter to a value between 32M and 512M depending on your database size.
2-3. Add peripheral devices
If the above-mentioned two methods did not have a significant effect, you can try an addition of peripheral devices (e.g., memory, hard disk).
3. Benchmark
Obtain server status information using “SHOW GLOBAL STATUS” or “EXPLAIN”. The best values of each variable largely depends on each system. Fine-tune for your own system.
4. Reference documents
This article was written with reference to the following book.
“High Performance MySQL” By Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz, Derek J. Balling, Publisher: O’Reilly Media
