Home
  •  

MySQL Server Query Cache Hit Percent Monitor

Microsoft.MySQLServer.ManagedServer.Unix.QueryCacheHitPct.Monitor (UnitMonitor)

Knowledge Base article:

Summary

The percent of queries that are satisfied from the query cache. The query cache stores the contents of a select statement with the corresponding result; these results are invalidated after an insert, update, or delete statement. The query cache size is controlled using the global variable “query_cache_size”. The size of a query that can be inserted into the query cache is controlled by the global variable “query_cache_limit”. The monitor threshold is analyzed against an average of values returned in polls to the agent where the Number of Samples parameter controls the number of samples included in the calculation of the average.

Causes

An unhealthy state indicates the percentage of queries satisfied from the query cache is low. The query cache might not have enough storage for recently used queries. Additionally frequent insert, update, and delete statements will purge the query cache.

Resolutions

To address a low query cache hit percent increase the query_cache_size to allow additional queries to be stored. Increasing query_cache_limit will allow larger queries to be stored in the query cache if most queries are above that limit. Both of these variables can be modified by adding the respective variable name followed by size under the instance group in the configuration file.

Configuration

Default Configuration

Parameter

Default Value

Warning Threshold

Less than 90

Error Threshold

Less than 80

Number of Samples

3

Overrides can be used to change the parameter values defined above for all instances or for specific instances or groups.

Element properties:

TargetMicrosoft.MySQLServer.ManagedServer.Unix
Parent MonitorMicrosoft.MySQLServer.QueryCachePerformanceRollup
CategoryPerformanceHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityMatchMonitorHealth
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.Oss.WSManEnumeration.LessThanThreshold.ThreeState.MonitorType
RemotableTrue
AccessibilityPublic
Alert Message
MySQL Server Query Cache Hit Percent
The current Query Cache Hit Percent ({0}\%) for the MySQL Server is low
RunAsDefault

Source Code:

<UnitMonitor ID="Microsoft.MySQLServer.ManagedServer.Unix.QueryCacheHitPct.Monitor" Accessibility="Public" Target="Microsoft.MySQLServer.ManagedServer.Unix" TypeID="Oss!Microsoft.Oss.WSManEnumeration.LessThanThreshold.ThreeState.MonitorType" Enabled="onStandardMonitoring" ParentMonitorID="Microsoft.MySQLServer.QueryCachePerformanceRollup">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.MySQLServer.ManagedServer.Unix.QueryCacheHitPct.AlertMessage">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>MatchMonitorHealth</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Data/Context/Value$</AlertParameter1>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState HealthState="Success" MonitorTypeStateID="StatusOK" ID="OverThreshold"/>

    <OperationalState HealthState="Warning" MonitorTypeStateID="StatusWarning" ID="UnderWarningThreshold"/>

    <OperationalState HealthState="Error" MonitorTypeStateID="StatusError" ID="UnderErrorThreshold"/>

  </OperationalStates>

  <Configuration>

    <Interval>300</Interval>

    <TargetSystem>$Target/Host/Property[Type="Unix!Microsoft.Unix.Computer"]/NetworkName$</TargetSystem>

    <Uri>http://schemas.microsoft.com/wbem/wscim/1/cim-schema/2/MySQL_ServerStatistics?__cimnamespace=root/mysql</Uri>

    <WSManFilter/>

    <ObjectName>MySQL Server</ObjectName>

    <CounterName>Query Cache Hit Pct</CounterName>

    <InstanceXPath>/DataItem/WsManData/*[local-name(.)='MySQL_ServerStatistics']/*[local-name(.)='InstanceID']</InstanceXPath>

    <InstanceName>$Target/Property[Type="Microsoft.MySQLServer.Installation"]/InstanceID$</InstanceName>

    <Value>$Data/WsManData/*[local-name(.)='MySQL_ServerStatistics']/*[local-name(.)='QCacheHitPct']$</Value>

    <WarningThreshold>90</WarningThreshold>

    <ErrorThreshold>80</ErrorThreshold>

    <NumSamples>3</NumSamples>

  </Configuration>

</UnitMonitor>