Show HN: 1M rows/s from Postgres to Python • Hacker News Board

Re: Show HN: 1M rows/s from Postgres to Python

#51

Post by _qqea » Thu, Aug 04, 2016, 10:22 PM UTC

Thaxll wrote: post #21

1st1 wrote: post #17

Earlier quoted context omitted.

Python and nodejs applications are usually deployed in a multiprocess configuration (and Go apps without GOMAXPROCS). So in production, applications in all of those languages use all CPU cores. For benchmarks, we didn't want to complicate things with multi-process setup, the idea was to compare the raw performance of all drivers.

"usually deployed" where? Every Python app I've seen in Prod don't use multi process to bypass the limitation of GIL/#cores.

They don't use the multiprocess module but most of them are deployed with something like uWSGI or Gunicorn, which spawns the worker processes and forwards requests to them and the responses back.

Re: Show HN: 1M rows/s from Postgres to Python

#52

Post by zzzeek » Thu, Aug 04, 2016, 10:24 PM UTC

1st1 wrote: post #50

zzzeek wrote: post #47

Earlier quoted context omitted.

> This simply isn't true for all use cases. Everytime I talk about my blog post, I am super careful to qualify: in this case, " usually has little to do w/ speed" and other qualifications. The blog post as well. But everytime, I get a response, "but that's not always TRUE!" We agree. It is not always true. But it is usually true :). > If you have to manage 100-1000s of connections Which I said, usually you are not. P…

>> If you have to manage 100-1000s of connections > Which I said, usually you are not. Postgresql connections are expensive. You do not want 100s-1000s. I was talking about a micro-service that has to support a lot of connections and work with a DB. >> some of each can be long-living sessions such as websockets > you use websockets to talk to your Postgresql database? Where did I say I was talking about HTTP web serv…

If you write a program that has thousands of persistent websockets open, you do not want all those thousands of connections to each have their own, persistent, long lived database transaction. You will not like your process listing nor will you be happy about the amount of table and row locking going on. Transactions and connection scope should be short lived, and if you're using a connection pool, you definitely are going for that. As my blog post stated, you'd use a thread pool for the database access part, given that the http-facing side is async.

Re: Show HN: 1M rows/s from Postgres to Python

#53

Post by 1st1 » Thu, Aug 04, 2016, 10:26 PM UTC

1st1 wrote: post #50

zzzeek wrote: post #47

Earlier quoted context omitted.

> This simply isn't true for all use cases. Everytime I talk about my blog post, I am super careful to qualify: in this case, " usually has little to do w/ speed" and other qualifications. The blog post as well. But everytime, I get a response, "but that's not always TRUE!" We agree. It is not always true. But it is usually true :). > If you have to manage 100-1000s of connections Which I said, usually you are not. P…

>> If you have to manage 100-1000s of connections > Which I said, usually you are not. Postgresql connections are expensive. You do not want 100s-1000s. I was talking about a micro-service that has to support a lot of connections and work with a DB. >> some of each can be long-living sessions such as websockets > you use websockets to talk to your Postgresql database? Where did I say I was talking about HTTP web serv…

> If you write a program that has thousands of persistent websockets open, you do not want all those thousands of connections to each have their own, persistent, long lived database transaction. [..]

Mike, I've never suggested to have a DB connection per HTTP request. Please re-read my other comment in this thread.

Re: Show HN: 1M rows/s from Postgres to Python

#54

Post by zzzeek » Thu, Aug 04, 2016, 10:31 PM UTC

1st1 wrote: post #50

zzzeek wrote: post #47

Earlier quoted context omitted.

> This simply isn't true for all use cases. Everytime I talk about my blog post, I am super careful to qualify: in this case, " usually has little to do w/ speed" and other qualifications. The blog post as well. But everytime, I get a response, "but that's not always TRUE!" We agree. It is not always true. But it is usually true :). > If you have to manage 100-1000s of connections Which I said, usually you are not. P…

