メニュー

1-12 MySQLのパフォーマンスチューニング

スロークエリの設定

MySQLパフォーマンスチューニング -my.cnfの見直し- – Qiita
MySQL サーバーのパフォーマンスチューニングを my.cnf の設定項目から解説します。状況僕が担当したサービスが　秒間5リクエスト でサーバーダウン。今までは ただ波が去…
qiita.com
MySQLのチューニング slow logの設定方法（/etc/my.cnf） – Linux環境 – MySQL入門 – Webkaru
MySQLのチューニングに必要な（便利な） slow log の設定方法を紹介します。
webkaru.net

スロークエリとは、調べると処理速度が遅いSQLのこと。※RDB（リレーショナルデータベース）で発生すること？

/etc/my.cnf.dの中にslow_query.cnfを作成し、スロークエリの設定を加える。

[mysqld]
slow_query_log=ON
slow_query_log_file=/var/log/mysql/slow_query.log
long_query_time=3
log_queries_not_using_indexes=OFF

long_query_timeは1〜3秒くらいを目安にする。

/etc/my.cnf.dにあるmysql-server.cnfの中を確認すると、/var/log/mysql/の中に他のログを吐き出しているので、スロークエリも出力場所を合わせることにした。

出力先を変えるなら、ログファイルは予め作成しておいた方が良いとのこと。設定反映前にログファイルを準備する。

touch /var/log/mysql/slow_query.log
chown mysql:mysql /var/log/mysql/slow_query.log

chownでユーザー所有権とグループ所有権を変更すること。

log_queries_not_using_indexes

ONにすることで、インデックスが効かないクエリーもログに出力されるようになります。
ただし、インデックスを効かないクエリーもログに出力した場合、出力量が増大し、改善すべきクエリーを検出しにくくなる可能性があります。

https://qiita.com/kaburankattara/items/909f3bf9d9e4ad33a0c2

負荷がかかりそうなので必要に応じてONにする方針にし、基本はOFFに設定することにした。

キャッシュ設定

ファイル管理のため、/etc/my.cnf.dには新たにcache.cnfを作成する。

[mysqld]
thread_cache_size=100

MySQL8系からはサポートされていない設定

クエリキャッシュの設定の登録はできなかった。MySQL8系からはサポートされていない。

query_cache_limit=16M
query_cache_size=512M
query_cache_type=1
MySQL 8.0 : クエリーキャッシュのサポート終了 | Yakst
役に立つ場面もある一方、スケーラビリティー上問題があるとされてきたMySQLのクエリーキャッシュが、MySQL 8.0で廃止されることになる。その背景と理由。
yakst.com

最初に間違えてクエリキャッシュを登録しようとしたため、mysqlを再起動できなかった。

systemctl restart mysqld
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xeu mysqld.service" for details.

再起動できない原因を探すには、ログを確認する。

cat /var/log/mysql/mysqld.log

query_cache_limitを設定すると「〜〜 [Server] unknown variable ‘query_cache_limit=16M’.」とのようなエラーメッセージが入る。
ひとつずつ検証し、エラー対応していくことで、mysqlは無事に再起動できた。

メモ：MySQLのキャッシュはどうすればいいのか？→そもそもDB側で処理することではないのでは？

Query Rewrite プラグインの利用と性能影響について | スマートスタイル TECH BLOG
はじめに MySQL では 5.7 から、Query Rewrite プラグイン を使用することができます。 このプラグインが有効になっている場合、サーバーでクエリが実行される前にチェッ…
blog.s-style.co.jp

安直に「Query Rewriteを導入した方がいいのか？」と思ったが、MySQLでキャッシュするとオーバーヘッドが大きくなるので、アプリケーション側で対応した方がよい。まずはMySQLにアクセスせず、キャッシュを管理する方法を検討した方がよいかも。

クエリーキャッシュを使用すると、アプリケーション側はDBにアクセスし、DBに保存されているキャッシュを読み込むか否かを振り分けることになる。が、これだと毎回DBにアクセスすることになるので、オーバーヘッドが発生する。特に動的なサイトはオーバーヘッドが大きくなる。
なのでDBにアクセスするのではなく、キャッシュは別で管理し、アプリケーション側で変更の有無を判定できるようにする。判定後、アプリケーション側ではDBにアクセスするか、キャッシュを返すかの処理をする。

メモ：キャッシュ管理のメモ

キャッシュ処理として一番楽なのはページを丸ごとをキャッシュを取ってしまうことだが、会員情報を扱うページがあるとNG。

WordPressだと、Transients APIを使ってメモリにキャッシュさせる。メモリ(一時保存)をキャッシュとして使える。
WordPressでは基本的にTransients APIから値を返すような仕様になっているとのこと。

Transients – Common APIs Handbook | Developer.WordPress.org
Overview This page contains the technical documentation of WordPress Transients API, which offers a simple and standardized way of storing…
developer.wordpress.org

Transients APIを使うことで、有効期限が切れたときや更新されたときにDBにアクセスできる。

いちばんめのサーバー構築の流れ