MyISAMからInnoDBに切替

一般的なケースでは、innodb_buffer_pool_sizeだけ気にしておけばよいでしょう。しかし、key_buffer_size、 tokudb_cache_size、 query_cache_size、 table_cache、 table_open_cacheについても考える必要がある場合もあります。

MySQLのメモリー使用量を最適化する設定のベストプラクティス | Yakst

mysql> show variables;

msyql> set global key_buffer_size = 128 * 1024 * 1024;

MySQL最低限のメモリ設定 - Qiita

innodb_dedicated_serverをONにすると、自動でinnodb_buffer_pool_size

第92回 innodb_dedicated_serverによるbuffer_poolの割り当て：MySQL道普請便り｜gihyo.jp … 技術評論社

MySQL 5.6のインストール後にチューニングすべき項目 | Yakst

set autocommit=1;　自動でコミットが有効（デフォルト値）。

現在の設定値

mysql> show variables like 'innodb_buffer_pool_size';

innodb_buffer_pool_size | 2147483648

mysql> show variables like 'innodb_file_per_table';

innodb_file_per_table | ON

mysql> show variables like 'autocommit';

autocommit | ON

mysql> show variables like 'innodb_flush_method';

innodb_flush_method | O_DIRECT

RDSの場合は、AWSコンソールのパラメータグループで設定するっぽい。

現在の値で、問題なさそう。

ということは、単純にサーバのスペックをひとつ上げるしかないのだろうか。

いろいろと調べたところ、

結局はINDEXの問題だった。

MyISAMの時は、INDEXを貼ることによって、UPDATEの時にテーブルロックが掛かるから逆に処理が遅くなることがあって、だからINDEXを貼ってない箇所があった。

それがInnoDBになると、レコードロックだから、反対に全文検索する時にとても遅くなってた。だからINDEXを貼ることでSELECTが速くなり、MyISAMの時の懸念だったUPDATE時のテーブルロックもInnoDBだとレコードロックだから気にしなくていいから、改善。

処理が速くなって、CPU使用率も下がって、前の水準に戻った。

改善方法は、

SQL文の速度を測って、EXPLAINでINDEXの状況を調べて、INDEXを貼る。

ちなみに、メモリ使用量はそのままで、書き込みIOPSもそのままだ。

これは何が原因なんだろうか。

---------------------------------------

https://aws.amazon.com/jp/premiumsupport/knowledge-center/rds-instance-high-cpu/

SHOW PROCESSLIST コマンドは、どのスレッドが MySQL インスタンスで現在実行中であるかを表示
SHOW FULL PROCESSLIST;

読み取り専用トランザクションではない、現在実行中のすべての InnoDB トランザクションに関する情報
※このコマンドを何回も実行して検出されるSQL文は大体遅いSQL文なのでINDEXを貼る等、改善が必要
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

InnoDB トランザクションがリクエストしたが受信していないロックについての情報
※たぶんロックされてるSQL文を表示してくれる
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

ブロックされた InnoDB トランザクションごとに 1 つまたは複数の行を提供
※ロック影響により待ちになっているトランザクションを表示
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

ロックされているトランザクションの関係を見れる
SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id;

InnoDB ストレージエンジンの状態に関する情報
※Buffer pool sizeやFree buffersの値が見れる
SHOW ENGINE INNODB STATUS;

サーバーのステータスに関する情報
SHOW GLOBAL STATUS;

---------------------------------------

https://thinkit.co.jp/article/9658

インデックスを指定してSQLを実行　USE INDEX(some_index)
mysql> SELECT COUNT(some_column) FROM some_table USE INDEX(some_index) WHERE some_column = xxx;

対象のインデックスを無視してSQLを実行
IGNORE INDEX

---------------------------------------