Bug #105926 Query information_schema.processlist has performance impact on normal queries
Submitted: 17 Dec 2021 8:41 Modified: 17 Dec 2021 10:53
Reporter: shangshang yu Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:8.0.18 OS:Any
Assigned to: CPU Architecture:Any

[17 Dec 2021 8:41] shangshang yu
Description:
Use short connection execute query: 
mysql -uroot -pxxx -h -P3306 -e "select * from information_schema.processlist;"

The query will generate a temporary table, when the connection closing, will  acquire buf_pool->LRU_list_mutex  to lock whole buffer pool LRU in order to remove temporary table pages´╝Ü

#1  0x0000000002288808 in Fil_shard::space_truncate (this=0x7f1d642ddce8, space_id=4294501266, size_in_pages=5) at /mnt/yushangshang/software/mysql-8.0.18/storage/innobase/fil/fil0fil.cc:4180
#2  0x00000000020f16df in ibt::Tablespace::truncate (this=this@entry=0x7f1d64476538) at /mnt/yushangshang/software/mysql-8.0.18/storage/innobase/srv/srv0tmp.cc:142
#3  0x00000000020f3017 in ibt::Tablespace_pool::free_ts (this=0x7f1d644724b8, ts=0x7f1d64476538) at /mnt/yushangshang/software/mysql-8.0.18/storage/innobase/srv/srv0tmp.cc:229
#4  0x00000000020f35c5 in ibt::free_tmp (ts=<optimized out>) at /mnt/yushangshang/software/mysql-8.0.18/storage/innobase/srv/srv0tmp.cc:377
#5  0x0000000001f47e17 in ~innodb_session_t (this=0x7f01c800f748, __in_chrg=<optimized out>) at /mnt/yushangshang/software/mysql-8.0.18/storage/innobase/include/sess0sess.h:94
#6  destroy (this=<synthetic pointer>, p=0x7f01c800f748) at /mnt/yushangshang/software/mysql-8.0.18/storage/innobase/include/ut0new.h:735
#7  ut_delete<innodb_session_t> (ptr=0x7f01c800f748) at /mnt/yushangshang/software/mysql-8.0.18/storage/innobase/include/ut0new.h:1032
#8  innobase_close_connection (hton=<optimized out>, thd=0x7f01c80008c0) at /mnt/yushangshang/software/mysql-8.0.18/storage/innobase/handler/ha_innodb.cc:5685
#9  0x00000000010a3f41 in closecon_handlerton (thd=0x7f01c80008c0, plugin=<optimized out>) at /mnt/yushangshang/software/mysql-8.0.18/sql/handler.cc:914
#10 0x0000000000e76bb6 in plugin_foreach_with_mask (thd=thd@entry=0x7f01c80008c0, funcs=funcs@entry=0x7f1d69064bf0, type=type@entry=1, state_mask=4294967287, state_mask@entry=8, arg=arg@entry=0x0) at /mnt/yushangshang/software/mysql-8.0.18/sql/sql_plugin.cc:2686
#11 0x0000000000e76dcd in plugin_foreach_with_mask (thd=thd@entry=0x7f01c80008c0, func=func@entry=0x10a3f00 <closecon_handlerton(THD*, plugin_ref, void*)>, type=type@entry=1, state_mask=state_mask@entry=8, arg=arg@entry=0x0) at /mnt/yushangshang/software/mysql-8.0.18/sql/sql_plugin.cc:2699
#12 0x00000000010a80fe in ha_close_connection (thd=thd@entry=0x7f01c80008c0) at /mnt/yushangshang/software/mysql-8.0.18/sql/handler.cc:926
#13 0x0000000000df066a in THD::release_resources (this=this@entry=0x7f01c80008c0) at /mnt/yushangshang/software/mysql-8.0.18/sql/sql_class.cc:1028
#14 0x0000000000f79683 in handle_connection (arg=arg@entry=0x45663f0) at /mnt/yushangshang/software/mysql-8.0.18/sql/conn_handler/connection_handler_per_thread.cc:309
#15 0x00000000023c2ef5 in pfs_spawn_thread (arg=0x45603c0) at /mnt/yushangshang/software/mysql-8.0.18/storage/perfschema/pfs.cc:2854
#16 0x00007f1d7eecde45 in start_thread () from /lib64/libpthread.so.0
#17 0x00007f1d7cf90b3d in clone () from /lib64/libc.so.6

The normal queries should do buf_page_make_young, it will wait the buf_pool->LRU_list_mutex, if the server has a huge buffer pool, it will take a long time.

How to repeat:
prepare a server has huge buffer pool, eg: 100GB  buffer pool and 300GB database data, when normal queries executing(eg: sysbench tools), and use short connection to execute query:

mysql -uroot -pxxx -hxxx -P3306 -e "select * from information_schema.processlist;"
[17 Dec 2021 10:53] MySQL Verification Team
Hello Shangshang yu,

Thank you for the report and feedback.
Imho this most likely duplicate of Bug #98869, which is fixed in 8.0.23 by WL14100. I see you are using 8.0.18 and surely affected since it was fixed in 8.0.23. I request you to try latest GA version 8.0.27 and inform us if you are still seeing the issue. Thank you.

Marking this as duplicate of Bug #98869

regards,
Umesh