The percent of full table scans made in the MySQL Server. Full table scan are when the server sequentially checks each row of the table for the validity of a condition. Full table scans can occur frequently on tables with a low number of rows. 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 full table scans is high. Full table scans can occur on tables with fewer than 10 rows or a short row length. Full table scans can also occur when comparing indexed columns with constant values.
To address high percent of table scans try the following:
Optimize the MySQL query that are running full table scans
Use MySQL command “ANALYZE TABLE tbl_name” to update key distributions
Use MySQL option in command “FORCE INDEX” to manually opt out of the MySQL optimizer for the given index
Change the variable ‘max_seeks_for_key’ to a value that reflects needed lookups to retrieve data.
Default Configuration
Parameter | Default Value |
Warning Threshold | Greater than 80 |
Error Threshold | Greater than 95 |
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.RowOperationsPerformanceRollup | ||
| Category | PerformanceHealth | ||
| Enabled | True | ||
| Alert Generate | True | ||
| Alert Severity | MatchMonitorHealth | ||
| Alert Priority | Normal | ||
| Alert Auto Resolve | True | ||
| Monitor Type | Microsoft.Oss.WSManEnumeration.GreaterThanThreshold.ThreeState.MonitorType | ||
| Remotable | True | ||
| Accessibility | Public | ||
| Alert Message |
| ||
| RunAs | Default |
Home
ENU <UnitMonitor ID="Microsoft.MySQLServer.ManagedServer.Unix.FullTableScanPct.Monitor" Accessibility="Public" Target="Microsoft.MySQLServer.ManagedServer.Unix" TypeID="Oss!Microsoft.Oss.WSManEnumeration.GreaterThanThreshold.ThreeState.MonitorType" Enabled="onStandardMonitoring" ParentMonitorID="Microsoft.MySQLServer.RowOperationsPerformanceRollup">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.MySQLServer.ManagedServer.Unix.FullTableScanPct.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="UnderThreshold"/>
<OperationalState HealthState="Warning" MonitorTypeStateID="StatusWarning" ID="OverWarningThreshold"/>
<OperationalState HealthState="Error" MonitorTypeStateID="StatusError" ID="OverErrorThreshold"/>
</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>Full Table Scan 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(.)='FullTableScanPct']$</Value>
<WarningThreshold>80</WarningThreshold>
<ErrorThreshold>95</ErrorThreshold>
<NumSamples>3</NumSamples>
</Configuration>
</UnitMonitor>