How to determine MySQL queries per day?

Question

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.

score 25 · Accepted Answer · answered Jan 23 '14 at 20:43

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

score 16 · Answer 2 · answered Apr 20 '17 at 22:26

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

score 0 · Answer 3 · edited Jul 10 '24 at 18:51

Newer versions of MySQL have the global_status in performance_schema rather than information_schema, so here is Matty's answer for newer MySQL versions.

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 performance_schema.global_status where VARIABLE_NAME = 'Uptime_since_flush_status'), 2) as per_second,
  round(VARIABLE_VALUE / ((select VARIABLE_VALUE from performance_schema.global_status where VARIABLE_NAME = 'Uptime_since_flush_status') / (60)))       as per_minute,
  round(VARIABLE_VALUE / ((select VARIABLE_VALUE from performance_schema.global_status where VARIABLE_NAME = 'Uptime_since_flush_status') / (60*60)))    as per_hour,
  round(VARIABLE_VALUE / ((select VARIABLE_VALUE from performance_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 performance_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 performance_schema.global_status where VARIABLE_NAME = 'Uptime_since_flush_status')),'%Hh %im') as report_period_duration
from
  performance_schema.global_status
where
  VARIABLE_NAME in ('Com_select', 'Com_delete', 'Com_update', 'Com_insert');

How to determine MySQL queries per day?

3 Answers3

Linked