Tune MySQL for Better Performance

By
Richard
Published: July 29, 2009Posted in: Web TalkTags:
Tune MySQL for Better Performance

This is probably the most important factor when your site uses mysql for it’s information.  The best way to start is by installing mysql tuner on your server.

http://mysqltuner.com/

You should also install mysql administrator, it will allow you to truly see what is going on.

http://dev.mysql.com/downloads/gui-tools/5.0.html

Once installed and executed you should see something similar to the results displayed below:

>>  MySQLTuner 1.0.0 – Major Hayden <major@mhtx.net>

>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

>>  Run with ‘–help’ for additional options and output filtering

——– General Statistics ————————————————–

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.81-community-log

[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 283M (Tables: 361)

[--] Data in InnoDB tables: 112K (Tables: 7)

[--] Data in MEMORY tables: 0B (Tables: 1)

[!!] Total fragmented tables: 13

——– Performance Metrics ————————————————-

[--] Up for: 1d 20h 46m 2s (30M q [189.613 qps], 943K conn, TX: 5B, RX: 2B)

[--] Reads / Writes: 58% / 42%

[--] Total buffers: 3.0G global + 2.7M per thread (1000 max threads)

[OK] Maximum possible memory usage: 5.7G (72% of installed RAM)

[OK] Slow queries: 0% (18/30M)

[OK] Highest usage of available connections: 43% (435/1000)

[OK] Key buffer size / total MyISAM indexes: 2.4G/133.5M

[OK] Key buffer hit rate: 99.9% (554M cached / 404K reads)

[OK] Query cache efficiency: 77.1% (9M cached / 12M selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 4M sorts)

[OK] Temporary tables created on disk: 14% (16K on disk / 116K total)

[OK] Thread cache hit rate: 99% (435 created / 943K connections)

[OK] Table cache hit rate: 44% (417 open / 936 opened)

[OK] Open file limit used: 1% (788/65K)

[OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)

[OK] InnoDB data size / buffer pool: 112.0K/8.0M

>>  MySQLTuner 1.0.0 – Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with ‘–help’ for additional options and output filtering
——– General Statistics ————————————————–
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.81-community-log
[OK] Operating on 64-bit architecture
——– Storage Engine Statistics ——————————————-
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 283M (Tables: 361)
[--] Data in InnoDB tables: 112K (Tables: 7)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 13
——– Performance Metrics ————————————————-
[--] Up for: 1d 20h 46m 2s (30M q [189.613 qps], 943K conn, TX: 5B, RX: 2B)
[--] Reads / Writes: 58% / 42%
[--] Total buffers: 3.0G global + 2.7M per thread (1000 max threads)
[OK] Maximum possible memory usage: 5.7G (72% of installed RAM)
[OK] Slow queries: 0% (18/30M)
[OK] Highest usage of available connections: 43% (435/1000)
[OK] Key buffer size / total MyISAM indexes: 2.4G/133.5M
[OK] Key buffer hit rate: 99.9% (554M cached / 404K reads)
[OK] Query cache efficiency: 77.1% (9M cached / 12M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 4M sorts)
[OK] Temporary tables created on disk: 14% (16K on disk / 116K total)
[OK] Thread cache hit rate: 99% (435 created / 943K connections)
[OK] Table cache hit rate: 44% (417 open / 936 opened)
[OK] Open file limit used: 1% (788/65K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
[OK] InnoDB data size / buffer pool: 112.0K/8.0M

To get the results above, use the following settings:

set-variable = max_connections=1000

safe-show-database

log-slow-queries

long_query_time=10

query_cache_size=536870912

query_cache_type=1

query_cache_limit=8388608

thread_cache_size=16384

table_cache=524288

key_buffer_size=2621440000

The server specs for this type of configuration is as follows:

  • Quad Xeon 2.0GHz Processors
  • 8GB of Ram
  • 64bit RedHat OS

The reason for the 64bit system is that mysql cannot use more than 2gb of ram on any other type of system.   I also configured the hard drives so that the OS has one hard drive, MySQL has it’s own and the Site Files have thier own.  They are all High Speed Reading Hard drives for less latency.  So far this has been able to handle a ton of traffic as you can see from the mysql tuner stats above.  It has plenty of room for growth without maxing out mysql.  This was my first experience with a 64 bit system and it took a little bit to get tweaked but I am more than satisfied with my clients results.

Hope this information was valuable to someone.  Please leave your comments below!!!!!!!

About the Author