![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
MySQLTunerでMYSQLの設定を見直す。
[root@colinux tools]#wget mysqltuner.pl HTTP request sent, awaiting response... 302 Found Location: http://mysqltuner.pl/mysqltuner.pl [following] --11:52:57-- http://mysqltuner.pl/mysqltuner.pl => `mysqltuner.pl' Reusing existing connection to mysqltuner.pl:80. HTTP request sent, awaiting response... 200 OK Length: 38,688 (38K) [text/plain] 100%[====================================>] 38,688 71.29K/s 11:52:58 (71.28 KB/s) - `mysqltuner.pl' saved [38688/38688] [root@colinux tools]# ls -l total 40 -rw-r--r-- 1 root root 38688 2008-12-01 18:37 mysqltuner.pl [root@colinux tools]# chmod 0700 mysqltuner.pl [root@colinux tools]# ls -l total 40 -rwx------ 1 root root 38688 2008-12-01 18:37 mysqltuner.pl [root@colinux tools]# ./mysqltuner.pl >> 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 Please enter your MySQL administrative login: [hup] [!!] Key buffer size / total MyISAM indexes: 16.0K/155.0K [!!] Query cache is disabled [OK] Temporary tables created on disk: 0% (0 on disk / 18 total) [!!] Thread cache is disabled [!!] Table cache hit rate: 18% (4 open / 22 opened) [OK] Open file limit used: 0% (3/1K) [OK] Table locks acquired immediately: 100% (21 immediate / 21 locks) [!!] Connections aborted: 37% [OK] InnoDB data size / buffer pool: 144.0K/8.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Set thread_cache_size to 4 as a starting value Increase table_cache gradually to avoid file descriptor limits Your applications are not closing MySQL connections properly Variables to adjust: key_buffer_size (> 155.0K) query_cache_size (>= 8M) thread_cache_size (start at 4) table_cache (> 4)
■■■ サーバー① ■■■
[root@localhost tools]# ./mysqltuner.pl <code> >> 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 Please enter your MySQL administrative login: root Please enter your MySQL administrative password: </code> [root@localhost tools]# -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.25-rc-log [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: 24M (Tables:98) [--] Data in InnoDB tables: 384K (Tables:8) [!!] Total fragmented tables: 28 -------- Performance Metrics ------------------------------------------------- [--] Up for: 2h 48m 57s (6K q [0.646 qps], 323 conn, TX: 38M, RX: 739K) [--] Reads / Writes: 93% / 7% [--] Total buffers: 42.0M global + 1.6M per thread (151 max threads) [OK] Maximum possible memory usage: 277.9M (27% of installed RAM) [!!] Slow queries: 8% (526/6K) [OK] Highest usage of available connections: 17% (27/151) [OK] Key buffer size / total MyISAM indexes: 16.0M/1.3M [OK] Key buffer hit rate: 99.6% (22K cached / 100 reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 369 sorts) [!!] Temporary tables created on disk: 43% (497 on disk / 1K total) [!!] Thread cache is disabled [OK] Table cache hit rate: 21% (64 open / 299 opened) [OK] Open file limit used: 9% (99/1K) [OK] Table locks acquired immediately: 100% (6K immediate / 6K locks) [OK] InnoDB data size / buffer pool: 384.0K/8.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate 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 Variables to adjust: query_cache_size (>= 8M) tmp_table_size (> 16M) max_heap_table_size (> 16M) thread_cache_size (start at 4)
■■■ サーバー② ■■■
[root@localhost ~]$ ./mysqltuner.pl <code> >> 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 Please enter your MySQL administrative login: root Please enter your MySQL administrative password:</code> [root@localhost ~]$ -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.0.51a [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 403M (Tables: 145) [--] Data in InnoDB tables: 47M (Tables: 14) [!!] Total fragmented tables: 13 -------- Performance Metrics ------------------------------------------------- [--] Up for: 101d 8h 30m 57s (4M q [0.466 qps], 695K conn, TX: 3B, RX: 256M) [--] Reads / Writes: 97% / 3% [--] Total buffers: 90.0M global + 18.3M per thread (100 max threads) [OK] Maximum possible memory usage: 1.9G (47% of installed RAM) [OK] Slow queries: 0% (5/4M) [OK] Highest usage of available connections: 54% (54/100) [OK] Key buffer size / total MyISAM indexes: 64.0M/276.2M [OK] Key buffer hit rate: 96.6% (261M cached / 8M reads) [!!] Query cache is disabled [!!] Sorts requiring temporary tables: 24% (45K temp sorts / 189K sorts) [OK] Temporary tables created on disk: 2% (27K on disk / 1M total) [!!] Thread cache is disabled [!!] Table cache hit rate: 0% (4 open / 675K opened) [OK] Open file limit used: 0% (8/1K) [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks) [!!] InnoDB data size / buffer pool: 47.2M/8.0M
——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
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)
sort_buffer_size (> 63K)
read_rnd_buffer_size (> 15M)
thread_cache_size (start at 4)
table_cache (> 4)
innodb_buffer_pool_size (>= 47M)