MySQL 8.0 Metrics for Windows

This template assesses the performance of a MySQL Server database by retrieving performance data from the built-in performance_schema.GLOBAL_STATUS, performance_schema.SESSION_VARIABLES and other pseudo-tables.

Prerequisites

MySQL server 8.0.13.

MySQL ODBC 32-bit driver must be installed on SAM server.

CREATE USER USERNAME@ADDRESS IDENTIFIED BY “PASSWORD”;

where:

USERNAME is a user with administrative privileges;

ADDRESS is the IP address of your SAM server;

PASSWORD is the user password.

Credentials

Database user name and password.

Components without predetermined threshold values provide guidance such as "Use the lowest threshold possible" or "Use the highest threshold possible" to help you find a threshold appropriate for your application. For details, see Manage thresholds in SAM.

Portions of this document are based on the following:

What's the formula for calculating Key Efficiency, Key Buffer Used, and Query Cache Hitrate in MySql? (© 2019 Stack Exchange Inc., available at https://dba.stackexchange.com, acquired on May 9, 2019)
MySQL 8.0 Reference Manual (© 2019 Oracle Corp., available at https://dev.mysql.com, acquired on May 9, 2019)
- Server System Variables
- Server Status Variables
- Performance Schema Status Variable Tables
- Types of Error Log Filtering

Component monitors

Click here for an overview about SAM application monitor templates and component monitors. SAM API Poller templates are also available.

Total Memory Used (MB)

This component monitor returns the possible total memory usage of MySQL, in MB, using the following formula: read_buffer_size+sort_buffer_size)*max_connections+key_buffer_size.

Kilobytes Received

This counter returns the number of kilobytes received from all clients.

Kilobytes Sent

This counter returns the number of kilobytes sent to all clients.

Created Temporary Disk Tables

This counter returns the number of internal, on-disk, temporary tables created by the server while executing statements. This value should be as low as possible.

If an internal temporary table is initially created as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size or max_heap_table_size values, whichever is less.

If the value returned from this counter is large, you may want to increase the value of tmp_table_size or max_heap_table_size to lessen the likelihood that internal temporary tables in memory will be converted to on-disk tables.

Created Temporary Files

This counter the number of temporary files MySQL has created.

Opened Table Definitions

This counter returns the number of .frm files that were cached.

Opened Tables

This counter returns the number of tables that were opened. This should be as low as possible. If the value returned is large, you may want to increase the value of table_open_cache.

Opened Files

This counter returns the number of files that were opened with the my_open()function.

Statements Executed

This counter returns the number of statements executed by the server.

Key Reads

This counter returns the number of physical reads of a key block from disk. This value should be as low as possible. If the value returned from this counter is large, you may want to increase the value of key_buffer_size.

Key Writes

This counter returns the number of physical writes of a key block to disk.

Table Locks Immediate

This counter returns the number of times that a request for a table lock could be granted immediately.

Table Locks Waited

This counter returns the number of times that a request for a table lock could not be granted immediately and a wait was needed. This value should be as low as possible. If the counter returns a value that is high and you have performance problems, you may want to optimize your queries. You may also consider either splitting your tables or using replication.

Threads Cached

This counter returns the number of threads in the thread cache.

Threads Connected

This counter returns the number of currently open connections.

Threads Created

This counter returns the number of threads created to handle connections. This value should be as low as possible. If the counter returns a value that is high, you may want to increase the value of thread_cache_size.

Threads Running

This counter returns the number of threads that are running.

Up Time

This counter returns the number of seconds that the server has been up.

Transactions that use disk

This counter returns the number of transactions that used the temporary binary log cache but exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction. This value should be as low as possible.

Transactions that use cache

This counter returns the number of transactions that used the temporary binary log cache.

Joins that perform table scans

This counter returns the number of joins that perform table scans because they do not use indexes. If the returned value is not zero, you should carefully check the indexes of your tables.

Select_scan refers to a table that is completely read in sequence from the hard drive. For such tables Explain lists "All" in the "Type" column. Table scans are not desirable because they are slow (meaning they are limited by the speed of the hard drive). However, table scans are prevalent. It is not uncommon to see a server where 50% of all Select queries are Select_scan. The fundamental reason why a Select results in a table scan is because no index on the table can satisfy the conditions of the query (i.e., everything after Where), or there are no indexes, so all queries will result in a table scan. From a performance perspective it is safe to say you always want to decrease this value. However, in some cases this value may be increased after optimization because the server is then able to do more. Ultimately, it will have to decrease again when the QPS (queries per second) increases.

