lifehack | Page 17 of 21 | てくめも@ecoop.net

SQLite でパフォーマンスチューニング – インデックス

クエリを実行して、インデックスの利用状況を調べるには、公式サイトからダウンロードできる idxchk を使います。

tclsqlite3 idxchk db_filename ‘SELECT … ‘

結果は次のような感じになります。

table index(es) column(s)
—— ———- ——————————
foo (none)
boo <pk> <integer primary key or ro>

SQLite version: 3.2.8

この場合 boo テーブルの Primary Key がインデックスのように作用していて、boo, foo にはインデックスがない、あるいはこのクエリに影響を与えるインデックスがない、ということです。
参考:
– IdxChk – 英語のオンラインマニュアル

SQLite でパフォーマンスチューニング – クエリのベンチマーク

パフォーマンスチューニングにおいて、クエリの実行時間を調べるのは基本ですが、
SQLite では SQL 文だけでは、かかった時間を調べることができなさそうなのでたとえば次のようにします。

time sqlite db_filename “SELECT …” > /dev/null

結果はこんな感じになります。

real 0m0.239s
user 0m0.050s
sys 0m0.060s

time は AT＆T UNIX 系標準のコマンドで、引数に渡したプログラムの実行時間を出力します。

もちろん、SQLite にかぎらず、どんなプログラムに対しても実行可能です。

参照:
– MANPAGE of TIME

SQLite3.x で COPY を使うとエラーになる

SQLite3.x で

COPY footable from ‘foo.csv’ USING DELIMITERS ‘,’;

などとして csv から読み込もうとすると、

SQL error: near “COPY”: syntax error

となり実行できません。

調べてみると、3.0 以降削除された機能だそうで、公式のバグトラックによると

3.0 以降、不自由ですが COPY コマンドは削除しました。
このコマンドは SQL 非標準であり、UTF-16 での実装方法についてどうすればいいか見出せなかったためです。

とのことでした。

2.x と 3.x を両方いれて、2.x で csv からコピーしてdumpしてやることで 3.x でも CSV の利用は可能です。

$ sqlite /tmp/foocsv.tmp “COPY foo FROM ‘foo.csv’ USING DELIMITERS ‘,'”
$ sqlite /tmp/foocsv.tmp ‘.dump’ > /tmp/foocsv.sql
$ sqlite3 ./applyTo.db < /tmp/foocsv.sql

参考:
– Ticket 982: COPY command does not work in 3.0.8 as it does in 2.8.13
– SQLite が認識できるクエリ言語

テーブルロックの話

テーブルロックをうまく使うと、
・MyISAM でのパフォーマンス向上
・MyISAM で(InnoDB を使わずに)簡易的にトランザクションの一部機能を実現できる
といううまみがある。(InnoDB, BDB だと逆効果になりうる。

テーブルロックの基本的なクエリは次のとおり。

– 読み込みロック

LOCK TABLES foo READ

テーブルの読み込みロッククエリを実行すると、ロック中は現在の接続(と同時に実行中の他の全ての接続)で、指定したテーブル(例では `foo`)の更新が一切できなくなる。
複数の参照系クエリを連続で送る時に、途中でデータが書きかわると困る場合に利用する。サブクエリなどを使って１クエリにまとめてしまっている場合は不要。

別の接続で書き込みロックが発生している場合は、読み込みロックを獲得するまで待ちに入る。
なお、読み込みロックは性質的にパフォーマンスを向上させるものではなさそう。

– 書き込みロック

LOCK TABLES foo WRITE

テーブルの書き込みロッククエリを実行すると、ロック中は現在の接続以外からしか読み書きができなくなる。既にロック(読み/書きいずれか)が発生している場合はロックが獲得できるまで待ちに入る。また、デフォルトで READ よりも優先度が高い。

書き込みロックによってパフォーマンスが向上する可能性があるのは、処理時間の短いクエリを多く実行する場合(ロック解除時にまとめて実行するようになるため)。

更新を１回のクエリでしか実行しないのであれば、書き込みロックは不要。

(MySQL 4.1 公式リファレンスマニュアルより)
LOCK TABLES の使用時にいくつかの面で処理が迅速になる理由は、
MySQL でキーのキャッシュが UNLOCK TABLES が呼び出されるまで
フラッシュされないためである（通常、キーのキャッシュは各 SQL
ステートメントの後にフラッシュされる）。
それによって、MyISAM テーブルに対する挿入、更新、削除処理が
迅速化される。

– ロック解除

UNLOCK TABLES

テーブルロック解除クエリが実行されるか接続が終了した時点で、現在のスレッドが保持している全てのテーブルロックを解除する。
WRITE ロックの場合はこの時点で実際にデータベース上の値を書き換える。

参考:
– MySQL4.1 リファレンスマニュアル: LOCK TABLES および UNLOCK TABLES 構文
– 釣ったよ！釣りとコンピュータ: MySQL InnoDB の利用: テーブル・ロックについて

InnoDB が本当に必要な時とは

行レベルロックが本当に必要な時のみ。
InnoDB を利用し、トランザクション処理を行う場合、MyISAM にはないエラー処理が必要になったり、対処事項が増えたり、MyISAM よりパフォーマンスに劣る面があるという点に留意すること。

InnoDB を利用しなくても済むような、以下の代替手段が存在する。

– サブクエリ

UPDATE user SET presence = 1 WHERE
job_id = (SELECT id FROM job WHERE name = ‘teacher’);

虎の子で条件分岐も使えるし、サブクエリだけで結構何でもできてしまう。

– LOCK TABLES, UNLOCK TABLES]
今日のエントリを参照。

