Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand and master the negative show processlist time number of mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Show processlist

Function interface: mysqld_list_processes

Stack frame:

# 0 mysqld_list_processes (thd=0x7ffedc008250, user=0x0, verbose=false) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_show.cc:2370#1 0x00000000015ccaab in mysql_execute_command (thd=0x7ffedc008250, first_level=true) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:3789#2 0x00000000015d2fde in mysql_parse (thd=0x7ffedc008250 Parser_state=0x7fffec5bd600) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901#3 0x00000000015c6b72 in dispatch_command (thd=0x7ffedc008250, com_data=0x7fffec5bdd70 Command=COM_QUERY) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490#4 0x00000000015c58ff in do_command (thd=0x7ffedc008250) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021#5 0x000000000170e578 in handle_connection (arg=0x5f4fe40) at / mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312#6 0x0000000001945538 in pfs _ spawn_thread (arg=0x6809be0) at / mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190#7 0x00007ffff7bcfaa1 in start_thread () from / lib64/libpthread.so.0#8 0x00007ffff6b37c4d in clone () from / lib64/libc.so.6

In fact, this time is easy to understand as follows:

Server time-time when the command was initiated

The slave SQL thread is the server time-Event header time (from the master library)

So it is possible to have negative numbers. The calculation time percona here is different from the official version as follows:

Official 5.6.25. 5.7.23 (negative numbers may occur):

If (thd_info- > start_time) protocol- > store_long ((longlong) (now-thd_info- > start_time))

Percona 5.7.22 (avoid negative numbers and replace them with 0):

If (thd_info- > start_time) {protocol- > store_long ((thd_info- > start_time > now)? 0: (longlong) (now-thd_info- > start_time);}

You can see that percona makes a logical change to the calculation time, and the negative number is displayed as 0. The test for negative numbers is as follows:

2. Select * from information_schema.processlist

Function interface: fill_schema_processlist

Stack frame:

# 0 Field_long::store (this=0x7ffedc0164c8, nr=-1783905, unsigned_val=false) at / mysqldata/percona-server-locks-detail-5.7.22/sql/field.cc:4121#1 0x000000000165062d in Fill_process_list::operator () (this=0x7fffec5bb5f0, inspect_thd=0x67be360) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_show.cc:2591#2 0x0000000000eef320 in Do_THD::operator () (this=0x7fffec5ba580 Thd=0x67be360) at / mysqldata/percona-server-locks-detail-5.7.22/sql/mysqld_thd_manager.cc:46#3 0x0000000000eefa0f in std::for_each (_ _ first=0x7fffec5ba5d0, _ _ last=0x7fffec5ba5e8, _ _ fags...) At / usr/lib/gcc/x86_64-redhat-linux/4.4.7/../include/c++/4.4.7/bits/stl_algo.h:4200#4 0x0000000000eeeecc in Global_THD_manager::do_for_all_thd_copy (this=0x3003340, func=0x7fffec5bb5f0) at / mysqldata/percona-server-locks-detail-5.7.22/sql/mysqld_thd_manager.cc:262#5 0x0000000001637280 in fill_schema_processlist (thd=0x7ffedc008250, tables=0x7ffedc0016a8) Cond=0x0) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_show.cc:2608#6 0x000000000164ce11 in do_fill_table (thd=0x7ffedc008250, table_list=0x7ffedc0016a8, qep_tab=0x7ffedc015d40) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_show.cc:8791#7 0x000000000164d31b in get_schema_tables_result (join=0x7ffedc015660 Executed_place=PROCESSED_BY_JOIN_EXEC) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_show.cc:8921#8 0x0000000001621761 in JOIN::prepare_result (this=0x7ffedc015660) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:909#9 0x000000000157e699 in JOIN::exec (this=0x7ffedc015660) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:124#10 0x0000000001620327 in handle_query (thd=0x7ffedc008250, lex=0x7ffedc00a880, result=0x7ffedc0025c8 Added_options=0, removed_options=0) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:185#11 0x00000000015d1f77 in execute_sqlcom_select (thd=0x7ffedc008250, all_tables=0x7ffedc0016a8) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5445#12 0x00000000015ca380 in mysql_execute_command (thd=0x7ffedc008250 First_level=true) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:2939#13 0x00000000015d2fde in mysql_parse (thd=0x7ffedc008250, parser_state=0x7fffec5bd600) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901#14 0x00000000015c6b72 in dispatch_command (thd=0x7ffedc008250, com_data=0x7fffec5bdd70) Command=COM_QUERY) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490#15 0x00000000015c58ff in do_command (thd=0x7ffedc008250) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021#16 0x000000000170e578 in handle_connection (arg=0x5f4fe40) at / mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312#17 0x0000000001945538 in pfs _ spawn_thread (arg=0x6809be0) at / mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190#18 0x00007ffff7bcfaa1 in start_thread () from / lib64/libpthread.so.0#19 0x00007ffff6b37c4d in clone () from / lib64/libc.so.6

The simple record is as follows:

Fill_process_list class 5.7

Fill_schema_processlist function 5.6

About the Time coming from the following treatment, it is also possible for something like-1 to appear.

Type_conversion_status Field_long::store (longlong nr, bool unsigned_val) {ASSERT_COLUMN_MARKED_FOR_WRITE; type_conversion_status error= TYPE_OK; int32 res; if (unsigned_flag) {if (nr)

= (1LL (longlong) INT_MAX32) {res= (int32) INT_MAX32; error= TYPE_WARN_OUT_OF_RANGE;} else res= (int32) nr;} if (error) set_warning (Sql_condition::SL_WARNING, ER_WARN_DATA_OUT_OF_RANGE, 1); # ifdef WORDS_BIGENDIAN if (table- > s-> db_low_byte_first) {int4store (ptr,res) } else#endif longstore (ptr,res); return error;} III. Testing of different phenomena

We see here to prove the above statement. They come from different sources.

This is the end of the content of "how to understand and master the negative show processlist time numbers of mysql". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.