Last active
March 30, 2018 21:53
-
-
Save nszceta/ab4175b03fa8e033f8cc565ac610e92e to your computer and use it in GitHub Desktop.
asyncpg vs psycopg2
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
""" | |
a@MacBook-Pro ~ $ ab -c100 -n10000 http://127.0.0.1:8000/asyncpg/select | |
This is ApacheBench, Version 2.3 <$Revision: 1807734 $> | |
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ | |
Licensed to The Apache Software Foundation, http://www.apache.org/ | |
Benchmarking 127.0.0.1 (be patient) | |
Completed 1000 requests | |
Completed 2000 requests | |
Completed 3000 requests | |
Completed 4000 requests | |
Completed 5000 requests | |
Completed 6000 requests | |
Completed 7000 requests | |
Completed 8000 requests | |
Completed 9000 requests | |
Completed 10000 requests | |
Finished 10000 requests | |
Server Software: | |
Server Hostname: 127.0.0.1 | |
Server Port: 8000 | |
Document Path: /asyncpg/select | |
Document Length: 49791 bytes | |
Concurrency Level: 100 | |
Time taken for tests: 40.839 seconds | |
Complete requests: 10000 | |
Failed requests: 0 | |
Total transferred: 498840000 bytes | |
HTML transferred: 497910000 bytes | |
Requests per second: 244.86 [#/sec] (mean) | |
Time per request: 408.393 [ms] (mean) | |
Time per request: 4.084 [ms] (mean, across all concurrent requests) | |
Transfer rate: 11928.43 [Kbytes/sec] received | |
Connection Times (ms) | |
min mean[+/-sd] median max | |
Connect: 0 0 0.6 0 26 | |
Processing: 40 406 158.8 405 1467 | |
Waiting: 37 401 158.5 401 1462 | |
Total: 40 407 158.8 406 1468 | |
Percentage of the requests served within a certain time (ms) | |
50% 406 | |
66% 412 | |
75% 419 | |
80% 423 | |
90% 442 | |
95% 757 | |
98% 777 | |
99% 796 | |
100% 1468 (longest request) | |
a@MacBook-Pro ~ $ ab -c100 -n10000 http://127.0.0.1:8000/psycopg2/select | |
This is ApacheBench, Version 2.3 <$Revision: 1807734 $> | |
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ | |
Licensed to The Apache Software Foundation, http://www.apache.org/ | |
Benchmarking 127.0.0.1 (be patient) | |
Completed 1000 requests | |
Completed 2000 requests | |
Completed 3000 requests | |
Completed 4000 requests | |
Completed 5000 requests | |
Completed 6000 requests | |
Completed 7000 requests | |
Completed 8000 requests | |
Completed 9000 requests | |
Server Software: | |
Server Hostname: 127.0.0.1 | |
Server Port: 8000 | |
Document Path: /psycopg2/select | |
Document Length: 49791 bytes | |
Concurrency Level: 100 | |
Time taken for tests: 39.625 seconds | |
Complete requests: 10000 | |
Failed requests: 0 | |
Total transferred: 498840000 bytes | |
HTML transferred: 497910000 bytes | |
Requests per second: 252.36 [#/sec] (mean) | |
Time per request: 396.253 [ms] (mean) | |
Time per request: 3.963 [ms] (mean, across all concurrent requests) | |
Transfer rate: 12293.87 [Kbytes/sec] received | |
Connection Times (ms) | |
min mean[+/-sd] median max | |
Connect: 0 2 1.0 2 5 | |
Processing: 12 394 21.3 386 444 | |
Waiting: 9 391 21.2 383 441 | |
Total: 16 396 21.3 389 446 | |
Percentage of the requests served within a certain time (ms) | |
50% 389 | |
66% 395 | |
75% 415 | |
80% 418 | |
90% 425 | |
95% 431 | |
98% 434 | |
99% 440 | |
100% 446 (longest request) | |
Completed 10000 requests | |
Finished 10000 requests | |
a@MacBook-Pro ~ $ | |
""" | |
import os | |
import asyncio | |
import uvloop | |
from asyncpg import connect, create_pool | |
from sanic import Sanic | |
from sanic.response import json, text | |
import psycopg2.pool | |
def jsonify(records): | |
""" | |
Parse asyncpg record response into JSON format | |
""" | |
return [dict(r.items()) for r in records] | |
app = Sanic(__name__) | |
TEST_SQL_QUERY = 'SELECT * FROM sanic_post'; | |
DSN = 'postgres://a:@127.0.0.1/testing' | |
@app.listener('before_server_start') | |
async def register_db(app, loop): | |
app.pool_1 = await create_pool( | |
DSN, loop=loop, min_size=8, max_size=8) | |
app.pool_2 = psycopg2.pool.PersistentConnectionPool( | |
dsn=DSN, minconn=8, maxconn=8) | |
async with app.pool_1.acquire() as connection: | |
await connection.execute('DROP TABLE IF EXISTS sanic_post') | |
await connection.execute("""CREATE TABLE sanic_post ( | |
id serial primary key, | |
content varchar(50), | |
post_date timestamp | |
);""") | |
for i in range(0, 1000): | |
await connection.execute(f"""INSERT INTO sanic_post | |
(id, content, post_date) VALUES ({i}, {i}, now())""") | |
@app.get('/asyncpg/select') | |
async def root_get(request): | |
async with app.pool_1.acquire() as connection: | |
results = await connection.fetch(TEST_SQL_QUERY) | |
payload = {'posts': jsonify(results)} | |
return json(payload) | |
@app.get('/psycopg2/select') | |
async def psycopg2_select(request): | |
conn = app.pool_2.getconn() | |
conn.autocommit = True | |
with conn.cursor() as curs: | |
curs.execute(TEST_SQL_QUERY) | |
columns = [x.name for x in curs.description] | |
results = curs.fetchall() | |
payload = {'posts': [{x: y for x, y in zip(columns, result)} for result in results]} | |
return json(payload) | |
if __name__ == '__main__': | |
app.run(host='127.0.0.1', port=8000, debug=False) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment