6 ms·

MySQL query comments in Rails

35 points by themcgruff 14y ago

tlack 14y ago
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.
famousactress 14y ago
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...
- rmc 14y ago
  Django debug toolbar can show you where awk/grep queries are called
  - famousactress 14y ago
    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.
- girasquid 14y ago
  Playfire has released something that will tack a comment onto all your queries for you: https://github.com/playfire/django-append-url-to-sql/ https://github.com/playfire/django-append-url-to-sql/
  - famousactress 14y ago
    Brilliant. Thanks!
Pewpewarrows 14y ago
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.
eli 14y ago
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-cache-whitespace-and-comments/ http://www.mysqlperformanceblog.com/2008/03/20/mysql-query-c... Otherwise, neat idea, I like it.
- themcgruff 14y ago
  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.)
  - dholowiski 14y ago
    Why?
    - themcgruff 14y ago
      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.
    - ruckusing 14y ago
      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.
  - eli 14y ago
    I did not realize that. Interesting. Thanks!
- Gigablah 14y ago
  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 http://bugs.mysql.com/bug.php?id=824 [2] http://bugs.mysql.com/bug.php?id=40986 http://bugs.mysql.com/bug.php?id=40986
rosser 14y ago
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.
- golango 14y ago
  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.
billybob 14y ago
That's pretty nice. It would also be nice for ActiveRecord migrations to let me add comments to the database and the schema file.
jhuckestein 14y ago
Does anyone know if this exists for postgres?
- qrush 14y ago
  Not yet! I'll gladly merge psql patches for this :)
- sandGorgon 14y ago
  +1 it'll be great if its a solution that will work with jdbc (for jruby)

35 points by themcgruff 14y ago

tlack 14y ago

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.

famousactress 14y ago

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

rmc 14y ago
Django debug toolbar can show you where awk/grep queries are called
- famousactress 14y ago
  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.
girasquid 14y ago
Playfire has released something that will tack a comment onto all your queries for you: https://github.com/playfire/django-append-url-to-sql/ https://github.com/playfire/django-append-url-to-sql/
- famousactress 14y ago
  Brilliant. Thanks!

Pewpewarrows 14y ago

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.

eli 14y ago

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-cache-whitespace-and-comments/ http://www.mysqlperformanceblog.com/2008/03/20/mysql-query-c... Otherwise, neat idea, I like it.

themcgruff 14y ago
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.)
- dholowiski 14y ago
  Why?
  - themcgruff 14y ago
    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.
  - ruckusing 14y ago
    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.
- eli 14y ago
  I did not realize that. Interesting. Thanks!
Gigablah 14y ago
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 http://bugs.mysql.com/bug.php?id=824 [2] http://bugs.mysql.com/bug.php?id=40986 http://bugs.mysql.com/bug.php?id=40986

rosser 14y ago

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.

golango 14y ago
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.

billybob 14y ago

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

jhuckestein 14y ago

Does anyone know if this exists for postgres?

qrush 14y ago
Not yet! I'll gladly merge psql patches for this :)
sandGorgon 14y ago
+1 it'll be great if its a solution that will work with jdbc (for jruby)