MySQL (pre 5.7.9) for Linux/Unix

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

This template supports the SolarWinds Platform Agent for Linux.

Prerequisites

Install MySQL ODBC on the SolarWinds Platform server

Enable Remote Access to the MySQL Database Server.

Credentials

Database user name and password.

Configuration for SolarWinds Platform agent for Linux monitoring

The following instructions assume that MySQL is installed on a Linux-based computer.

Depending on your Linux distribution, verify the example commands used. These examples use CentOS commands.

1. Log in to the Linux-based computer with administrator privileges.

   You will need to create a user account on the server and in the MySQL database.

2. Install the MySQL ODBC driver (© 2021, Oracle Corp., available at dev.mysql.com).

3. (Required) Grant the following access for local access to monitor through SAM and the SolarWinds Platform.

   Enter the IP address of this MySQL Linux-based computer, replacing the example value of 10.100.100.100.

   mysql -uroot -p
   CREATE USER 'dbuser'@'%' IDENTIFIED BY 'Password';
   GRANT ALL ON testdb.* TO dbuser@'%' WITH GRANT OPTION
   GRANT SELECT ON performance_schema.* TO dbuser@ WITH GRANT OPTION

For 'dbuser@'%', the % character grants access from the specified external IP addresses, and also from other systems on the local subnet for dbuser. If you decide to remove that the % wildcard, make sure dbuser can access the database from the specific IP address beforehand. For reference, see GRANT OPTION (© 2021, Oracle Corp., available at dev.mysql.com, obtained on December 15, 2021).

4. Test access to the database using the newly created account credentials.

   Command: mysql -u dbuser -p

Configure and assign a SAM template with the credentials.

1. Click Settings > All Settings > SAM Settings > Manage Templates.
2. Search for and select a MySQL template.
   (Recommended: Create a copy of the template to work with.)
3. Modify template settings and component monitors, depending on metrics you want to monitor.
4. To enter credential:
   1. Select a component monitor and click Assign Credentials.
   2. Enter the credentials you created for the database and click OK.
5. Test the access for the template by assigning it to a node. Click Test Node to assign and test the access. If you encounter issues, verify the credentials and privileges for the account.

Troubleshooting

Error: Data source name not found and no default driver specified. This error occurs on a CentOS system.

Solution: Verify the CentOS system has the mysql-connector-odbc installed. Use the following command to receive a package and version name on the target Linux-based computer: rpm -q mysql-connector-odbc

To verify you have the appropriate credentials assigned for each component monitor.

1. Click Settings > All Settings > SAM Settings > Manage Templates.
2. Search for the SAM template. Select it and click Edit.
3. Select the component monitor to check assigned credentials.
4. To modify, click Assign Credentials, enter credentials, and click OK.
5. Test the template.

If issues continue, verify the appropriate driver is defined on the CentOS system to match the connection string, as shown here.

cat /etc/odbcinst.ini
[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
UsageCount=1
[MySQL ODBC 5.3 Unicode Driver]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
UsageCount=1

Portions of this topic are based on Server System Variables (© 2021 Oracle Corp., available at dev.mysql.com/doc/, obtained on July 26, 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.

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.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

Kilobytes Sent

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

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

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.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

Created Temporary Files

This counter the number of temporary files MySQL has created.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

Opened Table Definitions

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

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

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.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

Opened Files

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

If the my_open() function is not used, the count will not be incremented.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

Statements Executed

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

This includes only statements sent to the server by clients and no longer includes statements executed within stored programs, unlike the Queries variable. This variable does not count the following commands: COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

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.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

Key Writes

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

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

Table Locks Immediate

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

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

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.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

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.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

Transactions that use cache

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

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

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.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

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.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

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.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

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.

To enable the slow query log, start mysqld with the --log-slow-queries[=file_name] option. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

Max Used Connections

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

Free Memory in Query Cache (MB)

This counter returns the amount of free memory in MB for the query cache.

Queries Registered in Query Cache

This counter returns the number of queries registered in the query cache.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

Deleted Queries from Cache

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

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

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.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

Aborted Clients

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

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

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.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

Slow Launch Threads

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

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

Sort Scan

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

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

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.

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

Queries

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

This component has the Count statistic as difference option enabled. It will return the difference between two polling intervals.

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.

MySQL Process

This monitor checks whether MySQL is up and running.