背景
MediaWikiにベータを詰め込んだせいか、データベースアクセスのパフォーマンスが悪化してしまった。
以前、MYSQL5.5で施していたチューニングがMYSQL5.7で使えるのかどうか、、、お試し。
環境(ローカルサーバ)
ハードウェア
- CPU Intel Core i7 8700K BOX
- MEM DDR4-2666 DIMM 16GB x 2
- M/B GIGABYTE Z370 HD3
- SSD Intel SSD 545s 512GB SSDSC2KW512G8X1
- HDD SEAGATE ST4000DM004 4TB
- G/B 玄人志向 GF-GTX1080Ti-E11GB/OC/DF
ソフトウェア
- OS Ubuntu 18.04.1 LTS
- PHP PHP 7.2.10-0ubuntu0.18.04.1 (cli) (built: Sep 13 2018 13:45:02) ( NTS )
- MYSQL Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu)
- HTTPサーバ Apache/2.4.29 (Ubuntu)
- NVIDIAドライバ NVIDIA-SMI 390.87 Driver Version: 390.87
- CUDA Toolkit 10.0
- cuDNN v7.3.1 (Sept 28, 2018), for CUDA 10.0
- OpenBLAS
- libopenblas-base/bionic 0.2.20+ds-4 amd64
- libopenblas-dev/bionic 0.2.20+ds-4 amd64
- python-numpy (1:1.13.3-2ubuntu1)
- python-scipy (0.19.1-2ubuntu1)
- python-matplotlib (2.1.1-2ubuntu3)
- python-yaml (3.12-1build2)
- HDF5
- python-h5py (2.7.1-2)
- libhdf5-serial-dev (1.10.0-patch1+docs-4)
- graphviz (2.40.1-2)
- python-pip
- python-dev
- pydot-ng-2.0.0
- Bazel
- openjdk-8-jre:amd64 (8u181-b13-0ubuntu0.18.04.1)
- bazel (0.18.0)
- Python 2.7.15rc1
- pip 9.0.1 from /usr/lib/python2.7/dist-packages (python 2.7)
- virtualenv 15.1.0
- GNOME 3.28.2
メモリ比較
参考：Linux のメモリー管理 http://www.math.kobe-u.ac.jp/HOME/kodama/tips-free-memory.html
MYSQL5.5稼働中の専用サーバ
パンパンですよ。
# free total used free shared buffers cached Mem: 16309108 16015940 293168 0 157052 9765860 -/+ buffers/cache: 6093028 10216080 Swap: 4095992 228260 3867732
MYSQL5.7テスト環境のローカルサーバ
$ free
total used free shared buff/cache available
Mem: 32815984 10062628 716236 324372 22037120 21949352
Swap: 153605116 4136 153600980
MYSQL5.7専用サーバのコンテンツ避難サーバ
total used free shared buff/cache available Mem: 4194304 309268 3720920 17084 164116 3750686 Swap: 8388608 0 8388608
クエリキャッシュ
https://qiita.com/mamy1326/items/d1548d8cf4528277172a
https://qiita.com/ryurock/items/9f561e486bfba4221747
先人も奮闘されている。
現在の設定はMYSQLから確認できるようだ。
mysql> SHOW VARIABLES LIKE '%query_cache%'; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 6 rows in set (0.00 sec)
mysql.cnfでの設定は、
(以前、MYSQL5.5) query_cache_type= 1 query_cache_limit = 4M query_cache_size = 64M (現在、MYSQL5.7テスト環境 デフォルト状態) query_cache_limit = 1M query_cache_size = 16M (変更後、MYSQL5.7テスト環境 & 避難サーバ) query_cache_type= 1 query_cache_limit = 4M query_cache_size = 64M
ちなみに、現状は
mysql> show session status like 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 16760152 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 9360521 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+----------+ 8 rows in set (0.00 sec)
と全く機能しておりません。
しばらく、これで様子見をしよう。
データベース全体とスレットごとの各パラメータ最適化
参考：
- MySQL でメモリ不足が発生したときのパラメータチューニング https://qiita.com/kyrieleison/items/9f303dd046e2fb82fea3
- MySQLのメモリ関係のシステム変数 http://tetsuyai.hatenablog.com/entry/20111006/1317873012
- 最速！ mysql5.7 インストール手順 https://hit.hateblo.jp/entry/2016/12/20/114402
グローバルバッファの設定
key_buffer_size
MyISAM で索引検査をする際にインデックスを格納するバッファーのサイズ。MyISAM を利用するならば大きい方が良いが、そうでなければ小さくてもOK。
SELECT @@global.key_buffer_size; MYSQL5.5稼働 ： 134217728 (128M) MYSQL5.7テスト： 16777216 (16M) MYSQL5.7避難 ： 8388608 (8M)
innodb_buffer_pool_size
InnoDB で使用するバッファサイズ。総メモリの70-80%程度
SELECT @@global.innodb_buffer_pool_size; MYSQL5.5稼働 ： 4294967296 (4G) MYSQL5.7テスト： 134217728 (128M) MYSQL5.7避難 ： 134217728 (128M)
ショボショボですね。テスト環境では25-30G、避難サーバでは1-2Gくらいあげよう。
innodb_log_buffer_size
InnoDB log bufferはInnoDBテーブルに対するトランザクションログを管理している。トランザクション終了時、バッファーが溢れたとき、一定時間ごとにディスクに書き込みが生じる。一つのトランザクションで多くのデータを扱うようであれば大きくしても良いが。1-8MB程度推奨となっているが、最大でも64MB程度だろう。
SELECT @@global.innodb_log_buffer_size; MYSQL5.5稼働 ： 67108864 (64M) MYSQL5.7テスト： 16777216 (16M) MYSQL5.7避難 ： 16777216 (16M)
どうでも良さそうだが、せっかくなので「64M」に統一してみよう。
net_buffer_length
max_allowed_packet参照
max_allowed_packet
パケットメッセージバッファは送受信するパケットを格納するメモリ上の領域。net_buffer_lengthで初期化され、必要に応じてmax_allowed_packetまで拡張される。クライアントが実行できるSQL文の最大長はmax_allowed_packetによって制限される。一般的にはもっとも大きいBLOBまたはTEXTにする。
SELECT @@global.net_buffer_length; MYSQL5.5稼働 ： 16384 (16k) MYSQL5.7テスト： 16384 (16k) MYSQL5.7避難 ： 16384 (16k) SELECT @@global.max_allowed_packet; MYSQL5.5稼働 ： 16777216 (16M) MYSQL5.7テスト： 16777216 (16M) MYSQL5.7避難 ： 4194304 ( 4M)
MEDIUM BLOBくらいにしておくか。つまり、「16M」で統一しておく。
innodb_log_file_size
メモリ関連のパラメータではないが、パフォーマンスに関連する。innodb_log_fileがいっぱいになると、メモリ上のinnodb_buffer_poolの中の更新された部分のデータを、ディスク上のInnoDBのデータファイルに書き出す仕組みになっているらしい。よって、innodb_buffer_pool_sizeの値を大きくしたらinnodb_log_file_sizeの値も大きめに調整しないとパフォーマンス向上に繋がらないらしい。
SELECT @@global.innodb_log_file_size; MYSQL5.5稼働 ： 268435456 (256M) MYSQL5.7テスト： 50331648 ( 48M) MYSQL5.7避難 ： 50331648 ( 48M)
「256M」で統一しておく。
スレッドバッファの設定
スレッドバッファはスレッド (コネクション) 毎に確保される。スレッドバッファに多くのメモリを割り当てると、コネクションが増えた際にメモリ不足に陥る。スレッドバッファかコネクション数を絞る必要がある。
sort_buffer_size
ソート処理に利用されるメモリ上の領域。sort_buffer_sizeを超えた分はテンポラリファイルに書き出されてソートが実行される。大きなデータをソートする場合、このパラメータを大きくするとメモリ上でソートが実行されるため、パフォーマンスが向上する。OLTP系では概ねデフォルト（2MB）のままでok。DWH系で大きなデータをソートする場合、セッションごとに動的に調整すると良い。
SELECT @@global.sort_buffer_size;
MYSQL5.5稼働 ： 1048576(1M)
MYSQL5.7テスト： 262144(256k)
MYSQL5.7避難 ： 262144(256k)
デフォルトが2MBのようなので「2M」にしておく。
read_buffer_size
インデックスを使わないテーブルフルスキャン時に利用される.。インデックスを使わないテーブルスキャンに使うメモリ上の領域。このパラメータを大きくするとメモリ上でテーブルスキャンが実行されるため、パフォーマンスが向上する。パフォーマンスを考えるならば、このパラメータを操作するよりも先にインデックスの利用を検討するべき。OLTP系ではあまり大きな値は必要ない。
SELECT @@global.read_buffer_size;
MYSQL5.5稼働 ： 1048576 ( 1M)
MYSQL5.7テスト： 131072 (128k)
MYSQL5.7避難 ： 131072 (128k)
「1M」にしておく。
read_rnd_buffer_size
インデックスを利用したソートに利用される。このバッファはインデックスによるソート（ファイルソートではない）を実行するとき、ソートしたレコードの読み出しに使うメモリ上の領域。大きなデータをフェッチする場合、このパラメータを大きくするとディスクI/Oが減少するため、パフォーマンスが向上する。OLTP系ではデフォルト（256KB）のままで問題ない。DWH系で大きなデータをフェッチする場合、セッションごとに動的に調整すると良い。
SELECT @@global.read_rnd_buffer_size; MYSQL5.5稼働 ： 4194304 ( 4M) MYSQL5.7テスト： 262144 (256k) MYSQL5.7避難 ： 262144 (256k)
「4M」にしておく。
join_buffer_size
インデックスを使わない結合に利用されるメモリ上の領域。テーブル結合ではフルテーブルスキャンが実行されるが、このパラメータを大きくするとメモリ上で結合が実行されるため、パフォーマンスが向上する。しかし、そもそもインデックスを使わないテーブル結合は避けるべきであり、このパラメータを操作するよりも先にインデックスの利用を検討するべき。
SELECT @@global.join_buffer_size; MYSQL5.5稼働 ： 131072 (128k) MYSQL5.7テスト： 262144 (256k) MYSQL5.7避難 ： 262144 (256k)
「128k」に減らしておく。
myisam_sort_buffer_size
MyISAM テーブルのインデックス作成、あるいはソートするときに利用される。
SELECT @@global.myisam_sort_buffer_size; MYSQL5.5稼働 ： 67108864 (64M) MYSQL5.7テスト： 8388608 ( 8M) MYSQL5.7避難 ： 8388608 ( 8M)
MyISAMなんて使っていないからいらん？「1M」に減らしておく。
max_connections
最大コネクション数。同時にコネクションを貼ることができるコネクションの数。大きすぎた場合、本当にそれだけの接続があった場合にメモリが足らなくなるので現実的な値を設定しよう。
現在の設定を調査：
mysql> select @@global.max_connections;
で知ることができる。
MYSQL5.5稼働 ：180 (設定ファイルに記載) MYSQL5.7テスト：151 (設定ファイルに記載していないが、デフォルト？) MYSQL5.7避難 ：151 (設定ファイルに記載していないが、デフォルト？)
まあ、そんなに必要か、、、という感じもするが、ひとまずテスト環境も避難サーバーも「180」で設定することにしよう。
その他
max_heap_table_size
MEMORYテーブル（旧HEAPテーブル）はMEMORYストレージエンジンによってメモリ上に作成されるテーブル。MEMORYテーブルはmax_heap_table_sizeを超えるテーブルを作成できない（ただしこの制約はCREATE TABLE文、ALTER TABLE文を実行したときに適用されるため、max_heap_table_sizeを変更しても既存のMEMORYテーブルには影響しない）。とくに理由がなければtmp_table_sizeと同じ値にする。
参考：
- tmp_table_size のチューニングとメモリ上に一時テーブルが作れないクエリ https://qiita.com/tyoro/items/5436a5172b547e5e52f5
tmp_table_size
テンポラリテーブルはその接続でのみ現れ、接続が終了するとドロップされる一時的なテーブルである。テンポラリテーブルはそのサイズがtmp_table_size以下であればMEMORYテーブルとしてメモリ上に作成され、tmp_table_sizeを超えるとISAMテーブルとしてディスクに書き出される。副問い合わせを含むような複雑なクエリを実行している場合、このパラメータを大きくするとテンポラリテーブルがMEMORYテーブルとして作成されるため、パフォーマンスが向上することがある。なお、MEMORYテーブルの大きさはmax_heap_table_sizeによっても制約されるため、tmp_table_sizeを大きくする場合は同時にmax_heap_table_sizeも大きくする。
SELECT @@global.max_heap_table_size; MYSQL5.5稼働 ： 67108864 (64M) MYSQL5.7テスト： 16777216 (16M) MYSQL5.7避難 ： 16777216 (16M) SELECT @@global.tmp_table_size; MYSQL5.5稼働 ： 67108864 (64M) MYSQL5.7テスト： 16777216 (16M) MYSQL5.7避難 ： 16777216 (16M)
テスト環境では「64M」に、避難サーバーでは現状維持の「16M」にする。
table_open_cache
table_open_cache
テーブルキャッシュは接続終了後もテーブルをメモリ上に維持しておき、次の接続時に再利用できるようにキャッシュするメモリ上の領域です。値はキャッシュに保持するテーブルの最大数を意味します。5.1以前はtable_cacheという名前でした。最大同時接続数が多い場合、このパラメータを大きくすると再接続時のオーバーヘッドが軽減されるため、パフォーマンスが向上します。注意すべきは、MySQLは同じテーブルに対して同時にアクセスするスレッドがあった場合、それぞれが同じテーブルを重複して開く、ということです。こうすることによってマルチスレッド環境でのパフォーマンスを向上させています。そのため、MySQLが開くテーブルの最大数は最低でもmax_connectionsと同数になります。”最低でも”というのは、joinを含むクエリでは複数のテーブルが開かれることがあるためで、その場合はmax_connections × n（nはjoinによって結合されるテーブル数）が最大数となります。
SELECT @@global.table_open_cache;
MYSQL5.5稼働 ： 2048 MYSQL5.7テスト： 2000 MYSQL5.7避難 ： 2000
「2048」にしておく
thread_cache_size
thread_cache_size
スレッドキャッシュは接続終了後のサーバースレッドを解放せず、次の接続時に再利用できるようにキャッシュするメモリ上の領域です。値はキャッシュに保持するスレッドの最大数を意味します。最大同時接続数が多い場合、このパラメータを大きくすると再接続時のオーバーヘッドが軽減されるため、パフォーマンスが向上します。
SELECT @@global.thread_cache_size;
MYSQL5.5稼働 ： 180 MYSQL5.7テスト： 8 MYSQL5.7避難 ： 9
「180」にしておく
max_binlog_size
binlog_cache_size
バイナリログキャッシュは未コミットのトランザクション情報（単一トランザクションの中で実行されたSQLステートメント）をキャッシュするメモリ上の領域です。値はキャッシュに保持するSQL文の最大数を意味します。InnoDBなどトランザクションに対応したストレージエンジンを使用し、かつバイナリログを有効にしている場合にのみ割り当てられ、バイナリログファイルへの出力はトランザクションがコミットされるまでバイナリログキャッシュに保留されます。トランザクション情報がbinlog_cache_sizeを超えると、スレッドはトランザクション情報を保存するためにテンポラリテーブルを作成します。大きなトランザクションを実行する場合、このパラメータを大きくするとテンポラリテーブルの作成を回避できるため、パフォーマンスが向上します。
SELECT @@global.max_binlog_size;
MYSQL5.5稼働 ： 268435456 (256M) MYSQL5.7テスト： 104857600 (100M) MYSQL5.7避難 ： 104857600 (100M)
「256MB」にしておく
wait_timeout
wait_timeout
タイムアウト待ち時間は反応のない接続を終了する前に、サーバーが待機する時間です。最後の実行からこの秒数が経過すると、サーバーは接続を終了します。この値はTCP/IPとUnixソケットによる接続だけに適用されます。
SELECT @@global.wait_timeout;
MYSQL5.5稼働 ： 60 MYSQL5.7テスト： 28800(8時間) MYSQL5.7避難 ： 28800(8時間)
「60」にしておく。
innodb_data_file_path
SELECT @@global.innodb_data_file_path; MYSQL5.5稼働 ：ibdata1:10M:autoextend MYSQL5.7テスト：ibdata1:12M:autoextend MYSQL5.7避難 ：ibdata1:12M:autoextend
デフォルトで良いだろう。