7 ms·

"Slowness is likely a result of the fact that your request is communicating with other services across your network. In our case, these other services are Postg

"Slowness is likely a result of the fact that your request is communicating with other services across your network. In our case, these other services are PostgreSQL, Redis, Cassandra, and Memcached, just to name a few. Slow database queries and network latency generally outweigh the performance overhead of a robust framework such as Django." This seems to fly in the face of everything I've experienced with frameworks... Is this true? The bottleneck for me is usually never the database backend, unless you've written horrible queries... Maybe it's just because I'm not doing queries complex enough on the scale that disqus is?

mattrobenolt 13y ago
It's definitely not a blanket statement. For us, it is. Considering most requests aren't just 1 simple query either. If you even do 5 simple queries, each at 20ms each, that's 100ms spend just waiting for the db. Plus other systems in place. And honestly for us, we'd be happy if all queries were ~20ms. :) Lots are unfortunately slower than that. Especially if all 45k/s were doing the queries.
- bliti 13y ago
  How many database servers do you have? How do you have them handle the queue?
  - mattrobenolt 13y ago
    1 primary master, with a few read slaves and a few shards.
    - bliti 13y ago
      Thanks for answering my questions. (:
- beambot 13y ago
  With asynchronous (go-like) channels becoming so popular, I'd love to see some non-blocking Django ORM database calling features. That way, you could fire off a few non-blocking db requests and keep executing on the app server (eg. rendering?) until you hit a point where you needed the database data. I know you could roll your own implementation... but building this into the ORM would still be really slick.
  - mattrobenolt 13y ago
    There are frameworks that work like this, iirc. I think some in Scala, but I'm not sure. IMO, the added complexity wouldn't be worth it with things like Varnish. I think there are better solutions and this is attacking the wrong problem. But, YRMV. It depends on the situation at hand always.
    - yummyfajitas 13y ago
      Basically anything living in the akka+spray world is written this way. But akka+spray is not an framework in the same sense that django is.
      - lmm 13y ago
        Are there async drivers for e.g. mysql yet? Last time I did that I had a very nice async spray frontend, but it was all backed by a pool of regular blocking threads because that was the only way to use the database driver.
        yummyfajitas 13y ago
        There is this, but it's not particularly mature: https://github.com/mauricio/postgresql-async https://github.com/mauricio/postgresql-async I also use https://github.com/etaty/rediscala https://github.com/etaty/rediscala for accessing redis. But even without async drivers for MySQL, you can still do: val sqlResult: Future[StuffFromSql] = future { val stmt = connection.createStatement("...") ... } This will tie up a thread, but you can still run other actions in parallel with it.
mattrobenolt 13y ago
Another thing to consider is contention and competition for the database's attention. If a query is fast in isolation, it's probably not going to be fast at tens of thousands per second.
bliti 13y ago
You might tell Django to find one thing in the database, but it might do more than one query to get it. Which is a bottle neck. Most of the projects that I have worked with that use Django at scale, tend to move away from the ORM once it starts to hit 3K requests per second. Including a Redis DB for cached content usually allows for Django to work better.
- mattrobenolt 13y ago
  We still use the ORM for most things. If you know how to use it right, you can make your queries pretty efficient. I'm not claiming this is the best solution, but it works. It takes patience and understanding to use efficiently and make sure you're not using excessive amounts of queries. Specifically avoiding O(n) queries. Which are easy to achieve in Django.
  - bliti 13y ago
    Yeah, it really depends a lot on the models, too. If you go FK crazy then you push the ORM into making weird joins all the time. Which kills performance. Do you run the actual ORM commands or pass raw SQL to it?
    - mattrobenolt 13y ago
      We do a mix of both, but mostly the actual ORM methods.
  - hardwaresofton 13y ago
    So this is kind of exactly what I witnessed in real life -- a friend of mine who uses Django for his stack had essentially a nested O(n^2) query, because of the ease with which the ORM allows you to query things as if they were objects in memory... Needless to say, this was very bad, and we both knew it, but it took some digging to find the right django code to implement it (we knew what we WANTED to do, and could write it out in SQL) This particular friend was using the django testing middleware, and even with the massive amount of queries he was doing to the database, the database was not the biggest slowdown (each query went in quick, came out quick), the marshalling he was doing on the django side was...
est 13y ago
> Slow database queries and network latency If someone could add prepared statements to Django ORM, we could see a significant performance increase. And mysql protocol support compressions, if your very buzy page requires move big text/blob out of mysql it could be very useful.
- hardwaresofton 13y ago
  Wow, is it still true that django doesn't do any connection pooling? I have long since switched to Flask, personally (no hard feelings, just like flask's minimal nature) -- and I haven't been keeping up with developments
  - est 13y ago
    https://docs.djangoproject.com/en/dev/releases/1.6/ https://docs.djangoproject.com/en/dev/releases/1.6/ > Django now supports reusing the same database connection for several requests. Pooling is onething, prepared statements is another.
    - hardwaresofton 13y ago
      Very true, that's what I was thinking, and saw this post http://stackoverflow.com/questions/15856604/django-mysql-prepared-statements http://stackoverflow.com/questions/15856604/django-mysql-pre... and read "django has no concept of connection pooling, and every page view uses a brand new database connection. MySQL prepared statements only exist for the life of the connection/session in which they are defined" on that page... So they're somewhat connected right? My understanding of a prepared statement was a statement that a *SQL server caches for quick use -- which is only kind of right (looked it up again @ https://en.wikipedia.org/wiki/Prepared_statement https://en.wikipedia.org/wiki/Prepared_statement and it's not too far off). Then question marks went off as to why this still wasn't a thing yet.
      - est 13y ago
        You are correct. Prepared statements without connection pooling is moot
crucialfelix 13y ago
yes, database and specifically disk i/o is usually our bottleneck. I use newrelic and I can see the time taken of the database vs python. slow page events are almost always caused by database bottlenecks. edit: actually the fact that a django response needs to block for the database means that overall concurrency is a serious bottleneck. again, due to database. but it doesn't need to be a bottleneck if the requests didn't have to block. a more modern architecture like scala play! or node.js can serve other trivial requests while the database is still working. with django I have had to have two servers (8 CPU, 12 processes) so that nobody is blocked (eg. for just a trivial serve from cache) just because a few too many requests are needing more database objects for the page they are serving. so this is a django architecture issue.
- mattrobenolt 13y ago
  We run many more than 12 processes per server. That's how you can get around that. :) But that's frankly more of a Python architecture problem than Django. Django is compatible with coroutine libraries like gevent, just most of the ecosystem is not compatible, so that makes things difficult at the size we're at. Realistically though, this isn't a problem for us and if things are done right, shouldn't be a problem for you either. Just use more processes and you'll be fine unless you're running out of RAM or something.
  - deleted 13y ago
    [deleted]
  - crucialfelix 13y ago
    how many CPUS and how many processes per machine ? I guess I used to run 8 on a 4 CPU machine. I tried 9-12 processes but it always tripped over itself. > unless you're running out of RAM or something. yep, on the new machines I run 6 just to keep the total memory down. and that also sucks: having to use lots of memory just to get concurrency.
    - mattrobenolt 13y ago
      We run 40 processes on each with 16 cores and 24G of RAM.

mattrobenolt 13y ago

It's definitely not a blanket statement. For us, it is. Considering most requests aren't just 1 simple query either. If you even do 5 simple queries, each at 20ms each, that's 100ms spend just waiting for the db. Plus other systems in place. And honestly for us, we'd be happy if all queries were ~20ms. :) Lots are unfortunately slower than that. Especially if all 45k/s were doing the queries.

