How to determine MySQL queries per day?
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
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