References > Template Reference > MySQL > MySQL (5.7.9 or later) for Linux/Unix

MySQL (5.7.9 or later) for Linux/Unix

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

Works with the Orion Agent for Linux.

Prerequisites

MySQL 5.7.9 or later. MySQL ODBC must be installed on SAM server. Remote Access to MySQL Database Server must be enabled. Log into the MySQL database in order to monitor and execute the following query:

;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.

Configuration for Orion agent for Linux monitoring

For the following instructions, you should have MySQL installed on a Linux-based computer.

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

  1. Login 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. (Required) To begin, you should have unixODBC installed to support Microsoft Windows ODBC. If not, download and install the unixODBC driver to the Linux-based computer.
  3. Install the MySQL ODBC driver.

    Command: yum install mysql-connector-odbc

  4. Create a user account for the database.

    The following commands create a MySQL account of dbuser with the password Password1 for the database dbtest. The commands also grant all access to the user account.

    create database testdb;
    create user 'dbuser'@'localhost' identified by 'Password1';
    grant all on dbtest.* to 'dbuser' identified by 'Password1';
  5. (Required) Grant the following access for local access to monitor through SAM and the Orion 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 'Password1';
    GRANT ALL ON testdb.* TO dbuser@'%' IDENTIFIED BY 'Password1';
    GRANT SELECT ON performance_schema.* TO dbuser@'10.100.100.100' IDENTIFIED BY 'Password1';
  6. Test access to the database using the newly created account credentials.

    Command: mysql -u testuser -p

Configure and assign a SAM template with the credentials.

  1. On the Web Console, click Settings > All Settings > SAM Settings > Manage Templates.
  2. Search for the MySQL templates. SolarWinds recommends creating a copy of the template. Select a template based on version and click Copy.
  3. Modify the settings of the template and component monitors based on the metrics you want to monitor.
  4. To enter credentials, select the component monitor checkboxes and click Assign Credentials.
  5. Enter the credentials you created for the database and click OK.
  6. 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

Verify you have the appropriate credentials assigned for each component monitor.

  1. On the Web Console, click Settings > All Settings > SAM Settings > Manage Templates.
  2. Search for the SAM template. Select 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 you continue to encounter issues, verify the appropriate driver is defined on the CentOS system to match the connection string.

For example:

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 document are based on the following article: http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_thread_cache_size http://dba.stackexchange.com/questions/20083/whats-the-formula-for-calculating-key-efficiency-key-buffer-used-and-query-ca

Monitored Components

For details on monitors, see SAM Component Monitor Types.

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 have been 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 have been 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 have been 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.