Webサイト制作コースのお申し込みはこちら Webサイト制作コースのお申し込みはこちら

MySQLのチューニングに役立つツールについてまとめています。

MySQLのサーバパラメータチューニングに使えるツール

MySQLのサーバパラメータを調整するには、診断ツールの出力した内容をもとに、一つ一つパラメータを変更し、動作を検証していくのが良いでしょう。

同時に複数のパラメータを変更して不具合が出た場合、どのパラメータが原因となったのかを突き止めるのに多くの時間がかかります。

phpMyAdminでチューニングすべきポイントがわかる

MySQLの管理ツールとして有名なphpMyAdminは、強力な診断機能を備えています。

参考）phpMyAdmin公式サイト

phpMyAdminを開き、状態→アドバイザを指定すると、問題があると思われる事象と、推奨設定が表示されます。特にメモリに関する設定は、パフォーマンスに与える影響が大きいため、重点的にチェックすると良いでしょう。

また、状態→モニタで、実行されているクエリ数、接続・プロセス数、トラフィックがリアルタイムにグラフ表示されます。

状態→クエリの統計では、より詳細なクエリの統計情報が確認できます。

少し下にスクロールさせると、円グラフ表示が確認できます。この例では、90%をselect文が占めていることがわかります。

コマンドラインで使えるチューニングツール MySQL Tuner

MySQL Tunerは、古くから使われているコマンドラインのMySQL診断ツールです。最新のMySQL 8や、派生データベースのMariaDBにも対応しています。

利用するには、githubからダウンロードし、zipファイルを解凍します。

  1. $ wget https://github.com/major/MySQLTuner-perl/archive/master.zip
  2. --2020-01-25 22:55:08--  https://github.com/major/MySQLTuner-perl/archive/master.zip
  3. Resolving github.com (github.com)... 192.30.253.113
  4.  :
  5.  :
  6. $ unzip master.zip
  7. Archive:  master.zip
  8. 0b66c2338736779e3b150c7d125b1012d95a961f
  9.  :
  10.  :
  11. $ ls
  12. MySQLTuner-perl-master  master.zip

コマンド本体は、解凍後にできる./MySQLTuner-perl-master/mysqltuner.pl になります。以下のように実行します。

