MySQL Database Performance Counters

MySQL is an open-source relational database management system (RDBMS) that is widely used in web-based applications and websites. It is based on Structured Query Language (SQL), a language used to access and manipulate data within databases. It is a popular choice of database for many web-based applications due to its affordability, reliability, and flexibility.

MySQL is a powerful database platform that provides various features, including the ability to store, retrieve and modify data, create and manage databases, and manage user access. It is designed to be easy to use and understand, allowing users to quickly create and modify databases. It also provides a wide range of functions for data manipulation and storage, including Full-Text Indexing, Triggers, Stored Procedures, and Replication.

MySQL is designed to be highly available, meaning it is always available for use. It can be used to store and manage large amounts of data and is optimized for the most commonly used queries, allowing it to quickly return results. It also supports data security and data integrity, making it an ideal choice for applications that require secure data storage.

MySQL is used in various applications, including e-commerce websites, content management systems, online forums, and social networking sites. It is also used in many large organizations, such as banks and government agencies. MySQL is a popular choice among web developers due to its flexibility, scalability, and affordability.

Key Features of MySQL Database

Open Source: MySQL is an open-source relational database management system, meaning it is free to use and distribute.
Cross-Platform Support: MySQL is available for a variety of operating systems, such as Microsoft Windows, Mac OS, and Linux.
Scalability and Flexibility: MySQL can scale up to handle extremely large databases with billions of records, and it can also be customized to meet specific needs.
Easy to Use: MySQL is simple and easy to learn, making it a popular choice among those just starting out with databases.
High Performance: MySQL is capable of handling large amounts of data quickly and efficiently.
Secure: MySQL has multiple layers of security to ensure the safety of your data.
Comprehensive Support: MySQL provides comprehensive documentation and support services to help you get the most out of your database.

Performance Counters of MySQL Database

Connections

Threads connected: The number of currently open connections.
Threads cached: The number of threads in the thread cache.
Threads running: The number of threads that are not sleeping.
Attempts: The number of connection attempts (successful or not) to the MySQL server.
Aborted clients: The number of connections aborted (client died without closing the connection properly) per second to the MySQL server.
Aborted connects: The number of failed connections per second 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. The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily stops answering new requests. It is necessary to increase this only if for a large number of connections in a short period of time.
Max allowed: The number of simultaneous client connections allowed. By default, this is 150, beginning with MySQL 5.1.15. (Previously, the default was 100.)
Max allowed per user: The maximum number of simultaneous connections allowed to any given MySQL account. A value of 0 means “no limit.”
Max threads cached: How many threads should the server cache for reuse? When a client disconnects, the client 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 there are a lot of new connections. (Normally, this does not provide a notable performance improvement if a good thread implementation is in place.)

IO Requests

Bytes received: The number of bytes received from all clients.
Bytes sent: The number of bytes sent to all clients.
Open files: The number of files that are open.

Select Rates

Select full join: The number of joins that perform table scans because they do not use indexes. If this value is not 0, it is advised to carefully check the indexes of the 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, it is advised to carefully check the indexes of the tables.
Select scan: The number of joins that did a full scan of the first table

Read Rates

Handler read first: The number of times the first entry was read from an index. 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.
Handler read key: The number of requests to read a row based on a key. If this value is high, it is a good indication that the tables are properly indexed for the queries.
Handler read next: The number of requests to read the next row in key order. This value is incremented when querying an index column with a range constraint or when performing an index scan.
Handler read previous: The number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY … DESC.
Handler read random: The number of requests to read a row based on a fixed position. This value is high when performing a lot of queries that require sorting of the result. Probably a lot of queries require MySQL to scan entire tables or there are joins that do not use keys properly.
Handler read random next: The number of requests to read the next row in the data file. This value is high when performing a lot of table scans. Generally, this suggests that the tables are not properly indexed or that the queries are not written to take advantage of the indexes available.

Key Rates

Key blocks not flushed: The number of key blocks in the key cache that have changed but have not yet been flushed to disk.
Key blocks unused: The number of unused blocks in the key cache. This value cannot be used to determine how much of the key cache is in use.
Key blocks used: The number of used blocks in the 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 (cache):. The number of requests to read a key block from the cache.
Key reads (disk): The number of physical reads of a key block from disk. If Key_reads is large, then the key_buffer_size value is probably too small.
Key write requests (cache): The number of requests to write a key block to the cache.
Key writes (disk): The number of physical writes of a key block to disk.

Commands Rates

Flush commands: The number of executed FLUSH statements.
Handler commit: The number of internal COMMIT statements.
Handler delete: The number of times that rows have been deleted from tables.
Handler rollback: The number of requests for a storage engine to perform a rollback operation.
Handler savepoint: The number of requests for a storage engine to place a savepoint.
Handler savepoint rollback: The number of requests for a storage engine to roll back to a savepoint.
Handler update: The number of requests to update a row in a table.
Handler write: The number of requests to insert a row in a table.

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 cache low memory prunes: The number of queries that were deleted from the query cache because of low memory.
Not cached queries: The number of non-cached queries (not cacheable, or not cached due to the query_cache_type setting).
Cached queries: The number of queries registered in the query cache.
Total blocks: The total number of blocks in the query cache.

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, the table_open_cache value is probably too small.
Table locks immediate: The number of times that a table lock was acquired immediately.
Table locks waited: The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high and performance problems occur, it is advised to first optimize the queries, and then either split the table or tables or use replication.
Table open cache: The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires.
Max heap table size: This variable sets the maximum size to which MEMORY tables are allowed 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.

Memory

Binlog cache size: The size of the cache to hold the SQL statements for the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled (–log-bin option). With large, multiple-statement transactions, this cache size can be increased to get more performance.
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 non-empty 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 size of the buffer that is used for joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increasing the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.
Key buffer size: Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache. The maximum allowable setting for key_buffer_size is 4GB. The effective maximum size might be less, depending on the available physical RAM and per-process RAM limits imposed by the operating system or hardware platform.
Increase the value to get better index handling (for all reads and multiple writes) to as much as available: Using a value that is 25% of total memory on a machine that mainly runs MySQL is quite common. However, if the value is too large (for example, more than 50% of the total memory) the system might start to page and become extremely slow. MySQL relies on the operating system to perform file system caching for data reads, so it is necessary to leave some room for the file system cache.
% Key buffer size: Percentage of the index block buffer used.
Warning: This value is an approximation because some space in the key buffer is allocated internally for administrative structures.
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). This variable cannot be set to more than 1GB or to less than 4096 bytes. The default value is 1GB. A transaction is written in one chunk to the binary log, so it is never split between several binary logs.
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 problems occur with memory fragmentation, it might help to increase this a bit.
Read buffer size: Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans. To cope with many sequential scans, it may prove useful to increase this value, which defaults to 131072.
Read random buffer size: When reading rows in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks. Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so the global variable should not be set to a large value. Instead, change the session variable only from within those clients that need to run large queries.
Sort buffer size: Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations.
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.

Miscellaneous

Slow queries: 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. With the –log-slow-queries option, the query is logged to the slow query log file. This value is measured in real-time, not CPU time, so a query that is under the threshold on a lightly loaded system might be above the threshold on a heavily loaded one. The minimum value is 1. The default is 10.
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, it may prove useful to consider increasing the value of the sort_buffer_size system variable.
Sort range: The number of sorts that were done using ranges.
Sort rows: The number of sorted rows.
Sort scan: The number of sorts that were done by scanning the table.
Not flushed delayed rows: The number of rows waiting to be written in INSERT DELAY queues.

Description

Textual description of the server.

Database Version: The version of the MySQL database.