MySQL (5.7.9 or later) for Windows

This SAM application monitor template assesses the performance of a MySQL Server database by retrieving performance data from the built-in performance_schema.global_status pseudo-table.

Prerequisites

Install MySQL ODBC on the SolarWinds Platform server.

Enable remote access on the MySQL Database Server.

where:

USERNAME is a user with administrative privileges;
ADDRESS is the IP address of your SolarWinds Platform server;
PASSWORD is the user password.

Credentials

Database user name and password

For reference, see:

Server System Variables (© 2021 Oracle Corp., available at dev.mysql.com/doc/, obtained on September 28, 2021)
What's the formula to calculate Key Efficiency, Key Buffer Used, and Query Cache Hitrate in MySql?(© 2021 Stack Exchange, available at dba.stackexchange.com, obtained on September 28, 2021)

Component monitors

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. See also Manage application monitor thresholds in SAM.

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

The number of kilobytes received from all clients.

Kilobytes Sent

The number of kilobytes sent to all clients.

Created Temporary Disk Tables

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

The number of .frm files that were cached.

Opened Tables

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

The number of files that were opened with the my_open()function.

Statements Executed

The number of statements executed by the server.

Key Reads

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

The number of physical writes of a key block to disk.

Table Locks Immediate

The number of times that a request for a table lock could be granted immediately.

Table Locks Waited

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

The number of threads in the thread cache.

Threads Connected

The number of currently open connections.

Threads Created

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

The number of threads that are running.

Up Time

The number of seconds that the server has been up.

Transactions that use disk

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

The number of transactions that used the temporary binary log cache.

Joins that perform table scans

The number of joins that perform table scans because they do not use indexes. If the returned value is not zero, 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 (that is, everything after Where), or there are no indexes, so all queries result in a table scan. From a performance perspective, you always want to decrease this value. However, in some cases this value may increase after optimization because the server can do more. Ultimately, it will have to decrease again when the Queries per Second (QPS) value increases.

Joins that check for key usage

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

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

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

Max Used Connections

The maximum number of connections that have simultaneously been in use since the server started.

Free Memory in Query Cache (MB)

The amount of free memory in MB for the query cache.

Queries Registered in Query Cache

The number of queries registered in the query cache.

Deleted Queries from Cache

The number of queries that were deleted from the query cache because of low memory. The value returned should consistently be zero.

Opened Connections

The number of active connections.

Aborted Connections

The number of failed attempts to connect to the MySQL server.

Aborted Clients

The number of connections that were aborted because the client died without closing the connection properly.

Thread Cache Size

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

The number of threads that have taken more than slow_launch_time seconds to create.

Sort Scan

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

Sort Rows

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

The number of statements executed by the server.

Key Read Efficiency

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

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

The size of the buffer used for index blocks. Also known as the key cache.

MySQL Process

This monitor checks whether MySQL is up and running.

Search SolarWinds Support

MySQL (5.7.9 or later) for Windows

Component monitors