MySQLを診断してチューニングのアドバイスをしてくれるオープンソフトのMySQLTunerを使ってみました
Perlで書かれているプログラムです
MySQLTuner
https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
- mysqltunerのインストール
- mysqltunerの実行
yumでインストールできるかなってことでいつものように調べてみます。Version 1.1.1ですがepel repoにありましたので取り敢えずインストールしてみます
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
[root@host1 ~]# yum info mysqltuner Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * addons: ftp.riken.jp * base: ftp.riken.jp * centosplus: ftp.riken.jp * epel: ftp.riken.jp * extras: ftp.riken.jp * remi: mirrors.mediatemple.net * updates: ftp.riken.jp addons | 1.9 kB 00:00 base | 1.1 kB 00:00 centosplus | 1.9 kB 00:00 dag | 1.9 kB 00:00 epel | 3.6 kB 00:00 extras | 2.1 kB 00:00 remi | 2.5 kB 00:00 updates | 1.9 kB 00:00 updates/primary_db | 529 kB 00:00 Available Packages Name : mysqltuner Arch : noarch Version : 1.1.1 Release : 1.el5 Size : 28 k Repo : epel Summary : MySQL configuration assistant URL : http://mysqltuner.com/ License : GPLv3+ Description: MySQLTuner is a script written in Perl that will assist you with : your MySQL configuration and make recommendations for increased : performance and stability. Within seconds, it will display : statistics about your MySQL installation and the areas where it can : be improved. [root@host1 ~]# yum install mysqltuner Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * addons: ftp.riken.jp * base: ftp.riken.jp * centosplus: ftp.riken.jp * epel: ftp.riken.jp * extras: ftp.riken.jp * remi: mirrors.mediatemple.net * updates: ftp.riken.jp Setting up Install Process Resolving Dependencies --> Running transaction check ---> Package mysqltuner.noarch 0:1.1.1-1.el5 set to be updated --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: mysqltuner noarch 1.1.1-1.el5 epel 28 k Transaction Summary ================================================================================ Install 1 Package(s) Upgrade 0 Package(s) Total download size: 28 k Is this ok [y/N]: y Downloading Packages: mysqltuner-1.1.1-1.el5.noarch.rpm | 28 kB 00:00 Running rpm_check_debug Running Transaction Test Finished Transaction Test Transaction Test Succeeded Running Transaction Installing : mysqltuner 1/1 Installed: mysqltuner.noarch 0:1.1.1-1.el5 Complete! |
mysqltunerを実行してみます。プログラムは/usr/bin/mysqltunerになります
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
[root@host1 ~]# mysqltuner >> MySQLTuner 1.1.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: -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.33 [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: 89M (Tables: 38) [--] Data in InnoDB tables: 1M (Tables: 73) [!!] Total fragmented tables: 86 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 4h 31m 46s (564K q [5.493 qps], 5K conn, TX: 3B, RX: 95M) [--] Reads / Writes: 93% / 7% [--] Total buffers: 168.0M global + 2.7M per thread (151 max threads) [OK] Maximum possible memory usage: 573.8M (28% of installed RAM) [OK] Slow queries: 0% (0/564K) [OK] Highest usage of available connections: 8% (13/151) [OK] Key buffer size / total MyISAM indexes: 8.0M/19.6M [OK] Key buffer hit rate: 100.0% (16M cached / 5K reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 139K sorts) [!!] Temporary tables created on disk: 37% (70K on disk / 187K total) [!!] Thread cache is disabled [OK] Table cache hit rate: 93% (88 open / 94 opened) [OK] Open file limit used: 12% (126/1K) [OK] Table locks acquired immediately: 99% (650K immediate / 651K locks) [OK] InnoDB data size / buffer pool: 1.2M/128.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries 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) |
[!!]マークの項目がチューニングすれば動作の改善が見込まれるようです
小規模な運用ならば手軽に診断できて便利ですね
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
[root@host1 ~]# mysqltuner --help MySQLTuner 1.1.1 - MySQL High Performance Tuning Script Bug reports, feature requests, and downloads at http://mysqltuner.com/ Maintained by Major Hayden (major@mhtx.net) - Licensed under GPL Important Usage Guidelines: To run the script with the default options, run the script without arguments Allow MySQL server to run for at least 24-48 hours before trusting suggestions Some routines may require root level privileges (script will provide warnings) You must provide the remote server's total memory when connecting to other servers Connection and Authentication --host <hostname> Connect to a remote host to perform tests (default: localhost) --socket <socket> Use a different socket for a local connection --port <port> Port to use for connection (default: 3306) --user <username> Username to use for authentication --pass <password> Password to use for authentication Performance and Reporting Options --skipsize Don't enumerate tables and their types/sizes (default: on) (Recommended for servers with many tables) --checkversion Check for updates to MySQLTuner (default: don't check) --forcemem <size> Amount of RAM installed in megabytes --forceswap <size> Amount of swap memory configured in megabytes Output Options: --nogood Remove OK responses --nobad Remove negative/suggestion responses --noinfo Remove informational responses --nocolor Don't print output in color |