Vlad Fedorkov

Performance consulting for MySQL and Sphinx

Metrics overview in the MySQL health check report

Download : Report sample : Main page : Request support

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.

Questions

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.

Queries

Number of queries went from inside of MySQL server like executed by stored procedures, queries executed by replication, etc. Report shows per second values.

MySQL Read to Write ratio

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

Handler_read_rnd

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.

Innodb_dblwr_writes

Number of writes to InnoDB doublewrite buffer. Report shows per second values.

Innodb_log_writes

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

Select_scan

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.

Select_full_join

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.

Created_tmp_tables

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.

Created_tmp_disk_tables

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.

MySQL Locking

Innodb_log_waits

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_locks_waited

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.

MySQL connections

Threads_created

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.

Connections

How many connection MySQL receives. Report shows per second average.

Aborted_connects

How many clients has been failed to connect to MySQL server. Report shows per second average.

If you have a questions regarding your MySQL server statistics please feel free to contact me to schedule a review.

Back to MySQL health check page.

If you find this page useful please share it to others!

Your email address will not be published. Required fields are marked *

*