TL;DR

呼び出し元	エントリーポイント	リスト関数
SHOW PROCESSLIST (option=OFF)	mysqld_list_processes	Global_THD_manager::do_for_all_thd_copy
information_schema.processlist	fill_schema_processlist	Global_THD_manager::do_for_all_thd_copy
performance_schema.processlist	table_processlist::make_row	table_processlist::read_row_values
SHOW PROCESSLIST (option=ON)	table_processlist::make_row	table_processlist::read_row_values

Global_THD_manager::do_for_all_thd_copy は全体の1/8 ずつLOCK_thd_remove と LOCK_thd_list のmutexを取るのでその瞬間の新規接続とコネクション切断ができない
- https://github.com/mysql/mysql-server/blob/87307d4ddd88405117e3f1e51323836d57ab1f57/sql/mysqld_thd_manager.cc#L286-L307

MySQL 8.2.0で information_schema.processlist がdeprecatedになり、今までの SHOW PROCESSLIST も performance_schema.processlist からのSELECTに書き換えられる予定だというリリースノートがあった。

https://dev.mysql.com/doc/relnotes/mysql/8.2/en/news-8-2-0.html

前からオラクル青山のセミナーとかで「SHOW PROCESSLISTやinformation_schema.processlistはmutex取るからオススメしない、performance_schemaのthreadsやprocesslistがオススメ」って言われていた（現地で聞いた気がするのでたぶん2020以前だと思うんだけど）

MySQL 5.7.39とそれ以降から performance_schema_show_processlist=ONにすると SHOW PROCESSLIST は performance_schema.processlist からのSELECTに内部的にディスパッチされる。

(8.2.0でそもそもinformation_schema.processlistが非推奨でperformance_schema.processlistが当然のデフォルトになるようなので、この変数自体も8.2.0とそれ以降では非推奨)

ネイティブな ( デフォルトの performance_schema_show_processlist=OFF のままの) SHOW PROCESSLIST

(gdb) bt
+bt
#0  mysqld_list_processes(THD*, char const*, bool, bool) () at /home/yoku0825/mysql-8.0.35/sql/sql_show.cc:2961
#1  0x0000000000e41602 in Sql_cmd_show_processlist::execute_inner(THD*) () at /home/yoku0825/mysql-8.0.35/sql/sql_class.h:1276
#2  0x0000000000e200c4 in Sql_cmd_dml::execute(THD*) () at /home/yoku0825/mysql-8.0.35/sql/sql_select.cc:793
#3  0x0000000000dc63a1 in mysql_execute_command(THD*, bool) () at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:4719
#4  0x0000000000dca030 in dispatch_sql_command(THD*, Parser_state*) () at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:5368
#5  0x0000000000dcb39e in dispatch_command(THD*, COM_DATA const*, enum_server_command) () at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:2054
#6  0x0000000000dcd727 in do_command (thd=thd@entry=0x7fec74000d20) at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:1439
#7  0x0000000000f24500 in handle_connection (arg=arg@entry=0x6cc4830) at /home/yoku0825/mysql-8.0.35/sql/conn_handler/connection_handler_per_thread.cc:302
#8  0x00000000024f91b5 in pfs_spawn_thread (arg=0x6be2d20) at /home/yoku0825/mysql-8.0.35/storage/perfschema/pfs.cc:3042
#9  0x00007fecd4b28ea5 in start_thread (arg=0x7fecc0657700) at pthread_create.c:307
#10 0x00007fecd3142b0d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

SELECT * FROM information_schema.processlist

