compare python use py-postgresql & direct pgbench's performance-白红宇

使用py-postgresql驱动链接PostgreSQL, 使用unix socket链接.

测试插入性能, 单线程插入100万数据需要172秒.

$ vi test.pyimport postgresqlimport timeconn = { "user": "postgres",          "database": "postgres",         "unix": "/data01/pgdata/pg_root/.s.PGSQL.1921"}db = postgresql.open(**conn)db.execute("create table if not exists tt(id int)")ins = db.prepare("insert into tt values($1)")print(time.time())for i in range(0,1000000):  ins(i)print(time.time())postgres@localhost-> python test.py1422871147.82199071422871319.8280523耗费172秒TOP 593328 postgres  20   0  189128  16960   7632 R  81.3  0.0   0:09.31 /usr/local/bin/python3 test.py                                 593329 postgres  20   0 8628412  26260  24088 S  32.2  0.0   0:04.41 postgres: postgres postgres [local] idle

使用while循环亦如此 :

postgres@localhost-> cat test.pyimport postgresqlimport timeconn = { "user": "postgres",          "database": "postgres",         "unix": "/data01/pgdata/pg_root/.s.PGSQL.1921"}db = postgresql.open(**conn)db.execute("create table if not exists tt(id int)")ins = db.prepare("insert into tt values($1)")print(time.time())i = 0while i<1000000:  ins(i)  i=i+1print(time.time())postgres@localhost-> python test.py1422872074.10509851422872255.3471527

postgres@localhost-> cat test.pyimport postgresqlimport timeconn = { "user": "postgres",          "database": "postgres",         "unix": "/data01/pgdata/pg_root/.s.PGSQL.1921"}db = postgresql.open(**conn)db.execute("create table if not exists tt(id int)")ins = db.prepare("insert into tt values($1)")print(time.time())i = 0while i<1000000:  ins(i)  i=i+1print(time.time())

使用pgbench单线程插入100万, 只需要55秒.

postgres@localhost-> pgbench -M prepared -n -r -f ./test.sql -c 1 -j 1 -t 1000000transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 1number of threads: 1number of transactions per client: 1000000number of transactions actually processed: 1000000/1000000tps = 18156.624380 (including connections establishing)tps = 18157.507920 (excluding connections establishing)statement latencies in milliseconds:        0.001353        \setrandom id 0 1000000        0.052901        insert into tt values (:id)postgres=# select 1000000/18157.507920;      ?column?       --------------------- 55.0736369994104345(1 row)

之前使用sysbench测试和pgbench测试相差也巨大.

sysbench, py-postgresql和C+libpq比性能损耗差距巨大.

python循环消耗极低, 看样子还是在驱动上, 未使用异步接口:

print(time.time())for i in range(0,1000000):  passprint(time.time())postgres@localhost-> python test.py1422872588.54884841422872588.610911

其他

1. py-postgresql的例子

事务使用db.xact():

>>> import postgresql>>> db = postgresql.open('pq://user:password@host:port/database')>>> db.execute("CREATE TABLE emp (emp_first_name text, emp_last_name text, emp_salary numeric)")>>> make_emp = db.prepare("INSERT INTO emp VALUES ($1, $2, $3)")>>> make_emp("John", "Doe", "75,322")>>> with db.xact():...  make_emp("Jane", "Doe", "75,322")...  make_emp("Edward", "Johnson", "82,744")

[参考]