13

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.

  • 1
    The Queries global status variable is counting everything since the server was last started... which was SHOW STATUS LIKE 'Uptime'; seconds ago. Many status variables are cleared with FLUSH STATUS; but Queries is not, at least in the test servers I confirmed it on just now, which were MySQL 5.5.19 and 5.6.14. – Michael - sqlbot Jan 23 '14 at 23:46
13

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

  • Thanks @mfouilleul, this is helpful. I'm going to combine this with the duration var and figure out the volume of queries. – AJB Jan 24 '14 at 1:12
  • 1
    Just to clarify, show global status like 'Com_%'; commands are for the whole server, right? What would an alternative be in a shared enviroment -- e.g.: to estimate how far we are from max_questions/ max Queries Per Hour (QPH) being hit. – Fabien Snauwaert Sep 28 '18 at 16:42
8

I use this view to keep an eye on the number of queries per second, minute, hour and day:

create or replace view _dba_query_stats as
select 
  SUBSTRING(VARIABLE_NAME, 5) as query_type, 
  VARIABLE_VALUE as total_count, 
  round(VARIABLE_VALUE / ( select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status'), 2) as per_second,
  round(VARIABLE_VALUE / ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status') / (60)))       as per_minute,
  round(VARIABLE_VALUE / ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status') / (60*60)))    as per_hour, 
  round(VARIABLE_VALUE / ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status') / (60*60*24))) as per_day,
  FROM_UNIXTIME(round(UNIX_TIMESTAMP(sysdate()) - (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status'))) report_period_start,
  sysdate() as report_period_end,
  TIME_FORMAT(SEC_TO_TIME((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status')),'%Hh %im') as report_period_duration
from 
  information_schema.GLOBAL_STATUS 
where 
  VARIABLE_NAME in ('Com_select', 'Com_delete', 'Com_update', 'Com_insert');

Sample output:

query_type total_count per_second per_minute per_hour per_day report_period_start report_period_end   report_period_duration
DELETE               0          0          0       0        0 2017-04-16 03:46    2017-04-20 22:14:56 114h 28m
INSERT           36595       0.09          5     320     7672 2017-04-16 03:46    2017-04-20 22:14:56 114h 28m
SELECT        14842019      36.02       2161  129656  3111738 2017-04-16 03:46    2017-04-20 22:14:56 114h 28m
UPDATE          189137       0.46         28    1652    39654 2017-04-16 03:46    2017-04-20 22:14:56 114h 28m

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.