What is your read:write ratio in MySQL?

In a recent post, I mentioned that we needed a MySQL clustering solution that scaled well with both reads and writes. I was fairly sure we handled more reads than writes. So I asked around: what percentage of our operations were reads? Nobody knew. Not content to leave the question unanswered, I spent 5 minutes figuring it out.

Do you know how to figure out what percentage of your MySQL operations are reads vs. writes?

It’s actually quite easy to do. One query and a calculator will tell you.

SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_insert', 'Com_update', 'Com_select', 'Com_delete');

Here were our results:

Command Count
Com_delete 102179
Com_insert 94037342
Com_select 372330876
Com_update 58800298

A little math will give you your read-to-write ratio. Ours is 71:29.

Take a minute to find your read-to-write ratio and post it in a comment. Let’s see what variation there is in this world.

Now write yourself a little script that takes these values (from time to time) and plots their differences. See how your ratio changes over time.

MySQL Reads and Writes

What is your read:write ratio in MySQL?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s