(gdb) bt
+bt
#0  fill_schema_processlist (thd=0x7fec74000d20, tables=0x7fec7400d630) at /home/yoku0825/mysql-8.0.35/sql/sql_show.cc:3212
#1  0x0000000000e38db5 in do_fill_information_schema_table(THD*, Table_ref*, Item*) () at /home/yoku0825/mysql-8.0.35/sql/sql_show.cc:4904
#2  0x0000000001305a4d in MaterializeInformationSchemaTableIterator::Init (this=0x7fec74020118) at /home/yoku0825/mysql-8.0.35/sql/iterators/row_iterator.h:227
#3  0x0000000000e9f65c in Query_expression::ExecuteIteratorQuery(THD*) () at /opt/rh/devtoolset-11/root/usr/include/c++/11/bits/unique_ptr.h:421
#4  0x0000000000e9fa7c in Query_expression::execute(THD*) () at /home/yoku0825/mysql-8.0.35/sql/sql_union.cc:1823
#5  0x0000000000e200c4 in Sql_cmd_dml::execute(THD*) () at /home/yoku0825/mysql-8.0.35/sql/sql_select.cc:793
#6  0x0000000000dc63a1 in mysql_execute_command(THD*, bool) () at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:4719
#7  0x0000000000dca030 in dispatch_sql_command(THD*, Parser_state*) () at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:5368
#8  0x0000000000dcb39e in dispatch_command(THD*, COM_DATA const*, enum_server_command) () at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:2054
#9  0x0000000000dcd727 in do_command (thd=thd@entry=0x7fec74000d20) at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:1439
#10 0x0000000000f24500 in handle_connection (arg=arg@entry=0x6cc4830) at /home/yoku0825/mysql-8.0.35/sql/conn_handler/connection_handler_per_thread.cc:302
#11 0x00000000024f91b5 in pfs_spawn_thread (arg=0x6be2d20) at /home/yoku0825/mysql-8.0.35/storage/perfschema/pfs.cc:3042
#12 0x00007fecd4b28ea5 in start_thread (arg=0x7fecc0657700) at pthread_create.c:307
#13 0x00007fecd3142b0d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

SELECT * FROM performance_schema.processlist

(gdb) bt
+bt
#0  table_processlist::make_row(PFS_thread*) () at /home/yoku0825/mysql-8.0.35/storage/perfschema/table_processlist.cc:157
#1  0x00000000024ef861 in rnd_next (buf=0x7fec740b9480 "\377", this=0x7fec740b7de0) at /home/yoku0825/mysql-8.0.35/storage/perfschema/ha_perfschema.cc:1722
#2  ha_perfschema::rnd_next (this=0x7fec740b7de0, buf=0x7fec740b9480 "\377") at /home/yoku0825/mysql-8.0.35/storage/perfschema/ha_perfschema.cc:1713
#3  0x0000000001040b2c in handler::ha_rnd_next (this=0x7fec740b7de0, buf=0x7fec740b9480 "\377") at /home/yoku0825/mysql-8.0.35/sql/handler.cc:2971
#4  0x000000000119467b in TableScanIterator::Read (this=0x7fec740a83f8) at /home/yoku0825/mysql-8.0.35/sql/iterators/row_iterator.h:245
#5  0x0000000000e9f6e3 in Query_expression::ExecuteIteratorQuery(THD*) () at /opt/rh/devtoolset-11/root/usr/include/c++/11/bits/unique_ptr.h:421
#6  0x0000000000e9fa7c in Query_expression::execute(THD*) () at /home/yoku0825/mysql-8.0.35/sql/sql_union.cc:1823
#7  0x0000000000e200c4 in Sql_cmd_dml::execute(THD*) () at /home/yoku0825/mysql-8.0.35/sql/sql_select.cc:793
#8  0x0000000000dc63a1 in mysql_execute_command(THD*, bool) () at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:4719
#9  0x0000000000dca030 in dispatch_sql_command(THD*, Parser_state*) () at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:5368
#10 0x0000000000dcb39e in dispatch_command(THD*, COM_DATA const*, enum_server_command) () at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:2054
#11 0x0000000000dcd727 in do_command (thd=thd@entry=0x7fec74000d20) at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:1439
#12 0x0000000000f24500 in handle_connection (arg=arg@entry=0x6cc4830) at /home/yoku0825/mysql-8.0.35/sql/conn_handler/connection_handler_per_thread.cc:302
#13 0x00000000024f91b5 in pfs_spawn_thread (arg=0x6be2d20) at /home/yoku0825/mysql-8.0.35/storage/perfschema/pfs.cc:3042
#14 0x00007fecd4b28ea5 in start_thread (arg=0x7fecc0657700) at pthread_create.c:307
#15 0x00007fecd3142b0d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

