General MySQL load
General section of the statistic report gives you an idea of the load behavior on your MySQL server. Besides three important metrics explained below MySQL counters report showing number of SELECTs, INSERTs, UPDATEs and DELETEs per second. Fist column called «Uptime» indicating per-second value average from last MySQL server start. «Live#1» and «Live#2» columns shows per second averages for the time then report were collecting MySQL statistics. Live stats collection performing in two passes 10 second each by default. Please note that MySQL providing absolute value while report shows per second averages.
Gives you an idea of how much queries MySQL has served from “real” clients remotely connected to MySQL server. This number is not limited by SELECTs/INSERTs/UPDATEs and DELETEs but also includes DDL commands like CREATE and ALTER, administrative and service commands. SHOW PROCESSLIST, USE, GRANT are all included here too. Report shows per second values.
Number of queries went from inside of MySQL server like executed by stored procedures, queries executed by replication, etc. Report shows per second values.
Read/write ratio is one of the key metrics of MySQL server and shows number of selects divided by number of writing queries (INSERTs, UPDATEs and DELETEs). R/W rate > 1 means that your server is reading more than writing and you can possibly benefit from data cashing on MySQL and application level. R/W rate more than 10 means your server behavior is mostly read-only. In the opposite R/W rate < 1 means you are mostly writing and MySQL settings might need be optimized for writing. Though it is main metric it’s number is aggregated and doesn’t provide all the details. It could be one table where you writing and others are read-only so tunning decisions should be made carefully.
MySQL IO metrics
Number of random reads. Random reads are ok for SSD disks but might be an issue for SATA and SAS drives and even arrays. Might be an indication of some queries that missing proper indexes. Report shows per second values.
Innodb_data_writes and Innodb_data_reads
Number of write request from InnoDB engine. Related to writes to log files, double write buffer and data files. Report shows per second average. Sum of these values can be compared to number of IOPS your hardware is capable to handle per second. If you are close to the cap (can be confirmed with iostat during peak time) you are probably need perform deeper review of the MySQL server configuration, queries and system.
Number of writes to InnoDB doublewrite buffer. Report shows per second values.
Number of writes to log file per second.
Key buffer efficiency
Key_read_requests and Key_reads
These statistics are related to key buffer usage by MyISAM storage engine. Generally key_read_requests should be much (100x, 1000x+) more than actual key_reads which is indication that your key buffer big enough to fit all hot data for MyISAM tables. If you see that most read request ending up with key reads it means than MySQL are reading data from disk which could be quite slow.
MySQL query-related stats
Indicate queries which perform full table scans. These queries can usually be fixed by adding an appropriate indexes. Complete MySQL query review usually helps to find and fix them. Report shows per second average.
Number of joins which perform full scan on joined table. These queries are very slow but relatively easy to fix by adding indexes on the joined table. These queries usually appear on the slow queries section of this health check report is slow query logging is properly enabled.
How many temporary tables MySQL created per second. Some queries like subqueries and GROUP BY forces MySQL to create temporary table. Sometimes these queries are ok, sometimes they can be rewritten and sometimes it’s smart to move them to out of MySQL completely. You can find some details in my MySQL Anti-Queries presentation I did on Percona Live conference.
When temporary table doesn’t fit into in-memory space MySQL uses even slower on disk table to store temporary data. This should be avoided as much as possible by tuning tmp_table_size and max_heap_table_size in my.cnf. Please be careful not to set them too big.
Non-zero value indicates writes waited on InnoDB log buffer flush. High value can usually be fixed by tunning size of InnoDB log buffer. Log file and number can be considered for review as well.
Table level locks are usual for write-intensive environment with MyISAM engine. You can consider testing InnoDB in your environment to avoid table-level locks.
Number of threads created (Report shows per second value) when new client connects to MySQL. Establishing connection to MySQL is relatively fast but MySQL uses one thread per client connection and thread creation can take time. You may want to tune up thread_cache_size variable to avoid thread creation or use connection pool.
How many connection MySQL receives. Report shows per second average.
How many clients has been failed to connect to MySQL server. Report shows per second average.
If you find this page useful please share it to others!