はじめまして。ブログを担当しているNと申します。
ブログ絡みの技術ネタをと依頼をされましたが、
ブログは枯れた技術を多く使っていて目新しいことはあまりないので、
以前行ったチューニング内容について紹介したいと思います。
2008年にブログの記事データについて行ったDB+アプリでのチューニングです。


ブログの記事データはMySQLのMaster-Slave構成で保持していて、
Slaveサーバーをスケールアウトしてブログの閲覧のリクエストを処理しています。
SlaveのMySQLのバージョンは4.1でEngineはMyISAMです。


記事テーブルには以下のようなデータを保持しています。　
　記事ID,ブログID,記事タイトル,日付,テーマ,公開区分,ステータス,･･･


チューニング前の記事テーブルには以下のようなINDEXを張っていました。

Key_name	Seq_in_index	Collation	Index_type
Primary	1	記事ID	BTREE
記事_INDEX_1	1	ブログID	BTREE
記事_INDEX_1	2	日付	BTREE
記事_INDEX_1	3	記事ID	BTREE

チューニング前のアプリは以下のようなSQLを発行して、表示するための項目を1度で取得していました。

SELECT　
　　記事ID,　
　　記事タイトル,　
　　日付,　
　　テーマ
FROM　
　　記事テーブル
WHERE
　　ブログID = xxx AND
　　公開区分 = xxx AND
　　ステータス = xxx AND
　　日付 <= 現在時刻
ORDER BY
　　日付 DESC
LIMIT 0,10

ここで問題になったのが、ユーザー数とPVの増加に伴って、
Slaveのスケールアウトだけではリクエストをすばやく処理できないことでした。
ユーザー数の増加に伴ってデータ量が増加し、見られるページも増えることにより、
range検索を行っている部分でDISK I/Oが増えていき、
それが原因でレスポンスが遅くなり負荷が高騰していました。


そこでDISK I/Oを減らすためにはどうしたらよいかと検討を行い、
explainを取った結果がすべて「Using index」にしたら減らせないかと考えました。
そこでINDEXを以下のように変更しました。


チューニング後のINDEX

Key_name	Seq_in_index	Collation	Index_type
Primary	1	記事ID	BTREE
記事_INDEX_1	1	ブログID	BTREE
記事_INDEX_1	2	日付	BTREE
記事_INDEX_1	3	ステータス	BTREE
記事_INDEX_1	4	記事ID	BTREE
記事_INDEX_1	5	公開区分	BTREE

アプリ側のSQLもINDEXの変更に伴って、以下のように変更

1. where句は変更せずに、selectする項目を記事IDだけに変更

SELECT
　　記事ID
FROM
　　記事テーブル
WHERE
　　ブログID = xxx AND
　　公開区分 = xxx AND
　　ステータス = xxx AND
　　日付 <= 現在時刻
ORDER BY
　　日付 DESC
LIMIT 0,10

2. where句に1で取得した記事IDを指定して、取得した記事IDの数だけ1つずつselectを行う

SELECT
　　記事タイトル,
　　日付,
　　テーマ
FROM
　　記事テーブル
WHERE
　　記事ID = xxx AND
　　公開区分 = xxx AND
　　ステータス = xxx AND
　　日付 <= 現在時刻

この変更により同じデータを取得するのに、SQLの発行回数が1回だったものが、1+N回になりました。
その結果、AP-DB間のトラフィックは増えたのですが、
このチューニングによってDBサーバーの負荷については改善されました。
当時の監視状況のグラフが以下になります。

・Load Average


・Disk I/O


・Slow Query


APサーバーの当時のグラフが残っていないのですが、
SQLの発行回数が増えても、DBのレスポンスが向上したことによって、
APサーバーには何も問題が起こらなかったことを覚えています。


また、このチューニングを行って以降、
現在まで記事データの取得でブログの閲覧に問題が出るようなものは起こっていません。
さらにMySQLのバージョンを4.1→5.1に変更するなどして、
同じ構成での性能アップを図っているところです。