Database tuning: ratio vs. rate

Mar 1, 2010 / By Sheeri Cabral

Tags: ,

Baron makes an excellent point in Why you should ignore MySQL’s key cache hit ratio — ratio is not the same as rate. Furthermore, rate is [often] the important thing to look at.

This is something that, at Pythian, we internalized a long time ago when thinking about MySQL tuning. In fact, mysqltuner 2.0 takes this into account, and the default configuration includes looking at both ratios and rates.

If I told you that your database had a ratio of temporary tables written to disk of 20%, you might think “aha, my database is slow because of a lot of file I/O caused by writing temporary tables to disk!”. However, that 20% ratio may actually mean a rate of 2 per hour — which is most likely not causing excessive I/O.

To get a sense of this concept, and also how mysqltuner works, I will show the lines from the mysqltuner default configuration that deal with temporary tables written to disk. The format is that the fields are separated by three pipes (|||), and the fields are:

label
threshold check
formula
recommendation if “threshold check” is met

Here is the line from the default configuration file that calculates the rate of temporary tables written to disk:

% temp disk tables|||>25|||Created_tmp_disk_tables / (Created_tmp_tables + Created_tmp_disk_tables) * 100|||Too many temporary tables are being written to disk.  Increase max_heap_table_size and tmp_table_size.

mysqltuner will parse that as:

if
the value of Created_tmp_disk_tables/(Created_tmp_tables + Created_tmp_disk_tables)*100
>25
then print out the last field.

So that means that a ratio of 25% or more is the threshold. But we found that many clients have a ratio much less than 25%, but still had excessive temporary tables written to disk. So the default configuration also contains a rate calculation of temporary tables written to disk:

temp disk rate|||=~ /second|minute/|||&hr_bytime(Created_tmp_disk_tables/Uptime)|||Too many temporary tables are being written to disk.  Increase max_heap_table_size and tmp_table_size.

mysqltuner will parse that as:

if
the value of &hr_bytime(Created_tmp_disk_tables/Uptime)
matches “second” or “minute”
then print out the last field.

The hr_bytime() function in mysqltuner takes a number that is a per-second rate and makes it “human readable” (hence “hr”) by returning the order of magnitude at which the value is >1. For example:

hr_bytime(2) returns “2.0 per second”
hr_bytime(0.2) returns “12.0 per minute”
hr_bytime(0.02) returns “1.2 per minute”
hr_bytime(0.002) returns “7.2 per hour”
hr_bytime(0.0002) returns “17.28 per day”

Certainly, 0.02 looks small, but “12 per minute” is a better metric for a DBA to understand the problem.

Because the configuration file for mysqltuner 2.0 contains the threshold and check, it is fairly simple to change what the threshold is, and to check both rates and ratios. mysqltuner also allows you to output in different formats (currently there’s “pretty” and “csv”, but it’s easy to add a perl subroutine to do something different with the output), which makes it ideal for doing regular tuning checks for what is most important for you.

Pythian uses it on one client to provide weekly reports, which we add to a spreadsheet so that differences are easy to see. (yes, output directly to a database is on the “features we want to add” — mysqltuner is just a perl script, so if anyone in the community wants to add it, they can create a branch and request the feature to be added into the main trunk…it is all on launchpad, at https://launchpad.net/mysqltuner, so community contributions are recommended and encouraged.)

5 Responses to “Database tuning: ratio vs. rate”

  • Eric Bergen says:

    I believe Baron is making the point that rate is important and ratio is not. You seem to support that point yet you show how mysqltuner output both ratio and rate. If you have internalized ratio as unimportant why is it still in mysqltuner and why are you trying to justify it?

  • Hi Eric,

    Good comment! I should amend — my point is that rate is not *as* important as ratio, for *immediate* tuning needs, and for some calculations.

    Knowing the ratio can be very important in many cases. For example, you could look at the ratio of slow queries vs. the rate of slow queries. Maybe the rate of slow queries is 50 per hour, but the ratio is 50% of your queries (because you’re a small shop without a lot of traffic). In that case you’d want to know that half your queries are “slow” (maybe you are logging queries not using indexes, and half your queries fall into that category).

    mysqltuner is meant for all sorts of shops, small and large. Pythian likes to be *proactive*, not just *reactive*, and ratios can help find latent problems. However, I do agree that rates are also important, particularly for finding current bottlenecks.

    In addition, mysqltuner 2.0 tries to show as much of the same information as the original did, and the original shows rate. I don’t like to remove information, as it’s easy enough for people to do it, and I think showing them both is important.

    I believe it’s important to look at *both* rate and ratio — I will amend my post to say “rate is often the important thing to look at” instead of “rate is the important thing to look at” because the current wording implies that ratio is always the important figure, and I think it depends on the case.

    Even for those that do believe that rates aren’t important, though, I wanted to show folks that it’s trivial to make your own checks in mysqltuner.

  • Tuningbox says:

    I agree with Sheeri. Ratios can find latent problems. I have applied it as well and it worked.

  • Welcome to the world of mature maintenance practices. Oracle tuning was all about ratios once up a time as well. I imagine the same paradigm shift will cement itself further as the MySQL product internal instrumentation matures and you can actually see what processes are doing and tune accordingly. MySQL is definitely coming of age.

  • I think the ratio is not so important. MySQL is always maintained in all the better. Ratios can find latent problems.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>