The Good, the Blog & the Ugly - Tim Cook's Weblog
- All
- MySQL
- Performance
- Personal
- Sun
Thursday September 17, 2009
querystat - DTrace script to monitor your queries, query cache and server thread pre-emption
I was recently helping some colleagues check what was happening with their MySQL queries, and wrote a DTrace script to do it. Time to share that script.
First of all, a look at some output from the script:
mashie[bash]# ./querystat.d -p `pgrep mysqld`
Tracing started at 2009 Sep 17 16:28:35
2009 Sep 17 16:28:38 throughput 3 queries/sec
2009 Sep 17 16:28:41 throughput 4 queries/sec
2009 Sep 17 16:28:44 throughput 528 queries/sec
2009 Sep 17 16:28:47 throughput 1603 queries/sec
2009 Sep 17 16:28:50 throughput 1676 queries/sec
^C
Tracing ended at 2009 Sep 17 16:28:51
Average latency, all queries: 107 us
Latency distribution, all queries (us):
value ------------- Distribution ------------- count
16 | 0
32 |@@ 170
64 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 3728
128 |@@@@@ 533
256 | 26
512 | 18
1024 | 2
2048 | 1
4096 | 0
8192 | 1
16384 | 1
32768 | 0
Query cache statistics:
count hit: 6
count miss: 4474
avg latency miss: 107 (us)
avg latency hit: 407 (us)
Latency distribution, for query cache hit (us):
value ------------- Distribution ------------- count
64 | 0
128 |@@@@@@@@@@@@@ 2
256 |@@@@@@@ 1
512 |@@@@@@@@@@@@@@@@@@@@ 3
1024 | 0
Latency distribution, for query cache miss (us):
value ------------- Distribution ------------- count
16 | 0
32 |@@ 170
64 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 3728
128 |@@@@@ 531
256 | 25
512 | 15
1024 | 2
2048 | 1
4096 | 0
8192 | 1
16384 | 1
32768 | 0
Average latency when query WAS NOT pre-empted: 73 us
Average latency when query WAS pre-empted: 127 us
Pre-emptors:
[...]
mysql 6
Xorg 18
sched 25
firefox-bin 44
sysbench 3095
|
You can see that while the script is running (prior to pressing <Ctrl>-C), we get a throughput count every 3 seconds.
Then we get some totals, some averages, and even some distribution histograms, covering all queries, then with breakdowns on whether we used the query cache, and whether the thread executing the query was pre-empted.
This may be useful for determining things like:
- Do I have some queries in my workload that consume a lot more CPU than others?
- Is the query cache helping or hurting?
- Are my database server threads being pre-empted (kicked off the CPU) by (an)other process(es)?
Things have become easier since I first tried this, and had to use the PID provider to trace functions in the database server.
If you want to try my DTrace script, get it from here. NOTE: You will need a version of MySQL with DTrace probes for it to work.
Posted at 05:04PM Sep 17, 2009 by timc in MySQL | Comments[0]
Friday September 04, 2009
nicstat - the Solaris and Linux Network Monitoring Tool You Did Not Know You Needed
Update - October 2009
Just a little one - nicstat now works on shared-ip Solaris zones.
Update - September 2009
OK, this is heading toward overkill...
The more I publish updates, the more I get requests for enhancement of nicstat. I have also decided to complete a few things that needed doing.
The improvements for this month are:
- Added support for a "fd" or "hd" (in reality anything starting with an upper or lower-case F or H) suffix to the speed settings supplied via the "-S" option. The Linux version now calculates %Util the same way as the Solaris version.
- Added a script, enicstat, which uses ethtool to get speeds and duplex modes for all interfaces, then calls nicstat with an appropriate -S value.
- Made the Linux version more efficient.
- Combined the Solaris and Linux source into one nicstat.c. This is a little ugly due to #ifdef's, but that's the price you pay.
- Wrote a man page.
- Wrote better Makefile's for both platforms
- Wrote a short README
- Licensed nicstat under the Artistic License 2.0
All source and binaries will from now on be distributed in a tarball. This blog entry will remain the home of nicstat for the time being.
Lastly, I have heard the requests for easier availability in OpenSolaris. Stay tuned.
Update - August 2009
That's more like it - we should get plenty of coverage now :)
A colleague pointed out to me that nicstat's method of calculating utilization for a full-duplex interface is not correct.
Now nicstat will look for the kstat "link_duplex" value, and if it is 2 (which means full-duplex), it will use the greater of rbytes or wbytes to calculate utilization.
No change to the Linux version. Use the links in my previous post for downloading.
Update - July 2009
I should probably do this at least once a year, as nicstat needs more publicity...
A number of people have commented to me that nicstat always reports "0.00" for %Util on Linux. The reason for this is that there is no simple way an unprivileged user can get the speed of an interface in Linux (quite happy for someone to prove me wrong on that however).
Recently I got an offer of a patch from David Stone, to add an option to nicstat that tells it what the speed of an interface is. Pretty reasonable idea, so I have added it to the Linux version. You will see this new "-S" option explained if you use nicstat's "-h" (help) option.
I have made another change which makes nicstat more portable, hence easier to build on Linux.
History
A few years ago, a bloke I know by the name of Brendan Gregg wrote a Solaris kstat-based utility called nicstat. In 2006 I decided I needed to use this utility to capture network statistics in testing I do. Then I got a request from a colleague in PAE to do something about nicstat not being aware of "e1000g" interfaces.
I have spent a bit of time adding to nicstat since then, so I thought I would make the improved version available.
Why Should I Still Be Interested?
nicstat is to network interfaces as "iostat" is to disks, or "prstat" is to processes. It is designed as a much better version of "netstat -i". Its differences include:
- Reports bytes in & out as well as packets.
- Normalizes these values to per-second rates.
- Reports on all interfaces (while iterating)
- Reports Utilization (rough calculation as of now)
- Reports Saturation (also rough)
- Prefixes statistics with the current time
How about an example?
eac-t2000-3[bash]# nicstat 5
Time Int rKB/s wKB/s rPk/s wPk/s rAvs wAvs %Util Sat
17:05:17 lo0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
17:05:17 e1000g0 0.61 4.07 4.95 6.63 126.2 628.0 0.04 0.00
17:05:17 e1000g1 225.7 176.2 905.0 922.5 255.4 195.6 0.33 0.00
Time Int rKB/s wKB/s rPk/s wPk/s rAvs wAvs %Util Sat
17:05:22 lo0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
17:05:22 e1000g0 0.06 0.15 1.00 0.80 64.00 186.0 0.00 0.00
17:05:22 e1000g1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
eac-t2000-3[bash]# nicstat -i e1000g0 5 4
Time Int rKB/s wKB/s rPk/s wPk/s rAvs wAvs %Util Sat
17:08:49 e1000g0 0.61 4.07 4.95 6.63 126.2 628.0 0.04 0.00
17:08:54 e1000g0 0.06 0.04 1.00 0.20 64.00 186.0 0.00 0.00
17:08:59 e1000g0 239.2 2.33 174.4 33.60 1404.4 71.11 1.98 0.00
17:09:04 e1000g0 0.01 0.04 0.20 0.20 64.00 186.0 0.00 0.00
|
For more examples, see the man page.
References & Resources
-
nicstat-1.21.tar.gz - source and binaries (for Solaris 9
i386/SPARC and Linux i386).
Note - the Solaris binaries will work on later releases; and probably on earlier releases of Solaris - as Solaris is just like that...
- brendangregg.com - Downloads, the original nicstat
- solarisinternals.com - Performance Tool List
- OpenSolaris Forums - Posting from Brendan about recent updates to nicstat
- Blog O' Matty - Viewing NIC throughput with nicstat
- Weak Focus - Nicstat, Solaris network utilization
Posted at 02:35PM Sep 04, 2009 by timc in Performance | Comments[3]
This is a personal weblog, I do not speak for my employer.