デフォルト設定はNG？MySQLのメモリのチューニング手順
MySQLのメモリの適切なチューニングを実施していない場合、メモリ不足などによりシステム障害を引き起こす可能性があります。特にデフォルトの設定のままにしている場合、バッファーサイズが小さすぎる可能性が高く、早めの見直しが必要です。ここでは、メモリが適切に割り当てられているか確認する手順を解説します。
1．ストレージエンジンの種類の確認
MySQLでは、利用するストレージエンジンによりメモリの使い方が異なります。そのため、MySQLのメモリチューニングを検討する場合、まずはお使いのデータベースで使用されているストレージエンジンの種類を確認する必要があります。
なお、MySQLの場合、ストレージエンジンはテーブル毎に設定可能なため、テーブル毎で使用するストレージエンジンを分けている場合は注意が必要です。 テーブル毎のストレージエンジンは以下のコマンド等で確認可能です。
select table_name, engine from information_schema.tables where table_schema = '[データベース名]';
テーブル毎のストレージエンジンを表示した画面 ここからは、ストレージエンジンが「InnoDB」である場合のメモリチューニング方法について解説します。
2．バッファプールのサイズの確認
ここでは、MySQLのパフォーマンスとメモリ使用に関わる代表的な設定項目であるバッファプール（InnoDBがテーブルとインデックスのデータをキャッシュするメモリ領域）のチューニングについて解説します。
バッファプールは、MySQLの設定ファイルである「my.cnf」（Linux）または「my.ini」（Windows）の中に記載されている「innodb_buffer_pool_size」を変更または追記することでチューニングします。
「innodb_buffer_pool_size」は、バッファプールのサイズを定義するものです。この値のデフォルトは128Mとなっています。
※my.cnfはデフォルトで「/etc」配下、my.iniはデフォルトで「C:\ProgramData\MySQL\MySQL Server x.x\」（x.xはバージョン）に配置されています。
例：innodb_buffer_pool_sizeを1Gに設定する場合
innodb_buffer_pool_size = 1G
一般的に、データアクセスの際に都度データベースにアクセスするよりも、キャッシュから読み込む方が早いため、バッファプールを用意することがパフォーマンスの向上に繋がります。 しかし、この値を大きく設定しすぎると、サーバー本体の空きメモリが不足してパフォーマンスに問題が発生する可能性があるため、一概に大きい値に設定すれば良いというわけではありません。
MySQLのみが稼働する専用のサーバーでInnoDBのみを利用している場合、メインメモリの50～75%程度を割り当てると言われていたり、また、AWS RDSでMySQLを使用する場合のデフォルト値はメインメモリの3/4に設定されています。しかし、アプリケーションのデータ使用傾向や利用頻度により、バッファプールを大きくしてもパフォーマンスが向上しないケースもあります。
データベースのデータサイズのうち、アプリケーションが頻繁に利用するデータ量の割合が高い場合は、キャッシュを多く設定する方がパフォーマンスが向上します。 しかし、データサイズのうちアプリケーションが頻繁にアクセスするデータ量の割合が低い場合については、キャッシュの利用頻度が低いため効果が高くありません。
管理するアプリケーションがどれくらいバッファプールを使用しているかは、以下のクエリで確認できます。
show engine innodb status \G
出力された結果の中から、「BUFFER POOL AND MEMORY」のセクションを参照します。
「BUFFER POOL AND MEMORY」の各種項目を表示した画面様々な項目がありますが、以下の3つに注目しましょう。
- 「Buffer pool size」（バッファプールに割り当てられたページサイズ）
- 「Free buffers」（バッファプールの空き）
- 「Database pages」（バッファプールにキャッシュされたページサイズ）
アプリケーションの高負荷時に上記のコマンドを実行し、「Buffer pool size」のうちの「Free buffers」の割合が小さい場合、割り当てられたバッファが不足している可能性がありますので、「innodb_buffer_pool_size」の値を増やします。
逆に、「Buffer pool size」のうちの「Free buffers」の割合が常に大きい場合は、割り当てられたバッファが大きすぎる可能性がありますので、サーバー本体のパフォーマンスの状況を加味して「innodb_buffer_pool_size」の値を減らすことを検討しても良いかもしれません。
MySQLのパフォーマンス可視化を手軽に実施できるツール
ここまで、MySQLのメモリを手動でチューニングする手順について解説しました。上記の手順の注意点として、管理対象のデータベースが少ない場合は問題ありませんが、組織内でさまざまなデータベースを管理している場合、手間や時間がかかり効率的とは言えません。
ManageEngineでは、データベースのパフォーマンスを可視化して手軽に管理できるツール「Applications Manager」をご提供しています。MySQLのどこがパフォーマンス低下の原因となっているか、グラフや表形式で特定できます。例えば、メモリのチューニングに必要なバッファープールのサイズも簡単に把握できます。また、事前に設定したしきい値に基づき、MySQLに問題が発生した際にメールで迅速に通知を受け取ることも可能です。
Applications ManagerでMySQLのパフォーマンスを可視化した画面 Applications Managerは専門知識や経験が豊富でない方でも操作しやすい管理画面を備えています。ぜひ体験サイトで実際の操作感をお試しください。
インストール不要な体験サイト
ManageEngineのアプリケーション性能監視ツール「Applications Manager」では、ツールの使用感を確認できるGUIベースのサイトを提供しております。制限はありますが、実際に操作いただくことも可能です。
インストールや設定は一切必要ないので、お気軽にお試しください。
