Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Asynchronous Python and Databases

techspot.zzzeek.org

Post reply on HN
Print view View original

61–70 of 76 posts

Re: Asynchronous Python and Databases

#61

Post by laurencerowe »

zzzeek wrote: post #59
jnowlan wrote: post #54

Zeek, I've been lucky enough to have benefitted from Sqlalchemy and Mako (but it's been awhile). Thanks. This article looked like it was going to hit the sweet spot of stuff I'm curious about, but I found I was still left with questions. If you (or anyone) will indulge me... I'll try and ask a question to help clarify matters. I work at a University on legacy ERP system(s). During registration there are 800+ concurre…

if you want to send out a series of long-reply SQL calls and wait for them all in batch, that is doable with Postgresql's async support, but they'd all be on distinct database connections, so you wouldn't get transactional consistency between these calls, but maybe that's not important. You can do the same thing with threads but it would mean you'd need to spin up that many threads, but at least would be something yo…

Postgres lets you synchronize snapshots across connections so that they all see the same data (though subsequent changes are not visible to the other transactions unless you export another snapshot.) http://www.postgresql.org/docs/9.4/static/functions-admin.ht...

This lets you parallelize work across multiple processes while maintaining consistency. When the client is the limiting factor you can use this with multiprocessing. When the db server is the limiting factor you can just use threads (or async.) Postgres backend processes are essentially single threaded.

Re: Asynchronous Python and Databases

#62

Post by PythonicAlpha »

amelius wrote: post #33
PythonicAlpha wrote: post #29

Earlier quoted context omitted.

I disagree. It is not analogous to cooperative multitasking, since Windows 3.1 dealt with different applications -- but asynchronous programming is targeting only a single (server) application. I also would add, that asynchronous implemented server applications are doing oftentimes better than systems that primarily rely on threading.

> but asynchronous programming is targeting only a single (server) application As systems get more complex, this is not true anymore, because thinking otherwise would put the composability of the software at risk. Large software is typically built from many heterogeneous components. > I also would add, that asynchronous implemented server applications are doing oftentimes better than systems that primarily rely on th…

> Large software is typically built from many heterogeneous components.

That is just one of the reasons, why software is getting more and more unreliable (not the asynchronous implementation). Software stacks are getting more and more complex and implementors loose track of the complexity.

Throwing dirt on software with clear and less complex implementations, is not going to make things better.

Of course I would not argue, that asynchronous implementation is for any software and in particular with today's development tools, but just to compare it with different types of things to make it look bad, is no proper argumentation style.

Re: Asynchronous Python and Databases

#63

Post by randlet »

jsmeaton wrote: post #44
robbles wrote: post #41

Earlier quoted context omitted.

I've definitely seen the first - probably the first major performance issue that everyone runs into when using an ORM. By "N rows", do you mean having a view that only renders a constant number of rows, but your database query returns an larger collection that's then filtered in Python code? That seems like an obvious bug that can be usually fixed with a LIMIT clause.

LIMIT/OFFSET isn't the optimal way to do pagination either, because the database has to scan to offset before fetching the limit. I'm having a hard time trying to find the particular resource that dives into it, but here are the basics: SELECT * FROM t OFFSET 1000 LIMIT 10; -- does not use index, scans table vs SELECT * FROM t WHERE id between 1000 and 1010; -- uses index Edit: http://www.slideshare.net/Eweaver/effic…

I was wondering what you do when say record 1001 gets deleted...your second query will only return 9 rows.

It's explained in your linked slides that LIMIT is ok, it's the offset that you need to worry about so your query looks like it would be better written:

  SELECT *
  FROM t
  WHERE id > 1000 LIMIT 10;

Re: Asynchronous Python and Databases

#64

Post by thrownaway2424 »

plq wrote: post #57
thrownaway2424 wrote: post #53

Earlier quoted context omitted.

"Ever tried to ssh into a one-thread-per-connection setup under heavy load? Assuming you managed to log in, it'll be very very difficult to get htop to execute when it's competing with 2k other processes for cpu time." Nothing about your comment is compatible with my experience. It is certainly not true that a Linux box with 2000 threads blocked on i/o will be having any sort of bad time. If you're really got 2000 th…

