Django SQL Sampling | Hacker News Reader

Django SQL Sampling

colinhowe.co.uk

25 pointscolinhowe8 comments

Not a bad idea, though I think DB-specific (and app-independent) solutions are pretty awesome already.

For Postgres: http://pgfouine.projects.postgresql.org/ Sample: http://pgfouine.projects.postgresql.org/reports/sample_defau...

Pgfouine is awesome, but one thing that's appealing about this one is the traceback showing where the query occurs--not always obvious given the Django ORM.

I write one of these for every large project during the optimization stage (beta testing with real data and use). I could not possibly recommend it more - not only for SQL tuning but also for tuning the cache.

I haven't looked at the source just yet, but in my own implementations, I usually keep a hash of the results as well so I can see how often the results change. If they rarely change, you have a prime candidate for heavy caching.

Another solid metric to follow are queries that are run often per request. So if I have the same query firing 5 times in a single request (even a light one) with the same returned data, I'll find a way to make sure it only runs once per request (or less).

This gave me two awesome ideas - thank you!

1. Hash the results as you say to try and find queries that could benefit from being cached 2. Graph the change in a queries performance over time - useful for spotting queries that were fast due to a small table and are now becoming troublesome

I'll start looking at these in the next few nights :)

Another similar project is http://code.playfire.com/django-append-url-to-sql/ although this is only helps for SQL executed in the request-response model, not for queue jobs, etc.

Can someone (or the author) explain to me how this is different from what I get from Django-Debug-Toolbar. DjDT tells me what queries are run and how much time each took.

This will group up queries from lots of different requests (and things like Celery) - not just queries. Also, due to the frequency being controllable this could be used in a production system (it is being used in ours).

The idea is to help you find your trouble spots - DjDT is amazing once you know where they are :)

This looks interesting. I've bookmarked it and will give it a try at work.

Reply on news.ycombinator.com