what the blog?

{upgrade in progress..}

5

MySQL tuning for dummies

Introduction

There is so many people asking how to improve their mysql instance, and also so many replies that increase key_buffer, sort_buffer, cache size… etc… which believe will lighten up the performance. However, fellows never notice those parameters was actually efficient to their setup.

So here you are MySqlTuner,

Download

Download the script

wget mysqltuner.pl

Analyse your database


[levin@ tmp ]$ ./mysqltuner.pl 
>>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: 

Output result



-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.84-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 124M (Tables: 1474)
[--] Data in InnoDB tables: 6M (Tables: 353)
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 115

-------- Performance Metrics -------------------------------------------------
[--] Up for: 13d 20h 21m 1s (4M q [3.607 qps], 289K conn, TX: 14B, RX: 574M)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 186.0M global + 50.2M per thread (180 max threads)
[!!] Maximum possible memory usage: 9.0G (341% of installed RAM)
[OK] Slow queries: 0% (18/4M)
[OK] Highest usage of available connections: 65% (118/180)
[OK] Key buffer size / total MyISAM indexes: 128.0M/46.6M
[OK] Key buffer hit rate: 99.8% (11M cached / 18K reads)
[OK] Query cache efficiency: 76.2% (2M cached / 2M selects)
[!!] Query cache prunes per day: 16731
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 77K sorts)
[!!] Temporary tables created on disk: 36% (45K on disk / 125K total)
[OK] Thread cache hit rate: 99% (118 created / 289K connections)
[!!] Table cache hit rate: 7% (1K open / 13K opened)
[OK] Open file limit used: 78% (1K/2K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 6.5M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-bdb to MySQL configuration to disable BDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 32M)
    tmp_table_size (> 24M)
    max_heap_table_size (> 16M)
    table_cache (> 1024)

Work it out

You’ll see “Variables to adjust” recommendation, keep it mind that it just a reference, please always check the total buffer memory not exceed your physical memory, otherwise all tuning is not making sense.

[!!] Maximum possible memory usage: 9.0G (341% of installed RAM)

The above findings from the report, the total possible memory usage up to 9GB, but the physical memory just 2.6GB only, so when this database under stress, the whole system will generate extremely high paging activities.

[--] Total buffers: 186.0M global + 50.2M per thread (180 max threads)

50.2M per thread X 180 max connection = 9GB , indeed if you’re running a web server, the max thread will not reach that high if your application is using “persistent” mysql connection. On the other hand, it’s the time to review how many concurrent connection required according to your systems.

Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 32M)
    tmp_table_size (> 24M)
    max_heap_table_size (> 16M)
    table_cache (> 1024)

Apply new tuning

First of all, we have to reduce the memory usage from 9GB to 2GB.
key_buffer 64M -> 24M
sort_buffer 16M -> 4M
join_buffer =16M -> 4M

And then increase the suggested value, and re-run the MySQLTuner after certain SQL loading, keep it mind if you re-run it rapidly without actually stress the database, then the result will not be accurate.

max_heap_table_size = 128M
table_cache = 2048
query_cache_limit = 128M
query_cache_size = 32M
tmp_table_size = 128M

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.84-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 124M (Tables: 1474)
[--] Data in InnoDB tables: 6M (Tables: 353)
[!!] Total fragmented tables: 112

-------- Performance Metrics -------------------------------------------------
[--] Up for: 35s (341 q [9.743 qps], 73 conn, TX: 67K, RX: 32K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 298.0M global + 8.6M per thread (180 max threads)
[OK] Maximum possible memory usage: 1.8G (68% of installed RAM)
[OK] Slow queries: 0% (0/341)
[OK] Highest usage of available connections: 31% (56/180)
[!!] Key buffer size / total MyISAM indexes: 24.0M/46.6M
[!!] Key buffer hit rate: 90.0% (320 cached / 32 reads)
[OK] Query cache efficiency: 67.3% (210 cached / 312 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 29 total)
[!!] Thread cache hit rate: 23% (56 created / 73 connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 4% (2K/65K)
[OK] Table locks acquired immediately: 100% (101 immediate / 101 locks)
[OK] InnoDB data size / buffer pool: 6.5M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
    key_buffer_size (> 46.6M)
 

Only the key_buffer_size is insufficient now, and I accepted due to physical memory limit and I can’t reduce the number of connection.

Thank you

mysql

levin • January 14, 2011


Previous Post

Next Post

Comments


    Warning: call_user_func() expects parameter 1 to be a valid callback, function 'griffin_comment' not found or invalid function name in /u01/mydream.com.hk/wwwroot/blog/wp-includes/class-walker-comment.php on line 174

      Warning: call_user_func() expects parameter 1 to be a valid callback, function 'griffin_comment' not found or invalid function name in /u01/mydream.com.hk/wwwroot/blog/wp-includes/class-walker-comment.php on line 174

    Warning: call_user_func() expects parameter 1 to be a valid callback, function 'griffin_comment' not found or invalid function name in /u01/mydream.com.hk/wwwroot/blog/wp-includes/class-walker-comment.php on line 174

      Warning: call_user_func() expects parameter 1 to be a valid callback, function 'griffin_comment' not found or invalid function name in /u01/mydream.com.hk/wwwroot/blog/wp-includes/class-walker-comment.php on line 174

    Warning: call_user_func() expects parameter 1 to be a valid callback, function 'griffin_comment' not found or invalid function name in /u01/mydream.com.hk/wwwroot/blog/wp-includes/class-walker-comment.php on line 174

Leave a Reply