MySQL Server

Version 9

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

     

    Prerequisites: MySQL 5.1.12 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.


    Changing the default port:

    To change the default port from 3306, you will need to change the connection string. For example, to change the port to 3310, the connection string would be as follows:

    Server={MySQL ODBC 5.1 Driver};Port=3310;Server=${IP};Database={INFORMATION_SCHEMA};Uid=${USER};Pwd=${PASSWORD};


    Note: You may need to expand the Advanced section to indicate whether you are running in 32-bit or 64-bit mode. Mismatched versions will cause an error.


    Monitored Components

    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 more information, seehttp://knowledgebase.solarwinds.com/kb/questions/2415.

    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 component monitor 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 thetmp_table_size ormax_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_sizeto 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.

    Created Temporary Tables

    This counter returns the number of internal temporary tables created by the server while executing statements.

    Opened Table Definitions

    This counter returns the number of .frm files that have been cached.

    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.

    Opened Files

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

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

    Statements Executed

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

    Note: 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.

    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 ofthread_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 ofbinlog_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.

    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.

    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.

    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.

    Note: 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.

    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.

    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.

    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.

    Select Scan

    This counter returns the number of joins that did a full scan of the first table.

    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.

    Select Full Join

    This counter returns the number of joins that perform table scans because they are not using indexes.

    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, Explainlists 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 aSelect_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.

    If this counter is not 0, you should carefully check the indexes of your tables.

    Select Range Check

    This counter returns the number of joins without keys that check for key usage after each row.

    Select_range_check is a little better than Select_full_join and uses the same range principles as Select_range. The difference isSelect_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, Explainstill 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.

    If this counter is not 0, you should carefully check the indexes of your tables.

     

    Copyright 2014. Portions of this document are based on the following::
    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

    Last updated: 03/11/2015