Tunning MySQL dengan mysqltuner.pl
Mencari konfigurasi terbaik untuk mysql memang sangat susah. Perlu pengalaman dan jam terbang yang cukup banyak untuk tahu bagian mana saja yang harus di tunning dan mana yang harus dibiarkan.
Pagi ini saya browsing kanan kiri dan akhirnya nemu satu aplikasi yang bernama mysqltunner .
Aplikasi kecil ini mampu membaca database mysql dan menyarankan beberapa tunning yang bisa kita lakukan untuk meng optimalkan setting konfigurasi mysql.
mysqltunner.pl bisa anda dapatkan dari mysqltunner.com
wget http://mysqltuner.com/mysqltuner.pl
Kemudian anda tinggal jalankan saja dengan command
perl mysqltuner.pl
Apabila ditanyakan username dan password silahkan masukan username root dan password nya.
dan berikut ini hasilnya
>> MySQLTuner 1.0.0 - Major Hayden
>> 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.51a
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 346M (Tables: 13693)
[--] Data in InnoDB tables: 7M (Tables: 151)
[--] Data in MEMORY tables: 126K (Tables: 3)
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 1523
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 21h 1m 47s (2M q [14.857 qps], 87K conn, TX: 4B, RX: 450M)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.3G (67% of installed RAM)
[OK] Slow queries: 0% (0/2M)
[OK] Highest usage of available connections: 18% (90/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/46.3M
[OK] Key buffer hit rate: 97.8% (21M cached / 470K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 344K sorts)
[!!] Joins performed without indexes: 34995
[!!] Temporary tables created on disk: 28% (46K on disk / 162K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 303K opened)
[OK] Open file limit used: 0% (126/225K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 7.5M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)
Seperti anda lihat pada bagian recomendation, saya disarankan untuk melakukan beberapa perubahan pada my.cnf saya.
Silahkan lakukan perubahan dan ulangi lagi mysqltuner.pl nya supaya di cek kembali apakah setting yang kita lakukan sudah benar atau tidak.
Berikut ini my.cnf yang saya tambahkan
[mysqld]
set-variable = max_connections=500
safe-show-database
query_cache_size = 8M
key_buffer_size = 50M
Berikut ini hasil mysqltuner setelah edit my.cnf
>> MySQLTuner 1.0.0 - Major Hayden
>> 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.51a
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 346M (Tables: 13693)
[--] Data in InnoDB tables: 7M (Tables: 151)
[--] Data in MEMORY tables: 0B (Tables: 3)
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 1523
-------- Performance Metrics -------------------------------------------------
[--] Up for: 24s (1K q [47.333 qps], 210 conn, TX: 2M, RX: 80K)
[--] Reads / Writes: 28% / 72%
[--] Total buffers: 84.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.4G (69% of installed RAM)
[OK] Slow queries: 0% (0/1K)
[OK] Highest usage of available connections: 0% (4/500)
[OK] Key buffer size / total MyISAM indexes: 50.0M/46.2M
[!!] Key buffer hit rate: 37.5% (8 cached / 5 reads)
[!!] Query cache efficiency: 0.8% (1 cached / 119 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 73% (17 open / 23 opened)
[OK] Open file limit used: 0% (34/225K)
[OK] Table locks acquired immediately: 100% (222 immediate / 222 locks)
[OK] InnoDB data size / buffer pool: 7.5M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Variables to adjust:
query_cache_limit (> 2M, or use smaller result sets)
thread_cache_size (start at 4)
Silahkan bandingkan bagian Variables to adjust
You’re currently reading an entry written by Admin
- Published:
- 23.12.08 / 6am
- Post Navigation:
- « Network Printing di Ubuntu
Roundcube Webmail Tidak Bisa Delete Mail »
Comments are closed
Comments are currently closed on this entry.