MySQL Tuner

28 Jan 2009

Salah satu tools yang sangat membantu anda dalam melakukan proses tuning Database MySQL anda adalah MySQL Tuner.

Cara mendapatkannya cukup mudah, tinggal ketik wget maka otomatis file nya akan ter-download (asalkan server anda terkoneksi ke internet)

Cara menggunakannya tinggal ketik perl pada console anda (syaratnya server anda sudah ter-install perl)

[root@bedak ~]# perl

 >>  MySQLTuner 1.0.0 - Major Hayden
 >>  Bug reports, feature requests, and downloads at
 >>  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.45-log
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2M (Tables: 20)
[--] Data in InnoDB tables: 10G (Tables: 120)
[--] Data in MEMORY tables: 15M (Tables: 2)
[!!] BDB is enabled but isn’t being used
[!!] Total fragmented tables: 2

——– Performance Metrics ————————————————-
[--] Up for: 12d 12h 40m 15s (88M q [81.429 qps], 16M conn, TX: 147B, RX: 22B)
[--] Reads / Writes: 57% / 43%
[--] Total buffers: 864.0M global + 25.0M per thread (400 max threads)
[!!] Maximum possible memory usage: 10.6G (90% of installed RAM)
[OK] Slow queries: 0% (849K/88M)
[!!] Highest connection usage: 100%  (401/400)
[OK] Key buffer size / total MyISAM indexes: 32.0M/873.1M
[OK] Key buffer hit rate: 99.1% (48M cached / 450K reads)
[OK] Query cache efficiency: 33.3% (12M cached / 36M selects)
[!!] Query cache prunes per day: 64207
[OK] Sorts requiring temporary tables: 0% (24 temp sorts / 14M sorts)
[!!] Temporary tables created on disk: 49% (4M on disk / 8M total)
[OK] Thread cache hit rate: 99% (401 created / 16M connections)
[OK] Table cache hit rate: 39% (3K open / 7K opened)
[OK] Open file limit used: 1% (133/8K)
[OK] Table locks acquired immediately: 99% (99M immediate / 99M locks)
[!!] InnoDB data size / buffer pool: 10.9G/500.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
    Reduce or eliminate persistent connections to reduce connection usage
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    max_connections (> 400)
    wait_timeout (< 120)
    interactive_timeout (< 28800)
    query_cache_size (> 4M)
    innodb_buffer_pool_size (>= 10G)

Dari hasil tersebut semakin memudahkan anda dalam melakukan proses tuning server mysql anda. Selamat mencoba




Follow Me


Recent Post