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

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

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

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

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

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

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

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

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

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

³ 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

⁴ (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

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