A few of the basic settings to get a better performance out of your MySQL server.
We are assuming, of course, that the server role is limited to just running MySQL and that is has sufficient memory and disk to meet the application needs. The example in this document uses 16G of RAM.
MySQL as some logging features to identify slow queries. This configuration logs queries that take longer that 2 seconds to complete.
log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2 log-queries-not-using-indexes
Then use the tools
to evaluate the logs.
Using the above configuration the logs will show both slow and non-indexed queries which may be noise.
mysqldumpslow /var/log/mysql/mysql-slow.log Count: 1 Time=0.42s (0s) Lock=0.00s (0s) Rows=0.0 (0), debian-sys-maint[debian-sys-maint]@localhost select count(*) into @discard from `information_schema`.`PARTITIONS` Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), debian-sys-maint[debian-sys-maint]@localhost select count(*) into @discard from `information_schema`.`TRIGGERS`
Additionally, external tools should be used to measure system resource consumption (RAM, CPU, HDD, NET, etc).
Increase Memory Allocations
Generally setting the key_buffer to 40% system ram, it's shared across whole server. Increase query caches, to keep them around a bit longer. Per connection buffers set to comfortable values, too large can drag a machine down when connection count is high.
The use of the
SHOW STATUS and/or
SHOW FULL PROCESSLIST.
We like to give lots of space to MySQL.
key_buffer_size = 6553M innodb_buffer_pool_size = 6553M
See the key_buffer_size and innodb_buffer_pool_size docs for more information.
query_cache_limit = 2M query_cache_size = 64M query_cache_type = 1 sort_buffer_size=2M read_buffer_size=2M read_rnd_buffer_size=1M thread_concurrency = #cores *2
According to the MySQL team:
table_cache is related to max_connections. For example, for 200 concurrent running connections, you should have a table cache size of at least 200 * N, where N is the maximum number of tables per join in any of the queries which you execute. You must also reserve some extra file descriptors for temporary tables and files.
For our an example system that has about 164 concurrent connections and the maximum join count of eight our setting becomes
# 164 * 8 + 20% = 1574 table_cache = 1600
To find the value for thread_concurrency use the following shell snippet.
~ # echo $(( $(grep processor /proc/cpuinfo |wc -l) * 2 )) 16