MySQL Database Performance Counters

A popular relational database management system (RDBMS) for use with websites and web-based applications is MySQL. Structured Query Language (SQL), a language used to access and modify data within databases, serves as its foundation. Due to its accessibility, dependability, and flexibility, it is a preferred database for many web-based applications.

It is possible to save, retrieve, and alter data with MySQL, as well as create and administer databases and control user access, among other functions. Users can quickly create and edit databases thanks to their simple design and ease of use. Additionally, it offers a vast array of data storage and processing features, including as Full-Text Indexing, Triggers, Stored Procedures, and Replication.

Because MySQL is created to be highly accessible, it is always usable. Large volumes of data can be stored and managed with it, and because it is optimized for the most popular searches, it can quickly deliver results. It is the perfect option for applications that demand secure data storage because it offers both data security and data integrity.

Online forums, social networking sites, content management systems, e-commerce websites, and other applications all use MySQL. Numerous sizable businesses, including banks and government institutions, employ it as well. Due to its adaptability, scalability, and affordability, MySQL is a preferred option among web developers.

Key Features of MySQL Database

Open Source: MySQL is a relational database management system that is available under an open-source license, making it free to use and share.
Cross-Platform Support: Support for Multiple Operating Systems: MySQL is accessible for some platforms, including Microsoft Windows, Mac OS, and Linux.
Scalability and Flexibility: MySQL may be configured to fit a variety of requirements and scales up to accommodate exceptionally big databases with billions of records.
Easy to Use: MySQL is a well-liked option among people just getting started with databases because it is straight forward to understand.
High Performance: MySQL can quickly and effectively handle enormous amounts of data.
Secure: To protect your data, MySQL has many security layers in place.
Comprehensive Support: To help you make the most of your database, MySQL offers comprehensive documentation and support services.

MySQL database performance counters

Connections

Threads connected: The number of open connections at the moment.
Threads cached: The total number of threads that have been cached.
Threads running: The total number of active threads.
Attempts: Number of (successful or unsuccessful) connection attempts made to the MySQL server.
Aborted clients: Clients that failed to correctly close their connections to the MySQL server each second are referred to as aborted clients.
Aborted connects : The number of unsuccessful connections made to the MySQL server per second.
Max used: Since the server’s launch, most connections have ever been active at once.
Outstanding connection requests: The maximum number of pending connection requests that MySQL is capable of handling. This is relevant when the primary MySQL thread receives a large number of connection requests quickly. The main thread then checks the connection and initiates a new thread, which takes some time (albeit not much). How many requests can be stacked over this brief period before MySQL temporarily stops accepting new requests is indicated by the back_log value? Only if there will be a lot of connections in a short amount of time will it be required to raise this.
Max allowed: Maximum number of permitted concurrent client connections. Starting with MySQL 5.1.15, this is 150 by default. (Prior to now, 100 was the default.)
The maximum permitted per user: the most connections that can be made at once to a single MySQL account. A 0 value denotes “no limit.”
Maximum cached threads: The server should cache how many threads for later use. If there are fewer threads in the cache than the thread_cache_size when a client disconnects, the client threads are stored there. If at all possible, threads drawn from the cache are used to satisfy requests; new threads are only produced when the cache is empty. In cases where there are numerous new connections, this value might be raised to enhance performance. (Typically, if a good thread implementation is in place, this does not provide a noticeable speed boost.)

OI Requests

Bytes received: The total amount of data that all clients have sent.
Bytes sent: The total amount of bytes transmitted to all clients.
Open files: The total number of open files.

Select Rates

Select full join: The amount of joins that execute table scans because they don’t employ indexes is known as a complete join. It is advisable to thoroughly check the table indexes if this value is not 0.
Select full range join: The number of joins that used a range search on a reference table in the select full range join.
Select Range: The number of joins that are used ranges from the first table in the chosen range. Despite the fact that the value is fairly high, this is typically not a serious problem.
Select range check: The number of joins without keys that check for key usage after each row is the select range check. It is advisable to thoroughly check the table indexes if this value is not 0.
Select scan: The number of joins that completely scanned the first table during the select scan.

