Log in

No account? Create an account
MySQL query stats via packet capture - brad's life — LiveJournal [entries|archive|friends|userinfo]
Brad Fitzpatrick

[ website | bradfitz.com ]
[ userinfo | livejournal userinfo ]
[ archive | journal archive ]

MySQL query stats via packet capture [Mar. 7th, 2004|09:47 pm]
Brad Fitzpatrick
[Tags|, ]

Played with Net::Pcap today, decoding MySQL connections in-flight and doing query stats, broken down by selects vs. all types of writes. Pretty cool. (Because MySQL's stats suck... or require restarts to enable.)

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.

[User Picture]From: gaal
2004-03-07 11:13 pm (UTC)
Just remember capture may lose packets on busy networks. It's great for stochastic measurements like this but don't rely on seeing e.g. everything related to a particular transaction.
(Reply) (Thread)
[User Picture]From: brad
2004-03-07 11:19 pm (UTC)
Yeah, I know.

For instance, in my upcoming "real time per query" statistic, I'll only count the transaction in the stats if I hear both the request and response. (as opposed to, say, hearing 10000 requests and dumping the time stats, assuming any unheard responses were still in progress and using the current time as their ending time...)

And now that I know what the MySQL protocol looks like, I'm sure I could improve my chances of not dropping packets by pushing up the packet filtering into my compiled pcap matching rule, as opposed to doing it in Perl. Net::Pcap is pretty nice. It feels more like C than a Perl API, so I guess I should say libpcap is pretty nice.
(Reply) (Parent) (Thread)
[User Picture]From: gaal
2004-03-07 11:49 pm (UTC)
Don't forget fragmentation then, either :-)
(Reply) (Parent) (Thread)