Joins that check for key usage

This counter returns the number of joins without keys that check for key usage after each row. If this is not zero, you should carefully check the indexes of your tables.

Select_range_check is a little better than Select_full_join and uses the same range principles as Select_range. The difference is Select_range_check is not sure whether it can use a range to join the table so it keeps checking in case it finds that it can. This "uncertainty" is an effect of the join. With Select_range there is only one table, therefore, MySQL can be certain ahead of time. With multiple tables, the preceding tables may alter the range conditions and MySQL cannot be certain ahead of time. For such tables, Explain still lists type All because a type range is not certain. For such tables, MySQL also lists "Range checked for each record (index map: #)" in the "Extra" column. Like Select_range at least one of the tables require an index for this optimization to be possible, otherwise the table will probably cause a Select_full_join. If MySQL does use a range to join the table it will not increment Select_range, it still only increments Select_range_check.

Joins that perform full scan

This counter returns the number of joins that did a full scan of the first table. This value should be as low as possible.

Select_full_join is the same as Select_scan with the difference being that Select_full_join applies to the second and subsequent tables in the join's plan for a multiple table query. For such tables, Explain lists type: All. Select_full_join results if there are no indexes on the table, or if no indexes can be used to join the table.

A query similar to: SELECT * FROM tbl1, tbl2 WHERE tbl1.col1 = tbl2.col1; without any indexes results in a Select_scan and a Select_full_join; Select_scan for the first table, and Select_full_join for the second. Select_full_join is no more desirable than Select_scan. Together, the two are even worse. When Explain lists type: All for each table in a join, "this output indicates that MySQL is generating a Cartesian product of all the tables; that is, every combination of rows," (MySQL manual). In simpler terms, two tables of 10 rows each joined together does not result in 20 rows, it results in 100 rows (10 multiplied by 10). In real-world applications, tables usually have at least a few thousand rows, so the cross product (a.k.a. Cartesian product) of even two small tables can quickly become enormous.

Slow Queries

This counter returns the number of queries that have taken more than long_query_time seconds. The returned value should be less than 10.

Max Used Connections

This counter returns the maximum number of connections that have simultaneously been in use since the server started.

Opened Connections

This counter returns the number of active connections.

Aborted Connections

This counter returns the number of failed attempts to connect to the MySQL server.

Aborted Clients

This counter returns the number of connections that were aborted because the client died without closing the connection properly.

Thread Cache Size

This counter returns the number of threads the server should cache for reuse.

When a client disconnects, the client's threads are put in the cache. If there are fewer threads than the thread cache size, the threads remain in the cache. Requests for threads are satisfied by reusing threads taken from the cache, if possible. When the cache is empty, a new thread created.

This variable can be increased to improve performance if you have a lot of new connections. Normally, this does not provide noticeable performance improvement if you have good thread implementation. However, if your server sees hundreds of connections per second, you should set the thread cache size high enough so that most new connections use cached threads.

Slow Launch Threads

This counter returns the number of threads that have taken more than slow_launch_time seconds to create.

Sort Scan

This counter returns the number of sorts that were done by scanning the table by using Order By or Group By commands.

Sort Rows

This counter returns the number of sorted rows.

Sort_rows is a total count of the number of rows sorted in step two. Since step two can be bypassed in some cases, Sort_rows is not entirely inclusive. Also, since Sort_scan and Sort_range in step two are essentially the same, the Sort_rows value is not very indicative of anything. Suffice to say, most servers sort hundreds of millions of rows.

Queries

This counter returns the number of statements executed by the server.

Key Read Efficiency

This counter returns the ratio of the number of physical reads of a key block from the cache to the number of requests to read a key block from the cache in percentage. The MySQL performance is good if the value of Key Read Efficiency is 90 percent and above. Increasing the size of the cache improves the value of Key Read Efficiency and hence an improved the performance.

Key Write Efficiency