>> If you have to manage 100-1000s of connections > Which I said, usually you are not. Postgresql connections are expensive. You do not want 100s-1000s. I was talking about a micro-service that has to support a lot of connections and work with a DB. >> some of each can be long-living sessions such as websockets > you use websockets to talk to your Postgresql database? Where did I say I was talking about HTTP web serv…

> Mike, I've never suggested to have a DB connection per HTTP request.

right, so my suggestion is, keep the DB code in a thread pool. So you can use the async for your 1000-client frontend and not force it into the DB layer where it makes things much more complicated (or what I do, just use gevent :) ).

Re: Show HN: 1M rows/s from Postgres to Python

#55

Post by RedCrowbar » Thu, Aug 04, 2016, 10:38 PM UTC

zzzeek wrote: post #47

1st1 wrote: post #44

Earlier quoted context omitted.

> The TL;DR; for my blog post you mention is that async itself usually has little to do with speed when talking to a database, because the database is typically on the same network as your application, you're using typically no more than a few dozen connections per process (postgresql uses a process per connection, so using thousands of connections isn't feasible anyway), so the overhead of async and especially Pytho…

> This simply isn't true for all use cases. Everytime I talk about my blog post, I am super careful to qualify: in this case, " usually has little to do w/ speed" and other qualifications. The blog post as well. But everytime, I get a response, "but that's not always TRUE!" We agree. It is not always true. But it is usually true :). > If you have to manage 100-1000s of connections Which I said, usually you are not. P…

> Everytime I talk about my blog post, I am super careful to qualify: in this case, "usually has little to do w/ speed" and other qualifications. The blog post as well.

I created an asyncpg suite for your bigdata benchmark (https://bitbucket.org/zzzeek/bigdata). Here are the results obtained from running it on my laptop against local PostgreSQL 9.5.

300 connections:

    Python3.5.2 threads -- avg 9798.16 recs/sec
    Python3.5.2 gevent -- avg 9677.84 recs/sec
    Python3.5.2 uvloop/asyncpg -- avg 11558.24 r/sec

The modified suite is here: https://github.com/elprans/bigdata

Re: Show HN: 1M rows/s from Postgres to Python

#56

Post by allendoerfer » Thu, Aug 04, 2016, 10:38 PM UTC

zzzeek wrote: post #54

1st1 wrote: post #50

Earlier quoted context omitted.

>> If you have to manage 100-1000s of connections > Which I said, usually you are not. Postgresql connections are expensive. You do not want 100s-1000s. I was talking about a micro-service that has to support a lot of connections and work with a DB. >> some of each can be long-living sessions such as websockets > you use websockets to talk to your Postgresql database? Where did I say I was talking about HTTP web serv…

> Mike, I've never suggested to have a DB connection per HTTP request. right, so my suggestion is, keep the DB code in a thread pool. So you can use the async for your 1000-client frontend and not force it into the DB layer where it makes things much more complicated (or what I do, just use gevent :) ).

Sorry to interrupt: You two can click on the timestamp to answer your posts. Please continue, we are listening carefully.

And since I am already here @1st1: How hard would it be to create a synchronous version of your adapter?

Re: Show HN: 1M rows/s from Postgres to Python

#57

Post by 1st1 » Thu, Aug 04, 2016, 10:41 PM UTC

zzzeek wrote: post #54

1st1 wrote: post #50

Earlier quoted context omitted.

>> If you have to manage 100-1000s of connections > Which I said, usually you are not. Postgresql connections are expensive. You do not want 100s-1000s. I was talking about a micro-service that has to support a lot of connections and work with a DB. >> some of each can be long-living sessions such as websockets > you use websockets to talk to your Postgresql database? Where did I say I was talking about HTTP web serv…

> Mike, I've never suggested to have a DB connection per HTTP request. right, so my suggestion is, keep the DB code in a thread pool. So you can use the async for your 1000-client frontend and not force it into the DB layer where it makes things much more complicated (or what I do, just use gevent :) ).

> right, so my suggestion is, keep the DB code in a thread pool. So you can use the async for your 1000-client frontend and not force it into the DB layer where it makes things much more complicated (or what I do, just use gevent :) ).

Looks like a totally valid suggestion for folks who write async code and want to continue to use SQL Alchemy.

