はじめに
もっと早めに気づいておくべきでした。MySQL設定ファイルがデフォルトのままであることに。
デフォルトのまま運用してるとどうやら危険が危ないことになるみたいなので泣く泣く変更。
気づくのが運用開始前で良かったです。
環境
| サービス | バージョン |
|---|---|
| CentOS | 7.4.1708 |
| MySQL | 5.7.20 |
やったこと
- mysql.cnfに追記
- Laravel側の設定変更
- チューニングツールの導入
my.cnfのチューニング
まずはMySQLサービスを止める。
console
sudo systemctl stop mysqld
新規設定した部分
- 文字コードを「utf8」->「utf8mb4」に変更
- 併せてクライアント側の文字コードも「utf8mb4」になるよう変更
- クエリをキャッシュに一時保存することでデータベースへのアクセス回数を減らす
- 実行に時間がかかるクエリをログ出力できるようにする
- ibdata問題の対応を行う
- InnoDBの細かな設定
- その他設定
主なチューニングは こちら を参考にさせていただきました。最大限の感謝を。
しかしながら数ヶ所、シンタックスエラーが発生した部分があったため修正しています。
修正後の設定ファイルは下記設定ファイルを参照のこと。
設定ファイル
/etc/my.cnf
# Foradviceonhowtochangesettingspleasesee
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#######################################
# Default
#######################################
# MySQLの実データが格納されているディレクトリ
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#######################################
# QUERYCACHE
#######################################
# クエリキャッシュ最大サイズ
query_cache_limit=16M
# クエリキャッシュで使用するメモリサイズ
query_cache_size=512M
# クエリキャッシュのタイプ
# 0：OFF
# 1：ONSELECTSQL_NO_CACHE以外
# 2：DEMMANDSELECTSQL_CACHEのみ
query_cache_type=1
#######################################
# SLOWQUERYLOG
#######################################
# 処理が遅いクエリをログとして保存しておく
slow_query_log=1
# スロークエリと判定する秒数
long_query_time=1
# スロークエリログ保存場所
# 事前にスケルトンを生成してパーミッションを変更しておく
# MySQL5.7から記述方法が変わった模様
slow_query_log_file=/var/log/slow.log
#######################################
# INNODB
#######################################
# InnoDBデータとインデックスをキャッシュするサイズ
# 推奨：物理メモリの8割
innodb_buffer_pool_size=800M
# コミットされていないトランザクションのためのバッファサイズ
innodb_log_buffer_size=64M
# InnoDBの更新ログを記録するディスク上のファイルサイズ
# 推奨：innodb_buffer_pool_sizeの1/4程度
innodb_log_file_size=200M
# テーブル単位にデータ領域を確保する
innodb_file_per_table=1
# InnoDBの内部データ情報を持つバッファサイズ
# innodb_additional_mem_pool_size=20M
# MySQL5.7で廃止された模様
#######################################
# ETC
#######################################
# インデックス未使用時のJOINバッファサイズ
join_buffer_size=256K
# クライアント->サーバーに送信できるパケット最大長
max_allowed_packet=8M
# フルスキャン時のレコードバッファ
read_buffer_size=1M
# キーを使用したソートで読み込まれた行がキャッシュされるバッファ
read_rnd_buffer_size=2M
sort_buffer_size=4M# ソート時に使用されるバッファ
# MEMORYテーブルの最大サイズ
# このサイズを超えたMEMORYテーブルはディスク上に作成される
max_heap_table_size=16M
# スレッドバッファ
tmp_table_size=16M
# スレッドキャッシュ最大保持数
thread_cache_size=100
# 絵文字対策
# 要：Laravel側のconfig/database.phpファイルの修正
character-set-server=utf8mb4
# ハハパパ問題・寿司ビール問題対策
# 要：Laravel側のconfig/database.phpファイルの修正
collation-server=utf8mb4_bin
# コネクション数の上限引き上げ
max_connections=500
[client]
default-character-set=utf8mb4
# ex. [15:16:32] user@localhost [sample_db] mysql>
prompt='[\\R:\\m:\\s]\\U[\\d]mysql>'
コンソールからコマンドを投入して、シンタックスエラーが出ていないかチェックする。
console
# シンタックスチェック
mysqld --verbose --help > /dev/null
# 問題なければサービス起動
systemctl start mysqld
なお、これらの設定はDB・テーブル作成前に行わないと一部反映されない点に留意する。
意図的にスロークエリを実行する
コンソールからMySQLにログインして次のコマンドを叩くだけ。
console
SELECT SLEEP(10);
これで表示されない場合、
- 事前に
slow.logが生成されていない -
slow.logファイルのパーミッションが正しく与えられていない - そもそもスロークエリの設定が有効になっていない
などの原因が想定される。
クエリが有効になっているかどうかの確認は以下のコマンドで行う。
console
show variables like '%slow%';
+---------------------------+-------------------+
| Variable_name | Value |
+---------------------------+-------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/log/slow.log |
+---------------------------+-------------------+
Laravel側の設定
照合順序や文字コードなどは全てconfig/database.phpで管理している点に注意する。
config/database.php
return [
'fetch' => PDO::FETCH_CLASS,
'default' => env('DB_CONNECTION', 'mysql'),
'connections' => [
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8mb4', // 明示的に文字コードをutf8mb4に変更する
'collation' => 'utf8mb4_bin', // 「ハハパパ問題」「寿司ビール問題」対策
'prefix' => '',
'strict' => false,
'port' => 3306,
],
],
//********
//* 省略
//********
];
当然この設定もDB・テーブルを作成した後だと効果がない点に留意すること。
MySQL Tunerの導入
チューニング漏れや不適切な設定があった場合に警告を出してくれるツールを導入。
これで勝つる。
と思ったら言われたとおりチューニングするだけだと動かなくなる場合もあるらしいので設定は慎重に行わないといけないっぽいです。
console
# wgetとunzipコマンドをインストール
yum install -y wget
yum install -y unzip
cd /usr/share
# zipファイルのダウンロード
wget -O mysqltuner.zip https://github.com/rackerhacker/MySQLTuner-perl/archive/master.zip
# zipファイル展開
unzip mysqltuner.zip
# zipファイル削除
rm mysqltuner.zip
# 実行する
cd MySQLTuner-perl-master
./mysqltuner.pl --user (user) --pass (password)
実行結果
console
>> MySQLTuner 1.7.4 - 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
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.7.20-log
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.log(20K)
[OK] Log file /var/log/mysqld.log exists
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 15 warning(s).
[!!] /var/log/mysqld.log contains 1 error(s).
[--] 3 start(s) detected in /var/log/mysqld.log
[--] 1) 2017-12-21T02:53:13.433912Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2017-12-21T01:42:02.326674Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2017-12-21T00:48:47.591605Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2017-12-21T02:53:06.616684Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2017-12-21T01:42:01.922504Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 3M (Tables: 92)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] Bug #80860 MySQL 5.7: Avoid testing password when validate_password is activated
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 10m 38s (865 q [1.356 qps], 10 conn, TX: 319K, RX: 86K)
[--] Reads / Writes: 50% / 50%
[--] Binary logging is disabled
[--] Physical Memory : 1.8G
[--] Max MySQL memory : 2.6G
[--] Other process memory: 127.0M
[--] Total buffers: 1.5G global + 7.5M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.5G (82.15% of installed RAM)
[!!] Maximum possible memory usage: 2.6G (142.90% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/865)
[OK] Highest usage of available connections: 1% (2/151)
[OK] Aborted connections: 0.00% (0/10)
[!!] 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 / 219 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 212 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 15% (10 on disk / 66 total)
[OK] Thread cache hit rate: 80% (2 created / 10 connections)
[OK] Table cache hit rate: 93% (103 open / 110 opened)
[OK] Open file limit used: 0% (14/5K)
[OK] Table locks acquired immediately: 100% (102 immediate / 102 locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/43.0K
[!!] Read Key buffer hit rate: 50.0% (6 cached / 3 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 896.0M/3.2M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (44.6428571428571 %): 200.0M * 2/896.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 7 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Read buffer efficiency: 77.51% (2878 hits/ 3713 total)
[!!] InnoDB Write Log efficiency: 0% (7 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 7 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.
-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.
-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
MySQL started within 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.
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: http://bit.ly/2wgkDvS
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 16M, or use smaller result sets)
innodb_log_file_size should be (=112M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
この[!!]がついている部分が改善の余地ありと診断された部分みたいです。
所感
正直触るの怖いねん。
稼働前に気づくことがたくさんありすぎてやばたん。
このスライドとか読むとすごく楽しい気持ちになれる。