0

I used the following view to summarize some metrics in my db. Is there any way to log the select queries per specific table instead?

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');
2
  • Configuring MySQL's logging has got nothing to do with sw development, this is a pure admin question. Therefore, this question is off topic here on SO. You may get more help at the DBA sister site of SO.
    – Shadow
    Aug 12, 2019 at 12:37
  • Have you looked here? Be careful though, enabling logging for all queries can impose an overhead. I'm not sure that it's possible just to enable logging for SELECT queries only.
    – Vérace
    Aug 12, 2019 at 15:02

1 Answer 1

0

Slight optimization:

SELECT ...
    round(VARIABLE_VALUE / usfs.VARIABLE VALUE)
    ...
FROM information_schema.GLOBAL_STATUS
JOIN ( select VARIABLE_VALUE from information_schema.GLOBAL_STATUS
               where VARIABLE_NAME = 'Uptime_since_flush_status') AS usfs 

That way, it will fetch Uptime_since_flush_status only once.

Turning on the "general log" is probably the only way to get more details. And that would be quite messy, since you would have to deduce the "current database" for each query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.