Currently I'm just counting the occurences of each query type (canonicalized by replacing constants with "?", IN clauses to "IN (...)", etc...) and reporting a top-40 list every few seconds, but I plan to listen to responses too, and measure how long it takes for MySQL to reply, then I'll keep "total elapsed time" stats per query type as well.
For example:
10006 (27.79%) HANDLER READ user ... 3362 ( 9.34%) SELECT COUNT(*) FROM reluser WHERE userid=? AND type=? AND targetid=? 2680 ( 7.44%) SELECT groupmask FROM friends WHERE userid=? AND friendid=? 1965 ( 5.46%) SELECT s2lid, compdata, comptime FROM s2compiled 1781 ( 4.95%) SELECT userid, UNIX_TIMESTAMP(timeupdate) FROM userusage WHERE userid IN (...) 1326 ( 3.68%) SELECT upropid, value FROM userprop WHERE userid=? AND upropid IN (...) 1292 ( 3.59%) SELECT styleid, userid, name, modtime FROM s2styles WHERE styleid=? 815 ( 2.26%) SELECT COUNT(*) FROM memorable WHERE userid=? .... (cut)
But sometimes it's useful to get more details on a query, without constants being removed. For instance, here's a zoom of the reluser queries:
3538 (58.97%) SELECT COUNT(*) FROM reluser WHERE userid=? AND type='A' AND targetid=?
823 (13.72%) SELECT COUNT(*) FROM reluser WHERE userid=? AND type='P' AND targetid=?
680 (11.33%) SELECT u.user FROM user u, reluser ru WHERE ru.userid=u.userid AND ru.type='P' AND u.statusvis='V' AND ru.targetid=?
531 ( 8.85%) SELECT COUNT(*) FROM reluser WHERE userid=? AND type='B' AND targetid=?
328 ( 5.47%) SELECT userid FROM reluser WHERE targetid=? AND type='A'
49 ( 0.82%) SELECT u.user FROM reluser r, user u WHERE u.userid=r.userid AND r.targetid=? AND r.type='P' AND u.statusvis='V'
38 ( 0.63%) SELECT r.targetid, u.user FROM reluser r, user u WHERE r.targetid=u.userid AND r.userid=? AND r.type='B'
11 ( 0.18%) SELECT u.user FROM user u, reluser r WHERE r.userid=? AND r.targetid=u.userid AND r.type='A'
1 ( 0.02%) SELECT COUNT(*) FROM reluser WHERE userid=? AND type='M' AND targetid=?
1 ( 0.02%) SELECT u.user FROM reluser r, user u WHERE u.userid=r.targetid AND r.userid=? AND r.type='P' AND u.statusvis='V'Those could definitely be put in memcached. I guess it just never showed up on the radar until lately, because nowadays most stuff is in memcached.
