Select Page

Optimizing mysql with tuning-primer.sh

Optimizing mysql with tuning-primer.sh

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 tuning-primer.sh help you:

Get this script, http://forge.mysql.com/projects/view.php?id=44 upload it, unzip it, and install it in your /etc folder. Then run it from the command line by entering ./path-to-file/tuning-primer.sh

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:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Here is an example of  tuning-primer.sh output

SLOW QUERIES
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

WORKER THREADS

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

MAX CONNECTIONS

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.

MEMORY USAGE
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

KEY BUFFER
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
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

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

JOINS
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

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.

Categories