MySQLTuner メモ

公開日：2020年8月15日

カテゴリー: 手順書,

MariaDB 10.3 にて
今日時点で最新と思われる MySQLTuner 1.7.19 を利用。

サーバ構成はCentOS 8.2 + Nginx 1.18 + MariaDB 10.3 + PHP 7.4、スペックは vultr での 6 CPU、Memory 16 GB、320 GB SSD です。

メモリの使用量メモ

とりあえず。

free -h

              total        used        free      shared  buff/cache   available
Mem:           15Gi       1.9Gi       2.9Gi       834Mi        10Gi        12Gi
Swap:            0B          0B          0B

MySQLTuner インストール

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
./mysqltuner.pl

/usr/local/src/MySQLTuner-perl-master/mysqltuner.pl

MariaDBへのログイン情報を入力するとレコメンドが出る・・・はずが、[!!] FAIL Execute SQL / return code: 256 の嵐。WordPress用に作成した grant all privileges なユーザーで行ったのがいけなかったようです。root で ./mysqltuner.pl を実行するとエラー無く表示できました。

/usr/local/src/MySQLTuner-perl-master/mysqltuner.pl --user root --pass=YOUR_PASSWORD

MySQLTuner を久しぶりに使うのだけれども、以前より表示項目が多くなっていました。

初回実行

 >>  MySQLTuner 1.7.19 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 10.3.17-MariaDB
[OK] Operating on 64-bit architecture
 
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mariadb/mariadb.log exists
[--] Log file: /var/log/mariadb/mariadb.log(855B)
[OK] Log file /var/log/mariadb/mariadb.log is readable.
[OK] Log file /var/log/mariadb/mariadb.log is not empty
[OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb
[!!] /var/log/mariadb/mariadb.log contains 7 warning(s).
[OK] /var/log/mariadb/mariadb.log doesn't contain any error.
[--] 0 start(s) detected in /var/log/mariadb/mariadb.log
[--] 0 shutdown(s) detected in /var/log/mariadb/mariadb.log
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in InnoDB tables: 2.2G (Tables: 28)
[OK] Total fragmented tables: 0
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 620 basic passwords in the list.
 
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 27d 10h 4m 8s (1B q [459.990 qps], 7M conn, TX: 1187G, RX: 92G)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory     : 15.5G
[--] Max MySQL memory    : 3.2G
[--] Other process memory: 0B
[--] Total buffers: 417.0M global + 18.9M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.3G (8.70% of installed RAM)
[OK] Maximum possible memory usage: 3.2G (20.62% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (50/1B)
[OK] Highest usage of available connections: 33% (51/151)
[OK] Aborted connections: 0.02%  (1523/7204894)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 1B selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 45M sorts)
[!!] Joins performed without indexes: 181153
[!!] Temporary tables created on disk: 80% (17M on disk / 21M total)
[OK] Thread cache hit rate: 99% (895 created / 7M connections)
[!!] Table cache hit rate: 4% (140 open / 2K opened)
[OK] table_definition_cache(400) is upper than number of tables(187)
[OK] Open file limit used: 1% (59/4K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 6 thread(s).
[--] Using default value is good enough for your version (10.3.17-MariaDB)
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/123.0K
[!!] Read Key buffer hit rate: 92.4% (210 cached / 16 reads)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/2.2G
[!!] 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.54% (69120567402 hits/ 69439732208 total)
[!!] InnoDB Write Log efficiency: 34.85% (6941860 hits/ 19918070 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12976210 writes)
 
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 96.5% (507M cached / 17M reads)
 
-------- 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: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mariadb/mariadb.log file
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (4188) variable 
    should be greater than table_open_cache (2000)
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 2000)
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 2.2G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

[!!] が修正推奨項目。「Performance Metrics」にある「Maximum possible memory usage」が最大メモリ使用量で、ここを目安に調整してみます。初期設定だと総メモリの２割だけ使いますよという設定のようです、メモリをしっかり割り当てておらずもったいない状態です。

[OK] Maximum possible memory usage: 3.2G (20.62% of installed RAM)

先ず最後の重要な推奨項目（Recommendations）を設定後に、残りの問題をひとつひとつチェックします。

Recommendations

「Variables to adjust」を見ていきます。いっぺんに設定を変えるとよくわからなくなるので、ひとつ反映しては mysqltuner.pl を再実行、再確認していく作業になります。たまに新しく調整値がでます。

innodb_buffer_pool_size (>= 2.2G) if possible.

重要重要って言われる設定です。序盤に [--] Data in InnoDB tables: 2.2G (Tables: 28) とあるように、データベースのサイズをまるっと割り当てることを推奨しています。

mysqlにログインして確認する場合は以下

SELECT table_schema, 
       floor(SUM(data_length + index_length) / 1024 / 1024) AS ALL_MB,
       floor(SUM((data_length) / 1024 / 1024)) AS DATA_MB,
       floor(SUM((index_length) / 1024 / 1024)) AS INDEX_MB
FROM information_schema.tables
GROUP BY table_schema
ORDER BY sum(data_length + index_length) DESC;

+--------------------+--------+---------+----------+
| table_schema       | ALL_MB | DATA_MB | INDEX_MB |
+--------------------+--------+---------+----------+
| wordpress          |   2177 |    1623 |      554 |
| mysql              |      0 |       0 |        0 |
| information_schema |      0 |       0 |        0 |
| performance_schema |      0 |       0 |        0 |
+--------------------+--------+---------+----------+

なるほど 2.17G です。サーバへ設定。

vi /etc/my.cnf.d/mariadb-server.cnf

[mysqld]
innodb_buffer_pool_size = 3G

innodb_log_file_size should be (=384M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

innodb_buffer_pool_size を変更しても、innodb_log_file_size を合わせて変更しないと効果が薄いそうです。

[mysqld]
innodb_log_file_size = 384M

cd /var/lib/mysql
mv ib_logfile0 _ib_logfile0
mv ib_logfile1 _ib_logfile1
systemctl restart mariadb

join_buffer_size (> 256.0K, or always use indexes with JOINs)

ここは、その他のバッファも合わせて複数設定してみました。

値を推奨値にしてもメッセージは「どんどん上げてね」と促してきます。かなりメモリを使う部分なので、ここでも「Performance Metrics」にある「Maximum possible memory usage」で最大メモリ使用量の予想を確認しながら調整します

join_buffer_size = 8M
sort_buffer_size = 8M
read_rnd_buffer_size = 8M
max_allowed_packet = 16M

上の設定値ですと次のような感じになりました。

[OK] Maximum possible memory usage: 9.5G (61.12% of installed RAM)

その他の設定

その他でできそうなところのメモです

Log file Recommendations

[!!] /var/log/mariadb/mariadb.log contains 7 warning(s).

「ログにエラー出てるのでチェックしてください」。見ると海外からのMySQLログイン試行があった、こういうのDenyできないかな・・・

Performance Metrics

[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance

「MySQLでのDNS逆引き機能が有効になっています」。localhost を 127.0.0.1 として扱うアレ。無効にする場合は

vi /etc/my.cnf.d/mariadb-server.cnf

「skip-name-resolve」を追加

[mysqld]
skip-name-resolve

再起動

systemctl restart mariadb

[!!] Query cache may be disabled by default due to mutex contention.

「クエリキャッシュがミューテックス競合のためデフォルトで無効になってるかもしれません」。よくわかんない。

[!!] Query cache efficiency: 0.0% (0 cached / 1B selects)

「クエリキャッシュ効率：０％」。使ってないみたいです。

Twitter B! Hatena Facebook Google+ LINE Pocket