MySQL query comments in Rails | Hacker News Reader

MySQL query comments in Rails

37signals.com

35 pointsthemcgruff20 comments

I've been doing this on high traffic PHP sites for years. It makes mytop very informative and easy to understand, and I even occasionally use them for cheapo memcache caching instructions (built into db layer) when it's not worth precaching or coming up with a more sophisticated scheme. Highly recommended.

One difference though: I prepend the comment instead of appending it.

I dig this, a lot. Anyone know if there's a Django equivalent yet? If not, I know what I'll be doing with at least one evening this week...

Django debug toolbar can show you where awk/grep queries are called

Right, but that's a totally different solution. It only tells when I ask it, and it doesn't work for any query not issued in a rendered view (most of our data access is AJAX). I like the 37s solution because I can leave it running on a system and then find stack information in the slow query log later on.

Playfire has released something that will tack a comment onto all your queries for you: https://github.com/playfire/django-append-url-to-sql/

Brilliant. Thanks!

A similar thing I've tried to get implemented at a number of previous jobs is to dump the session ID in a SQL comment when issuing a query. We've had the session ID in application logs forever, but no way of correlating DB activity with application activity; this would fix that and allow a posteriori full-stack instrumentation.

if by session id you mean user session, that could completely compromise caching. mysql for instance, unless they fixed it, used to ignore query cache if the sql statement was different due to a different comment, e.g. a timestamp, an IP or a session id.

Note that adding comments to otherwise identical queries will typically cause the MySQL query cache to not realize they are the same. See: http://www.mysqlperformanceblog.com/2008/03/20/mysql-query-c...

Otherwise, neat idea, I like it.

Most, if not all production mysql installations have this cache disabled anyways. (Based on my experience at Engine Yard for 3 years and other places the last 5 years.)

Why?

Mutex issues and other problems with high insert / update rates. Google "mysql query cache issues" and "mysql query cache disable" for lots of posts about the various costs / benefits.

Also the MySQL query cache is not very granular, it gets cleared on every write to that table. Thus, we've seen in practice that with it enabled MySQL has poorer performance as its constantly being flushed. The overhead of maintaining it doesnt pay off.

Of course its a trade-off. If you have have substantially more reads than writes than it might be OK for your needs.

I did not realize that. Interesting. Thanks!

I think if you just prepend C-style comments (e.g. /* test */ SELECT...) it should still work with the query cache [1]. But leading line comments (like --) will not work [2].

[1] http://bugs.mysql.com/bug.php?id=824

[2] http://bugs.mysql.com/bug.php?id=40986

Does Rails not have an equivalent to the Django-Debug-Toolbar to easily debug stuff like this in development?

As for production, going with a fleshed-out service like NewRelic makes more sense to me. If I see slow queries start happening I'd want a broader report of what's going on in my system, not just a map of the query to a controller/action.

That's pretty nice. It would also be nice for ActiveRecord migrations to let me add comments to the database and the schema file.

Does anyone know if this exists for postgres?

Not yet! I'll gladly merge psql patches for this :)

+1 it'll be great if its a solution that will work with jdbc (for jruby)

Reply on news.ycombinator.com