メモ: mysqlチューニング

  • 1
    いいね
  • 0
    コメント

参考

まずは show variables;

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

optimize table

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> 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

flush tables

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

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

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

バージョン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になった)