Mysql – statistics State in MySQL Processlist

MySQLmysql-5.1performancequery-performancetransaction

I have a busy MySQL Server having query

SELECT  sys_sess_state, index_state, timeout_lvl, last_queued_dt,
        last_polled_wait_dt, create_id, create_dt, modify_id,
        modify_dt
    FROM  PQR_AM_SYSTEM_SESSION
    WHERE  (pqr_sess_id=592885621) FOR UPDATE

Where pqr_sess_id is Primary Key in table.

When I execute the query independently it is being finished in 0.00 secs, but i have observed many times that query is hanging is processlist with state statistics.

What may be the issue, I have found count of this query in slow log more than 10K times.

mysql> show full processlist;
+------+-----------------+---------------------+-----------+---------+------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id   | User            | Host                | db        | Command | Time | State                       | Info                                                                                                                                                                                                                                                                                                         |
+------+-----------------+---------------------+-----------+---------+------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2899 | root            | 192.168.9.217:49340 | abdulrony | Query   |   58 | statistics                  | SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION WHERE (pqr_sess_id=592885621) FOR UPDATE                                                                                                         | 
| 2977 | root            | 192.168.9.217:35082 | abdulrony | Query   |   30 | statistics                  | SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION WHERE (pqr_sess_id=593059341) FOR UPDATE                                                                                                         | 
| 3010 | root            | 192.168.9.217:44607 | abdulrony | Query   |    3 | statistics                  | SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION WHERE (pqr_sess_id=595647701) FOR UPDATE                                                                                                         | 
| 3011 | root            | 192.168.9.217:44287 | abdulrony | Query   |   33 | statistics                  | SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION WHERE (pqr_sess_id=593503321) FOR UPDATE                                                                                                         | 
| 3014 | root            | 192.168.9.217:49280 | abdulrony | Query   |    3 | statistics                  | SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION WHERE (pqr_sess_id=595641601) FOR UPDATE                                                                                                         | 
| 3016 | root            | 192.168.9.217:60243 | abdulrony | Query   |   51 | statistics                  | SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION WHERE (pqr_sess_id=592248641) FOR UPDATE                                                                                                         | 
| 3033 | root            | 192.168.9.217:49695 | abdulrony | Query   |   22 | statistics                  | SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION WHERE (pqr_sess_id=594780861) FOR UPDATE                                                                                                         | 
| 3034 | root            | 192.168.9.217:44473 | abdulrony | Query   |   27 | statistics                  | SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION WHERE (pqr_sess_id=594503601) FOR UPDATE                                                                                                         | 
| 3038 | root            | 192.168.9.217:55093 | abdulrony | Query   |    1 | statistics                  | SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION WHERE (pqr_sess_id=595625241) FOR UPDATE                                                                                                         | 
+------+-----------------+---------------------+-----------+---------+------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
22 rows in set (0.00 sec)

I know i can tune optimizer_search_depth by setting it to some low value The default value is as below, but i have a single table involve in query so i don't think will help.

SHOW variables like 'optimizer_search_depth';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| optimizer_search_depth | 62    | 
+------------------------+-------+
1 row in set (0.00 sec)

Best Answer

If the query thead stays longer for statistics indicates that the server is probably disk-bound performing other work.

How to reduce Disk-Bound:

1) Increase the size of innodb_buffer_pool_size

If you are using Innodb tables then When table data is cached in the InnoDB buffer pool, it can be processed over and over by queries without requiring any disk I/O. Specify the size of the buffer pool with the innodb_buffer_pool_size option. This memory area is important enough that busy databases often specify a size approximately 80% of the amount of physical memory.

2) In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() call (which InnoDB uses by default) and similar methods is surprisingly slow. If database write performance is an issue, conduct benchmarks with the innodb_flush_method parameter set to O_DSYNC.

3) Increase the size of innodb_log_buffer_size - Set the amount of memory allocated to the buffer storing InnoDB write-ahead log entries. For large transactions, the log can be loaded into the log buffer instead of writing log to the log files on disk untill the log buffer is flushed on each transaction commit. If you see large log I/Os in the show innodb status output at runtime, you probably need to set a larger value for the innodb_log_buffer_size parameter to save disk I/O.

4) Increase the memory used to cache the tables and the queries - check the cache hit ratio of them Check and increase these MySQL variables: query_cache_size, query_cache_limit, query_cache_min_res_unit, tmp_table_size, join_buffer_size , sort_buffer_size etc..

5) Make sure that proper index is applied to all tables on a server and use proper datatypes.

You can refer these links to resolve Disk-bound issue: http://dev.mysql.com/doc/refman/5.5/en/disk-issues.html https://blogs.oracle.com/luojiach/entry/mysql_innodb_performance_tuning_for

Related Question