7

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)
1
  • 1
    That particular table needs an index starting with pqr_sess_id.
    – Rick James
    Jun 29, 2020 at 16:03

3 Answers 3

7

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

1
  • 1
    Do not increase query_cache_size past about 50M; it does not scale well. Most production systems actually benefit from turning off the Query Cache.
    – Rick James
    Jun 29, 2020 at 16:02
0

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.

0

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_limit and table_definition_cache to about 2500000
  • changed table_open_cache and table_definition_cache to 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.