compare python use py-postgresql & direct pgbench's performance-优快云博客

本文详细介绍了使用py-postgresql驱动通过unixsocket链接PostgreSQL数据库，进行单线程插入百万数据的性能测试。测试结果显示，使用py-postgresql驱动耗时172秒，而使用pgbench工具仅需55秒。文章还探讨了循环操作对性能的影响，并提供了py-postgresql驱动的基础用法示例。

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

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

$ vi test.py
import postgresql
import time
conn = { "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.py
1422871147.8219907
1422871319.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.py
import postgresql
import time
conn = { "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 = 0
while i<1000000:
  ins(i)
  i=i+1
print(time.time())

postgres@localhost-> python test.py
1422872074.1050985
1422872255.3471527

postgres@localhost-> cat test.py
import postgresql
import time
conn = { "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 = 0
while i<1000000:
  ins(i)
  i=i+1
print(time.time())

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

postgres@localhost-> pgbench -M prepared -n -r -f ./test.sql -c 1 -j 1 -t 1000000
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 1
number of threads: 1
number of transactions per client: 1000000
number of transactions actually processed: 1000000/1000000
tps = 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):
  pass
print(time.time())

postgres@localhost-> python test.py
1422872588.5488484
1422872588.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")

[参考]

1. https://pypi.python.org/pypi/py-postgresql/