February 2, 2012 - Warren Held

Tuning MySQL For Better Performance

I found a great shell script (for Linux) that will analyze your MySQL and give you recommendations on what to change to improve MySQL’s performance.  It is called “MySQL Tuning Primer” and can be found here.  I included a couple snippets from the output.  I run this site (Apache & MySQL) on a “micro” Linux instance (with 613mb of RAM) in Amazon’s EC2 cloud and MySQL has been crashing.  From the output below I think it is safe to say I need to change how MySQL is using my RAM.  After tweaking the max connections and the caches in MySQL, and StartServers, MinSpareThreads, MaxSpareThreads, and ThreadsPerChild settings in Apache’s httpd.conf I was able to really cut down on my memory usage.

A couple snippets from the output of MySQL Tuning Primer:

Current max_connections = 151
Current threads_connected = 1
Historic max_used_connections = 8
The number of used connections is 5% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See “MEMORY USAGE” section to make sure you are not over-allocating

Max Memory Ever Allocated : 195 M
Configured Max Per-thread Buffers : 1.82 G
Configured Max Global Buffers : 96 M
Configured Max Memory Limit : 1.92 G
Physical Memory : 596 M

Max memory limit exceeds 90% of physical memory

Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 344 K
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 1.05 %
Current query_cache_min_res_unit = 4 K
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