test luadbi's postgresql driver performance (vs pgbench)

测试机环境 : 

Lua 5.1.5
CentOS 5.7 x64
PostgreSQL 9.3.2
CPU
model name      : Intel(R) Xeon(R) CPU           E5440  @ 2.83GHz
stepping        : 6
cpu MHz         : 2833.435

Luadbi测试结果 : 

[root@db-172-16-3-33 luadbi]# taskset -c 2 /opt/lua5.1/bin/lua
Lua 5.1.5  Copyright (C) 1994-2012 Lua.org, PUC-Rio
> require "DBI"
> dbh = assert(DBI.Connect('PostgreSQL', 'digoal', 'digoal', 'digoal', '/home/postgres/pg_root_5432', '5432'))
> dbh:autocommit(true)
> insert = assert(dbh:prepare("select ?::int"))
> function foo(cnt)
>>   local var1 = os.time()
>>   for i = 1,cnt do
>>     insert:execute(1)
>>   end
>>   return (os.time()-var1)
>> end
> print(foo(100000))
6
> print(foo(1000000))
61
> = 1000000/61.0;
16393.442622951

pgbench测试结果 : 

postgres@db-172-16-3-33-> vi test.sql
\set v 10
select :v::int;

postgres@db-172-16-3-33-> taskset -c 2 pgbench -M prepared -n -r -f ./test.sql -c 1 -j 1 -T 30 -U digoal digoal
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 1
number of threads: 1
duration: 30 s
number of transactions actually processed: 561432
tps = 18713.732544 (including connections establishing)
tps = 18715.020715 (excluding connections establishing)
statement latencies in milliseconds:
        0.001098        \set v 10
        0.050809        select :v::int;

同一机器下面lua5.1+luapgsql的测试结果

root@db-172-16-3-33-> taskset -c 2 /opt/lua5.1/bin/lua
Lua 5.1.5  Copyright (C) 1994-2012 Lua.org, PUC-Rio
> require "pgsql"
> conn = pgsql.connectdb('host=/home/postgres/pg_root_5432 port=5432 dbname=digoal user=digoal password=digoal')
> print(conn:errorMessage())

> conn:prepare('pre5','select $1',23)
> conn:execPrepared('pre5','10')
> function foo(cnt)
>>   local var1 = os.time()
>>   for i = 1,cnt do
>>     conn:execPrepared('pre5','10')
>>   end
>>   return (os.time()-var1)
>> end
> print(foo(100000))
6
> print(foo(1000000))
55
> = 1000000/55.0
18181.818181818

使用Lua 5.1和luadbi连接postgresql进行测试, 相比pgbench损失性能12.4%.

从性能角度来讲, 建议使用luapgsql连接PostgreSQL.

[参考]
1. http://blog.163.com/digoal@126/blog/static/163877040201412205316943/

2. http://blog.163.com/digoal@126/blog/static/163877040201412143349217/

3. http://blog.163.com/digoal@126/blog/static/163877040201412125644452/