メモ: mysqlチューニング

• MySQL クエリキャッシュ値設定と確認方法 - Qiitaもどうぞ。

• mysqlをdisる会 - Qiita をリスペクトしつつ自分に必要な内容を抜粋。

参考

• MySQL をチューニングする時のポイント一部始終 - ようへいの日々精進XP
• MySQLのメモリ設定を追求してみよう | OpenGroove
• DSAS開発者の部屋:5分でできる、MySQLのメモリ関係のチューニング！
• mysqldump --single-transaction に --flush-logs をつけてはいけない - @tmtms のメモ

まずは show variables;

• 全体の設定を確認できる。innodb_buffer_pool_size 値など。

optimize table

• オデの日記＠WEB系: MySQLで最初に見るべきチューニング設定項目

mysqlcheck -uroot --all-databases --optimize

ストレージエンジン

• InnoDB選ぶ。(mysql 5.5 以前までは標準だとMyISAM)

innodb_file_per_table

• 4.1.1以降から設定可能。
• 容量制限をかけることができますが、容量制限をかけた場合、その容量に到達した時点で
  書き込めなくなります。(mysqlデフォルトの挙動)
• これを防ぐオプション「innodb_file_per_table」忘れずに入れておきましょう。

innodb_buffer_pool_size

• mysqlでinnodbを長年使い続けている方ならもちろんご存知かと思いますが
  「innodb_buffer_pool_size」の話です。
  とりあえず脳死で総搭載メモリ量の80%くらいとっておけばいいです。（もちろんスワップを発生させない範囲で）
• mysql 4.1 からM以外にGも指定できるようだが、4.0ではMまでしか指定できない。

/etc/my.cnf

[mysqld]
innodb_buffer_pool_size = 8048M

確認

show variables like 'inno%';

innodb_log_file_size

• 初期値 5M

mysqldを停止し
/var/lib/mysql/ib_logfile[01]を削除して起動すれば指定したサイズで再作成される。

/etc/my.cnf

[mysqld]
innodb_log_file_size = 128M

文字コード

• 何も考えずにDBにつないでinsertとかするとlatin1ではいってしまったりします。
  latin1で入った場合、そのまま扱う分には大きな問題に”あまり”ならないのですが、
  ダンプして復元という話になってくると結構大きな問題になったりします。
  mysqlをいれたあとはDBやテーブルを作るよりも先に文字コードの設定をすることをおすすめします。

インデックス

• 複合indexを貼る場合は順番が大事です。とりあえず貼ったからといって順番を逆にしたりするとindexは効力を一切発揮しません。気をつけてね。

show innodb status\G

• MySQL :: MySQL 4.1 リファレンスマニュアル :: 7.5.10.1 SHOW INNODB STATUS と InnoDB モニタ
• mysql 5.5以降はSHOW ENGINE INNODB STATUSを使う。
• show innodb status
• 日々の覚書: InnoDB Monitorの仲間たち（InnoDBエンジン本体のアレ）
• メモ帳: November 2007 Archives

 mysql> SHOW INNODB STATUS\G
 *************************** 1. row ***************************
 Status: 
 =====================================
 170318 16:09:58 INNODB MONITOR OUTPUT
 =====================================
 Per second averages calculated from the last 5 seconds
 ----------
 SEMAPHORES
 ----------
 OS WAIT ARRAY INFO: reservation count 206, signal count 206
 Mutex spin waits 22, rounds 260, OS waits 9
 RW-shared spins 394, OS waits 197; RW-excl spins 3, OS waits 0
 ------------
 TRANSACTIONS
 ------------
 Trx id counter 0 24585
 Purge done for trx's n:o < 0 24584 undo n:o < 0 0
 Total number of lock structs in row lock hash table 0
 LIST OF TRANSACTIONS FOR EACH SESSION:
 ---TRANSACTION 0 0, not started, process no 843, OS thread id 140458259392256
 MySQL thread id 3390, query id 30022 localhost root
 SHOW INNODB STATUS
 --------
 FILE I/O
 --------
 I/O thread 0 state: waiting for i/o request (insert buffer thread)
 I/O thread 1 state: waiting for i/o request (log thread)
 I/O thread 2 state: waiting for i/o request (read thread)
 I/O thread 3 state: waiting for i/o request (write thread)
 Pending normal aio reads: 0, aio writes: 0,
  ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
 Pending flushes (fsync) log: 0; buffer pool: 0
 440 OS file reads, 2471 OS file writes, 1611 OS fsyncs
 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
 -------------------------------------
 INSERT BUFFER AND ADAPTIVE HASH INDEX
 -------------------------------------
 Ibuf for space 0: size 1, free list len 5, seg size 7,
 0 inserts, 0 merged recs, 0 merges
 Hash table size 1106407, used cells 1650, node heap has 4 buffer(s)
 0.00 hash searches/s, 0.00 non-hash searches/s
 ---
 LOG
 ---
 Log sequence number 0 247214771
 Log flushed up to   0 247214771
 Last checkpoint at  0 247214771
 0 pending log writes, 0 pending chkp writes
 1498 log i/o's done, 0.00 log i/o's/second
 ----------------------
 BUFFER POOL AND MEMORY
 ----------------------
 Total memory allocated 626511882; in additional pool allocated 1048320
 Buffer pool size   32768
 Free buffers       28540
 Database pages     4224
 Modified db pages  0
 Pending reads 0 
 Pending writes: LRU 0, flush list 0, single page 0
 Pages read 350, created 3874, written 12035
+0.00 reads/s, 0.00 creates/s, 0.00 writes/s
 No buffer pool page gets since the last printout
 --------------
 ROW OPERATIONS
 --------------
 0 queries inside InnoDB, 0 queries in queue
 Main thread process no. 843, id 140458268387072, state: waiting for server activity
 Number of rows inserted 770306, updated 47, deleted 0, read 649858
 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
 ----------------------------
 END OF INNODB MONITOR OUTPUT
 ============================
 
 1 row in set (0.01 sec)

• reads/s が 0に近ければ良い。

ERROR 1227 at line 1: Access denied. You need the SUPER privilege for this operation

• SHOW INNODB STATUS\G を実行するには特権じゃないとだめな様子。
  • mysql 5.5からこのコマンドはなくなった様子。
• show innodb status で データーベース の中身がメモリに入りきっているか確認 - Qiita

flush tables

現在開いているテーブルを閉じる

• MySQLでバッチ処理を回すときはちゃんとflush tablesしましょうという話ですよ。。。 - エピゴーネンで
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.4.3.1 MySQL でのテーブルのオープンとクローズの方法

mysql> show variables like 'query_cache_size';
mysql> show open tables;

mysqlのバージョンによるフォーマットの追加

• MySQLのINSERTを高速化するChange bufferingをソースコードから理解する - Akatsuki Hackers Lab | 株式会社アカツキ（Akatsuki Inc.)

バージョン4.1.x未満
バージョン4.1.x以上(innodb_file_per_tableが実装され、space idが必要になった)
バージョン5.0.3以下(ROW_FORMATオプションが追加され、Change buffer中にこれに対応するためのフラグが追加された)
バージョン5.5.0以上(Insert bufferingがdeleteやpurgeに対応したChange Bufferingになった)