Skip to content

MySQL

MySQL Logo

Visualize MySQL performance metrics in real-time during load tests. OctoPerf lets you visualize and analyze advanced metrics for your MySQL databases:

  • Capture and analyze MySQL metrics and statistics,
  • Correlate MySQL metrics with your other systems,
  • Create effective alerts on database throughput, latency, or errors.

MySQL performance issues can be seen on the client side or from resource constraints on the server itself, making it tedious to find the root cause of the issue. OctoPerf collects 30+ key monitoring metrics from your MySQL database.

The MySQL monitoring module ships with pre-selected counters and thresholds.

Supported versions

MySQL database servers version 5.5 and above are supported.

Configuration

MySQL Configuration Wizard

The MySQL monitor can be configured in two differents ways. The simple mode suits best for most configurations. The advanced mode is great for specifying advanced connection settings.

The database name is required by no specific database is monitored. This monitor executes SQL queries to get system and status variables. OctoPerf requires an account with authorization:

  • to connect to the database,
  • and to read and collect global status data and variables.

Simple Configuration

  • Hostname: hostname or IP of the MySQL database,
  • Port: database connection port, 3306 by default,
  • Database: database to connect to.

In simple configuration, the JDBC connection url is generated automatically.

Advanced Configuration

  • URL: JDBC connection url.

In advanced configuration, a specific JDBC connection URL with extra connection settings to the JDBC driver, such as timeout, cluster handling can be configured.

Credentials

MySQL username and password are required in both simple and advanced configuration.

Troubleshooting

** The server time zone value 'CEST' is unrecognized or represents more than one time zone.**

This error message indicates that the server timezone is different than expected. To fix it, switch to advanced configuration and at the end of the URL add:

  • &serverTimezone=UTC

In some rare situations, other options might be required:

  • &useUnicode=true
  • &useJDBCCompliantTimezoneShift=true
  • &useLegacyDatetimeCode=false
  • &serverTimezone=UTC

Monitored Counters

