MySQL Monitoring & Performance

Monitoring Thread in MySQL

Monitoring Status of All the Threads

% mysqladmin -u root -p processlist
mysql> SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
     Id: 7
   User: root
   Host: localhost:53564
     db: mysql
Command: Query
   Time: 0
  State: NULL
   Info: SHOW FULL PROCESSLIST
*************************** 2. row ***************************
...

Identify any threads & SQLs that have been run for a long time "Time"

Monitor Slow SQL Query in MySQL

Slow query

  • Check Slow Query log regualarly for slow SQL query
  • Message logs in the slow query log

To verify any SQLs that do not use index, start MySQL server with option:

log-queries-not-using-indexes

Monitor MySQL Status

MySQL status variables compose of MySQL server runtime statistics

+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Aborted_clients                   | 0        |
| Aborted_connects                  | 0        |
| Binlog_cache_disk_use             | 0        |
| Binlog_cache_use                  | 0        |
| Bytes_received                    | 664      |
| Bytes_sent                        | 51458    |
...

MySQL provides a few tools to monitor the server status

Using mysqladmin

Snapshot of the MySQL status

% mysqladmin -u root -p extended

Collect data continuously every 10s

% mysqladmin -u root -p extended -i10 -r

Reset Server status value

% mysqladmin -u root -p flush-status

Use mysql

mysql command Description
SHOW ENGINE INNODB STATUS\G Show InnoDB status
SHOW GLOBAL STATUS; Show server runtime status
SHOW SESSION STATUS; Show session runtime status
SHOW STATUS LIKE 'Table%'; Show statistics on table locking
SHOW GLOBAL STATUS LIKE 'Opened_tables%'; Show the number of open files
SELECT @@GLOBAL.sql_mode;

SELECT @@SESSION.sql_mode;
Show SQL mode
SHOW TABLE STATUS Show table summary include free space
SHOW TABLE STATUS LIKE 'tbl' Show table status
FLUSH STATUS Reset server status value

Monitoring MySQL

Traffic Analysis on MySQL

  1. Collect the following parameters continuously every 300s
    • queries : Amount of SQL statements executed so far
    • com_select : SELECT statements without using query cache
    • qcache_hits : SELECT statements using query cache
    • com_update: UPDATE statements
    • com_insert: INSERT statements
    • com_delete: DELETE statements
  2. Plot the amount of requests within the sample period (The difference during the sample period)
  3. Identify any surge of requests comparing with previous history
  4. Identify any change on the mix of requests
  5. Any changes may be caused by
    • A surge of client traffic
    • In-efficient or buggy SQLs introduced by application code, missing indexes and/or inaccurate statistics

Locate Bad SQLs on MySQL

Main reasons of bad SQLs

  • Missing proper index(s)
  • Tables are not analysis after major data changes
  • Bad SQL coding
  • Bad execution plan

To locate sign of bad SQLs, verify the value of

Name Description
slow_queries Amount of slow query (default, longer than 10s)
select_full_join Should be 0. If not, it indicates table are joined with table scan
select_range_check Should be 0. If not, it indicates table are joined with table scan

Use mysqldumpslow to analysis the slow query log

And compute the delta change during the sample period and compare the value with corresponding historical data

Name Description
handler_read_key High value indicates frequent usage of index. Expect high value for OLTP
handler_read_rnd High values indicates frequent table/index scan. Expect low value for OLTP
select_scan Number of full table scan. Expect low value for OLTP

With any sign of trouble:

  1. Review any SQLs logged in the slow query log
  2. Run explain plan to analysis the access path
  3. Perform SQL tuning

Locate Client Application Issue on MySQL

Name Description What to watch for
aborted_clients Number of client closed adruptly Indicate problem with the client application like J2EE/PHP server crash
threads_connected Number of concurrent client connection If it is un-expected high, make sure MySQL server & the machine have enough physical memory to support it
threads_running Number of client thread in running state inside MySQL server Un-expected high value indicates potential application code issue
handler_rollback Amount of transaction rolling back Un-usual high indicates client application bug

If Threads_connected reaches very close to max_connections

  • Reduce the client connection
  • Increase max_connections but make sure there is enough physical memory supporting additional connections

Locate MySQL Tuning Problem

Name Description Tunable Parameter
key_read_requests

key_reads
If key_reads/key_read_requests ratio is high, tune the key buffer for MyISAM for better key caching key_buffer_size
Qcache_hits

Qcache_hits