– LAST_INSERT_ID()
最後に挿入したレコードの一意な ID はどのように取得するのか
mysql_insert_id()
最後の挿入(INSERT),更新(UPDATE) で設定/生成されたレコードについての AUTO_INCREMENT 値が入る。AUTO_INCREMENT 値が更新/設定されなかった場合は 0 になる。

– GET_LOCK(), RELEASE_LOCK()
Java や C# のオブジェクト指定のロックのような、名前指定のロック。
ただしデッドロックを避けるため同時に複数のロックを獲得することはできないようになっている。つまり、GET_LOCK() は、前回のGET_LOCK() によるロックを開放する。

参考:
– 釣ったよ！釣りとコンピュータ: MySQL InnoDB のトランザクション

パスワードを語呂合わせから生成する

投稿日: 2006年1月19日2026年8月10日カテゴリー lifehackタグ securityパスワードを語呂合わせから生成するにコメントを残す

MySQL41 の SJIS 環境のクライアントからの文字化けを防ぐ

SJIS 以外は SET NAMES クエリでの設定で解決するが、SJIS ではクエリでは解決できない。

結論からいうと、

mysql –default-character-set=sjis [ -u username [ -p ]] […]

のように、クライアントでの接続時に –default-character-set=sjis と明示してやるだけで解決する(SET NAMES .. は不要)。my.cnf の [mysql] の項目に sjis と設定してやってもよい。

PHP でも my.cnf を見ているらしい。PHP ではこれ以外の妥当な設定の手段がないもよう。
現在は、一部のコードでのみ対応したい場合は、自分でラッパ関数を作ってやるのがよさそう。PEAR::DB の prepared statement 構文を使う場合は ujis (EUC_JP)として通信させてやらないとうまくいかなかった。

詳しく書くと、DB_Common::prepare で呼び出している内部関数で非 UTF8 のつもりでエスケープ処理を行うため、事前エンコードをすると文字が壊れてしまうのが原因。EUC-JP だと、ASCII コードに互換性があるためエスケープ処理で文字が壊れないのでエンコード後のエスケープでも問題ない。

//
// 単純なサンプル。実用コードではありませんよ。
//
$con->query(‘SET NAMES ujis’);
echo dec( $con->getOne(enc(‘SELECT description FROM product WHERE name = ?’), enc(array(‘苛性ソーダ’))) ) ;

/**
*
*/
function dec($val){
    return mb_convert_encoding($val, mb_internal_encoding(), ‘EUC-JP’);
}
/**
* エンコード。クエリを実行する前の前処理。
* @param string $val 変換対象の文字列
* @return string DB クエリの文字コードに変換した文字列
*/
function enc($val){
    return mb_convert_encoding($val, ‘EUC-JP’);
    // $val は mb_internal_encoding() の文字コード。それ以外も
    // 受け付ける場合, 第三引数を ‘auto,UTF-8’ 等にしておく。
}

参考:
– OSS Web – 接続キャラクタセットの変更
ここにあるとおり、show variables like ‘character\_set\_%’ では同じなのに、実際は内部的には違うのがなんとも。

Java で簡単に使える SMTP サーバパッケージ

Dumbster
Java 用のメール受信ができるパッケージ。
– メール送信処理の単体テストに使える。

SimpleSmtpServer server = SimpleSmtpServer.start(); // ローカルポート25 を Listen
// テストする処理: localhost 宛のメール送信処理
server.stop();

// 受信メール数
assertEquals(1, server.getReceivedEmailSize());
// 一通目のメールを取得する。
SmtpMessage email = server.getReceivedEmail().next();
// タイトル
assertEquals(“Test Subject”, email.getHeaderValue(“Subject”));
// 本文
assertEquals(“Test Body”, email.getBody());

SSH でよく接続が切れる場合の対処

sshd_config で

# Setting timeout of connections.
# Default is..
# ClientAliveInterval 0
# ClientAliveCountMax 3
# 2 minites (10sec * 12 times = 120 sec)
ClientAliveInterval 10
ClientAliveCountMax 12

とかして、2 分間は猶予を与えることにした。

– ClientAliveInterval – 生存チェックをする間隔(デフォルトでは一切チェックしない)
– ClientAliveCountMax – 生存チェックする回数(デフォルトは 3 回)

サーバ側がいじれない場合はクライアントサイドの ssh_config で

>

ServerAliveInterval 10
ServerAliveCountMax 12

とすれば同じ効果があるらしい。

参考:
– OpenSSH FAQ – 2.12 なにもしないで N 分たつと ssh 接続が固まるか、切れるかするんだけど。
– MAN – sshd_config.0
– MAN – ssh_config.0

カテゴリー: lifehack