The MySQL monitoring module collects the following metrics:

  • Connections:

    • Threads Connected: The number of currently open connections equivalent to number of threads,
    • Threads Cached: The number of threads in the thread cache,
    • Threads Running: The number of threads that are not sleeping,
    • Connections: The number of connection attempts (successful or not) to the MySQL server,
    • Aborted clients: The number of connections that were aborted because the client died without closing the connection properly,
    • Aborted connects: The number of failed attempts to connect to the MySQL server,
    • Max Used: The maximum number of connections that have been in use simultaneously since the server started,
    • Outstanding connection requests: The number of outstanding connection requests MySQL can have. This comes into play when the main MySQL thread gets very many connection requests in a very short time. It then takes some time (although very little) for the main thread to check the connection and start a new thread. Its value indicates how many requests can be stacked during this short time before MySQL momentarily stops answering new requests. You need to increase this only if you expect a large number of connections in a short period of time,
    • Max Allowed: The maximum permitted number of simultaneous client connections. By default, this is 151. Increasing this value increases the number of file descriptors that mysqld requires. If the required number of descriptors are not available, the server reduces the value of max_connections,
    • Max per User: The maximum number of simultaneous connections permitted to any given MySQL user account. A value of 0 (the default) means 'no limit',
    • Max Cached Threads: How many threads the server should cache for reuse. When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections,
    • Connection Errors Internal: The number of connections refused due to internal errors in the server, such as failure to start a new thread or an out-of-memory condition,
    • Connection Errors Max: The number of connections refused because the server max_connections limit was reached.

  • Input / Output:

    • KB/sec Received: The number of Kbytes received from all clients per second,
    • KB/sec Sent: The number of Kbytes sent to all clients per second,
    • Open Files: The number of files that are open. This count includes regular files opened by the server. It does not include other types of files such as sockets or pipes. Also, the count does not include files that storage engines open using their own internal functions rather than asking the server level to do so,

  • Keys:

    • Unflushed Key Blocks: The number of key blocks in the MyISAM key cache that have changed but have not yet been flushed to disk,
    • Unused Key Blocks: The number of unused blocks in the MyISAM key cache. You can use this value to determine how much of the key cache is in use,
    • Used Key Blocks: The number of used blocks in the MyISAM key cache. This value is a high-water mark that indicates the maximum number of blocks that have ever been in use at one time,
    • Key Read Requests: The number of requests to read a key block from the MyISAM key cache,
    • Key Reads: The number of physical reads of a key block from disk into the MyISAM key cache. If Key_reads is large, then your key_buffer_size value is probably too small,
    • Key Read Cache Misses: The cache miss percent rate is calculated as Key_reads / Key_read_requests,
    • Key Write Requests: The number of requests to write a key block to the MyISAM key cache,
    • Key Writes: The number of physical writes of a key block from the MyISAM key cache to disk,

  • Memory:

    • Binlog Cache Size: The size of the cache to hold changes to the binary log during a transaction,
    • Bulk Insert Buffer Size: MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB,
    • Join Buffer Size: The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible,
    • Key Buffer Size: Key buffer size in bytes,
    • % Key Buffer Size: The percentage of the key buffer in use is determined using key_buffer_size in conjunction with the Key_blocks_unused status variable and the buffer block size, which is available from the key_cache_block_size system variable,
    • Key Cache Block Size: The size in bytes of blocks in the key cache,
    • Max Binlog Size: If a write to the binary log causes the current log file size to exceed the value of this variable, the server rotates the binary logs (closes the current file and opens the next one). The minimum value is 4096 bytes. The maximum and default value is 1GB,
    • Preload Buffer Size: The size of the buffer that is allocated when preloading indexes,
    • Query Alloc Block Size: The allocation size of memory blocks that are allocated for objects created during statement parsing and execution. If you have problems with memory fragmentation, it might help to increase this parameter,
    • Read Buffer Size: Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072. The value of this variable should be a multiple of 4KB. If it is set to a value that is not a multiple of 4KB, its value will be rounded down to the nearest multiple of 4KB,
    • Read Range Buffer Size: This variable is used for reads from MyISAM tables, and, for any storage engine, for Multi-Range Read optimization,
    • Transaction Alloc Block Size: The amount in bytes by which to increase a per-transaction memory pool which needs memory,
    • Transaction PreAlloc Size: There is a per-transaction memory pool from which various transaction-related allocations take memory. The initial size of the pool in bytes is transaction_prealloc_size. For every allocation that cannot be satisfied from the pool because it has insufficient memory available, the pool is increased by transaction_alloc_block_size bytes. When the transaction ends, the pool is truncated to transaction_prealloc_size bytes,

  • Other:

    • Questions: Questions is incremented for all statements sent by client applications per second,
    • Slow Query count: The number of queries that have taken more than long_query_time seconds,
    • Long Query Time: If a query takes longer than this many seconds, the server increments the Slow_queries status variable,
    • Slow Launch Threads: The number of threads that have taken more than slow_launch_time seconds to create,
    • Slow Launch Time: If creating a thread takes longer than this many seconds, the server increments the Slow_launch_threads status variable,
    • Sort Merge Passes: The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable,
    • Sort Buffer Size: Each session that must perform a sort allocates a buffer of this size. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimization. At minimum the sort_buffer_size value must be large enough to accommodate fifteen tuples in the sort buffer,
    • Version: MySQL server version.

  • Query Cache:

    • Query Cache Free Blocks: The number of free memory blocks in the query cache,
    • Query Cache Free Memory: The amount of free memory for the query cache,
    • Query Cache Hits: The number of query cache hits,
    • Query Cache Inserts: The number of queries added to the query cache,
    • Query Low Memory Prunes: The number of queries that were deleted from the query cache because of low memory per second,
    • Query Cache Miss: The number of noncached queries (not cacheable, or not cached due to the query_cache_type setting),
    • Query Cache Size: The number of queries registered in the query cache,
    • Query Cache Total Blocks: The total number of blocks in the query cache,

  • Reads:

    • First Read: The number of times the first entry in an index was read. If this value is high, it suggests that the server is doing a lot of full index scans; for example, SELECT col1 FROM foo, assuming that col1 is indexed,
    • Key Read: The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries,
    • Last Key Read: The number of requests to read the last key in an index. With ORDER BY, the server will issue a first-key request followed by several next-key requests, whereas with ORDER BY DESC, the server will issue a last-key request followed by several previous-key requests,
    • Next Read: The number of requests to read the next row in key order. This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan,
    • Previous Read: The number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY ... DESC,
    • Position Read: The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly,
    • Data Read: The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have,

  • Selects:

    • Select Full Join: The number of joins that perform table scans because they do not use indexes. If this value is not 0, you should carefully check the indexes of your tables,
    • Select Full Range Join: The number of joins that used a range search on a reference table,
    • Select Range: The number of joins that used ranges on the first table. This is normally not a critical issue even if the value is quite large,
    • Select Range Check: The number of joins without keys that check for key usage after each row. If this is not 0, you should carefully check the indexes of your tables,
    • Select Scan: The number of joins that did a full scan of the first table,

  • Tables:

    • Open Tables: The number of tables that are open,
    • Opened Tables: The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small,
    • Immediate Lock Grants: The number of times that a request for a table lock could be granted immediately,
    • Waited Lock Grants: The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication,
    • Open Cache Hits: The number of hits for open tables cache lookups,
    • Open Cache Misses: The number of misses for open tables cache lookups,
    • Open Cache Instances: The number of open tables cache instances,
    • Max Heap Table Size: This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value,

  • Writes:

    • Flushes: The number of times the server flushes tables, whether because a user executed a FLUSH TABLES statement or due to internal server operation,
    • Commits: The number of internal COMMIT statements,
    • Deletes: The number of times that rows have been deleted from tables,
    • Rollbacks: The number of requests for a storage engine to perform a rollback operation,
    • Savepoints: The number of requests for a storage engine to place a savepoint,
    • Savepoint Rollbacks: The number of requests for a storage engine to roll back to a savepoint,
    • Updates: The number of requests to update a row in a table,
    • Inserts: The number of requests to insert a row in a table.

Going Further

Threads

MySQL connection manager creates one Thread for every client. Once a connection is closed, its thread is returned to the cache unless of course the cache is full. It is important to monitor how the number of threads changes during a load test. If more and more threads are created it can mean that the client connections are taking longer.

Aborted Clients / Connects

Unsuccessful connection attempts can occur for the following reasons:

  • A client attempts to access a database but has no privileges for it,
  • A client uses an incorrect password,
  • A connection packet does not contain the right information,
  • It takes more than connect_timeout seconds to obtain a connect packet.