同じSQL文で極端に検索が遅くなる時がある

質問

[環境]
OS：CentOS
　　　⇒メモリ：4G
　　　⇒CPU：2コア(種類はわかりませんでした)
DB：MySQL5.1.52
クライアント：Flex
サーバー：php, Apache

お世話になっております。

とあるSQL文を実行すると、1秒で結果が返ってくる時と、
20秒近く結果が返ってこない時があり、
原因が何なのか分からず質問させていただきました。

=以下詳細=

40万件と30万件レコードが存在する2つのテーブルを結合し、
結果を表示しているプログラムがあります。

この実行結果で、1秒で結果が返ってくる時と、
20秒近く結果が返ってこない時があります。
　※全く同じ条件で検索しています。
　※急にデータ量が増えたのではありません。

20秒近く結果が返ってこなくなる時間はだいたい30分くらいです。
30分過ぎると、1秒で結果が返ってくるようになります。
20秒近く結果が返ってこない時間帯になると、
他のプログラムのSQL文にタイムアウトが発生します。

基本的には1秒で結果が返ってきていますので、INDEXは正しく設定されていると思います。
同じ条件のSQL文で処理時間が違う原因として、考えられることはございますでしょうか？

どなたかお分かりになる方いらっしゃいましたら、
ご教示いただければと思います。

よろしくお願い致します。

回答No.4

nora1962 · ベストアンサー

MySQLはQuery Cacheを持っているのでテーブルの更新がない場合、同じクエリがリクエストされた場合は高速に結果を返すことができます。しかし、テーブルの更新があるとキャッシュが効かなくなります。
また、SQLの書き方によってはバッファキャッシュの競合が発生し遅延することも考えられます。
速い時間帯と遅い時間帯で
show processlist;
で接続数を確認してください。
また、CPUやI/Oの待ちを調べるためにはsarやiostatを用います。
http://www.uetyi.mydns.jp/wordpress/linux-server/entry-992.html

お礼

ご回答ありがとうございます。

今回の問題なのですが、調査するだけの
時間がなかったということもあり、
SQL及びテーブル構成の修正という対応を
とることになりました。

せっかくご回答くださってもらって申し訳ないのですが、
これにてクローズとさせていただきます。

他ご回答くださった方々もありがとうございました。
次回こういった問題が発生した際には、参考にさせていただきたいと思います。

回答No.3

yambejp

なんとも言えませんがキャッシュの問題じゃないですか？

explainで有効なインデックスが設定されているか確認しながら
チューニングするしかないと思いますよ

お礼

ご回答ありがとうございます。

インデックスを見直してみます。
また何かわかりましたら、追記いたします。

回答No.2

DIooggooID

ＤＢサーバ上で処理されている、DBMS以外の処理内容（負荷状況）が気になります。

ＤＢサーバ資源の負荷（cpuや、I/O[Disk]等）は、処理時間が短い時、長い時とで
どのようになっていますか？

お礼

ご回答頂きまして、ありがとうございます。

今まさに遅い時間帯で、MySQL以外にタスクは動いていません。
CPUですが、SQL実行時にmysqldのCPU使用率が100％になります。
このSQLが実行し終わると、全体のCPU使用率も1%前後まで下がります。

全体のメモリ使用率は
また、現時点でのmysqldのメモリ使用量はおよそ28％程のままで、
上下しておりません。

↓実際のtopコマンドで取得した内容
top - 16:30:56 up 12:52, 2 users, load average: 0.09, 0.12, 0.08
Tasks: 75 total, 1 running, 74 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.2%us, 0.2%sy, 0.0%ni, 99.5%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 4044540k total, 3299440k used, 745100k free, 190332k buffers
Swap: 4095992k total, 0k used, 4095992k free, 1818508k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ OMMAND
2963 mysql 15 0 1921m 1.1g 5656 S 3.0 28.6 25:14.87 mysqld

I/Oに関してましては、ちょっと確認方法がわからなかったため
現在調査中です。

よろしくお願い致します。

補足

すみません。訂正です。

お礼欄の
>全体のメモリ使用率は
>また、現時点でのmysqldのメモリ使用量はおよそ28％程のままで、
>上下しておりません。
の箇所の、書きかけのところですが、

>全体のメモリ使用量は、４G中およそ３Gになります。
>また、現時点でのmysqldのメモリ使用量はおよそ28％程のままで、
>上下しておりません。
です。

回答No.1

chie65536（@chie65535）

クエリの結果を、バックグラウンドで30分かけて、中間ファイルに作っているのでは？

一旦、中間ファイルが出来てしまえばそれを使うので1秒で結果が返って来るけど、中間ファイルが出来る前にやると中間ファイルを利用出来ないので20秒かかる、と考えれば、つじつまが合います。

お礼

ご回答頂きまして、ありがとうございます。

大変申し訳ないのですが、「中間ファイル」とは
どういったものを差すのでしょうか。

キャッシュやテンポラリとは違うものなのでしょうか。
調べ方が悪いのかもしれませんが、それらしきサイトにたどり着きませんでした・・・。

お手数ですがよろしくお願い致します。