> It is certainly not true that a Linux box with 2000 threads blocked on i/o will be having any sort of bad time. You and I must have very different perceptions about the way a server is "having a bad time" :) I was assuming they were at various stages of processing an incoming request, which means they were blocked on either legitimate disk i/o or swapping. It's very difficult to log in even locally in that case, be…

OK, but it sounds like the main problem there is local disk access, which is the great satan anyway. You an generate a machine-hosing writeout workload using only one thread on Linux, because Linux loves to starve readers if it can write instead.

I think the unstated second dimension of your comment is that most operating system distributions come out of the installer with absolutely the wrong parameters for running many threads. I think the default thread stack size is 2MB still, and the socket buffers are all huge, and there are limits on how many processes you can have that make people think those limits are meaningful, when they're really not.

Re: Asynchronous Python and Databases

#65

Post by Animats »

bayesianhorse wrote: post #32
Animats wrote: post #9

If you need that much performance out of Python, it's probably time to switch to Go. With Python, you still have the Global Interpreter Lock, even in PyPy. Multiple CPUs, which you probably have available, don't help.

The global interpreter lock does not apply to multiple processes. Web applications are embarrassingly parallel.

Multiple processes in Python at scale mean really clunky interprocess communication, lots of copies of everything the interpreter loaded, and lots of cache misses.

Re: Asynchronous Python and Databases

#67

Post by jnowlan »

zzzeek wrote: post #59
jnowlan wrote: post #54

Zeek, I've been lucky enough to have benefitted from Sqlalchemy and Mako (but it's been awhile). Thanks. This article looked like it was going to hit the sweet spot of stuff I'm curious about, but I found I was still left with questions. If you (or anyone) will indulge me... I'll try and ask a question to help clarify matters. I work at a University on legacy ERP system(s). During registration there are 800+ concurre…

if you want to send out a series of long-reply SQL calls and wait for them all in batch, that is doable with Postgresql's async support, but they'd all be on distinct database connections, so you wouldn't get transactional consistency between these calls, but maybe that's not important. You can do the same thing with threads but it would mean you'd need to spin up that many threads, but at least would be something yo…

So caching. Doesn't the db do that? And as much as I hate to say it, are the added complexities (webserver caches) better than the even more traditional approach - throw hardware at it? Always lots to think about. Thanks!

Re: Asynchronous Python and Databases

#68

Post by swasheck »

threeseed wrote: post #27

> For database code, you have exactly one technique to use in order to assure correct concurrency, and that is by using ACID-oriented constructs and techniques. Actually no. And in the transfer example he listed it isn't even how banks do it in the real world. Mostly they use eventual consistency. And there are alternatives to ACID albeit far less simple. You can use something like Zookeeper to handle transactions. O…

i'm curious about which banks in the real world use an eventually consistent transaction management approach and if you'd be willing to cite your sources.

Re: Asynchronous Python and Databases

#69

Post by swasheck »

RussianCow wrote: post #2

> ...the speed of Python is not nearly as fast as your database, when dealing in terms of standard CRUD-style applications... The blanket assumptions that most developers are building "standard CRUD-style applications" and that the database is never a bottleneck make it difficult for me to take this post seriously. Sure, if you're running a simple to-do list app, you probably won't need asynchronous I/O, but in my ex…

saying that it's the database is also a blanket statement and assumption. why was the majority of the time spent in the database? was it because of suboptimal code? was it because a subsystem has not been implemented properly? pointing fingers at the database type or the application server type is equally irresponsible.

Re: Asynchronous Python and Databases

#70

Post by bayesianhorse »

Animats wrote: post #65
bayesianhorse wrote: post #32

Earlier quoted context omitted.

The global interpreter lock does not apply to multiple processes. Web applications are embarrassingly parallel.

Multiple processes in Python at scale mean really clunky interprocess communication, lots of copies of everything the interpreter loaded, and lots of cache misses.

Good thing the stateless nature of web requests means database persistence is all the interprocess communication needed.
Post reply on HN