Com_select
if Qcache_hits/(Qcache_hits + Com_select) is un-expected low with what the application expect query_cache_size
Opened_tables If the value increase more than 2 per second, it indicates tuning problem with table cache table_open_cache
Threads_created Should be low in particular after all application servers have started thread_cache_size
Created_tmp_disk_tables High value indicates MySQL is using disk in stead of memory for temporary tables tmp_table_size, max_heap_table_size

Locate InnoDB Performance Issues

InnoDB exports part of the monitor data to MySQL status variables:

mysql> SHOW STATUS

Monitoring InnoDB using mysql

mysql> SHOW ENGINE INNODB STATUS

Output Data Periodically

  1. Drop table innodb_monitor if exist
    mysql> DROP TABLE innodb_monitor;
    
  2. Creating a dummy table called innodb_monitor. It triggers InnoDB to start logging data
    CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
    
  3. Stop monitor logging
    mysql> DROP TABLE innodb_monitor;
    

Output is send to

  • hostname.err (Error log)
  • innodb_status.pid

Note Output monitoring data will have some performance degradation and flood information into the error log, turn it on if needed only

Replace the above table name with one of the following for different type of monitoring data

Table name
innodb_monitor
innodb_lock_monitor
innodb_tablespace_monitor
innodb_table_monitor

Monitor information provided:

  • Semaphores: High contentions imply disk I/O or internal InnoDB contention
  • Foreign key error: (If any) Statements violate the reference key
  • Deadlock: (If any) Statements have deadlock problem
  • Transactions: Transactions & statements waiting for locks
  • File I/O: Information on the File I/O thread
  • Insert Buffer & Hash Index status
  • InnoDB log status
  • Buffer pool & memory status
  • Row operation summary

Tablespace information provided

  • Size, free space, info on extends

Table information provided

  • Table definition

Benchmarking

Benchmarking a SQL Statement on MySQL

# Run the SQL statements 100 times
mysql> SELECT BENCHMARK (100, 'SELECT * from tbl');

Times taken:
1 row in set (1.08 sec)

Time a SQL on MySQL

Enable Profiling

mysql> SET PROFILING = 1
...
RUN your SQLs
...
mysql> SHOW PROFILES;

+----------+------------+-----------------------+
| Query_ID | Duration   | Query                 |
+----------+------------+-----------------------+
|        1 | 0.00014600 | SELECT DATABASE()     |
|        2 | 0.00024250 | select user from user |
+----------+------------+-----------------------+
mysql> SHOW PROFILE for QUERY 2;

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000034 |
| checking query cache for query | 0.000033 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000011 |
| init                           | 0.000013 |
| optimizing                     | 0.000004 |
| executing                      | 0.000011 |
| end                            | 0.000004 |
| query end                      | 0.000002 |
| freeing items                  | 0.000026 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+

Explain Execution Plan on MySQL

Get information on how MySQL retrieve data for a SQL query so it may be optimized if needed.

Syntax

mysql> EXPLAIN SELECT * FROM tbl;

More detail information
mysql> EXPLAIN EXTENDED SELECT * FROM tbl;
mysql> SHOW WARNINGS;

Sample

mysql> EXPLAIN SELECT my_pk FROM tbl1\G
Output:
           id: 1
  select_type: SIMPLE
        table: tbl1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 228
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index
Field Description Explain Plain in Previous Example
table Table to be read. Read tbl1
type How it will be read. Read the whole index
possible_keys Suggested possible keys for indexing None
key Index to be used Use the primary key index
key_len Length of the key in the index 228 bytes
ref Value used for the index lookup None
rows Rows retrieved 2 rows retrieved
Extra More detail on how rows are accessed Use index to retrieve rows

More Examples

2 Full table scan with 20 rows from tbl1 and 1220 rows from tbl2.

table type possible_keys key  key_len ref   rows
tbl1  ALL  PRIMARY       NULL NULL    NULL  20
tbl2  ALL  PRIMARY       NULL NULL    NULL  1220

Full table scan for tbl1 and use the parent index to access tbl2

select * from tbl1 tbl2 where tbl1.id = tbl2.parent;

table type possible_keys key    key_len ref     rows
tbl1  ALL  PRIMARY       NULL   NULL    NULL    30
tbl2  ref  parent        parent 15      tbl1.id 300

MySQL Indexes

Locate Index Information on MySQL

Show indexes

mysql> SHOW INDEX FROM tbl1;

