Mysql – How to determine MySQL queries per day

MySQL

I'm investigating the big switch from MySQL to a NoSQL DBaaS and I've run into an issue trying to forecast expenses. Essentially, I can't figure out how many queries my current MySQL server handles per day to try and estimate the number of requests I'll be using with Cloudant, which charges $0.015 per 100 PUTs, POSTs, and DELETEs and $0.015 per 500 GETs and HEADs.

I've found a lot of information about using SHOW STATUS and SHOW GLOBAL STATUS to get the stats that MySQL collects on itself, but there's no timeframe reference.

For instance, SHOW GLOBAL STATUS returns the following:

Queries                           | 13576675

Which is great, except I have no idea the timeframe that wraps that number. 13 million queries when? Per month? Year? Since the beginning of time?

The MySQL docs don't really elaborate too much:

Queries

The number of statements executed by the server. This variable includes statements executed within stored programs, unlike
the Questions variable. It does not count COM_PING or COM_STATISTICS
commands. This variable was added in MySQL 5.0.76.

Thanks in advance for any help.

Best Answer

For SELECTs:

show global status like "Com_select";

UPDATEs:

show global status like "Com_update";

INSERTs: 

show global status like "Com_insert";

DELETEs:

show global status like "Com_delete";

ALl values are "cumulativ" since MySQL last restart.

So to get your SELECTs in one hour:

At 9pm:

[21:00:00] [DEV\(none)] mysql> show global status like "Com_select";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 671664 |
+---------------+--------+
1 row in set (0.00 sec)

At 10pm:

[22:00:00] [DEV\(none)] mysql> show global status like "Com_select";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 672363 |
+---------------+--------+
1 row in set (0.00 sec)

The number of SELECT in the past hour : 672363 - 671664 = 699

Best Regards

Related Question