4 ms·

If you have a Python CRUD app, there are many steps you should take to speed up the app before parallelizing queries. The first couple that come to mind: - Ca

by temuze 12y ago

If you have a Python CRUD app, there are many steps you should take to speed up the app before parallelizing queries. The first couple that come to mind: - Caching. The 90-10 rule applies to most CRUD apps. Use varnish to get rid of some requests before they hit your webserver or make a redis/memcache LRU layer to prevent queries from hitting your database. - Shifting more work away from Python to the database. A surprising number of apps don't do pagination properly. For example, if you want to show the top 10 X objects for each item Y, you don't need to get X * Y rows. You can use a window function to get the top 10 for each Y can get 10 * Y rows instead. - Using PyPy if you can. It's a free performance boost, in most cases. - Smarter indexing. Postgres's partial indexes are really powerful. - Intermediate caches. If you have common GROUP BY queries, a materialized view could go a long way. - If you reallllllly need it, you can Cython and rewrite slow parts of your app in C. My point is - parallelizing queries should be one of your last steps in speeding up your app. It adds a lot of complexity sometimes and there's a lot of cheap easy optimizations out there.

zzzeek 12y ago
Why don't I put caching on my threaded app and leave it at that? Async gets me nothing.
- deleted 12y ago
  [deleted]
- temuze 12y ago
  Memory perhaps? Presumably, the threads would be a little heavier. I would be curious to see how much memory each of your test cases used. However, if you have memory problems in your webserver, it might be worth just spinning up more webservers...
detaro 12y ago
Really? write Cython modules before parallelizing?
- temuze 12y ago
  Fair enough - probably not in an IO bound webserver :)
robbles 12y ago
> 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?
- temuze 12y ago
  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.
  - robbles 12y ago
    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.
    - jsmeaton 12y ago
      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-mysql http://www.slideshare.net/Eweaver/efficient-pagination-using... see slides 12-16
      - robbles 12y ago
        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.
yeukhon 12y ago
> - Shifting more work away from Python to the database. A surprising number of apps don't do pagination properly. This is very true. Get as much out of database before doing in python. Creation of objects such as new list is very expensive in Python, especially when you are dealing with large amount of data.Iterating over data just to do some aggregation that can be done in DB is slow and expensive. Cache the result later if you expect that to be useful.

by temuze 12y ago

zzzeek 12y ago

Why don't I put caching on my threaded app and leave it at that? Async gets me nothing.

deleted 12y ago
[deleted]
temuze 12y ago
Memory perhaps? Presumably, the threads would be a little heavier. I would be curious to see how much memory each of your test cases used. However, if you have memory problems in your webserver, it might be worth just spinning up more webservers...

detaro 12y ago

Really? write Cython modules before parallelizing?

temuze 12y ago
Fair enough - probably not in an IO bound webserver :)

robbles 12y ago

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

temuze 12y ago
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.
- robbles 12y ago
  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.
  - jsmeaton 12y ago
    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-mysql http://www.slideshare.net/Eweaver/efficient-pagination-using... see slides 12-16
    - robbles 12y ago
      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.

yeukhon 12y ago

> - Shifting more work away from Python to the database. A surprising number of apps don't do pagination properly. This is very true. Get as much out of database before doing in python. Creation of objects such as new list is very expensive in Python, especially when you are dealing with large amount of data.Iterating over data just to do some aggregation that can be done in DB is slow and expensive. Cache the result later if you expect that to be useful.