Show MyISAM table statistics

% myisamchk --description --verbose c:\mysql\data\mysql\user

Major Reason Index Are Not Used as Expected

For table:

CREATE TABLE tbl {
  col1 INT NOT NULL,
  col2 INT NOT NULL,
  col3 INT NOT NULL,
  ...
  PRIMARY KEY (col1, col2),
  INDEX idx_col3 (col3)
)
  • Missing Index
  • A function is apply to the indexed join column
    where UPPER(index_join_column) = 'mytest'
    
  • Pay attention to the order of the fields in index
    # Cannot use the primary key index because of the wrong order.
    SELECT ... WHERE col2=466
    
    # Can use the primary key index
    SELECT ... WHERE col1=4
    
  • Pattern matching
    # Can use index
    ... WHERE col1 LIKE 'test%';
    
    # Cannot use index
    ... WHERE col1 LIKE '%test';
    # Consider storing col1 in reverse order so it can change to LIKE 'tset%'
    
  • Analyze the table after major data changes or after loading data

MySQL Index Good Practice

  • Use Covering Index (aka concatenated/composite index)
    # All rows can be read from index only without accessing data block
    SELECT col1, col2 FROM tbl WHERE col3=13;
    
  • Use high selective column to narrow to as few rows as possible
  • Remove un-needed index
  • Use the smallest data type for column
  • Consider splitting less often access column to another table
  • Use a small Cluster Key (the primary key) for InnoDB since it always append to secondary index
  • InnoDB has a much slower implementation of count(*)

Forcing the use of index

  1. Try ANALYZE TABLE table first allowing MySQL to re-consider whether index(es) should be used
  2. If EXPLAIN indicates no change of the execution plan, index(es) access can be forced by:
    mysql> SELECT * FROM tbl1, tbl2 FORCE INDEX (index_column1, index_colum2)
    WHERE tbl1.column1=tbl2.colum2;
    

MyISAM Key Cache

Index block can be cached in in-memory Key cache.

To assign index of a table to a particular cache:

mysql> CACHE INDEX tbl1, tbl2 IN priority_cache;

Set cache size

mysql> SET GLOBAL priority_cache.key_buffer_size=128*1024;

Destroy a cache

mysql> SET GLOBAL priority_cache.key_buffer_size=0;

Setting cache size in Option file

key_buffer_size = 3G
priority_cache.key_buffer_size = 2G
init_file=/path/to/data/mysqld_init.sql

mysqld_init.sql

mysql> CACHE INDEX mydb.tab1, mydb.tab2 IN priority_cache;

Preload Cache

mysql> LOAD INDEX INTO CACHE tbl1, tbl2 IGNORE LEAVES;

Show cache size for the default cache

mysql> SHOW VARIABLES LIKE 'key_buffer_size';

MySQL Performance Maintenance

  • Analyze table to collect statistic information
    ANALYZE TABLE tbl1
    
    • When large amount of rows are added/deleted
    • When large amount of Index date are updated
  • De-fragment the table
    OPTIMIZE TABLE tbl1
    
    • When large amount of rows are deleted
    • When many columns are updated with data larger than the original (split row)
  • For frequent accesses that required sorted order, pre-sort the table by:
    # Sort it by index number 2
    % myisamchk --sort-index --sort-records=2
    

MySQL Tuning Parameters

ALWAYS ensure the configured total memory is below 80-90% of available physical memory

Tunable Parameter in Option File Description Value (MyISAM)1 Value (InnoDB)2
key_buffer_size In-memory cache MyISAM engine used to cache index 3 30-40% of available memory At least 40M
innodb_buffer_pool_size In-memory cache InnoDB engine used to cache data & index (use default) 50-80% of available memory
innodb_additional_mem_pool_size InnoDB uses this to store data dictionary information and internal data (use default) 20M
innodb_log_file_size The size of each InnoDB log file (on disk) in a log group

Large log file requires a longer recovery time

When the log file is full, it must write the modified contents of the buffer pool to disk in a checkpoint

A bigger file size reduce the checkpoint frequency
(use default) 25% of innodb_buffer_pool_size
innodb_log_buffer_size The size of buffer that InnoDB uses to write to the log files (use default) 8M (16M for frequent large transaction)
innodb_flush_log_at_trx_commit Control when log buffer is flushed (use default) 1 (100% Transaction safe) or 0 4
max_connections Max current client session Max concurrent clients at peak Max concurrent clients at peak