Read Rates

Handler read first: The number of times the first entry from an index was read by the handler. SELECT col1 FROM foo, assuming col1 is indexed, for example, would indicate that the server is performing many full index scans if this value is high.
Handler read key: The total number of requests to read a row based on a key made by the handler. It is a good sign that the tables are appropriately indexed for the queries if this value is high.
Next-handled read: the quantity of key-ordered next-row read requests. When an index column is queried with a range constraint or when an index scan is carried out, this value is increased.
Handler read previously: Number of requests to read the previous row in key order handled by the handler read previously. The main purpose of this read method is to enhance ORDER BY… DESC.
Handler read random: The number of requests to read a row based on a fixed position made by the handler read random. When several queries must be run and the results must be sorted, this value is high. There are probably many queries that require MySQL to scan entire tables, or there are joins that improperly employ keys.
Handler read random next: The number of requests to read the following row in the data file from the handler reading at random next. This number is high when several table scans are being conducted. This typically means that the queries were not written to make use of the available indexes or that the tables are not correctly indexed.

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 disc is known as the key blocks not flushed statistic.
Key blocks unused: Number of empty blocks in the key cache, or key blocks. The amount of the key cache that is being used cannot be calculated using this value.
Key blocks used: Blocks in the key cache that have been used as keys. As a high-water point, this value represents the most blocks that have ever been active at once.
Key read requests (cache): are as follows. how many times a key block has been requested to be read from the cache?
Key reads (disc): The total number of actual disc reads of a key block. The key_buffer_size setting is possibly too small if Key_reads is high.
Key write requests (cache): The quantity of requests to write a key block to the cache is known as the key write requests (cache).
Key writes (disc): The total number of actual disc writes made to a key block.

Commands Rates

Flushes commends The number of FLUSH statements that have been executed.
Handler commits The number of internal COMMIT statements for the handler.
Handler deletes: Number of times that rows have been deleted from tables using the handler delete.
Handler rollback: The number of requests for a storage engine to perform rollback operations is known as the handler rollback.
Handler savepoint: The number of requests for a storage engine to put a savepoint is known as the handler savepoint.
Handler savepoint rollback: The number of requests for a storage engine to roll back to a savepoint handled by the handler.
Handler update: The number of requests made to update a table row.
Handler writes The number of requests to add a row to a table, according to the handler.

Query Cache

Query cache free blocks: Blocks of memory that are free in the query cache, or the query cache free blocks.
Query cache free memory: Free memory for the query cache: How much memory is available for the query cache.
Query cache hits: Hits from the query cache are the total number of hits.
Query cache inserts: Number of queries added to the query cache, expressed as an integer.
Query cache low memory prunes: The number of queries that were removed from the query cache due to memory shortage.
Not cached queries: The total number of non-cached queries (those that were either not cacheable or that were not cached as a result of the query_cache_type parameter).
Instances of cached queries: The total number of instances of cached queries.
Blocks total: The sum of all the blocks in the query cache.

Tables

Open tables: The number of tables that are currently open.
Open tables: The number of tables that have been made available. The table_open_cache value is possibly too little if Opened_tables is large.
Table locks immediately: The frequency with which a table lock was obtained.
Table locks
Waited: The frequency with which a table lock could not be obtained immediately and a delay was necessary. It is recommended first to optimize the queries, then split the table or tables or use replication, if this is high and performance issues arise.
Table open cache: Number of open tables across all threads in the table open cache. The more this value is raised, the more file descriptors mysqld needs.
Maximum heap table size: This parameter determines how big MEMORY tables can get. The variable’s value is used to determine the MAX_ROWS values for the MEMORY table. No existing MEMORY table will be affected by setting this variable unless the table is recreated, changed, or deleted using an ALTER TABLE or TRUNCATE TABLE statement.

Memory

