Use sequence in each database

MySQLはカラムをAUTO_INCREMENTで定義すれば、新規連番を振ってくれる。振られた連番はLAST_INSERT_ID()関数で取得できる。

CREATE TABLE foo
(
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

mysql_query("INSERT INTO foo(name) VALUES('test')", $conn);
$res = mysql_query("SELECT LAST_INSERT_ID() AS id", $conn);
$row = mysql_fetch_assoc($res);
var_dump($row["id"]);

PostgreSQLはカラムをSERIAL、またはBIGSERIALで定義すれば、新規連番を振ってくれる。振られた連番はLASTVAL()関数で取得できる。

CREATE TABLE foo
(
  id SERIAL NOT NULL,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

pg_query($conn, "INSERT INTO foo(name) VALUES('test')");
$res = pg_query($conn, "SELECT LASTVAL() AS id");
$row = pg_fetch_assoc($res);
var_dump($row["id"]);

SQLiteはカラムを"INTEGER PRIMARY KEY"または"INTEGER NOT NULL PRIMARY KEY"で定義すれば、新規連番を振ってくれる。振られた連番はPDOのlastInsertId()メソッドで取得できる。

CREATE TABLE foo
(
  id INTEGER PRIMARY KEY,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

$stmt = $pdo->prepare("INSERT INTO foo(name) VALUES('test')");
$stmt->execute();
var_dump($pdo->lastInsertId());

Firebirdではシーケンス(旧ジェネレータ)を作成し、GEN_ID()関数にそのシーケンス名を渡し新規連番を取得する。その値でINSERTする。

CREATE TABLE FOO
(
  ID INTEGER NOT NULL PRIMARY KEY,
  NAME VARCHAR(24) NOT NULL
)

CREATE SEQUENCE FOO_ID_SEQ;

-------------------------------------------

$res = ibase_query($conn, 'SELECT GEN_ID(FOO_ID_SEQ, 1) AS ID FROM RDB$DATABASE');
$row = ibase_fetch_assoc($res);
$lastId = $row["ID"];
ibase_query($conn, "INSERT INTO FOO(ID, NAME) VALUES({$lastId}, 'test')");
var_dump($lastId);

Oracleではシーケンスオブジェクトを作成し、NEXTVALにより新規連番を取得する。その値でINSERTする。

CREATE TABLE FOO
(
  ID INTEGER NOT NULL PRIMARY KEY,
  NAME VARCHAR(24) NOT NULL
)

CREATE SEQUENCE FOO_ID_SEQ;

-------------------------------------------

$stmt = oci_parse($conn, "SELECT FOO_ID_SEQ.NEXTVAL AS ID FROM DUAL");
oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
$row = oci_fetch_assoc($stmt);
$lastId = $row["ID"];
$stmt = oci_parse($conn, "INSERT INTO FOO(ID, NAME) VALUES({$lastId}, 'test')");
oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
var_dump($lastId);

SQL ServerはカラムをIDENTIFYで定義すれば、新規連番を振ってくれる。振られた連番はSCOPE_IDENTITY()関数で取得できる。

CREATE TABLE foo
(
  id INTEGER IDENTITY(1, 1) NOT NULL,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

mssql_query("INSERT INTO foo(name) VALUES('test')", $conn);
$res = mssql_query("SELECT SCOPE_IDENTITY() AS id", $conn);
$row = mssql_fetch_assoc($res);
var_dump($row["id"]);

replicate by pgpool

>> 構成
pgpool - 192.168.0.50
master - 192.168.0.11
secondary - 192.168.0.16

$ vi /usr/local/pgpool/etc/pgpool.conf
listen_address = '192.168.0.50'
port = 5433

backend_host_name = '192.168.0.11'
backend_port = 5432

secondary_backend_host_name = '192.168.0.16'
secondary_backend_port = 5432

replication_mode  = true
load_balance_mode = true

>> ベンチマーク
プライマリキーで取得するSELECTを3回発行するプログラム。
プライマリキーは1〜100000の中からランダムに生成。
データは10万件。

$ ab -n 500 -c 100 -k http://www.example.com/testapp

[ 単一サーバ(192.168.0.11) ]

Requests per second:    247.18 [#/sec]
Time per request:       4.046 [ms]

Requests per second:    273.41 [#/sec]
Time per request:       3.658 [ms]

Requests per second:    269.48 [#/sec]
Time per request:       3.711 [ms]

[ load barancing & connection pooling ]

Requests per second:    261.33 [#/sec]
Time per request:       3.827 [ms]

Requests per second:    261.51 [#/sec]
Time per request:       3.824 [ms]

Requests per second:    256.63 [#/sec]
Time per request:       3.897 [ms]

後者の方が気持ち速いが、ほとんど変わらず。コネクションプーリングによるパフォーマンス向上がバランサ(というかpgpool自体)のオーバーヘッドで相殺されている感じ。
パフォーマンスが変わらずフェイルオーバーできるというのは結構良いかもしれない。

postgresql logging

PostgreSQLでログ(クエリログ)を採る方法。

PostgreSQL 8.3.0

"log_statement"を有効にする。

$ vi /usr/local/pgsql/data/postgres.conf

log_statement = 'all'

ログディレクトリを作成。

$ mkdir /usr/local/pgsql/logs

起動時にログファイルを指定する。

$ cd /usr/local/pgsql
$ ./bin/pg_ctl -o -i -D ./data -l logs/pglog.log start &

pgpool - connection pooling

$ tar zxvf pgpool-3.4.1.tar.gz
$ cd pgpool-3.4.1
$ ./configure \
--prefix=/usr/local/pgpool \
--with-pgsql=/usr/local/pgsql
$ make && make install

$ cd /usr/local/pgpool/etc
$ cp pgpool.conf.sample pgpool.conf
$ vi pgpool.conf
listen_address = '192.168.0.50'
port = 5433
backend_port = 5432
enable_pool_hba = true
num_init_children = 10
max_pool = 20

$ cp pool_hba.conf.sample pool_hba.conf
$ vi pool_hba.conf
host   all   all   192.168.0.0/24   trust

$ ../bin/pgpool -n &

--- Benchmark ---

$ ab -n 500 -c 100 -k http://www.example.com/testapp

[ PostgreSQL-8.3.0 ]

Requests per second:    127.55 [#/sec]
Time per request:       7.840 [ms]

[ PostgreSQL-8.3.0 (connection pooling) ]

Requests per second:    181.14 [#/sec]
Time per request:       5.521 [ms]

[ MySQL-5.0.41 ]

Requests per second:    186.16 [#/sec]
Time per request:       5.372 [ms]