However, if you don't use SQL Alchemy (or another ORM) and just want to work with the DB directly in an async/await code base, having a threadpool will only needlessly complicate things.

Re: Show HN: 1M rows/s from Postgres to Python

#58

Post by 1st1 » Thu, Aug 04, 2016, 10:49 PM UTC

allendoerfer wrote: post #56

zzzeek wrote: post #54

Earlier quoted context omitted.

> Mike, I've never suggested to have a DB connection per HTTP request. right, so my suggestion is, keep the DB code in a thread pool. So you can use the async for your 1000-client frontend and not force it into the DB layer where it makes things much more complicated (or what I do, just use gevent :) ).

Sorry to interrupt: You two can click on the timestamp to answer your posts. Please continue, we are listening carefully. And since I am already here @1st1: How hard would it be to create a synchronous version of your adapter?

A lot of code in asyncpg is datatypes parsers, buffer abstractions, API facade and other IO independent things. So good news is that it would be possible to reuse a lot of code.

Protocol is implemented in two layers -- the base layer is IO and framework independent. The higher level is designed for asyncio.

Long story short, it's possible to add a synchronous version, but it still will require a lot of work. The API will also have to be split in two.

Re: Show HN: 1M rows/s from Postgres to Python

#59

Post by zzzeek » Thu, Aug 04, 2016, 10:53 PM UTC

RedCrowbar wrote: post #55

zzzeek wrote: post #47

Earlier quoted context omitted.

> This simply isn't true for all use cases. Everytime I talk about my blog post, I am super careful to qualify: in this case, " usually has little to do w/ speed" and other qualifications. The blog post as well. But everytime, I get a response, "but that's not always TRUE!" We agree. It is not always true. But it is usually true :). > If you have to manage 100-1000s of connections Which I said, usually you are not. P…

> Everytime I talk about my blog post, I am super careful to qualify: in this case, "usually has little to do w/ speed" and other qualifications. The blog post as well. I created an asyncpg suite for your bigdata benchmark ( https://bitbucket.org/zzzeek/bigdata ). Here are the results obtained from running it on my laptop against local PostgreSQL 9.5. 300 connections: Python3.5.2 threads -- avg 9798.16 recs/sec Pytho…

that's great, you've written a very fast driver. My benchmarks are comparing psycopg2 to itself, under threads / gevent vs. asyncio, to show that asyncio is inherently slower. It follows that if you wrote your driver and all of its protocol improvements into a traditional blocking model, it would also be much faster. You've written something so fast that it overcomes the latency of asyncio (this whole thread is a huge plug for your driver, btw).

It's not that the latency of asyncio is really that big of a deal. The point I try to make is, asyncio != speed. For databases, 95% of the time, it means, a little less speed. Not a big deal, but if you aren't into writing explicit context switching directives throughout 100% of your code when the OS does it for you just fine, it isn't worth it, unless you are in the zone of thousands of arbitrarily performing connections, which is really more of an incoming client connection thing, not usually a database thing.

Re: Show HN: 1M rows/s from Postgres to Python

#60

Post by allendoerfer » Thu, Aug 04, 2016, 11:04 PM UTC

1st1 wrote: post #58

allendoerfer wrote: post #56

Earlier quoted context omitted.

Sorry to interrupt: You two can click on the timestamp to answer your posts. Please continue, we are listening carefully. And since I am already here @1st1: How hard would it be to create a synchronous version of your adapter?

A lot of code in asyncpg is datatypes parsers, buffer abstractions, API facade and other IO independent things. So good news is that it would be possible to reuse a lot of code. Protocol is implemented in two layers -- the base layer is IO and framework independent. The higher level is designed for asyncio. Long story short, it's possible to add a synchronous version, but it still will require a lot of work. The API…

I think you should open a bounty for it. As questions like "Can I use this with Django/SQLAlchemy/…?" showed, people love drop-in replacements that speed up their code, especially when it is at a low level like this.

Do not want to sound ungrateful, though. Thank you for your work also on you other contributions. Would definitely use this when applicable. Are more performance related projects like this and uvloop planned?