kuroの覚え書き

96の個人的覚え書き

速度アップにはマシンパワー増強とインメモリデータベースの利用で

現状フルサイズ350カラム、実際に運用時30カラム程度で、165万件くらいのデータベースなんだが、ほぼすべてのカラムがTextとして構築されているためにMySQLに乗り切らないことは前に書いた。

で、どうしてTextなんだということだが、要するにデータ解析の過程でno dataなところにNAもしくは . が入力されているためなんだが、だったらこれらを空白に置き換えてしまえば、データも圧縮され、Queryの速度も上がるんじゃない?

ということでやってみた。データベースから一旦textファイルに書き出し、sedでNAと. を取り除くことにする。

sed s/$'\t'NA/$'\t'/g exome.bak > exome_edit1.txt
sed s/$'\t'$'\.'/$'\t'/g exome_edit1.txt > exome_edit2.txt

TAB区切りテキストなのでNA....という形になっているのでNAをに置き換えることにする。NAで始まるコメントの欄があるとまずいかもしれないが、多分ない。同じように.も消す。
で、tableの設定も該当箇所をStringからFloatもしくはIntegerに書き換えて、インポートした。
で、諸々の設定を新しい設定に合わせて書き直し、データベースにアクセスしてみると・・・

全然速度変わらんし。

ファイルサイズは5GBから4.3GBくらいに下がっているので、全く意味が無いわけではないが。あと、350カラムあってもMySQLに構築できたので、環境移行するなら意味はあるかもしれない。

とはいってもそもそもMySQLSQLiteでは下手するとSQLiteのほうが軽快に動作するので、無理にMySQLに移行しなければならないという理由はない。MySQLだとMySQL Workbench等データベースの汎用フロントエンドが使いやすいかな?というレベルなので、メンテナンスツールをちゃんと作り込んでおけばSQLiteのほうが優位性があるように思う。

で、優位点の一つとしてSQLiteにはインメモリーデータベースというのがある。データベースをHDDに置くのではなく、メモリー上に作成して高速に検索するという機能である。
そのままではSQLiteをシャットダウンするとデータも消えてしまうが、どうやらHDDにおいたデータを最初にメモリ上にまるっとコピーして、リードオンリーで検索をかけて結果を表示させることが可能なようだ。データベースのメンテナンス時はやはり時間が掛かるが、一度構築してしまえば、セッションの最初に読み込みに時間がかかったとしても、その後の検索はサクサク行くはずだ。

http://labo.utsubo.tokyo/2017/02/23/sqlitepythonからインメモリで使う/

このサイトによると2倍以上高速化するらしいから、是非試してみようと思う。