The percent of hits MySQL is able to satisfy from the buffer pool versus disk. The innodb buffer pool contains least recently used index blocks. The buffer pool size is controlled using the global variable ‘innodb_buffer_pool_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.
An unhealthy state indicates the percentage of reads from the buffer pool versus total reads is low. The buffer pool might not have enough space to hold all inserted blocks.
To address low buffer pool hit percent try the following options:
Increase the size of the InnoDB buffer pool by modifying the variable ‘innodb_buffer_pool_size’ in the configuration file.
Divide the buffer pool into separate instances for concurrent memory read and write operations by modifying the variable ‘innodb_buffer_pool_instances’ in the configuration file.
Default Configuration
Parameter | Default Value |
Warning Threshold | Less than 95 |
Error Threshold | Less than 90 |
Number of Samples | 3 |
Overrides can be used to change the parameter values defined above for all instances or for specific instances or groups.
Target | Microsoft.MySQLServer.ManagedServer.Unix | ||
Parent Monitor | Microsoft.MySQLServer.InnoDBPerformanceRollup | ||
Category | PerformanceHealth | ||
Enabled | True | ||
Alert Generate | True | ||
Alert Severity | MatchMonitorHealth | ||
Alert Priority | Normal | ||
Alert Auto Resolve | True | ||
Monitor Type | Microsoft.Oss.WSManEnumeration.LessThanThreshold.ThreeState.MonitorType | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Default |
<UnitMonitor ID="Microsoft.MySQLServer.ManagedServer.Unix.InnoDB_BufferPool_HitPct.Monitor" Accessibility="Public" Target="Microsoft.MySQLServer.ManagedServer.Unix" TypeID="Oss!Microsoft.Oss.WSManEnumeration.LessThanThreshold.ThreeState.MonitorType" Enabled="onStandardMonitoring" ParentMonitorID="Microsoft.MySQLServer.InnoDBPerformanceRollup">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.MySQLServer.ManagedServer.Unix.InnoDB_BufferPool_HitPct.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>InnoDB Buffer Pool 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(.)='IDB_BP_HitPct']$</Value>
<WarningThreshold>95</WarningThreshold>
<ErrorThreshold>90</ErrorThreshold>
<NumSamples>3</NumSamples>
</Configuration>
</UnitMonitor>