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;"
