The percent of MySQL Server slow queries compared to all queries. Slow queries are queries run on MySQL Databases that take longer than the global variable ‘long_query_time’. This monitor takes the number of slow queries from the global status variable ‘Slow_queries’ and divides by the number of queries from the global status variable ‘Queries’. For MySQL 5.0 and 5.1 the number of queries is the sum of the following global status variables: ‘Questions’, ‘Com_ping’ , ‘Com_statistics’, ‘Com_stmt_prepare’, ‘Com_stmt_close’, and ‘Com_stmt_reset’. 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 queries that are greater than the global variable ‘long_query_time’ is high.
To address high percent of slow queries, slow queries can be determined by turning on slow query logging in the configuration file. This can be done by adding the following line along with a destination log file path under the instance group options: “log-slow-queries= file path to log”. Alternatively, the server can be started with the following option: “--log-slow-queries=file path to log”.
Default Configuration
Parameter | Default Value |
Warning Threshold | Greater than 15 |
Error Threshold | Greater than 25 |
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 |
<UnitMonitor ID="Microsoft.MySQLServer.ManagedServer.Unix.SlowQueryPct.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.SlowQueryPct.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>Slow Query 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(.)='SlowQueryPct']$</Value>
<WarningThreshold>15</WarningThreshold>
<ErrorThreshold>25</ErrorThreshold>
<NumSamples>3</NumSamples>
</Configuration>
</UnitMonitor>