「ウェブページに掲載されている広告のインプレッション数とクリック率を計測する」という案件を行うことになった。環境は、既に稼動しているIA32(Pentium!!!)シングル・メモリ512MB・linuxの計算機上で、Postfixメールサーバ+apacheウェブサーバ+MySQLサーバとして使用されている。(かなり以前に、こちらで納品したもの。定期的にアップデートは行われている)。
その環境では、ウェブサービスの一部でPHP+MySQL(innodb)が使用されている。そこに、今度はウェブページを表示させるたびに計測をさせることになるので、いくら軽快・高速なMySQLとはいえ、一日に何十万ものリクエスト毎に動作させるためには、それなりの変更が必要になるはず。
とりあえず、ハードウェアのアップグレードは不要と踏んだ(MySQLを使ってきた勘と、今回の処理内容を考えてみて、まず内部を見直してから、という判断)。
最初はデータベースの設計とプログラミング。要求がそれほど難しくないので、テーブル設計とクエリをきちんと最適化して行うことに骨は折れない。テーブル設計(インデックス含む)はもとより、データベースに対する要求とMySQLの都合にあわせた、レスポンスの良いクエリを送ることも重要。ちなみに、今回はMyISAMでデータベースを設計。データベースに情報が蓄積されていくだけのものなので(＝あとで変更等はされない)、MyISAMを採用。テーブルロックはガンガン掛かるだろうが、ロック解除待ちが入ってもアクセスする方には感じられない程度のものだろう。
次は、my.cnfを見直してチューニングポイントをセット。MySQLのチューニングは、いわゆる「お手本」的な考え方はあるものの、実際に設定する数値(メモリ割り当てなども)は、実際の稼動状態に合わせなくてはならない。動作を理解していないと、無駄なメモリを割り当ててリソース不足に陥り、逆にパフォーマンスを落としてしまったり、お手本にある通りの数値をそのまま書いてしまって、チューニングをしたつもりが効率の良さに全く寄与していなかったりと、いいことがない。
今回は、MySQLへの要求が大幅に増えるために、MySQLにはメモリの効率的利用をさせなければならない。
- key_bufferを増やす。インデックスをきちんと使っているデータベースならば、定番。
- innodbのテーブルサイズを見てみて、そこに割り当てるメモリを減らした。スレッドあたりのメモリ消費量を、パフォーマンスに影響を与えないところまで削減
- MySQLのスレッドが多数走るだろうから、スレッドあたりのメモリ容量・スレッドキャッシュ数などを見直したい。実際に走らせて見て、一番リクエストが集中する時間帯に、どの程度のスレッドが同時利用されるのかを見てから設定。
- スレッドごとの各バッファサイズを見直す。これは、プログラムの実装とクエリの内容がわかっているので、それにあわせる。主な動作は、(1)ウェブページへのリクエストによるINSERT/UPDATE・こちらはインデックスを使って軽く処理が出来るが、要求数は多い(2)案件のクライアントが様々な条件でデータ統計を見る・こちらはテーブルスキャンを行うケースがあるが、要求数は一日１回程度。この動作状況を考え、(1)のレスポンスと消費メモリを優先して、そちらに合わせてバッファサイズを小さく設定、実装メモリ内で並行した多数の要求が受け付けられるようにする。(2)では、なるべくMySQLの関数やソートを使ってやらせないで、取得したデータをPHPの方で要求に応じて出力を行う。これをポイントとした。
こんな感じで実稼動させながら何度かチューニングをしてみて、MySQLの状態とシステムの状態を平行して観察。MySQLのスレッドがシステム全体のメモリを圧迫している様子もないし、レスポンスも軽快そのもの。ということで、相当ロースペックなマシンで要求に応えられた。クライアントには詳しいことは解らないだろうけれども、なんとなく満足。