Dealing with MySQL heavy loads
In these days there is a very common question about how to deal with heavy loaded mysql servers, how to optimize them for optimal workload. I'm not going to teach you this way. I just wanna to introduce two main tools that can help with such problems and will give a simple solution for your server. It is a mysqltuner.pl and tuning-primer.sh, the 2nd is better solution for some serious server environments. After you run 1st script, at the end you will get the recommendations, for example like this:
Now you can play with your my.cnf mysql config and test the settings you have changed!
Yeahhh, this way you can't expect to configure mysql correctly for stability/performance but its possible.
Another tool, that i recommend is tuning-primer.sh, after you run it you will see something like this:
MAX CONNECTIONS
Current max_connections = 105
Current threads_connected = 2
Historic max_used_connections = 106
The number of used connections is 100% of the configured maximum.
You should raise max_connections
MEMORY USAGE
Max Memory Ever Allocated : 3.10 G
Configured Max Per-thread Buffers : 3.02 G
Configured Max Global Buffers : 48 M
Configured Max Memory Limit : 3.07 G
Physical Memory : 3.92 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 71 M
Current key_buffer_size = 14 M
Key cache miss rate is 1 : 214
Key buffer free ratio = 44 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere
QUERY CACHE
Query cache is enabled
Current query_cache_size = 24 M
Current query_cache_used = 14 M
Current query_cache_limit = 18 M
Current Query cache Memory fill ratio = 59.59 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 15 M
Current read_rnd_buffer_size = 320 K
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 2.00 M
You have had 94208 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
It will show what configuration is and what it prefer to tune for better performance, so you can play with this too.
Remember! do not waste your memory (ram) for unnecessary buffers, and do not overhead the total memory size. If it will be as shown here, then good. Do not set to many connections too.
This script is tested on heavy loaded web hosting server, that runs several high tech web based projects. I can reveal it's configuration too:
The loads on this server are decreased by ~40% and now it runs a lot of better without any instability issues.
SQL queries decreased too ? hmm yeah. Why ? - to cut load!
And of course the server cpu and memory load is decreased too!
NOTE! Please do not play with this in production environments! ;-)
-------- Recommendations -----
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
Variables to adjust:
max_connections (> 105)
wait_timeout (< 1800)
interactive_timeout (< 28800) query_cache_size (> 24M)
join_buffer_size (> 2.0M, or always use indexes with joins)Now you can play with your my.cnf mysql config and test the settings you have changed!
Yeahhh, this way you can't expect to configure mysql correctly for stability/performance but its possible.
Another tool, that i recommend is tuning-primer.sh, after you run it you will see something like this:
WORKER THREADS
Current thread_cache_size = 64
Current threads_cached = 63
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fineMAX CONNECTIONS
Current max_connections = 105
Current threads_connected = 2
Historic max_used_connections = 106
The number of used connections is 100% of the configured maximum.
You should raise max_connections
MEMORY USAGE
Max Memory Ever Allocated : 3.10 G
Configured Max Per-thread Buffers : 3.02 G
Configured Max Global Buffers : 48 M
Configured Max Memory Limit : 3.07 G
Physical Memory : 3.92 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 71 M
Current key_buffer_size = 14 M
Key cache miss rate is 1 : 214
Key buffer free ratio = 44 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere
QUERY CACHE
Query cache is enabled
Current query_cache_size = 24 M
Current query_cache_used = 14 M
Current query_cache_limit = 18 M
Current Query cache Memory fill ratio = 59.59 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 15 M
Current read_rnd_buffer_size = 320 K
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 2.00 M
You have had 94208 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
It will show what configuration is and what it prefer to tune for better performance, so you can play with this too.
Remember! do not waste your memory (ram) for unnecessary buffers, and do not overhead the total memory size. If it will be as shown here, then good. Do not set to many connections too.
This script is tested on heavy loaded web hosting server, that runs several high tech web based projects. I can reveal it's configuration too:
[mysqld]
local-infile=0
back_log = 75
max_connections = 105
key_buffer = 14M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 12M
sort_buffer_size = 15M
table_cache = 5000
thread_cache_size = 64
wait_timeout = 1800
read_rnd_buffer_size = 320K
connect_timeout = 30
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 32M
thread_concurrency = 8
max_connect_errors = 10
query_cache_limit = 18M
query_cache_size = 24M
long_query_time=2
query_cache_type = 1
default-storage-engine = MyISAM
open_files_limit = 8022
log-queries-not-using-indexes
concurrent_insert=2
low_priority_updates=1The loads on this server are decreased by ~40% and now it runs a lot of better without any instability issues.
SQL queries decreased too ? hmm yeah. Why ? - to cut load!
And of course the server cpu and memory load is decreased too!
NOTE! Please do not play with this in production environments! ;-)
No comments: