MySQL InnoDBのパフォーマンス調整の実践
2768 ワード
JavaEyeサイトのデータベースサーバが引っ越した時にホスト業者のスタッフにひどく殴られたので、ハードディスクが全部切れてしまいました.私がデータベースサーバを再インストールした時、Percona patchのMySQL 5.0バージョンをダウンロードし、MySQLが持参したheavy innodbプロファイルを使用して変更し、my.cnfとして起動しました.データベース・サーバの物理メモリは6 GBで、そのうち4 GBはMySQLで使用できます.my.cnfに関する構成パラメータは以下の通りです.
buffer poolが大きいほど、物理メモリの50%-80%が推奨されています.log_file_sizeも大きいほど良いので、公式推奨log sizeを合わせるとbuffer poolの25%-100%に達します.memlockを使用するとMySQLメモリがswapに入るのを避けることができますが、これらはデフォルトの推奨構成であり、疑問な点はありません.しかし、データベース・サーバが起動すると、動作が正常ではありません.次の現象が現れます.
1、オペレーティングシステムメモリDisk Cacheは2.7 GBを使用した
2、オペレーティングシステムのswap空間は200 MBぐらいを使って、ずっとswap in/swap outを行っている
3、CPUのIO Waitが高く、平均10%以上
この現象は非常に奇妙で矛盾しているように見える.IO Waitが高いのは明らかにswapを頻繁に使ってメモリのページを変えるためだが、問題は物理メモリが非常に空いていることだ.オペレーティングシステムには2.7 GBの空き物理メモリがあるのに、どうして少し吐かないで、swapを使わなければならないのだろうか.
行きたいのはただ1つの可能性だけで、MySQLは非常に巨大で、頻繁なファイルの読み書き操作が存在して、オペレーティングシステムに2.7 GBのDisk Cacheを割り当てざるを得なくて、それによって物理のメモリの不足をもたらして、swapを使うことを余儀なくされました.巨大なファイルの読み書き操作を引き起こす可能性があるのはbuffer poolのflushとlog fileのflush操作です.したがって、プロファイルは次のように変更されます.
log file sizeと数を減らしてO_を使うDIRECT.再起動後、データベース・サーバは正常に戻ります.オペレーティングシステムDisk Cacheは900 MBに低下し,Swapは200 MB以上を使用したが,swap in/swap out操作は行われず,CPUのIO Waitは2−3%に低下した.
今回のMySQL InnoDBのチューニング経験を通じて、MySQLの公式推薦配置と合わない疑問点を発見し、思考と探求に値する.
1、innodb_flush_methodはO_を使うべきかどうかDIRECT?
すべてのMySQLチューニングの推奨事項は、ハードウェアにプリフェッチ機能がない場合は、O_を使用すると述べています.DIRECTは、O_DIRECTはオペレーティングシステムのファイルシステムDisk Cacheをスキップし、MySQLにディスクを直接読み書きさせた.
でも私の実践ではO_を使わなければDIRECTでは、オペレーティングシステムがinnodbの読み書きキャッシュに大量のDisk Cacheを開発せざるを得ず、読み書き性能を向上させるどころか、読み書き性能が急激に低下した.さらにbuffer poolのデータキャッシュとオペレーティングシステムDisk CacheキャッシュはDouble bufferの浪費をもたらし、明らかに私のこの実践から見ると、浪費が非常に強い.
O_と言うDIRECTによるMySQLのディスクの直接読み書きによるパフォーマンスの低下は、全く杞憂だと思います.JavaEyeのデータベースモニタリングから見ると、Innodbのbuffer poolヒット率は非常に高く、98%以上あり、本格的なディスク操作は微々たるものです.1%のディスク操作でDisk Cacheを得るために、double bufferのメモリ容量の98%を浪費することは、性能的にもメモリリソースの消費からも賢明ではありません.
2、innodb_log_file_sizeはいったい大きいのか、それとも小さいのか.
すべてのMySQLチューニングの推奨事項は、innodb_log_file_sizeは大きいほど良いので、無駄なbuffer poolのflush操作を避けます.
しかし、私の実践ではinnodb_log_file_sizeが大きすぎるとinnodbのlog書き込み操作が著しく増加し、オペレーティングシステムにより多くのDisk Cacheオーバーヘッドが必要になります.
だから私の経験から見るとinnodb_flush_method=O_DIRECTは必須ですがinnodb_log_file_sizeも大きすぎるべきではありません.
出典:
http://robbin.iteye.com/blog/461382
memlock
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
#innodb_flush_method=fdatasync buffer poolが大きいほど、物理メモリの50%-80%が推奨されています.log_file_sizeも大きいほど良いので、公式推奨log sizeを合わせるとbuffer poolの25%-100%に達します.memlockを使用するとMySQLメモリがswapに入るのを避けることができますが、これらはデフォルトの推奨構成であり、疑問な点はありません.しかし、データベース・サーバが起動すると、動作が正常ではありません.次の現象が現れます.
1、オペレーティングシステムメモリDisk Cacheは2.7 GBを使用した
2、オペレーティングシステムのswap空間は200 MBぐらいを使って、ずっとswap in/swap outを行っている
3、CPUのIO Waitが高く、平均10%以上
この現象は非常に奇妙で矛盾しているように見える.IO Waitが高いのは明らかにswapを頻繁に使ってメモリのページを変えるためだが、問題は物理メモリが非常に空いていることだ.オペレーティングシステムには2.7 GBの空き物理メモリがあるのに、どうして少し吐かないで、swapを使わなければならないのだろうか.
行きたいのはただ1つの可能性だけで、MySQLは非常に巨大で、頻繁なファイルの読み書き操作が存在して、オペレーティングシステムに2.7 GBのDisk Cacheを割り当てざるを得なくて、それによって物理のメモリの不足をもたらして、swapを使うことを余儀なくされました.巨大なファイルの読み書き操作を引き起こす可能性があるのはbuffer poolのflushとlog fileのflush操作です.したがって、プロファイルは次のように変更されます.
memlock
innodb_buffer_pool_size = 2G
innodb_log_file_size = 64M
innodb_log_files_in_group = 2
innodb_flush_method=O_DIRECT log file sizeと数を減らしてO_を使うDIRECT.再起動後、データベース・サーバは正常に戻ります.オペレーティングシステムDisk Cacheは900 MBに低下し,Swapは200 MB以上を使用したが,swap in/swap out操作は行われず,CPUのIO Waitは2−3%に低下した.
今回のMySQL InnoDBのチューニング経験を通じて、MySQLの公式推薦配置と合わない疑問点を発見し、思考と探求に値する.
1、innodb_flush_methodはO_を使うべきかどうかDIRECT?
すべてのMySQLチューニングの推奨事項は、ハードウェアにプリフェッチ機能がない場合は、O_を使用すると述べています.DIRECTは、O_DIRECTはオペレーティングシステムのファイルシステムDisk Cacheをスキップし、MySQLにディスクを直接読み書きさせた.
でも私の実践ではO_を使わなければDIRECTでは、オペレーティングシステムがinnodbの読み書きキャッシュに大量のDisk Cacheを開発せざるを得ず、読み書き性能を向上させるどころか、読み書き性能が急激に低下した.さらにbuffer poolのデータキャッシュとオペレーティングシステムDisk CacheキャッシュはDouble bufferの浪費をもたらし、明らかに私のこの実践から見ると、浪費が非常に強い.
O_と言うDIRECTによるMySQLのディスクの直接読み書きによるパフォーマンスの低下は、全く杞憂だと思います.JavaEyeのデータベースモニタリングから見ると、Innodbのbuffer poolヒット率は非常に高く、98%以上あり、本格的なディスク操作は微々たるものです.1%のディスク操作でDisk Cacheを得るために、double bufferのメモリ容量の98%を浪費することは、性能的にもメモリリソースの消費からも賢明ではありません.
2、innodb_log_file_sizeはいったい大きいのか、それとも小さいのか.
すべてのMySQLチューニングの推奨事項は、innodb_log_file_sizeは大きいほど良いので、無駄なbuffer poolのflush操作を避けます.
しかし、私の実践ではinnodb_log_file_sizeが大きすぎるとinnodbのlog書き込み操作が著しく増加し、オペレーティングシステムにより多くのDisk Cacheオーバーヘッドが必要になります.
だから私の経験から見るとinnodb_flush_method=O_DIRECTは必須ですがinnodb_log_file_sizeも大きすぎるべきではありません.
出典:
http://robbin.iteye.com/blog/461382