Symfoware

Symfowareについての考察blog

PostgreSQLのUNLOGGED TABLEで、データ登録の高速化

例えば、テキストファイルに出力したログを解析する際、
一旦データベースに格納してSQLで抽出したい場合。

データの一貫性よりも、とにかくテキストから高速にテーブルへ登録したいと思います。

PostgreSQLにはトランザクションログ (WAL)の出力を抑止して、高速化する
「UNLOGGED TABLE」があるのを知りました。

PostgreSQL 9.1 の新機能

通常のテーブルとどのくらい違うか試してみます。



テーブルの作成



FreeBSD 10にインストールしたPostgreSQL 9.3.4でテストします。

idというプライマリキーと、text型のフィールドを10個持つテーブルを作成しました。


  1. CREATE TABLE t_normal (
  2. id int NOT NULL,
  3. col1 text,
  4. col2 text,
  5. col3 text,
  6. col4 text,
  7. col5 text,
  8. col6 text,
  9. col7 text,
  10. col8 text,
  11. col9 text,
  12. col10 text,
  13. PRIMARY KEY(id)
  14. )



もうひとつ、同じ形式でUNLOGGEDをつけて作成します。


  1. CREATE UNLOGGED TABLE t_unlogged (
  2. id int NOT NULL,
  3. col1 text,
  4. col2 text,
  5. col3 text,
  6. col4 text,
  7. col5 text,
  8. col6 text,
  9. col7 text,
  10. col8 text,
  11. col9 text,
  12. col10 text,
  13. PRIMARY KEY(id)
  14. )






登録サンプル



Python + Psycopg2でデータを10万件登録するサンプルを作成しました。
Psycopg2のインストールや使い方はこちら。
PsycopgでPythonからPostgreSQL 9.3.2に接続する


  1. # -*- coding:utf-8 -*-
  2. import psycopg2
  3. import datetime
  4. start = datetime.datetime.now()
  5. conn = psycopg2.connect(
  6.     host = "192.168.1.101",
  7.     port = 5432,
  8.     database="sample",
  9.     user="pgadmin",
  10.     password="password")
  11. cur = conn.cursor()
  12. query = u"""
  13. INSERT INTO t_normal (
  14. id, col1, col2, col3, col4, col5, col6, col7, col8, col9, col10
  15. ) VALUES (
  16. %s
  17. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  18. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  19. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  20. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  21. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  22. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  23. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  24. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  25. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  26. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  27. )
  28. """
  29. for i in xrange(100000):
  30.     cur.execute(query, [i])
  31. conn.commit()
  32. cur.close()
  33. conn.close()
  34. end = datetime.datetime.now()
  35. total = end - start
  36. print u"%d.%03d秒" % (total.seconds, total.microseconds / 1000)





通常テーブルとUNLOGGEDテーブルに対してそれぞれ実行してみます。

試行回数通常テーブルUNLOGGED
127.089秒26.112秒
225.910秒26.226秒
326.874秒26.505秒
426.412秒26.147秒
526.616秒25.962秒
平均26.580秒26.190秒


ほとんど差がないです。なんでだ・・・

冷静に考えてみると、「トランザクションログをとらない」というテーブルなので、
一括コミットだと差がないのかも。

ということで、10,000件のデータを毎回コミットする処理に変更してみます。


  1. # -*- coding:utf-8 -*-
  2. import psycopg2
  3. import datetime
  4. start = datetime.datetime.now()
  5. conn = psycopg2.connect(
  6.     host = "192.168.1.101",
  7.     port = 5432,
  8.     database="sample",
  9.     user="pgadmin",
  10.     password="password")
  11. cur = conn.cursor()
  12. query = u"""
  13. INSERT INTO t_normal (
  14. id, col1, col2, col3, col4, col5, col6, col7, col8, col9, col10
  15. ) VALUES (
  16. %s
  17. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  18. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  19. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  20. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  21. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  22. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  23. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  24. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  25. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  26. ,'DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT-DUMMY_TEXT'
  27. )
  28. """
  29. # 登録件数を1万件に変更
  30. for i in xrange(10000):
  31.     cur.execute(query, [i])
  32.     # 毎回コミットするよう変更
  33.     conn.commit()
  34. cur.close()
  35. conn.close()
  36. end = datetime.datetime.now()
  37. total = end - start
  38. print u"%d.%03d秒" % (total.seconds, total.microseconds / 1000)




測定した結果がこちら。

試行回数通常テーブルUNLOGGED
114.223秒6.882秒
214.153秒6.952秒
314.017秒6.956秒
414.311秒6.955秒
513.817秒7.021秒
平均14.104秒6.953秒



通常のテーブルに比べ、UNLOGGEDテーブルは倍近く速くなっています。


UNLOGGEDテーブルは、頻繁にコミットが必要な局面で威力を発揮しそうです。
ちゃんと試してみないと駄目ですね。



【参考URL】

PostgreSQL 9.1 の新機能

CREATE TABLE

datetime - 基本的な日付型および時間型
関連記事

テーマ:データベース - ジャンル:コンピュータ

  1. 2014/06/17(火) 22:13:41|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
<<MongoDB 2.6のmongodb.confを編集し、HTTP Interfaceを有効にする | ホーム | PHPフレームワーク「Phalcon」をFreeBSD 10 + nginxで動かす>>

コメント

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
http://symfoware.blog68.fc2.com/tb.php/1426-b13d9d81
この記事にトラックバックする(FC2ブログユーザー)