Stoner's World

Red Hat Certified Engineer logo

At least, Red Hat thinks so. In August, I finally got around to taking the RHCE exam…and passed. Cert #805009770342158.  Don’t believe me? Click on the logo and plug in my cert number and see for yourself.

On many blogs, pages, books I read about measuring MySQL performance in terms of Queries Per Second (qps) and read/write ratio. Unfortunately, most places don’t explicitly tell you how to measure those. One of the reasons, I suspect, is the slowly changing internals of MySQL for reporting the nitty-gritty statistics to figure these out. Well, I’m here to say I don’t know them either, but…I’m going to take a stab at it and I could use some help.

My first stab is at measuring the read/write ratio. I’m working against MySQL 5.0.x releases and I’m looking at the Com_* counters from SHOW GLOBAL STATUS, grabbing them every $sleep_time seconds. There’s quite a number of them to look at. Many can be ignored, like Com_show_grants or Com_show_plugins. Of the rest, they get dumped into either a “read” or “write” bucket. From there, just do some simple math (calculate deltas, running totals, etc.). There are 2 Com_* counters that I consider as both read and write, Com_insert_select and Com_replace_select, because they read data from a table and write it back to a table. Here’s the list of Com_* counters I’m considering:

Reads: Com_select, Com_insert_select, Com_replace_select
Writes: Com_alter_*, Com_create_*, Com_delete*, Com_drop_*, Com_insert*, Com_load*, Com_rename*, Com_truncate, Com_update*

There are several Com_* counters that can (potentially) generate reads/writes in the database. Things like Com_check, Com_flush, Com_repair, etc. I’m not sure how to consider those. For now, I’m ignoring them. So, community, am I getting the correct counters? Am I interpreting them correctly?

Oh, you want to see the code? Fine. Jump over here.

Overall, I have a problem with counting statements. It doesn’t take into consideration data volume. If you use an extended INSERT, you could load thousands of rows but only register a single INSERT in the Com_insert counter. Similarly, if you do a ‘DELETE FROM tablename’, it only registers as a single statement in the Com_delete counter but it could cause a huge number of disk writes (in proportion to read statements.) So, after getting statement counting nailed down, I’ll be investigating how to measure data volume read versus data volume written. Looking at both statement counters and volume measurements should give admins a better idea how their database is performing.