Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
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.
Attachments
Issue Links
- relates to
-
MDEV-7216 COM_Select goes up together with Qcache_hits
-
- Closed
-
Activity
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.
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.
Test suite for the bug
– source include/have_query_cache.inc
#
- 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;
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).
QC should be transparent, so SELECT_COMMANDS should count QC answers.
EMPTY_QUERIES (both) counts queries with empty results (So I was wrong initially).
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)