こんにちは、株式会社ビービットの玉越です。

ｙｏｋａさん、ありがとうございます。

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

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

128MB/2時間=18KB/secとなるのは再度実際の値で計算してみました。
もちろん、128MBの部分はサーバへのテキスト転送量なので、実際に書き込まれ
るデータ量とは異なるでしょうが、参考値にはなるかと思います。

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

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

何か知見をお持ちの方、調査方法をご存知の方はご教示いただけますと幸いです。


On Thu, 17 May 2007 23:20:46 +0900
"Yoshio Kawano" <kawano@xxxxxxxxxx> wrote:

> お世話になっております。
> ｙｏｋａです。
> 
> 見当違いな回答かもしれないので、
> 参考にならないかも知れません。
> 
> 私も、大量なデータを処理した時に、速度の壁にぶち当たりました。
> 結果、逐次、ｍｙＳＱＬのテーブルに登録する処理を止めて、
> 一時、ファイルに出力し、溜まった時点でｍｙSQLのテーブルにロードする方法に切
> 替ました。
> 1レコードを書き込むのに、Insert構文だと、0.01msかかっていたものが、
> ファイルの出力にすると、0.003以下に下がったと思います。
> 
> ファイルの構成は、書き込むテーブルの構成と同じにしておき
> mysqlimportコマンドが使えるフォーマットにしておきました。
> 
> 統計を計算する場合、
> 　１）ファイルをソートし
> 　２）コントロールブレイク方式での演算
> で行いました。
> 
> テーブルに登録する前に、データを計算してから登録するので
> かなり早くなりました。
> 
> ----- Original Message ----- 
> From: "Hiroki Tamakoshi" <hiroki.tamakoshi@xxxxxxxxxx>
> To: <ml@xxxxxxxxxx>
> Sent: Thursday, May 17, 2007 9:01 PM
> Subject: [mysql 13957] 高速なデータの書き込み方法
> 
> 
> > こんにちは、株式会社ビービットの玉越です。
> >
> > 長文で失礼致します。
> >
> > データを高速に書き込む方法についてご相談させて下さい。
> > 方法でなくても、○○を調べてみてはどうか、というご助言だけでもいただけま
> > すと本当に助かります。
> >
> > まず、経緯を最初にご説明します。
> >
> > 現在、Webのアクセスログの解析データをMySQLに格納しています。
> > サイトアクセスの数や、サイト閲覧者が検索エンジンで検索してからサイトへ来
> > たときのその検索キーワード一覧などを毎時ごと、毎日ごと、毎週ごと・・・に
> > 記録しています。
> >
> > もちろん、閲覧したい条件を指定してSELECT COUNT(*)すれば結果は得られるの
> > ですが、ログは毎秒どんどん溜まっていき、数千万行にもなりますので、結果が
> > 返ってくるまでに時間が相当かかります。
> >
> > 結果をすぐに知るために、想定される条件それぞれについて予め計算しておき、
> > 計算結果をMySQLに格納しています。
> >
> > 計算するごとに書き込むと遅いので、ある程度の量のログを計算しておき、計算
> > 結果が溜まったらそれを
> > 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ですから、いくらなんでもディスクアクセスに時間がかか
> > りすぎています。
> >
> > 何を見直せばよいのか分からず途方にくれているのですが、何か検討すべき点は
> > ありますでしょうか？
> > テーブルの断片化、インデックスの張り過ぎ、etc...
> >
> > 下記に現在の構成を記述致します。
> >
> > よろしくお願い致します。
> >
> >
> > ■ハードウェア
> > ・サードウェーブ
> >   ExPrime Server
> >   T-75-ECO
> >   D-XELT2SATA-SU7520
> >
> > ・Intel Xeon LV 2GHz
> > ・メモリ 4GB
> > ・HDD 1.5TB(RAID5構成)
> >
> > ■OS
> > ・Fedora Core 5（5の最新版にはアップデート済み）
> >   kernel-2.6.18-2257
> >   ファイルシステムはXFS
> >
> > ■MySQL
> > ・5.0.27
> >   ストレージエンジンはInnoDB
> >
> > ■my.cnfパラメータ
> > [mysqld]
> > datadir=/var/lib/mysql
> > socket=/var/lib/mysql/mysql.sock
> > old_passwords=1
> > delayed_queue_size=32M
> > max_connections=2000
> > sort_buffer_size=64M
> > myisam_sort_buffer_size=64M
> > key_buffer_size=64M
> > query_cache_size=128M
> > bulk_insert_buffer_size=128M
> > max_allowed_packet=128M
> > table_cache=512
> > thread_cache_size=16
> > default-character-set=utf8
> > character-set-server=utf8
> >
> > skip-innodb-doublewrite
> > innodb_file_per_table
> > innodb_buffer_pool_size=1G
> > innodb_additional_mem_pool_size=128M
> > innodb_log_file_size=500M
> >
> > [mysql.server]
> > user=mysql
> > basedir=/var/lib
> > default-character-set=utf8
> >
> > [mysqld_safe]
> > log-error=/var/log/mysqld.log
> > pid-file=/var/run/mysqld/mysqld.pid
> >
> > [client]
> > default-character-set=utf8
> >
> >
> > --
> > 株式会社ビービット　玉越 大輝
> > ユーザビリティ コンサルタント
> > beBit,Inc. Tamakoshi Hiroki hiroki.tamakoshi@xxxxxxxxxx
> > --------------------------------------------------------
> > 〒105-0001 東京都港区虎ノ門1-18-1 虎ノ門10森ビル7F
> > TEL: 03-3509-7602 / FAX: 03-3509-7605
> > URL: http://www.bebit.co.jp/
> > --------------------------------------------------------
> > ◆◆◆お知らせ◆◆◆
> > ・ビービット新刊書籍 『ユーザ中心ウェブサイト戦略』発売
> >   http://www.bebit.co.jp/news/2006/book.html
> >   http://www.amazon.co.jp/gp/product/4797333529/
> >
> >
> 
> 

--
株式会社ビービット　玉越 大輝
ユーザビリティ コンサルタント
beBit,Inc. Tamakoshi Hiroki hiroki.tamakoshi@xxxxxxxxxx
--------------------------------------------------------
〒105-0001 東京都港区虎ノ門1-18-1 虎ノ門10森ビル7F
TEL: 03-3509-7602 / FAX: 03-3509-7605
URL: http://www.bebit.co.jp/
--------------------------------------------------------
◆◆◆お知らせ◆◆◆
・ビービット新刊書籍 『ユーザ中心ウェブサイト戦略』発売
  http://www.bebit.co.jp/news/2006/book.html
  http://www.amazon.co.jp/gp/product/4797333529/