

MySQL query comments in Rails - themcgruff
http://37signals.com/svn/posts/3130-mini-tech-note-mysql-query-comments-in-rails

======
tlack
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
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
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
Brilliant. Thanks!

------
rosser
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
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
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...](http://www.mysqlperformanceblog.com/2008/03/20/mysql-query-cache-
whitespace-and-comments/)

Otherwise, neat idea, I like it.

~~~
themcgruff
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
Why?

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

------
Pewpewarrows
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
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
Does anyone know if this exists for postgres?

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

