Query cache hitrate
Job details
| Name: | Query cache hitrate |
| Platform: | Mysql |
| Category: | Capacity |
| Description: | Shows the query cache hit rate. |
| Long description: | Shows the query cache hit rate. |
| Version: | 3.1 |
| Default schedule: | 1,11,21,31,41,51 * * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & is_mysql_branch=‘1′]/.[hasengine=‘YES’ & use_global_variables_performance_schema = ‘1′ ] |
Parameters
| Name | Default value | Description |
|---|---|---|
| warning threshold | 50 | The minimum percentage value value of hit rate amongst cacheable queries (over a period of time defined by the [time threshold] parameter) before a warning is triggered. |
| alarm threshold | 500 | The minimum percentage value of hit rate amongst cacheable queries (over a period of time defined by the [time threshold] parameter) before an alarm is triggered. |
| threshold (time) | 20 | A period of time (in minutes) which must be passed (combined with the hit rate amongst cacheable queries) before an alarm or a warning is returned by the procedure. |
| enable warnings and alarms | NO | If set to “NO” (default), the alert will only collect statistics without returning status warning or alarm. Value “YES“ will activate the alert. |
| history threshold | 7 | The maximum number of day to keep statistics for in the history tables. |
| min query cache hits | 100000 | The minimum average value of QCACHE_HITS (over a period of time defined by the “threshold (time)” parameters) before an alarm or a warning is triggered. |
Job Summary
- Purpose: The purpose of this dbWatch monitoring job is to monitor and report on the Query cache hit rate in a MySQL database instance. This is essentially the ratio of the cache hits to the total number of queries, calculated with the formula ‘Qcache_hits / (Qcache_hits + Qcache_inserts).’
- Why: This job is essential to ensure optimal performance and reliability of the MySQL database. Monitoring the Query cache hit rate helps in assessing the system’s efficiency in retrieving previously executed queries from the cache, rather than parsing and executing the queries again. However, the job also acknowledges the fact that query cache has been disabled by default from MySQL 5.6 onwards due to scalability issues with high-workload tasks on multi-core machines.
- Manual checking: You can check this manually in the database by issuing these SQL commands:
SELECT cast(VARIABLE_VALUE as signed) FROM performance_schema.global_status WHERE variable_name = 'QCACHE_HITS';SELECT cast(VARIABLE_VALUE as signed) FROM performance_schema.global_status WHERE variable_name = 'QCACHE_INSERTS';Output Breakdown
Information extracted from the XML file can be organized under the following categories:
General Information
- Artifact ID: mysql_query_cache
- Group: com.dbwatch.job
- Name: Query cache hitrate
- Version: 3.1
- Company: dbwatch.com
- Default schedule: 1,11,21,31,41,51 * * *
Dependencies
- Main procedure: dbw_query_cache
- Dependency tables: dbw_query_cache_histr and dbw_query_cache_last
Long Description
“The job calculates the query cache hit rate. The query cache stores the text of a SELECT statement and the corresponding result sent to the client. If an identical statement is received later, the server gets the results from the query cache rather than parsing and executing the statement again. The query cache has been disabled-by-default since MySQL 5.6 due to non-scalability with high-throughput workloads on multi-core machines. The hit rate among cacheable queries is measured using the formula: Qcache_hits / (Qcache_hits + Qcache_inserts).”
Report Template
- Version: 2
- Title: Query cache
- Description: Query cache
- Default Schedule: 0 * * *
- Text: Query cache
- Chapter Title: Query cache
Report Presentations
There are two presentations included in the Job:
1. Query cache hitrate:
- Select-call: Retrieves “Query cache hitrate” and “History date”
2. Query cache hit history:
- Select-call: Retrieves “Cache hits”, “Cache inserts” and “History date”
Post-implementation
No specific actions are described for post-implementation.
Replacement During Installation
The monitoring job does not specify any replacement requirements during installation.
Force Install
The job is assigned to force-install regardless of the presence of older versions.
Category
The Jobs falls under the ‘Capacity’ category.
Compatibility Condition
The compatibility query suggests that this job is compatible with MySQL database instances which have the performance-schema-global-variables enabled.