Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.8-1
Description
Change sql_cache.cc and sql_cache.h to add hit rates and statistics
usable with qc_info plugin
Statistics:
total hits / total time expent in query cache
hits in three categories:
1) low period category: period between the last hit and the current hit < query expent time
2) high period category: period < query expent time * 10 and > query expent time
3) outlier category: period > 1 second, and period > query expent time * 10
3.1) in a future version instead of * 10, we could use number of competitors (concurrent) when inserting this query at query cache
for example if we have 9 concurrent inserts of the same query at query cache this number *10 is an acceptable value for high period, if we have n concurrent inserts a (n+1) value is acceptable
These categories helps to understand what kind of query we have cached:
1) low period hits: in this case if we lose this query entry, we will have a performace problem, and users will tell "the server is slow now"
2) high period hits: we have a query that is executed with a good frequency, removing it we will have a higher i/o in disks
3) outlier period hits: this query isn't a query that we should cache, or we can loose it and we don't see users reporting "hey the server is slow, or the disks is spinning a lot"
this allow a better query cache control (MDEV-4584) and a better cache hit rate when optimizing queries with SQL_CACHE/SQL_NO_CACHE
—
a second part could be an variable (query_cache_stats=0/1) to turn off/on the statistics and use a block memory just to statistics instead of one big block sharing memory with queries flags
in this case:
turn on -> off, should clean this stats memory block
turn off -> on, should create stats with 0 value (maybe a problem with lock contention), or clean all query cache and start from zero
changing query cache queries (invalidate, table invalidate, reallocation, etc) should handle the stats block too
—
tables of this plugin:
query_cache_queries:
QUERY_CACHE_ID | STATEMENT_SCHEMA | STATEMENT_TEXT | RESULT_FOUND_ROWS | QUERY_ROWS | SELECT_ROWS_READ | QUERY_HITS | QUERY_HITS_PERIOD_LOW | QUERY_HITS_PERIOD_HIGH | QUERY_HITS_PERIOD_OUTLIERS | QUERY_HITS_TOTAL_TIME_US | QUERY_HITS_MEAN_PERIOD_US | QUERY_HITS_MEAN_PERIOD_LOW_US | QUERY_HITS_MEAN_PERIOD_HIGH_US | QUERY_INSERT_TIME | QUERY_LAST_HIT_TIME | SELECT_EXPEND_TIME_US | SELECT_LOCK_TIME_US | TABLES_TYPE | RESULT_LENGTH | RESULT_BLOCKS_COUNT | RESULT_BLOCKS_SIZE | RESULT_BLOCKS_SIZE_USED | FLAGS_CLIENT_LONG_FLAG | FLAGS_CLIENT_PROTOCOL_41 | FLAGS_PROTOCOL_TYPE | FLAGS_MORE_RESULTS_EXISTS | FLAGS_IN_TRANS | FLAGS_AUTOCOMMIT | FLAGS_PKT_NR | FLAGS_CHARACTER_SET_CLIENT | FLAGS_CHARACTER_SET_RESULTS | FLAGS_COLLATION_CONNECTION | FLAGS_LIMIT | FLAGS_TIME_ZONE | FLAGS_SQL_MODE | FLAGS_MAX_SORT_LENGTH | FLAGS_GROUP_CONCAT_MAX_LEN | FLAGS_DIV_PRECISION_INCREMENT | FLAGS_DEFAULT_WEEK_FORMAT | FLAGS_LC_TIME_NAMES |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | teste | select * from t1 | 3 | 3 | 3 | 0 | 0 | 0 | 0 | 0 | 1411247500.713750 | 232 | 35 | NON TRANSACT | 78 | 1 | 512 | 142 | 1 | 1 | 0 | 0 | 0 | 1 | 1 | utf8 | utf8 | utf8_general_ci | -1 | SYSTEM | PIPES_AS_CONCAT,ALLOW_INVALID_DATES | 1024 | 1024 | 5 | 0 | en_US |
query_cache_queries_tables:
QUERY_CACHE_ID | SCHEMA | TABLE_NAME | TABLE_SUFFIX |
---|---|---|---|
1 | teste | t1 | (here we display partition name like P#p0) |
query_cache_tables
TABLE_SCHEMA | TABLE_NAME | TABLE_HASHED | TABLE_TYPE | QUERIES_IN_CACHE |
---|---|---|---|---|
teste | t1 | 1 | NON_TRANSACT | 1 |
Attachments
Issue Links
- duplicates
-
MDEV-4571 CLOSE - Query cache like features
- Closed
-
MDEV-4589 CLOSE - query cache expent time searching query
- Closed
- includes
-
MDEV-4581 QUERY CACHE - ADD more columns in QC_INFO plugin
- Closed
- is blocked by
-
MDEV-6781 bug with query cache when using views
- Closed
- relates to
-
MDEV-4454 Query Cache Partitioning
- Open
-
MDEV-6777 Query cache with inconsistent results + TTL
- Open
-
MDEV-6778 Include number of concurrent queries while insert into query cache
- Open
- links to