スレッドメモ

◆ [mysql] 高速なデータの書き込み方法

[13957] Hiroki Tamakoshi (5/17)

計算するごとに書き込むと遅いので、ある程度の量のログを計算しておき、計算
結果が溜まったらそれを
INSERT ... VALUES ..., ..., ... ON DUPLICATE KEY UPDATE a = VALUES(a)+b
を使用して一気に書き込むようにしていますが、これが遅くて困っています。

上記構文を利用している理由は、既に同じ条件での計算結果があればそれをイン
クリメントして追加する必要があるためです。
また、INSERT ... VALUES ..., ..., ... により一度に大量のデータを書き込む
ことができます。

ところが、この1文の実行に2時間以上かかっている場合があります。その間、ディ
スクへのアクセス音が聞こえ続けますので、本当に書き込みに時間が掛かってい
ると思います。

VALUES ..., ..., ...の部分は多くても128MBです。
（bulk_insert_buffer_size=128Mにしています。あふれたらエラーになるはずで
すが、エラーになっていません）

128MB/2時間=18KB/secですから、いくらなんでもディスクアクセスに時間がかか
りすぎています。

[13958] Yoshio Kawano (5/17)

私も、大量なデータを処理した時に、速度の壁にぶち当たりました。
結果、逐次、ｍｙＳＱＬのテーブルに登録する処理を止めて、
一時、ファイルに出力し、溜まった時点でｍｙSQLのテーブルにロードする方法に切
替ました。
1レコードを書き込むのに、Insert構文だと、0.01msかかっていたものが、
ファイルの出力にすると、0.003以下に下がったと思います。

ファイルの構成は、書き込むテーブルの構成と同じにしておき
mysqlimportコマンドが使えるフォーマットにしておきました。

統計を計算する場合、
　１）ファイルをソートし
　２）コントロールブレイク方式での演算
で行いました。

[13959] Hiroki Tamakoshi (5/18)

私も、一旦ファイルに書いたものをLOAD DATA INFILEすれば速いかと思ってはお
りました。ただそれを採用していないのは、同じ条件であれば足し込む必要があ
るためです。

LOAD DATA INFILEにはIGNOREとREPLACEのオプションがありますが、UPDATEも欲
しかったところです。

実測すると、7.5MB〜16.4MBを100秒〜180秒で書き込んでいるので、書き込み速
度は80KB/sec〜140KB/secです。

ディスクはSATAでRAID5を組んでいるのですが、やはり遅すぎではないかと思い
ます。シークが大量に発生しているのでしょうか？

[13960] HIRATSUKA Sadao (5/18)

いくつかデータを作って実験してみたところ、興味深い結果がでました。

INSERT ... VALUES ..., ..., ... ON DUPLICATE KEY UPDATE ...のSQLは、
「VALUES ..., ..., ...」←ここで一度にバルクINSERT (or UPDATE)する行数の
二乗に比例した時間がかかります。

レコード長100バイト、13万レコードのテーブルで
INSERT ... ON DUPLICATE KEY UPDATEをすると、
一度に処理する行数に対して処理時間が以下のようになりました。

125行：0.06秒
250行：0.26秒
500行：1.01秒
1,000行：4.04秒
2,000行：16.09秒
4,000行：64.00秒 (このときのテスト用SQL文は453KB)

I/Oも少しは出ていますが、こちらの環境ではほぼCPUネックです。
きっと内部アルゴリズムがとても残念な実装になっているのだと思います。

[13961] とみたまさひろ (5/19)

> ディスクはSATAでRAID5を組んでいるのですが、やはり遅すぎではないかと思い
> ます。シークが大量に発生しているのでしょうか？

今回の遅い原因がディスクのIOネックかどうかはわかりませんが、データベー
スのように書き込みが多いデータは RAID5 には置かない方がいいんじゃない
かと思います。

[13962] 坂井 英夫 (5/19)

Hot swap のRAID5構成にすると、システムダウンも最小限で食い
止められ
データのBack up にもなるように思っています。

MySQL とは若干関係なくなるような気もしますが、
もう少し説明していただけると助かります。

[13963] とみたまさひろ (5/19)

一般に RAID5 は書き込みが遅いためです。ディスク容量に余裕があるのであ
れば、RAID1 とか RAID0+1 の方が良いと思います。

あとディスク故障時のパフォーマンスが大幅に低下します。私が知っている事
例では、１本のディクス故障時にアクセス速度が正常時の 1/10 程度にまで落
ちました。

これは何年も前の話ですし、ストレージ製品によりピンキリだと思うので、
一概には言えないとは思いますけど…。

[13965] kurokawa (5/19)

個人的にはRAID5が一番好きで10年以上前から愛用していました。

他のRAIDと比較した場合のマイナス点

・書き込み：遅い
・読み込み：やや遅い
・初期構成：遅い〜非常に遅い
・故障時再構成：非常に遅い
・信頼性：印象ほど良くない（RAID2以降とあまり変らない）
・コストパフォーマンス：大規模構成でないと悪い
　（HDD10本以上構成などで無いと価格優位性が出にくい）

RAIDが必要な状況で考えますと、故障したときの復旧が遅い
というのは致命傷になりがちです。

復旧処理（再構成）しつつ運用できますが、データ処理速度の
劣化がひどく、使えない状況になりやすいです。
それ以上に、もう一台HDDが壊れると、全データ消滅の恐れもあり
怖くて運用できなくなります。

RAID装置大手のnetapp（ネットアップ）では、RAID4構成
を採用しています。
これは、RAID5に近いのですが、パリティー情報を
特定のHDDに保存します。

パリティー情報格納ディスクが壊れると、非常に危険なので
推奨はダブルパリティー方式（オプション）
パリティーディスクのみをミラーリングします。

大容量と安全性を求めるならnetapp方式で、それをさらに
多重化する（必須かと思います）。
netappでは、データ変更量が、オンメモリーで処理できる
量以内であれば、非常に高速でした。
それを超えてしまうと、極度に速度が低下します。

メモリー量で価格が倍倍ゲームになっていきますので
事前の設計が非常に重要です。



ところで、SATAは、メインのRAIDには、普通選びません。
RAID構成以前の問題になります（速度を問題にするなら）。

RAID構成でSATAを選択する場合、バックアップ用など
速度は二の次、コストと容量を確保したい場合に
選定します。
ファイバーチャネルか、悪くてSCSIになると思います。

ATAやSATAをメインのデータ処理用に選ぶ場合、
回転速度（シークタイムと処理速度がともに遅い）が
遅いので、RAID0で高速化をはかり、それをRAID1構成に
して、安全性を図るのが適しています。

[13969] Kenji Irie (5/20)

でも相変わらずDB向けにもRAID5って提案されているんですね。
(どの点を重視するかで変わるとは思いますが…)

読み出し量が膨大で更新量の少ないwebコンテンツとかであればRAID5も有効だと思い
ます。

DBの場合って、常に書き込みが伴ってきますから(SELECTで読み出す時にも結局は書
き込みを行っていますよね?)書き込みの遅いRAID5はデメリットばかりがすごく強調
されると思いますがいかがでしょう?

個人的には↓の構成がアクセス性能的+信頼性には最適かと

・RAID1+0、RAID1の構成を元にRAID0を構成

これをn本で構成した場合
最低は1本、運が良いと(汗)n/2本までの故障に耐えられますからね。