使用py-postgresql驱动链接PostgreSQL, 使用unix socket链接.
测试插入性能, 单线程插入100万数据需要172秒.
使用while循环亦如此 :
使用pgbench单线程插入100万, 只需要55秒.
之前使用sysbench测试和pgbench测试相差也巨大.
$ 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.3471527postgres@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/