bliti 13y ago
How many database servers do you have? How do you have them handle the queue?
- mattrobenolt 13y ago
  1 primary master, with a few read slaves and a few shards.
  - bliti 13y ago
    Thanks for answering my questions. (:
beambot 13y ago
With asynchronous (go-like) channels becoming so popular, I'd love to see some non-blocking Django ORM database calling features. That way, you could fire off a few non-blocking db requests and keep executing on the app server (eg. rendering?) until you hit a point where you needed the database data. I know you could roll your own implementation... but building this into the ORM would still be really slick.
- mattrobenolt 13y ago
  There are frameworks that work like this, iirc. I think some in Scala, but I'm not sure. IMO, the added complexity wouldn't be worth it with things like Varnish. I think there are better solutions and this is attacking the wrong problem. But, YRMV. It depends on the situation at hand always.
  - yummyfajitas 13y ago
    Basically anything living in the akka+spray world is written this way. But akka+spray is not an framework in the same sense that django is.
    - lmm 13y ago
      Are there async drivers for e.g. mysql yet? Last time I did that I had a very nice async spray frontend, but it was all backed by a pool of regular blocking threads because that was the only way to use the database driver.
      - yummyfajitas 13y ago
        There is this, but it's not particularly mature: https://github.com/mauricio/postgresql-async https://github.com/mauricio/postgresql-async I also use https://github.com/etaty/rediscala https://github.com/etaty/rediscala for accessing redis. But even without async drivers for MySQL, you can still do: val sqlResult: Future[StuffFromSql] = future { val stmt = connection.createStatement("...") ... } This will tie up a thread, but you can still run other actions in parallel with it.

mattrobenolt 13y ago

Another thing to consider is contention and competition for the database's attention. If a query is fast in isolation, it's probably not going to be fast at tens of thousands per second.

bliti 13y ago

You might tell Django to find one thing in the database, but it might do more than one query to get it. Which is a bottle neck. Most of the projects that I have worked with that use Django at scale, tend to move away from the ORM once it starts to hit 3K requests per second. Including a Redis DB for cached content usually allows for Django to work better.

mattrobenolt 13y ago
We still use the ORM for most things. If you know how to use it right, you can make your queries pretty efficient. I'm not claiming this is the best solution, but it works. It takes patience and understanding to use efficiently and make sure you're not using excessive amounts of queries. Specifically avoiding O(n) queries. Which are easy to achieve in Django.
- bliti 13y ago
  Yeah, it really depends a lot on the models, too. If you go FK crazy then you push the ORM into making weird joins all the time. Which kills performance. Do you run the actual ORM commands or pass raw SQL to it?
  - mattrobenolt 13y ago
    We do a mix of both, but mostly the actual ORM methods.
- hardwaresofton 13y ago
  So this is kind of exactly what I witnessed in real life -- a friend of mine who uses Django for his stack had essentially a nested O(n^2) query, because of the ease with which the ORM allows you to query things as if they were objects in memory... Needless to say, this was very bad, and we both knew it, but it took some digging to find the right django code to implement it (we knew what we WANTED to do, and could write it out in SQL) This particular friend was using the django testing middleware, and even with the massive amount of queries he was doing to the database, the database was not the biggest slowdown (each query went in quick, came out quick), the marshalling he was doing on the django side was...

est 13y ago

> Slow database queries and network latency If someone could add prepared statements to Django ORM, we could see a significant performance increase. And mysql protocol support compressions, if your very buzy page requires move big text/blob out of mysql it could be very useful.

hardwaresofton 13y ago
Wow, is it still true that django doesn't do any connection pooling? I have long since switched to Flask, personally (no hard feelings, just like flask's minimal nature) -- and I haven't been keeping up with developments
- est 13y ago
  https://docs.djangoproject.com/en/dev/releases/1.6/ https://docs.djangoproject.com/en/dev/releases/1.6/ > Django now supports reusing the same database connection for several requests. Pooling is onething, prepared statements is another.
  - hardwaresofton 13y ago
    Very true, that's what I was thinking, and saw this post http://stackoverflow.com/questions/15856604/django-mysql-prepared-statements http://stackoverflow.com/questions/15856604/django-mysql-pre... and read "django has no concept of connection pooling, and every page view uses a brand new database connection. MySQL prepared statements only exist for the life of the connection/session in which they are defined" on that page... So they're somewhat connected right? My understanding of a prepared statement was a statement that a *SQL server caches for quick use -- which is only kind of right (looked it up again @ https://en.wikipedia.org/wiki/Prepared_statement https://en.wikipedia.org/wiki/Prepared_statement and it's not too far off). Then question marks went off as to why this still wasn't a thing yet.
    - est 13y ago
      You are correct. Prepared statements without connection pooling is moot

crucialfelix 13y ago

yes, database and specifically disk i/o is usually our bottleneck. I use newrelic and I can see the time taken of the database vs python. slow page events are almost always caused by database bottlenecks. edit: actually the fact that a django response needs to block for the database means that overall concurrency is a serious bottleneck. again, due to database. but it doesn't need to be a bottleneck if the requests didn't have to block. a more modern architecture like scala play! or node.js can serve other trivial requests while the database is still working. with django I have had to have two servers (8 CPU, 12 processes) so that nobody is blocked (eg. for just a trivial serve from cache) just because a few too many requests are needing more database objects for the page they are serving. so this is a django architecture issue.

mattrobenolt 13y ago
We run many more than 12 processes per server. That's how you can get around that. :) But that's frankly more of a Python architecture problem than Django. Django is compatible with coroutine libraries like gevent, just most of the ecosystem is not compatible, so that makes things difficult at the size we're at. Realistically though, this isn't a problem for us and if things are done right, shouldn't be a problem for you either. Just use more processes and you'll be fine unless you're running out of RAM or something.
- deleted 13y ago
  [deleted]
- crucialfelix 13y ago
  how many CPUS and how many processes per machine ? I guess I used to run 8 on a 4 CPU machine. I tried 9-12 processes but it always tripped over itself. > unless you're running out of RAM or something. yep, on the new machines I run 6 just to keep the total memory down. and that also sucks: having to use lots of memory just to get concurrency.
  - mattrobenolt 13y ago
    We run 40 processes on each with 16 cores and 24G of RAM.