Home
  •  

MySQL Server Key Cache Write Percent Monitor

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

Knowledge Base article:

Summary

The percent of physical writes of a key block to disk versus requests to write a key block to the key cache. The MyISAM Key cache is a data structure that contains the most-used index blocks. The key cache size is controlled using the global variable ‘key_buffer_size’. 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 writes to the key cache compared to the writes to disk is low. The key cache might not have enough space to store all key blocks. The key cache is updated with a least recently used algorithm.

Resolutions

To address low percent of key cache hits increase key_buffer_size in the configuration file. This can be done by adding the following line along with size under the instance group options: “key_buffer_size= size”. Alternatively the server can be started with the following option: “--key_buffer_size=size”

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.KeyCachePerformanceRollup
CategoryPerformanceHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityMatchMonitorHealth
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.Oss.WSManEnumeration.LessThanThreshold.ThreeState.MonitorType
RemotableTrue
AccessibilityPublic
Alert Message
MySQL Server Key Cache Write Percent
The current Key Cache Write Percent ({0}\%) for the MySQL Server is low
RunAsDefault

Source Code:

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

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.MySQLServer.ManagedServer.Unix.KeyCacheWritePct.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>Key Cache Write 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(.)='KeyCacheWritePct']$</Value>

    <WarningThreshold>90</WarningThreshold>

    <ErrorThreshold>80</ErrorThreshold>

    <NumSamples>3</NumSamples>

  </Configuration>

</UnitMonitor>