MySQL query comments in Rails

tlack 14 years 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 14 years 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...

girasquid 14 years 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/

famousactress 14 years ago | | |

Brilliant. Thanks!

rmc 14 years ago | |

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

famousactress 14 years 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.

rosser 14 years 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 14 years 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.

eli 14 years 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-c...

Otherwise, neat idea, I like it.

themcgruff 14 years 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.)

eli 14 years ago | | |

I did not realize that. Interesting. Thanks!

dholowiski 14 years ago | | |

Why?

ruckusing 14 years 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.

themcgruff 14 years 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.

Gigablah 14 years 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

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

Pewpewarrows 14 years 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.

billybob 14 years 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 14 years ago |

Does anyone know if this exists for postgres?

qrush 14 years ago | |

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

sandGorgon 14 years ago | |

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