This counter returns the ratio of the number of physical writes of a key block to the cache to the number of requests to write a key block to the cache in percentage. For a good performance of the MySQL server, the value of Key Write Efficiency must be 90 percent and above. If it is found less, then you can increase the size of the cache to improve the performance.

Key Buffer Size

This counter returns the size of the buffer used for index blocks. Also known as the key cache.

CTE Max Recursion Depth

This monitor provides the common table expression (CTE) maximum recursion depth. The server terminates execution of any CTE that recurses more levels than the value of this variable.

Unit: Count

Histogram Generation Max Memory Size

This monitor provides the maximum amount of memory available for generating histogram statistics.

Unit: MB

Information Schema Stats Expiry

This monitor defines the period of time before cached statistics expire. The default is 86400 seconds (24 hours), but the time period can be extended to as much as one year.

To always retrieve the latest statistics directly from the storage engine and bypass cached values, set information_schema_stats_expiry to 0.

Unit: Seconds

Password History

This monitor defines the global policy for controlling reuse of previous passwords based on required minimum number of password changes. For an account password used previously, this variable indicates the number of subsequent account password changes that must occur before the password can be reused. If the value is 0 (the default), there is no reuse restriction based on number of password changes.

Unit: Count

Password Reuse Interval

This monitor defines the global policy for controlling reuse of previous passwords based on time elapsed. For an account password used previously, this variable indicates the number of days that must pass before the password can be reused. If the value is 0 (the default), there is no reuse restriction based on time elapsed.

Unit: Days

Regexp Stack Limit

This monitor provides the maximum available memory in bytes for the internal stack used for regular expression matching operations performed by REGEXP_LIKE() and similar functions.

Unit: MB

Regexp Time Limit

This monitor provides the time limit for regular expression matching operations performed by REGEXP_LIKE() and similar functions.

This limit is expressed as the maximum permitted number of steps performed by the match engine, and thus affects execution time only indirectly. Typically, it is on the order of milliseconds.

Unit: Milliseconds

Secondary Engine Execution Count

This monitor provides the Secondary engine execution count.

Unit: Count

Temptable Max Ram

This monitor provides the maximum amount of memory that can be occupied by the TempTable storage engine before it starts storing data on disk. The default value is 1073741824 bytes (1GiB).

Unit: GB

Activate All Roles On Login

This monitor gives the information whether session variable activate_all_roles_on_login is enabled or not.

If activate_all_roles_on_login is enabled (ON = 1), the server activates all roles granted to each account at login time. This takes precedence over default roles specified with SET DEFAULT ROLE.

If activate_all_roles_on_login is disabled (OFF = 0), the server activates the default roles specified with SET DEFAULT ROLE, if any, at login time.

Unit: Number

Show Create Table Verbosity

This monitor gives the information whether session variable show_create_table_verbosity is enabled or not.

Enable(ON) = 1

Disable(OFF) = 0

Enabling this variable causes SHOW CREATE TABLE to display ROW_FORMAT regardless of whether it is the default format.

Unit: Number

Sql Require Primary Key

This monitor gives the information whether session variable sql_require_primary_key is enabled or not.

Enable(ON) = 1

Disable(OFF) = 0

Whether statements that create new tables or alter the structure of existing tables enforce the requirement that tables have a primary key.

Enabling this variable helps avoid performance problems in row-based replication that can occur when tables have no primary key.

Unit: Number

SSL Fips Mode

This monitor gives the information about the status of session variable ssl_fips_mode. This variable controls whether to enable FIPS mode on the server side.

OFF (or 0): Disable FIPS mode.

ON (or 1): Enable FIPS mode.

STRICT (or 2): Enable “strict” FIPS mode.

Unit: Number

Use Secondary Engine

This monitor gives the information about the status of session variable use_secondary_engine.

Valid Values:

OFF = 0

ON = 1

FORCE = 2

Unit: Number

Windowing Use High Precision

This monitor gives the information whether session variable windowing_use_high_precision is enabled or not. This variable determines whether to compute window operations without loss of precision.

Valid Values:

Enabled(ON) = 1

Disabled(OFF) = 0

Unit: Number

MySQL Process

This monitor checks whether MySQL is up and running.

Search SolarWinds Support

MySQL 8.0 Metrics for Windows

Component monitors