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

41–50 of 76 posts

Re: Asynchronous Python and Databases

#41

Post by robbles »

temuze wrote: post #26
robbles wrote: post #24

Earlier quoted context omitted.

> a surprising number of apps don't do pagination properly Could you elaborate on that? What's "properly"? Do you mean that they don't do it at all, or they do it in memory instead of in an indexed query? Or is there a technique here that I'm missing?

A surprising number of people either make N queries because they don't what their code is doing (really easy for a beginner to do with Django models when not using prefetch) or, more commonly, they make queries that get N rows back. You almost never need N rows. N rows are bad because Python has to parse them. Odds are, there's a way to get a constant number of rows back for every query in every view of your webapp.

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.

Re: Asynchronous Python and Databases

#42

Post by krick »

> Python is Very, Very Slow compared to your database

I never actually tried to measure this, so I have 2 questions:

1. Have I understood correctly that author implies that webapp and DB are running on one server? But that's usually just not the case!

2. Does anybody have an idea, of how we could compose more or less realistic benchmark for this, preferably in language-agnostic manner, so I could write a few scripts, connect few databases on multiple hosts, run it and see, how expensive all this stuff actually is, app-code/db performance wise?

Re: Asynchronous Python and Databases

#43

Post by zzzeek »

krick wrote: post #42

> Python is Very, Very Slow compared to your database I never actually tried to measure this, so I have 2 questions: 1. Have I understood correctly that author implies that webapp and DB are running on one server? But that's usually just not the case! 2. Does anybody have an idea, of how we could compose more or less realistic benchmark for this, preferably in language-agnostic manner, so I could write a few scripts,…

> Have I understood correctly that author implies that webapp and DB are running on one server? But that's usually just not the case!

no. Please see the benchmark suite where I ran the async/threaded performance tests both on the same machine as well as on different machines. As for the PyMySQL example, I can assure you, running it on the same machine, different machines, whatever, you'll see something very similar as far as that Python profiling result.

Re: Asynchronous Python and Databases

#44

Post by jsmeaton »

robbles wrote: post #41
temuze wrote: post #26

Earlier quoted context omitted.

A surprising number of people either make N queries because they don't what their code is doing (really easy for a beginner to do with Django models when not using prefetch) or, more commonly, they make queries that get N rows back. You almost never need N rows. N rows are bad because Python has to parse them. Odds are, there's a way to get a constant number of rows back for every query in every view of your webapp.

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/efficient-pagination-using... see slides 12-16

Re: Asynchronous Python and Databases

#45

Post by bwross »

thrownaway2424 wrote: post #31
bwross wrote: post #12

So the basic take away seems to be: don't bother using async patterns for single, low latency connections to a server on your local network. For anything where you're dealing with thousands of connections from random Internet hosts, "just spawn a thread for it" does not cut it. If you take that approach, you're setting yourself up to be accidentally DoS'd at some point in the near future. Async, on the other hand, ha…

I'd want data. The system I work on does in fact spawn a thread to handle each and every connection and in fact each connection thread spawns numerous child threads to exploit available parallelism within the request. The code is fully blocking and linear and anyone can read it and see what it is doing. The mentioned system is one of the largest public networks services on earth. I am very skeptical of the idea that…

Show me your application and I'll tell you where it's either calling select() or is limiting connections to keep itself from locking up the OS.

Re: Asynchronous Python and Databases

#46

Post by jsmeaton »

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.

Multiple CPUs don't help for a single request, but the majority of deployments use processes (and threads within the processes) to run multiple requests in parallel. You can definitely saturate all the CPUs with a single Django application, provided the application server is running multiple processes.

Re: Asynchronous Python and Databases

#47

Post by robbles »

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…

Interesting, I wasn't aware of this. Thanks for the explanation - I'll have to keep this in mind the next time I'm implementing pagination for a very large table.

Re: Asynchronous Python and Databases

#48

Post by thrownaway2424 »

bwross wrote: post #45
thrownaway2424 wrote: post #31

Earlier quoted context omitted.

I'd want data. The system I work on does in fact spawn a thread to handle each and every connection and in fact each connection thread spawns numerous child threads to exploit available parallelism within the request. The code is fully blocking and linear and anyone can read it and see what it is doing. The mentioned system is one of the largest public networks services on earth. I am very skeptical of the idea that…

Show me your application and I'll tell you where it's either calling select() or is limiting connections to keep itself from locking up the OS.

You're right on the second point. I have it limited to 4 million sockets per machine.

Re: Asynchronous Python and Databases

#49

Post by rdtsc »

lmm wrote: post #39

> In practice, you'll end up with so many "yield from" lines in your code that you're right back to "well, I guess I could context switch just about anywhere", which is the problem you were trying to avoid in the first place. This is backwards, like when my Java colleague complained I was putting "final" in too many places. The point isn't the lines that contain "yield from". It's the lines that don't contain "yield…

> The point isn't the lines that contain "yield from". It's the lines that don't contain "yield from"

What is the point though? Why does it matter where context switches happen (either CPU or IO ones). You are doing the job of the scheduler which is like being teleported back to Windows 3.1. Adding "yield froms" turns your functions into generators. The job of code maybe is to update shopping carts or send tweets or something like that, now that function returns a generator and anything on top of it has to deal with it.

The original point, which I think you missed, is that the typical advantage of "yield from" (or deferreds, or callbacks) is that you don't need to worry about synchronization. Look no mutexes, this is fantastic! Except that is wrong. As the application grows, top level code starts to look like:

     r1 = yield from f1()
     r2 = yield from f2(r1)
     r3 = yield from f3(r2) 
     ...
With callbacks it looks even uglier. That code is logically approaching the code that looks like:

     r1 = f1()
     r2 = f2(r1)
     r3 = f3(r2)
     ...
In a multithreaded program without mutexes.

Therefore this thing exists: http://twistedmatrix.com/documents/8.1.0/api/twisted.interne... and I had to use it often enough. Because what happens is two clients would start 2 concurrent callback chains, and if they start updating some shared data ( a database or internal structure ) you've got a data race and you need to use the DeferredSemaphore.

Re: Asynchronous Python and Databases

#50

Post by elithrar »

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…

In many cases it's trickier than that too: if you're using your database's UUID's as the primary key, you'll need to find another column to use BETWEEN on and index that to make this work.

Plus, as stated in the slideshow, you can't provide jumps to arbitrary pages. In some cases this doesn't matter, but worth taking note of.

(pagination is frustrating!)

Post reply on HN