今日はなにの日。

気になったこと勉強になったことのメモ。

トップ > MySQL > 今日は、MySQL Tunerを試してみたの日。

今日は、MySQL Tunerを試してみたの日。

MySQL データベース 初級編

目次

とある日

MySQLパフォーマンスチューニングの基本ー実際のトラブルシュートから my.cnf まで

この資料の中で、MySQLの診断を行うツールとして、MySQL Tunerが紹介されていた。

簡単に、行えそうだったので趣味用の環境でやってみた。

環境

VirtualBox内の仮想環境下で、MySQLを構築しています。

ホストPC

項目名 値
OS Windows 10 Home
CPU Intel(R) Core(TM) i7-8550U CPU @ 1.80GHz
メモリ 8.0 GB

ゲストPC

Oracle VM VirtualBox

バージョン 6.0.14 r133895 (Qt5.6.2)

OS

mysql> \! cat /etc/os-release
NAME="CentOS Linux"
VERSION="8 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="8"
PLATFORM_ID="platform:el8"
PRETTY_NAME="CentOS Linux 8 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:8"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-8"
CENTOS_MANTISBT_PROJECT_VERSION="8"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="8"

メモリ

2048MB

ストレージ

20.00 GB

MySQL

mysql> status;
--------------
mysql  Ver 8.0.21 for Linux on x86_64 (Source distribution)

Connection id:          27
Current database:       hobby
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.21 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 11 hours 56 min 31 sec

Threads: 5  Questions: 1817  Slow queries: 0  Opens: 409  Flush tables: 3  Open tables: 312  Queries per second avg: 0.042

いざMySQL Tuner

MySQL Tunerとは

MySQL Tuner GitHub

MySQLTunerは、Perlで記述されたスクリプトであり、MySQLのインストールをすばやく確認し、パフォーマンスと安定性を向上させるための調整を行うことができます。現在の構成変数とステータスデータが取得され、いくつかの基本的なパフォーマンスの提案とともに簡単な形式で表示されます。

Perlで書かれているため、Perlが実行できる環境でないとだめですね。

最初CentOS7で試そうとしてて、Perlがインストールされておらず、Perlのインストールから行ったのでお気をつけて。

他にも、Perlの外部モジュールのインストールも行った。（Perlに詳しくないので詳しくはわからない）

注意事項

MySQL 8（部分的なサポート、パスワードチェックは機能しません）

試した環境は、MySQL8.0だったのでほかバージョンで行う結果と変わる可能性があります。

インストール

cd /usr/local/src/
wget -O MySQLTuner.zip https://github.com/rackerhacker/MySQLTuner-perl/archive/master.zip 
unzip MySQLTuner.zip
cd MySQLTuner-perl-master
chmod +x mysqltuner.pl

インストール場所は任意のところでよいかと。

wget,unzipがない場合はyumでインストールしてください。

権限の付与を忘れずに。

実行

[root@localhost ~]# /usr/local/src/MySQLTuner-perl-master/mysqltuner.pl -user root
 >>  MySQLTuner 1.7.20 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[!!] Attempted to use login credentials, but they were invalid

思ってた挙動と違った。

[!!] Attempted to use login credentials, but they were invalid

エラーっぽい文章を翻訳↓

ログイン認証情報を使用しようとしましたが、無効でした。

参考にした記事は、pssswordはあとから入力としてあってpasswordはコマンド時では必要ないと思ってた。

とりあえずGoogleで調べてみた。

GitHubのissuesに該当エラーの内容があった。

[!!] Attempted to use login credentials, but they were invalid

--passオプションを使用して機能していますか？

パスワードオプションをつけて実行してみる。

2ndトライ

[root@localhost ~]# prel /usr/local/src/MySQLTuner-perl-master/mysqltuner.pl --user root --pass 'password'
-sh: prel: コマンドが見つかりません

Perlのスペルを間違えた。

3ndトライ

