[MDEV-4981] Account for queries handled by query-cache in USER_STATISTICS (and in HOST_STATISTICS) Created: 2013-09-03  Updated: 2014-11-26  Resolved: 2013-11-07

Status: Closed
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Major
Reporter: Bruno Prémont (Inactive) Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-7216 COM_Select goes up together with Qcac... Closed

 Description   

[Affected version: at least 5.5.28a-MariaDB-log]

When a user performs queries handled by query-cache theses are not taken into account by USER_STATISTICS (and probably neither by HOST_STATISTICS).

They should properly increment COMMAND_SELECT, SENT_ROWS and when appropriate EMPTY_QUERIES.

Without updating those a user that performs the same SELECT over and over, each time hitting query cache will appear as performing no queries, just burning CPU time and exchanging bytes.

 Comments   
Comment by Oleksandr Byelkin [ 2013-09-30 ]

After some digging into sources I found that EMPTY_QUERIES counts really empty queries (when parser faces EOF and put SQLCOM_EMPTY_QUERY as a command, which is irrelevant to SQLCOM_SELECT completely)

Comment by Bruno Prémont (Inactive) [ 2013-09-30 ]

The description at https://mariadb.com/kb/en/user-statistics/ seems to not really agree on the meaning of this field:

CLIENT_STATISTICS
EMPTY_QUERIES int(21) The number of times this client's connections sent queries that returned no results to the server.

USER_STATISTICS
EMPTY_QUERIES int(21) The number of times this user's connections sent empty queries to the server.

Comment by Oleksandr Byelkin [ 2013-10-01 ]

Above is definitely bug in the description (of CLIENT_STATISTICS), I filed it here https://mariadb.atlassian.net/browse/MDEV-5083

USER_STATISTICS description is correct but maybe is not clear.

Comment by Oleksandr Byelkin [ 2013-10-01 ]

Test suite for the bug

– source include/have_query_cache.inc

#

  1. Tests with query cache
    #
    SET GLOBAL userstat=1;
    set GLOBAL query_cache_size=1355776;
    FLUSH USER_STATISTICS;
    FLUSH CLIENT_STATISTICS;
    reset query cache;
    flush status;

create table t1 (a int);
insert into t1 values (1);
select * from t1;
select SELECT_COMMANDS,ROWS_SENT from INFORMATION_SCHEMA.CLIENT_STATISTICS
where CLIENT="localhost";
select SELECT_COMMANDS,ROWS_SENT from INFORMATION_SCHEMA.USER_STATISTICS
where USER="root";
show status like "Qcache_hits";
select * from t1;
select * from t1;
select SELECT_COMMANDS,ROWS_SENT from INFORMATION_SCHEMA.CLIENT_STATISTICS
where CLIENT="localhost";
select SELECT_COMMANDS,ROWS_SENT from INFORMATION_SCHEMA.USER_STATISTICS
where USER="root";
show status like "Qcache_hits";

drop table t1;
SET GLOBAL query_cache_size=default;
SET GLOBAL userstat=default;

Comment by Oleksandr Byelkin [ 2013-10-01 ]

When SELECT_COMMANDS is a matter for discussing (which we will), but ROWS_SENT is definitely a bug (information is present in QC and returned in EOF packet so the problem is somewhere in information schema or statistics collection).

Comment by Oleksandr Byelkin [ 2013-10-01 ]

QC should be transparent, so SELECT_COMMANDS should count QC answers.

Comment by Oleksandr Byelkin [ 2013-10-01 ]

Normal execution is fixed. now have to check SP and PS.

Comment by Oleksandr Byelkin [ 2013-10-01 ]

EMPTY_QUERIES (both) counts queries with empty results (So I was wrong initially).

Comment by Oleksandr Byelkin [ 2013-10-04 ]

Committed for review.

Comment by Oleksandr Byelkin [ 2013-10-15 ]

pushed to 5.5

Generated at Thu Feb 08 07:00:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.