Brad Fitzpatrick (brad) wrote,
Brad Fitzpatrick
brad

MySQL query stats via packet capture

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.
Tags: mysql, tech
Subscribe
  • Post a new comment

    Error

    switch
    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
    Help
  • 3 comments