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/