MySQL と SQLite の差を体感

とうとう SQLite では手に負えない案件が生じてしまった。
データの件数は40万件程度。先日は400万件を扱って無事だったので、単にデータ件数だけの問題ではなく、インデックスだとか、テーブル設計だとかの総合的な問題だと思われる。

状況は以下のとおり。
元々は COBOL で設計してあった横に長~いテーブル。1レコード1KByteを超えるような長さ。これで40万件ほどのデータが手に入った。
こいつを Access + SQLite で管理しようとして、テーブル構造そのままに CSV 経由でインポート。
検索パターンを考慮して、5つほどインデックスを作成してやった。

これで、インデックスを使って集計関数を動かす分には、さすが SQLite という性能を発揮してくれたのだが、問題は、SELECT 文で、テーブル内のデータ、例えば漢字氏名だとか郵便番号だとかを直接参照しようとした場合。
インデックスは効いているはずなのだが、表示がとにかく遅い。
DBファイルを頭から全部読んでるんじゃないかというくらいにディスクアクセスが続く。
そしてやっと答えが表示された頃には...
PCのメモリを、仮想メモリまで含めて使い切ってしまっているらしく、いかにもメモリがディスクとの間でスワップしてますといった感じで、SQLite のみならず、他のソフトウェアまでが鈍重な動きになってしまっていた。
Access のウインドウ一つ開くのに何秒も待たされる。
こうなると Windows XP はもうだめで、開いているアプリケーションを全部閉じてしまっても、メモリのスワップまでは解消されない。再起動するまで、のろのろ OS になってしまう。
SELECT 文を発行するたびに、パソコンを再起動しなければいけないなんて...

SQLite のドキュメント類を読み漁ってみたが、スワップを回避する方法が見つからない。逆に「バッファはDB以下のサイズにはできません」と、PRAGMA 文の解説の中に書いてあったのが見つかり、状況はほぼ絶望的になってしまった。

趣味でやっているのなら、意地で SQLite のチューニングに取り掛かるところなのだが、業務の場合は時間との戦いでもあるので、できれば使いたくはなかったが、ここは SQLite の次に使い慣れた MySQL を投入。
MySQL なら、バッファを小さく設定しておけば、それを超えるメモリが必要になった場合、自動的に一時ファイルに書き出してくれる。
SQLite のように、何がなんでも OS からメモリを持ってこようとすると、OS のメモリスワップを使うことになり OS 全体のパフォーマンスに影響が及ぶのだが、MySQL のように、アプリケーションがアプリケーションの権限で一時ファイルを使うのであれば、OS のパフォーマンスにまで影響が及ぶことはない。
MySQL からの応答は遅くなるかも知れないが、他のアプリケーションはきびきび動く。

そして、この手で、本日の問題は無事解決することができた。

スペックに余裕の無い PC では、プロセスが上がりっぱなしになる MySQL のほうが不利ではないかと当初は思われていたのだが、大きな DB を扱う際には、意外なことに、このあたりのチューニングのできる MySQL のほうが有利だったようだ。

ブログ気持玉

クリックして気持ちを伝えよう!

ログインしてクリックすれば、自分のブログへのリンクが付きます。

→ログインへ

なるほど(納得、参考になった、ヘー)
驚いた
面白い
ナイス
ガッツ(がんばれ!)
かわいい

気持玉数 : 4

なるほど(納得、参考になった、ヘー) なるほど(納得、参考になった、ヘー) なるほど(納得、参考になった、ヘー) なるほど(納得、参考になった、ヘー)

この記事へのコメント

この記事へのトラックバック