1 Value (MyISAM) : Suggested value for system that data accesses are mainly from MyISAM tables

2 Value (InnoDB) : Suggested value for system that data accesses are mainly from InnoDB tables

3 MyISAM engine does not cache data directly. It relies on OS to cache data file and therefore less than 40% of available memory should be allocated

4 (1): Flush to disk every commit. (2): Disk cache is flush every second. 1-2 second of transactions will lost if the OS crash. Since it is faster, it can be a good candidate if 100% transaction safe is not required

Identify key hit ratio

mysql> SHOW STATUS LIKE '%key_read%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 12    |
| Key_reads         | 1     |
+-------------------+-------+

Key_read_requests: Total requests to index data

Key_reads: Requests that require file I/O

Tuning table_open_cache

"table_open_cache" caches open files to be re-usable by sessions. For MyISAM and InnoDB with individual tablespace, each table requires one open file per concurrent connection.

Hence if 100 connections are concurrently accessing tbl1, 100 open files plus whatever needed by MySQL server are needed.

Monitor the opened_tables and open_tables closely during peak hour

mysql> SHOW STATUS LIKE 'Opened_tables%';
mysql> SHOW STATUS LIKE 'Open_tables%';

If table_open_cache is very close to open_table (current opened table) and opened_tables (# of file open calls) keep increasing very fast at peak hour, increase the table_cache value.

Increase the max open file limit open_files_limit

  • when mysqld gives you the error "Too many open files" OR
  • table_open_cache is increased

If open_files_limit has increased above what is set for the MySQL process, restart MySQL by setting a higher OS value using ulimit -n

Tuning query_cache_size

"Query cache" cache a query result instead of executing the SQL again.

Verify Query Cache state & size

mysql> SHOW VARIABLES LIKE 'have_query_cache';
mysql> SHOW VARIABLES LIKE 'query_cache_size';
mysql> SHOW VARIABLES LIKE 'query_cache_type';

Showing Query Cache statistics

SHOW STATUS LIKE 'Qcache%';
SHOW STATUS LIKE 'Com_select%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 9426856 |
| Qcache_hits             | 2       |
| Qcache_inserts          | 1       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 13      |
| Qcache_queries_in_cache | 1       |
| Qcache_total_blocks     | 4       |
+-------------------------+---------+

Monitor:

Cache Hit ratio = Qcache_hits / (Qcache_hits + Com_select)

Qcache_lowmem_prunes: Queries removed from cache because low in memory

Qcache_queries_in_cache: Number of query in the cache

Increase query_cache_size by 20% and monitor the statistics again over peak hour. If there is no reasonable improvement, restore it to the old value. Re-iterate the tunning procedure but do not over commit memory when there is not enough physical memory.

Sample configuration

[mysqld]
query_cache_type = 1
query_cache_size = 200M

Thread cache

Monitor thread creation at peak hour

SHOW STATUS LIKE 'threads_created%';

If it keep increasing fast, increase thread_cache_size

Temporary Table Statistics

mysql> SHOW STATUS LIKE 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 4     |
+-------------------------+-------+

Created_tmp_disk_tables: The number of temporary tables created on disk

Tune tmp_table_size and max_heap_table_size accordingly to minimize frequent disk I/O for temporary table

Per Thread (Connection) Buffer Size Tuning

These turning parameter should be changed per session. Change it in GLOBAL variable forces all connection to use the same new setting.

Change the GLOBAL setting only if a significant portion of request needs:

  • Full table scan of large table or
  • Large full/range index scan
Option Description For
read_buffer_size Size of the buffers during read per connection Full table scan
join_buffer_size Size of the buffer for index scan and joins that do not use index For table join

Note: The value is per thread.

sort_buffer_size

sort_buffer_size: Size of the buffers for sorting per thread

mysql> SHOW STATUS LIKE 'sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 0     |
| Sort_scan         | 0     |
+-------------------+-------+

Sort is done in memory unless sort_buffer_size is not big enough and force part of the operation to be done on temporary file. If the number of such file based sorting (sort_merge_passes) is high comparing with the number of SQL request, increase the sort_buffer_size according.

MySQL Memory Consumption

Per server

  • key_buffer_size
  • innodb_buffer_pool_size
  • innodb_additional_memory_pool_size
  • innodb_log_buffer_size
  • query_cache_size.
  • OS File Cache

Per connection

  • read_buffer_size
  • sort_buffer_size
  • read_rnd_buffer_size
  • tmp_table_size