実行時に–helpを付加すると、コマンドラインオプションのヘルプが表示されます。

  1. $ ./MySQLTuner-perl-master/mysqltuner.pl --user root --pass 'my-secret-pw'
  2.  >>  MySQLTuner 1.7.19 - Major Hayden <major@mhtx.net>
  3.  >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
  4.  >>  Run with '--help' for additional options and output filtering
  5.  
  6. [--] Skipped version check for MySQLTuner script
  7. [OK] Logged in using credentials passed on the command line
  8. [OK] Currently running supported MySQL version 8.0.19
  9. [OK] Operating on 64-bit architecture
  10.  
  11. -------- Log file Recommendations ------------------------------------------------------------------
  12. [OK] Log file /var/lib/mysql/instance-2.err exists
  13. [--] Log file: /var/lib/mysql/instance-2.err(1K)
  14. [OK] Log file /var/lib/mysql/instance-2.err is readable.
  15. [OK] Log file /var/lib/mysql/instance-2.err is not empty
  16. [OK] Log file /var/lib/mysql/instance-2.err is smaller than 32 Mb
  17. [!!] /var/lib/mysql/instance-2.err contains 3 warning(s).
  18. [OK] /var/lib/mysql/instance-2.err doesn't contain any error.
  19. [--] 2 start(s) detected in /var/lib/mysql/instance-2.err
  20. [--] 1) 2020-01-17T03:58:41.454119Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock'
  21. [--] 2) 2020-01-17T03:58:41.367719Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.19'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  MySQL Community Server - GPL.
  22. [--] 1 shutdown(s) detected in /var/lib/mysql/instance-2.err
  23. [--] 1) 2020-01-17T03:58:46.647442Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.
  24.  
  25. -------- Storage Engine Statistics -----------------------------------------------------------------
  26. [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
  27. [--] Data in InnoDB tables: 293.6M (Tables: 13)
  28. [OK] Total fragmented tables: 0
  29.  
  30. -------- Analysis Performance Metrics --------------------------------------------------------------
  31. [--] innodb_stats_on_metadata: OFF
  32. [OK] No stat updates during querying INFORMATION_SCHEMA.
  33.  
  34. -------- Security Recommendations ------------------------------------------------------------------
  35. [--] Skipped due to unsupported feature for MySQL 8
  36.  
  37. -------- CVE Security Recommendations --------------------------------------------------------------
  38. [--] Skipped due to --cvefile option undefined
  39.  
  40. -------- Performance Metrics -----------------------------------------------------------------------
  41. [--] Up for: 7m 6s (105 q [0.246 qps], 44 conn, TX: 252K, RX: 10K)
  42. [--] Reads / Writes: 100% / 0%
  43. [--] Binary logging is enabled (GTID MODE: OFF)
  44. [--] Physical Memory     : 1.7G
  45. [--] Max MySQL memory    : 9.8G
  46. [--] Other process memory: 0B
  47. [--] Total buffers: 168.0M global + 65.1M per thread (151 max threads)
  48. [--] P_S Max memory usage: 72B
  49. [--] Galera GCache Max memory usage: 0B
  50. [OK] Maximum reached memory usage: 233.1M (13.78% of installed RAM)
  51. [!!] Maximum possible memory usage: 9.8G (591.28% of installed RAM)
  52. [!!] Overall possible memory usage with other process exceeded memory
  53. [OK] Slow queries: 0% (0/105)
  54. [OK] Highest usage of available connections: 0% (1/151)
  55. [OK] Aborted connections: 2.27%  (1/44)
  56. [--] Query cache have been removed in MySQL 8
  57. [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 9 sorts)
  58. [!!] Joins performed without indexes: 2
  59. [OK] Temporary tables created on disk: 0% (0 on disk / 16 total)
  60. [OK] Thread cache hit rate: 97% (1 created / 44 connections)
  61. [OK] Table cache hit rate: 79% (313 open / 393 opened)
  62. [OK] table_definition_cache(2000) is upper than number of tables(326)
  63. [OK] Open file limit used: 0% (6/1M)
  64. [OK] Table locks acquired immediately: 100% (8 immediate / 8 locks)
  65. [OK] Binlog cache memory access: 0% (0 Memory / 0 Total)
  66.  
  67. -------- Performance schema ------------------------------------------------------------------------
  68. [--] Memory used by P_S: 72B
  69. [--] Sys schema is installed.
  70.  
  71. -------- ThreadPool Metrics ------------------------------------------------------------------------
  72. [--] ThreadPool stat is disabled.
  73.  
  74. -------- MyISAM Metrics ----------------------------------------------------------------------------
  75. [--] MyISAM Metrics are disabled on last MySQL versions.
  76.  
  77. -------- InnoDB Metrics ----------------------------------------------------------------------------
  78. [--] InnoDB is enabled.
  79. [--] InnoDB Thread Concurrency: 0
  80. [OK] InnoDB File per table is activated
  81. [!!] InnoDB buffer pool / data size: 128.0M/293.6M
  82. [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25%
  83. [OK] InnoDB buffer pool instances: 1
  84. [--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
  85. [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
  86. [OK] InnoDB Read buffer efficiency: 92.22% (27008 hits/ 29288 total)
  87. [!!] InnoDB Write Log efficiency: 88.42% (733 hits/ 829 total)
  88. [OK] InnoDB log waits: 0.00% (0 waits / 96 writes)
  89.  
  90. -------- AriaDB Metrics ----------------------------------------------------------------------------
  91. [--] AriaDB is disabled.
  92.  
  93. -------- TokuDB Metrics ----------------------------------------------------------------------------
  94. [--] TokuDB is disabled.
  95.  
  96. -------- XtraDB Metrics ----------------------------------------------------------------------------
  97. [--] XtraDB is disabled.
  98.  
  99. -------- Galera Metrics ----------------------------------------------------------------------------
  100. [--] Galera is disabled.
  101.  
  102. -------- Replication Metrics -----------------------------------------------------------------------
  103. [--] Galera Synchronous replication: NO
  104. [--] No replication slave(s) for this server.
  105. [--] Binlog format: ROW
  106. [--] XA support enabled: ON
  107. [--] Semi synchronous replication Master: Not Activated
  108. [--] Semi synchronous replication Slave: Not Activated
  109. [--] This is a standalone server
  110.  
  111. -------- Recommendations ---------------------------------------------------------------------------
  112. General recommendations:
  113.     Control warning line(s) into /var/lib/mysql/instance-2.err file
  114.     MySQL was started within the last 24 hours - recommendations may be inaccurate
  115.     Reduce your overall MySQL memory footprint for system stability
  116.     Dedicate this server to your database for highest performance.
  117.     We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
  118.              See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
  119.              (specially the conclusions at the bottom of the page).
  120.     Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
  121. Variables to adjust:
  122.   *** MySQL's maximum memory usage is dangerously high ***
  123.   *** Add RAM before increasing MySQL buffer variables ***
  124.     join_buffer_size (> 256.0K, or always use indexes with JOINs)
  125.     innodb_buffer_pool_size (>= 293.6M) if possible.
  126.     innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

[!!]の項目を中心に、チューニングを行っていきます。スクリプトさえあれば、即診断できるのでお手軽なのですが、どのようにチューニングをすればよいのかが分からないというデメリットが有ります。

MySQLTunerを使ったMySQLのチューニングを業務にしている会社もあるぐらいなので、この状態だけを元にチューニングを行うのは相当なMySQLの知識が要求されるでしょう。

SQLのチューニングは、explainを使っておこなう

アプリケーション等で使われるSQLの最初に「explain」を付与して実行すると、実行計画が確認できます。

  1. mysql> explain select * from employees order by first_name;
  2. +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  3. | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
  4. +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  5. |  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299246 |   100.00 | Using filesort |
  6. +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  7. 1 row in set, 1 warning (0.00 sec)
  8.  
  9. mysql> explain select * from employees order by emp_no;
  10. +----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
  11. | id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra |
  12. +----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
  13. |  1 | SIMPLE      | employees | NULL       | index | NULL          | PRIMARY | 4       | NULL | 299246 |   100.00 | NULL  |
  14. +----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
  15. 1 row in set, 1 warning (0.00 sec)

typeの項目がALLになっているSQLは、テーブルフルスキャンが行われていることを示しています。適切なindexを付与し、実行速度を向上させましょう。

複数テーブルのjoinを行っているSQLでソートを行う場合、指定するカラムによって速度が激変する場合もあります。

【関連記事】
▶SQL Joinサンプル集　Joinで遅いSQLの原因を調べる方法

まとめ

ポテパンダの一言メモ
  • MySQLサーバチューニングには、phpMyAdminのアドバイザ情報が便利
  • MySQL Tunerは手軽に使えるが、チューニングには知識が必要
  • 個別のSQLは、explainを使ってチューニングすべき点を調べる

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか？

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。