performance_schema_show_processlist=ON にした SHOW PROCESSLIST

(gdb) bt
+bt
#0  table_processlist::make_row(PFS_thread*) () at /home/yoku0825/mysql-8.0.35/storage/perfschema/table_processlist.cc:157
#1  0x00000000024ef861 in rnd_next (buf=0x7fec740b9480 "\377", this=0x7fec740b7de0) at /home/yoku0825/mysql-8.0.35/storage/perfschema/ha_perfschema.cc:1722
#2  ha_perfschema::rnd_next (this=0x7fec740b7de0, buf=0x7fec740b9480 "\377") at /home/yoku0825/mysql-8.0.35/storage/perfschema/ha_perfschema.cc:1713
#3  0x0000000001040b2c in handler::ha_rnd_next (this=0x7fec740b7de0, buf=0x7fec740b9480 "\377") at /home/yoku0825/mysql-8.0.35/sql/handler.cc:2971
#4  0x000000000119467b in TableScanIterator::Read (this=0x7fec740c39c0) at /home/yoku0825/mysql-8.0.35/sql/iterators/row_iterator.h:245
#5  0x0000000001309f12 in MaterializeIterator<DummyIteratorProfiler>::MaterializeQueryBlock (this=this@entry=0x7fec740c3aa0, query_block=
      Python Exception <class 'gdb.error'> There is no member or method named _M_head_impl.:
@0x7fec740c3a50: {subquery_iterator = , select_number = 2, join = 0x7fec740c1fe0, disable_deduplication_by_hash_field = false, copy_items = true, m_total_operands = 0, m_operand_idx = 0, m_first_distinct = 0, temp_table_param = 0x7fec740c2150, is_recursive_reference = false, recursive_reader = 0x0}, stored_rows=stored_rows@entry=0x7fecc0654880)
    at /opt/rh/devtoolset-11/root/usr/include/c++/11/bits/unique_ptr.h:421
#6  0x000000000130af13 in MaterializeIterator<DummyIteratorProfiler>::Init (this=0x7fec740c3aa0) at /home/yoku0825/mysql-8.0.35/sql/iterators/composite_iterators.cc:928
#7  0x0000000000e9f65c in Query_expression::ExecuteIteratorQuery(THD*) () at /opt/rh/devtoolset-11/root/usr/include/c++/11/bits/unique_ptr.h:421
#8  0x0000000000e9fa7c in Query_expression::execute(THD*) () at /home/yoku0825/mysql-8.0.35/sql/sql_union.cc:1823
#9  0x0000000000e200c4 in Sql_cmd_dml::execute(THD*) () at /home/yoku0825/mysql-8.0.35/sql/sql_select.cc:793
#10 0x0000000000dc63a1 in mysql_execute_command(THD*, bool) () at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:4719
#11 0x0000000000dca030 in dispatch_sql_command(THD*, Parser_state*) () at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:5368
#12 0x0000000000dcb39e in dispatch_command(THD*, COM_DATA const*, enum_server_command) () at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:2054
#13 0x0000000000dcd727 in do_command (thd=thd@entry=0x7fec74000d20) at /home/yoku0825/mysql-8.0.35/sql/sql_parse.cc:1439
#14 0x0000000000f24500 in handle_connection (arg=arg@entry=0x6cc4830) at /home/yoku0825/mysql-8.0.35/sql/conn_handler/connection_handler_per_thread.cc:302
#15 0x00000000024f91b5 in pfs_spawn_thread (arg=0x6be2d20) at /home/yoku0825/mysql-8.0.35/storage/perfschema/pfs.cc:3042
#16 0x00007fecd4b28ea5 in start_thread (arg=0x7fecc0657700) at pthread_create.c:307
#17 0x00007fecd3142b0d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

日々の覚書

GA

2023/11/08

SHOW PROCESSLISTとinformation_schema.processlistとperformance_schema.processlistと

TL;DR

0 件のコメント :

コメントを投稿