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

71–76 of 76 posts

Re: Asynchronous Python and Databases

#71

Post by meowface »

jnowlan wrote: post #67
zzzeek wrote: post #59

Earlier quoted context omitted.

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!

>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?

That depends, do you want to save (some) development time or do you want to save (a lot of) money?

Re: Asynchronous Python and Databases

#72

Post by collyw »

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…

Side note, don't you need an ORDER BY when using LIMIT and OFFSET, as the database doesn't guarantee the order unless you specify it?

Re: Asynchronous Python and Databases

#73

Post by aidos »

meowface wrote: post #71
jnowlan wrote: post #67

Earlier quoted context omitted.

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!

>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? That depends, do you want to save (some) development time or do you want to save (a lot of) money?

When I was more junior I was always told to use caching as a last resort. It's a good attitude to take to make sure you're not doing something stupid and hiding it with caching. These days though I look for caching opportunities up-front. In fact, I'll design with them in mind.

I did some work for a client some time ago that were expecting a lot of read load. Their backend had a bunch of constantly changing data in mongo - but it only refreshed every 10 seconds. I told them initially to just output the aggregated data to an S3 object and have all the clients access it from there. They decided to run loads of servers instead, they were muttering something about AWS Autoscale (even though I told them that wouldn't help).

As expected, I got a call one Friday evening asking if I could take a look at why their servers were timing out. When I got there, there were about 15 frontend servers hammering 1 poor mongo box that was aggregating the same query again and again - and within any 10 second window always getting the same result. I stripped it down to 1 frontend box with an nginx cache (after jumping through a hoop to support jsonp).

After the dust settled they apparently didn't want to admit that it was something that could just be solved with caching so it was described as a configuration issue to the business.

Re: Asynchronous Python and Databases

#74

Post by lmm »

rdtsc wrote: post #49
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…

> You are doing the job of the scheduler which is like being teleported back to Windows 3.1.

Indeed it is. The difference is that modern programming techniques are good enough that we can do this with minimal overhead. Like how modern fighter planes have gone back to being aerodynamically unstable.

> 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.

That's one approach, but as you say it simply recapitulates the problems of traditional multithreading. If that were the only option, we might as well use threads and mutices.

But there are other options. We can accumulate effects that need to happen as a single transaction through our async chain (state monad) and then execute them all at once, with no possibility of yielding in the middle. In languages with true concurrency we can use an actor; in single-threaded event-driven interpreters we don't even need that. We get a model that has the power of open-and-close transactions (whether they be database transactions or or mutices), but is clearer and simpler to reason about.

Re: Asynchronous Python and Databases

#75

Post by lmm »

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,…

1. No.

2. https://www.techempower.com/benchmarks/ is the best effort I've seen. Still has all the problems of benchmarks (that is, it should be seen as giving an upper bound for performance of a particular tech stack, which you will almost never reach in practice), but it at least gives you end-to-end numbers on something like a realistic problem.

Re: Asynchronous Python and Databases

#76

Post by collyw »

swasheck wrote: post #68
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.

When I worked for an investment bank, it was all ACID databases.
Post reply on HN