Select Page

Optimizing mysql with

Optimizing mysql with

Digging into mySQL settings can be time consuming, like with any other component. Most of the time, and just by changing a few settings, you can expect a performance increase. The problem is to change what, and  to which value. This is where help you:

Get this script, upload it, unzip it, and install it in your /etc folder. Then run it from the command line by entering ./path-to-file/

MySQL Server must run a few days or weeks, or it wont be be safe to follow these recommendations.

To find out more information on how each of these runtime variables effects performance visit:

Here is an example of output

Current long_query_time = 5 sec.
You have 2856 out of 4725688 that take longer than 5 sec. to complete
The slow query log is enabled.
Your long_query_time seems to be fine


Current thread_cache_size = 128
Current threads_cached = 55
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine


Current max_connections = 100
Current threads_connected = 15
Historic max_used_connections = 55
The number of used connections is 55% of the configured maximum.
Your max_connections variable seems to be fine.

Max Memory Ever Allocated : 305 M
Configured Max Per-thread Buffers : 1017 M
Configured Max Global Buffers : 143 M
Configured Max Memory Limit : 1 G
Total System Memory : 2.99 G
Max memory limit seem to be within acceptable norms

Current MyISAM index space = 4 M
Current key_buffer_size = 5 M
Key cache miss rate is 1 : 3740
Key buffer fill ratio = 35.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 27 M
Current query_cach_limit = 2 M
Current Query cache fill ratio = 21.13 %
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won’t cache query results that are larger than query_cache_limit in size

Current sort_buffer_size = 4 M
Current record/read_rnd_buffer_size = 1020 K
Sort buffer seems to be fine

Current join_buffer_size = 1.00 M
You have had 7065 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.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.


About The Author

Cédric Walter

I worked with various Insurances companies across Switzerland on online applications handling billion premium volumes. I love to continuously spark my creativity in many different and challenging open-source projects fueled by my great passion for innovation and blockchain technology.In my technical role as a senior software engineer and Blockchain consultant, I help to define and implement innovative solutions in the scope of both blockchain and traditional products, solutions, and services. I can support the full spectrum of software development activities, starting from analyzing ideas and business cases and up to the production deployment of the solutions.I'm the Founder and CEO of Disruptr GmbH.