Binlog cache size: The size of the cache used to store the SQL statements used by the binary log during a transaction. If the server supports any transactional storage engines and if the server has the binary log enabled (-log-bin option), a binary log cache is allocated for each client. This cache size may be increased for larger, multi-statement transactions to improve speed.
Bulk insert buffer size: When adding data to non-empty tables, MyISAM employs a special tree-like cache to speed up bulk inserts for INSERT… SELECT, INSERT… VALUES (…), (.. .),…, and LOAD DATA INFILE. This setting establishes a byte restriction on the cache tree size per thread. This optimization is turned off by setting it to 0. 8MB is the default setting.
Join buffer size: The size of the buffer used for joins that don’t employ indexes and instead execute full table scans is referred to as the join buffer size. Adding indexes is typically the best technique to get fast joins. When adding indexes is not an option, increasing the amount of join_buffer_size will result in a faster full join. For each entire join between two tables, one join buffer is allocated. Multiple join buffers may be required for a complicated join involving various tables for which indexes are not used.
Key buffer size: Index blocks for MyISAM tables are buffered and shared by all threads. Key buffer size. The buffer size for index blocks is known as key_buffer_size. The key cache is another name for the key buffer. The key_buffer_size parameter has a maximum value of 4GB. Depending on the amount of physical RAM that is available and the per-process RAM limitations imposed by the operating system or hardware platform, the actual maximum size might be smaller.
Increase the value as much as possible to improve index handling (for all reads and multiple writes): On a system that mostly runs MySQL, a figure of 25% of total memory is frequently used. On the other hand, if the value is excessive (for instance, more than 50% of the entire memory), the
% Key buffer size: Percentage of the index block buffer that is used for the key buffer
Warning: This amount should be considered approximate because internal administrative structures have been allotted some space in the key buffer.
Max binlog size: The server rotates the binary logs (closes the current file and opens the following one) whenever a write to the binary log causes the current log file size to exceed the value of this variable. The maximum value for this variable is 1GB, and the minimum value is 4096 bytes. 1GB is the default setting. A transaction is never split among many binary logs; instead, it is written to the binary log in a single chunk.
Preload buffer size: The amount of space reserved for the buffer during the preloading of indexes.
Query alloc block size: The size of the memory blocks allocated for objects produced during statement parsing and execution is known as the query alloc block size. This could be slightly raised if memory fragmentation issues arise.
Read buffer size: For each table that a thread reads sequentially, it allows a buffer of this size (in bytes). This value, which by default is 131072, maybe usefully increased to handle numerous successive scans.
Read random buffer size: To avoid disc seeks, rows are read through this buffer when being read in sorted order after a key-sorting operation. The performance of ORDER BY can be greatly enhanced by setting the variable to a high value. A huge number should not be set for the global variable because this is a buffer that is allocated for each client. Instead, only clients who must execute complex queries should be allowed to modify the session variable.
Size of the sort buffer: Each thread that requires a sort allocates a buffer of this size. For speedier ORDER BY or GROUP BY operations, increase this value.
Transaction alloc block size: The size of a per-transaction memory pool that needs to be increased in terms of bytes is known as the transaction alloc block size.
Transaction preallocate size: A per-transaction memory pool is used for all memory allocations connected to transactions.

Miscellaneous

Slow Queries: The number of slow queries or queries that took more than long_query_time seconds.
Long Query Time – The server increments the Slow_queries state variable if a query takes longer than this number of seconds to complete. The query is logged in the slow query log when the -log-slow-queries option is used. A query that is below the threshold on a lightly loaded system may be above the threshold on a fully loaded system since this value is measured in real-time instead of CPU time. The smallest value is one. 10 is the default.
Slow Launch Threads: Threads that took longer than slow_launch_time seconds to start than other threads.
Slow Launch Time: The server increments the Slow_launch_threads state variable if it takes more than this number of seconds to start a thread.
Qualifying Combination Passes: The combination passes for the classification algorithm are the total number of combination passes required. If this number is high, you may consider increasing the value of the sort_buffer_size system variable.
Sort Rank: The number of rank-based sorts performed.
Sort Rows: The number of rows that have been sorted.
Rank Analysis: The total number of ranks performed after the table was scanned in the rank analysis.
Rows Delayed Not Flushed: The number of rows in the INSERT DELAY queues that have not yet been flushed.

Description
Textual description of the server.

The version of the database: The version of the MySQL database.