怎么理解并掌握mysql的show processlist time负数
本篇内容介绍了“怎么理解并掌握mysql的show processlist time负数”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
一、show processlist函数接口:mysqld_list_processes
栈帧:
#0mysqld_list_processes(thd=0x7ffedc008250,user=0x0,verbose=false)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_show.cc:2370#10x00000000015ccaabinmysql_execute_command(thd=0x7ffedc008250,first_level=true)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:3789#20x00000000015d2fdeinmysql_parse(thd=0x7ffedc008250,parser_state=0x7fffec5bd600)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901#30x00000000015c6b72indispatch_command(thd=0x7ffedc008250,com_data=0x7fffec5bdd70,command=COM_QUERY)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490#40x00000000015c58ffindo_command(thd=0x7ffedc008250)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021#50x000000000170e578inhandle_connection(arg=0x5f4fe40)at/mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312#60x0000000001945538inpfs_spawn_thread(arg=0x6809be0)at/mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190#70x00007ffff7bcfaa1instart_thread()from/lib64/libpthread.so.0#80x00007ffff6b37c4dinclone()from/lib64/libc.so.6
其实这个时间很好理解如下:
服务器时间 - 命令发起的时间
从库SQL线程则是 服务器时间 - Event header的时间(来自主库)
因此出现负数是可能的。这里计算时间percona 和 官方版本 不同如下:
官方 5.6.25 .5.7.23 都是(可能出现负数):
if(thd_info->start_time)protocol->store_long((longlong)(now-thd_info->start_time));
percona 5.7.22(避免出现负数用0代替):
if(thd_info->start_time){protocol->store_long((thd_info->start_time>now)?0:(longlong)(now-thd_info->start_time));}
可以看到percona 对计算时间做了逻辑变化,负数会显示为0。关于负数的测试如下:
函数接口:fill_schema_processlist
栈帧:
#0Field_long::store(this=0x7ffedc0164c8,nr=-1783905,unsigned_val=false)at/mysqldata/percona-server-locks-detail-5.7.22/sql/field.cc:4121#10x000000000165062dinFill_process_list::operator()(this=0x7fffec5bb5f0,inspect_thd=0x67be360)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_show.cc:2591#20x0000000000eef320inDo_THD::operator()(this=0x7fffec5ba580,thd=0x67be360)at/mysqldata/percona-server-locks-detail-5.7.22/sql/mysqld_thd_manager.cc:46#30x0000000000eefa0finstd::for_each<THD**,Do_THD>(__first=0x7fffec5ba5d0,__last=0x7fffec5ba5e8,__f=...)at/usr/lib/gcc/x86_64-redhat-linux/4.4.7/../../../../include/c++/4.4.7/bits/stl_algo.h:4200#40x0000000000eeeeccinGlobal_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#50x0000000001637280infill_schema_processlist(thd=0x7ffedc008250,tables=0x7ffedc0016a8,cond=0x0)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_show.cc:2608#60x000000000164ce11indo_fill_table(thd=0x7ffedc008250,table_list=0x7ffedc0016a8,qep_tab=0x7ffedc015d40)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_show.cc:8791#70x000000000164d31binget_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#80x0000000001621761inJOIN::prepare_result(this=0x7ffedc015660)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:909#90x000000000157e699inJOIN::exec(this=0x7ffedc015660)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:124#100x0000000001620327inhandle_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#110x00000000015d1f77inexecute_sqlcom_select(thd=0x7ffedc008250,all_tables=0x7ffedc0016a8)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5445#120x00000000015ca380inmysql_execute_command(thd=0x7ffedc008250,first_level=true)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:2939#130x00000000015d2fdeinmysql_parse(thd=0x7ffedc008250,parser_state=0x7fffec5bd600)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901#140x00000000015c6b72indispatch_command(thd=0x7ffedc008250,com_data=0x7fffec5bdd70,command=COM_QUERY)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490#150x00000000015c58ffindo_command(thd=0x7ffedc008250)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021#160x000000000170e578inhandle_connection(arg=0x5f4fe40)at/mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312#170x0000000001945538inpfs_spawn_thread(arg=0x6809be0)at/mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190#180x00007ffff7bcfaa1instart_thread()from/lib64/libpthread.so.0#190x00007ffff6b37c4dinclone()from/lib64/libc.so.6
简单记录如下:
Fill_process_list类 5.7
fill_schema_processlist 函数 5.6
关于其中的Time来自如下处理,关于好像 -1 也是可能处出现的。
type_conversion_statusField_long::store(longlongnr,boolunsigned_val){ASSERT_COLUMN_MARKED_FOR_WRITE;type_conversion_statuserror=TYPE_OK;int32res;if(unsigned_flag){if(nr<0&&!unsigned_val){res=0;error=TYPE_WARN_OUT_OF_RANGE;}elseif((ulonglong)nr>=(1LL<<32)){res=(int32)(uint32)~0L;//表达式-1error=TYPE_WARN_OUT_OF_RANGE;}elseres=(int32)(uint32)nr;}else{if(nr<0&&unsigned_val){nr=((longlong)INT_MAX32)+1;//Generateoverflowerror=TYPE_WARN_OUT_OF_RANGE;}if(nr<(longlong)INT_MIN32){res=(int32)INT_MIN32;error=TYPE_WARN_OUT_OF_RANGE;}elseif(nr>(longlong)INT_MAX32){res=(int32)INT_MAX32;error=TYPE_WARN_OUT_OF_RANGE;}elseres=(int32)nr;}if(error)set_warning(Sql_condition::SL_WARNING,ER_WARN_DATA_OUT_OF_RANGE,1);#ifdefWORDS_BIGENDIANif(table->s->db_low_byte_first){int4store(ptr,res);}else#endiflongstore(ptr,res);returnerror;}三、不同现象的测试
我们看到这里证明了上面的说法。他们来源不同。
“怎么理解并掌握mysql的show processlist time负数”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。