玩式草子─ソフトウェアとたわむれる日々

第33回 SQLiteでRDB再入門[その3]

この記事を読むのに必要な時間:およそ 7.5 分

この話題を取り上げ始めたのは,2月末のまだまだ寒い時期でしたが,あっと言う間に季節は巡り,桜の花も過ぎて,初夏の陽気すら感じる季節になりました。

過去2回では,依存関係情報やそれをSQLiteを使ってデータベースする際の仕組みなどを実際のスクリプトを紹介しながら説明してきました。今回はこの話題のまとめとして,説明してきたスクリプトを実際に使う際に生じた問題点やスクリプトの使用例などを紹介してみます。

依存関係情報収集スクリプトとSQLiteの罠

前回作成した2つのスクリプトをまとめて,⁠依存関係情報を集めるスクリプト」の意味でget_depends.pyという名称にしてみました。

さっそく,このスクリプトで手元の環境の依存関係情報のデータベース化を試したところ,処理にずいぶん時間がかかります。依存関係情報の検索範囲をファイルシステムの一部に限ってみても,検索,登録作業に3~4時間はかかるようでした。しかも,その時間のほとんどを費しているのがSQLiteのデータベースに依存関係を登録する処理で,出力されるメッセージを見る限り,前回紹介した1レコード4カラムの単純なデータすら,1秒あたり数件程度しか登録できないようです。

気軽に使えるのが特徴だとしても,データベースシステムの本質とも言えるデータの登録作業にこんなに時間がかかるようでは,とても実用的なシステムには使えないなぁ…,と失望しかけましたが,Google等で調べてみると,SQLiteのデータ登録(インサート)は,インサートごとにトランザクション処理が行われるので時間がかかる,といった情報が多数見つかりました。

この問題はSQLiteのホームページのFAQにも言及されていました。

[原文]

(19) INSERT is really slow - I can only do few dozen INSERTs per second

Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second.

Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe. For details, read about atomic commit in SQLite..

By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.

[拙訳]

(19)インサートがきわめて遅い - 1秒あたり数十件のインサートしか行えない

実のところ,SQLiteは,平均的なデスクトップPC上で50,000件/秒以上のインサートを容易にこなしますが,トランザクション処理が1秒あたり数十件しかできないのです。トランザクションの速度はHDDの回転速度に依存します。トランザクションには,通常,HDDの記録面が2周する必要があります。そのため7200RPMのHDDでは(1分あたり7200回転=1秒あたり120回転なので)1秒あたりに可能なトランザクションの回数は60回に制限されます。

トランザクションの速度がディスクドライブの速度に制限されるのは,SQLiteは(通常では)データが正しくディスク上に書き込まれたことを確認するまで,処理の完了を待つためです。こうしておけば,突然電源が切れたり,OSがクラッシュしたとしてもデータは安全ですから。詳細については,SQLiteのatomic commitについて調べてください。

デフォルトの設定では,インサートはそれぞれがトランザクション処理になります。しかし,複数のインサートをBEGIN...COMMITで囲めば,その中のインサートは1つのトランザクションにまとめられ,その結果,各インサートにかかる時間を大幅に改善することが可能です。

なるほど,SQLiteの場合,デフォルトでは1つのインサートが1つのトランザクションと扱われるので,大量のデータを1つずつインサートしようとすると長い時間がかかってしまうようです。

「トランザクション処理」とはデータベースの内容の整合性を保つための機能で,一連のデータの書き込みや削除などを一つの操作としてまとめ,もしその操作が途中で異常終了した場合,データベースを操作前の状態に戻す(ロールバック)ことで,データベースの内容に矛盾が生じることを回避する仕組みです。

上記FAQによると,複数のINSERT文をBEGIN...COMMITで囲めばいいそうですが,そのような処理はPythonではどうするのだろう…,と思ってSQLite3モジュールのドキュメントを読み返してみると,executemany()という関数が用意されていました。この関数を使えばタプルのリストに対してまとめてSQL文を実行することができそうです。

そこで,SQLiteのデータベースに登録するinsert_db()の部分を,executemany()を使うように修正したinsert_db2()を用意しました。

リスト1 insert_db2()

def insert_db2(dbname, t):
    conn = sqlite3.connect(dbname)
    try:
        print "inserting ", t
        conn.executemany('insert into depends values(?, ?, ?, ?)', t)
        conn.commit()
    except sqlite3.Error, e:
        print "An error occurred:", e.args[0]
        conn.rollback()

insert_db2()は,従来のinsert_db()のように1つのタプルごとにインサートを実行するのではなく,複数のタプルを集めたリストを受け取って一気にインサートするので,この関数を呼び出す部分も登録すべきデータ(タプル)をいったんリストに集めた上で渡すように修正しました。

リスト2 insert_db2()の使い方

    list = []
    for file in files:
        base = os.path.basename(file)
        tmp = get_depends(file)
        for i in tmp:
            (soname, realname) = split_parts(i)
            print("{0}, {1}, {2}, {3}".format(base, file, soname, realname))
            t = (base, file, soname, realname)
            # insert_db(dbname, t)
            list.append(t)

    insert_db2(dbname, list)

この改造でどれくらい処理速度が向上するかを確認するために,調査範囲を/usr/bin以下に限定してtimeコマンド経由で実行したところ,表1のような結果になりました。ざっと見で,38分かかっていた処理が30秒弱にまで改善したようです。

表1 insert_db2()の効果

 1つずつinsertまとめてinsert
実時間(real)38m4.179s0m27.210s
ユーザ時間(user)0m13.019s0m5.773s
システム時間(sys)0m19.659s0m5.293s

登録処理が終了したデータベースを調べると,22,524件のデータが登録されていましたので,この結果から計算すると,1つずつインサートした場合は1秒あたり9.8件まとめてインサートした場合は1秒あたり828件登録できたことになります。

この結果を見ると,FAQにもあるように,SQLiteではインサート時のトランザクション処理が律速段階になっていて,データをまとめてインサートすることが処理速度を向上させる鍵になっていることがわかります。このあたりは,同じRDBソフトウェアでもPostgreSQLやMySQLには無かった特徴で,SQLiteを利用する際には注意すべきポイントになるでしょう。

一方,get_depends.pyスクリプトのうち,指定したディレクトリ以下からELF形式のバイナリファイルを探す処理についても,/usr/include/や/usr/share/等,バイナリファイルが無いことがわかっているディレクトリはあらかじめ検索対象から省くようにして,検索速度の向上を目指しました。

これらの改造の結果,当初は5~6時間以上かかっていた,22万件のデータを収めた依存関係情報データベースの作成が,5分前後で終了するようになりました。これくらいの速度ならば,開発作業の合間に最新の情報に更新することも容易でしょう。

このスクリプトの完成版は筆者の日記のページに添付ファイルとして置いています。同じようなコードを再掲するのも何なので,興味ある人は上記サイトからご入手ください。

著者プロフィール

こじまみつひろ

Plamo Linuxとりまとめ役。もともとは人類学的にハッカー文化を研究しようとしていたものの,いつの間にかミイラ取りがミイラになってOSSの世界にどっぷりと漬かってしまいました。最近は田舎に隠棲して半農半自営な生活をしながらソフトウェアと戯れています。

URLhttp://www.linet.gr.jp/~kojima/Plamo/index.html

コメント

コメントの記入