たくさんのデータをINSERTする際の所要時間について思うところがあったので調べてみました。今回のテーマは「インデックス」です。
INSERTする際にINDEXを構築しながらデータを登録していくわけですが、これが予め並んでいれば、INDEX生成にかかる処理負担が減るのではないか、という発想です。シンプルに、以下のテーブルに対して、ランダムな値のINSERT文を生成し、(1)ランダムに発生したままのもの (2)カラム1の値でソートしたもの の2つについて所要時間を計測しました。
テーブルは、2カラムを持ち、1カラム目にプライマリーキーが設定されているだけの非常にシンプルなものです。
CREATE TABLE tb02a ( col1 varchar(128), col2 varchar(128), PRIMARY KEY (col1) );
- 件数
- 1万、2万、5万、10万、50万、58万(^^;) 件
- (本当は100万件の確認をしようとしたのだが、なんとランダムに発生させた文字列で duplicated key が発生しました。約58万件のところで。なのでここまでの測定値になります。エラーで終了してしまったことでcommitされていませんので、この部分は参考値程度ということで。
- トランザクション
- autocommit=0 にしてINSERT開始、全件INSERT完了後に一度だけ commit。
- INSERT例
INSERT INTO tb02a (col1, col2) VALUES ('x5owlho7pjIEMRU9mDP3kpbT6fRypHmmOqY9jUnjCHSnjxMhbtF2rRepc', 'Eatgr09HoIPZgiYEieLSgSt9K'); INSERT INTO tb02a (col1, col2) VALUES ('r9ARbXJI2NyPt9IAHSfL8ygnOmK_2tCgW98kgBLx5HJ_JO57gVvWZ8bNMc2Qmy1', 'zAnUqrL5lAttM0ank3T3TG2iTc9IxHDxaUJTcDpXAc7f5'); :
-
- カラムの値は 6-100バイトでランダムに生成
- 手順概略
- 上記のようなINSERT文が件数ぶん(1万, 2万, ...)記述されたファイル1を作成
- ファイル1を col1 の値でソートして、ファイル2を作成
- ファイル1とファイル2をそれぞれ mysql コマンドに喰わせて所要時間を測定。 測定は各3回行い、平均値を取った。
結果
件数(件) | unsorted(秒) | sorted(秒) |
---|---|---|
10,000 | 0.7453 | 0.7373 |
20,000 | 1.4397 | 1.4750 |
50,000 | 3.6003 | 3.7240 |
100,000 | 7.3470 | 7.5127 |
500,000 | 345.7677 | 39.6117 |
580,357 | 385.6767 | 41.5597 |
感想
- 今回の実験条件では、10万件程度まではソート済でも未ソートでもそれほど時間がかわらない。
- 50万件になって途端に大きな差がでる。やっぱりソート済だとはやく登録できる!
- ここの間の変化が唐突なので 20, 30, 40万件について追試を行いたい。
- この差がなぜ発生しているのかはまだ考察していない。思うところあればぜひご意見、追試、ご情報などをいただきたい。
- まったくの根拠レスに言うと、なんかメモリに入らなくなっちゃう閾値を超えたのかなーという印象を持っています。ランダムINSERTの場合。
基本情報
主な mysqld情報およびパラメタ情報は以下のとおり。
innodb_buffer_pool_size = 600M innodb_additional_mem_pool_size=20M innodb_log_buffer_size=10M innodb_log_file_size=40M key_buffer_size = 256M max_allowed_packet=64M innodb_flush_method=O_DSYNC innodb_data_file_path=ibdata1:1024M;ibdata2:1024M;ibdata3:1024M
- マシンスペック
Xeon CPU 3060 @ 2.40GHz Memory 2.0GB
謝辞
本測定作業に当たっては、(株)IMJモバイル様の各種検証用マシンを、他の方が使用していない空き時間に利用させていただきました。 測定にはある程度のまとまった時間が必要となります。長時間のまとまった時間を測定のためにマシンを使用させていただいたことに、感謝いたします。 ありがとうございました。 もうちょっと色々測定したいのでしばらくお世話になります m(_ _)m