Question :
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)
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
I think the statistics state is confusing. I encountered the same situation a few days ago, I searched google and also came up with the optimizer_search_depth. But when I check the information_schema.innodb_trx table, I found these SQLs all in the LOCK WAIT state. It means these queries are just waiting for the lock on the primary key, maybe you have several queries select the same value for update.
In my case it was a huge database – many databases, many tables. After startup Mariadb was working slowly, all the queries not executing fast enough but stuck on “statistics”.
I did 3 things:
- increased file limit to 4000000 in
etc/systemd/system/multi-user.target.wants/mariadb.service - increased
open_files_limitandtable_definition_cacheto about 2500000 - changed
table_open_cacheandtable_definition_cacheto about 3000 - installed “
libjemalloc” for Mariadb as memory usage was still growing.
That solved the problem. Of course you don’t have to set these numbers so high, that depends on your number of databases, tables & files.