[root@localhost ~]# perl /usr/local/src/MySQLTuner-perl-master/mysqltuner.pl --user root --pass 'password'
 >>  MySQLTuner 1.7.20 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 8.0.21
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql/mysqld.log exists
[--] Log file: /var/log/mysql/mysqld.log(3K)
[OK] Log file /var/log/mysql/mysqld.log is readable.
[OK] Log file /var/log/mysql/mysqld.log is not empty
[OK] Log file /var/log/mysql/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysql/mysqld.log contains 6 warning(s).
[OK] /var/log/mysql/mysqld.log doesn't contain any error.
[--] 4 start(s) detected in /var/log/mysql/mysqld.log
[--] 1) 2020-11-04T17:03:36.008659Z 0 [System] [MY-010931] [Server] /usr/libexec/mysqld: ready for connections. Version: '8.0.21'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution.
[--] 2) 2020-11-04T17:03:35.779174Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/lib/mysql/mysqlx.sock
[--] 3) 2020-11-04T02:50:14.281377Z 0 [System] [MY-010931] [Server] /usr/libexec/mysqld: ready for connections. Version: '8.0.21'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution.
[--] 4) 2020-11-04T02:50:14.039592Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/lib/mysql/mysqlx.sock
[--] 1 shutdown(s) detected in /var/log/mysql/mysqld.log
[--] 1) 2020-11-04T17:03:32.903284Z 0 [System] [MY-010910] [Server] /usr/libexec/mysqld: Shutdown complete (mysqld 8.0.21)  Source distribution.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 11.6M (Tables: 18)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3h 19m 38s (264 q [0.022 qps], 29 conn, TX: 98K, RX: 2M)
[--] Reads / Writes: 65% / 35%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 1.8G
[--] Max MySQL memory    : 9.8G
[--] Other process memory: 0B
[--] Total buffers: 168.0M global + 65.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 363.4M (19.87% of installed RAM)
[!!] Maximum possible memory usage: 9.8G (546.92% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/264)
[OK] Highest usage of available connections: 1% (3/151)
[!!] Aborted connections: 31.03%  (9/29)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[--] Query cache have been removed in MySQL 8
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 16 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 5 total)
[OK] Thread cache hit rate: 89% (3 created / 29 connections)
[OK] Table cache hit rate: 74% (271 open / 363 opened)
[OK] table_definition_cache(2000) is upper than number of tables(334)
[OK] Open file limit used: 0% (23/10K)
[OK] Table locks acquired immediately: 100% (300 immediate / 300 locks)
[OK] Binlog cache memory access: 92.31% (48 Memory / 52 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled on last MySQL versions.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/11.6M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.71% (355560 hits/ 356591 total)
[OK] InnoDB Write log efficiency: 98.70% (187722 hits/ 190196 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2474 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysql/mysqld.log file
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

やっと成功しました。

結果はすぐに表示されました。

その結果について詳しく見ていきたいと思います。

結果

冒頭が、[!!]で始まる行と最後の一般的な推奨事項飲みを表示します。

出力結果（英語）

[!!] /var/log/mysql/mysqld.log contains 6 warning(s).
[!!] Maximum possible memory usage: 9.8G (546.92% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[!!] Aborted connections: 31.03%  (9/29)
[!!] name resolution is active : a reverse name resolution is made for each new 
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysql/mysqld.log file
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

出力結果（日本語翻訳）

[！] /var/log/mysql/mysqld.logには6つの警告が含まれています。
最大可能なメモリ使用量。9.8G (インストールされているRAMの546.92%)
他のプロセスとの全体的なメモリ使用可能量がメモリを超えています。
"...... "接続が中断されました。31.03% (9/29)
[!!] 名前解決が有効です : 新しい名前では逆の名前解決が行われます。
[!!] InnoDB ログファイルのサイズと InnoDB バッファプールのサイズの比 (75 %): 48.0M * 2/128.0M 
-------- Recommendations ---------------------------------------------------------------------------
一般的な推奨事項。
    警告行を /var/log/mysql/mysqld.log ファイルに制御する。
    MySQLは過去24時間以内に開始されました。
    システムの安定性を高めるために、全体的なMySQLメモリ・フットプリントを削減します。
    このサーバーをデータベースに割り当てて、最高のパフォーマンスを実現します。
    閉じていない接続やネットワークの問題を削減または排除
    アカウントを IP またはサブネットのみで設定し、 skip-name-resolve=1 で設定を更新します。
    innodb_log_file_size および/または innodb_log_files_in_group を変更する前に、https://bit.ly/2TcGgtU を読んでください。
調整すべき変数。
  *** MySQLの最大メモリ使用量が危険なほど高い ***
  MySQLのバッファ変数を増やす前にRAMを追加する *** ***
    innodb_log_file_sizeは、可能であれば(=16M)にすべきであるので、InnoDBの合計ログファイルのサイズはバッファプールのサイズの25%に等しい。

詳細

/var/log/mysql/mysqld.log contains 6 warning(s).

/var/log/mysql/mysqld.logには6つの警告が含まれています。

その警告を確認してみる。

[hobby@localhost ~]$ sudo cat  /var/log/mysql/mysqld.log
2020-11-04T02:50:07.660917Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2020-11-04T02:50:14.259186Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-11-04T17:03:35.987398Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-11-04T20:13:56.350611Z 18 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2020-11-04T20:13:56.619376Z 19 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2020-11-04T20:13:56.809309Z 20 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

[Warning]の行だけ抜粋してみた。

root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

root@localhostが空のパスワードで作成されています ! 初期化のための安全なオプション --initialize-insecure をオフにすることを検討してください。

現在rootにはパスワード設定しているので問題ない。

CA certificate ca.pem is self signed.

CA証明書ca.pemは自己署名されています。

Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

プラグイン sha256_password が報告されました。''sha256_password'は非推奨であり、将来のリリースで削除される予定です。代わりにcaching_sha2_passwordを使用してください。

ユーザの認証方法が非推奨項目を設定しているようですね。

確認したが、root以外はcaching_sha2_passwordを使用している。

mysql> select user, host, plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+

認証方法が気になったので調べてみた。

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+

いずれ、変更しようと思う。

Maximum possible memory usage: 9.8G (546.92% of installed RAM)

最大可能なメモリ使用量。9.8G (インストールされているRAMの546.92%)

稼働しているMySQLの最大使用メモリを表示しています。 括弧に表示されたパーセントはサーバーのメモリとの対比です。 今回の警告は現在のMySQL設定で使用する最大メモリは10.3G必要であり、サーバーのメモリが2Gしかないことで表示されています。サーバー自体のメモリを増設する必要があります。もしくは最大接続数を減らす、個々のメモリの割り当て量を減らすなどの対応も考えられます。

メモリ足りてないようですな。

Overall possible memory usage with other process exceeded memory

他のプロセスとの全体的なメモリ使用可能量がメモリを超えています。

解説

「システムの安定化のためには、MySQLが必要とする全メモリを少なくした方がいい」と表示されています。 今回検証したサーバーではメモリ不足のはずなのですが。。。これはMySQLでメモリを大量に使用しているため、OSやその他アプリケーションに割り当てるメモリが少なくてシステムが安定しないことを示しています。データ量が多いのであれば、サーバーのスペック自体の見直しをしましょう！ということですね。

また、メモリ足りてないようですね。

Aborted connections: 31.03% (9/29)

"...... "接続が中断されました。31.03% (9/29)

通信エラーおよび中止された接続

クライアントプログラムが終了前に mysql_close() を呼び出さなかったら起こることらしい。

心当たりがすごくある。

name resolution is active : a reverse name resolution is made for each new

[!!] 名前解決が有効です : 新しい名前では逆の名前解決が行われます。

MySQLと名前解決，skip-name-resolve

Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M

[!!] InnoDB ログファイルのサイズと InnoDB バッファプールのサイズの比 (75 %): 48.0M * 2/128.0M

InnoDB バッファープール

InnoDB は、データとインデックスをメモリーにキャッシュするためのバッファープールと呼ばれるストレージ領域を維持しています。InnoDB バッファープールの仕組みを知り、頻繁にアクセスされるデータをメモリーに維持するためにそれを利用することは、MySQL チューニングの重要な側面です。

これも、メモリ使いすぎってことなのかな。

〆

思いの外すぐに終わった。

インストールから実行まで簡単にできた。

結果としてメモリが少ない。

VritualBox内の仮想環境なのでメモリは低く設定されているので仕方ない気もしますね。

MySQL Tunerのツール使うだけでアドバイスをしてくれるのは助かる。

参考

https://qiita.com/nooboolean/items/7efc5c35b2e95637d8c1

https://note.dimage.co.jp/blog_009_mysqltuner.html

https://github.com/major/MySQLTuner-perl/issues/248

https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0028