show processlist time負數的初探
本文主要解釋一下下面兩個語句的相應介面和時間計算方式。
- select * from information_schema.processlist;
- show processlist
歡迎關注我的《深入理解MySQL主從原理 32講 》,如下:
一、show processlist
函式介面:mysqld_list_processes
棧幀:
#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
其實這個時間很好理解如下:
- 伺服器時間 - 命令發起的時間
- 從庫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。關於負數的測試如下:
二、select * from information_schema.processlist;
函式介面:fill_schema_processlist
棧幀:
#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<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
#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
簡單記錄如下:
- Fill_process_list類 5.7
- fill_schema_processlist 函式 5.6
關於其中的Time來自如下處理,關於好像 -1 也是可能處出現的。
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 < 0 && !unsigned_val)
{
res=0;
error= TYPE_WARN_OUT_OF_RANGE;
}
else if ((ulonglong) nr >= (1LL << 32))
{
res=(int32) (uint32) ~0L; //表示式-1
error= TYPE_WARN_OUT_OF_RANGE;
}
else
res=(int32) (uint32) nr;
}
else
{
if (nr < 0 && unsigned_val)
{
nr= ((longlong) INT_MAX32) + 1; // Generate overflow
error= TYPE_WARN_OUT_OF_RANGE;
}
if (nr < (longlong) INT_MIN32)
{
res=(int32) INT_MIN32;
error= TYPE_WARN_OUT_OF_RANGE;
}
else if (nr > (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;
}
三、不同現象的測試
我們看到這裡證明了上面的說法。他們來源不同。
作者微信:gp_22389860
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7728585/viewspace-2653550/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL:show processlist Time負數的思考MySql
- mysql的show processlistMySql
- mysql show processlist stateMySql
- 轉載:mysql的show processlistMySql
- MySQL 之 show processlist 神器MySql
- mysql show processlist 詳解MySql
- MySQL show processlist故障處理MySql
- Mysql show processlist 排查問題MySql
- mysql show processlist命令詳解MySql
- MySQL 中 show full processlist 詳解MySql
- 故障分析 | show processlist 引起的效能問題
- MySQL8 show processlist 最佳化MySql
- MySQL show processlist命令詳解及檢視當前連線數MySql
- MySQL調優使用者監控之show processlistMySql
- 12、MySQL Case-show processlist 狀態一直處於Sending to clientMySqlclient
- mysql processlistMySql
- show sga和show parameter sga的區別
- 批量kill mysql processlist程式MySql
- 負數補碼(16進位制轉10進位制的負數)
- 【c語言】將正數變成對應的負數,將負數變成對應的正數C語言
- 負數的二進位制數問題
- mysql processlist詳細說明MySql
- TimesTen支援的連線數
- Oracle將數值轉化成負數的寫法Oracle
- 【MySQL】SHOW WARNINGS和SHOW ERRORS的作用是什麼?MySqlError
- JavaScript 負數轉換為正數JavaScript
- JavaScript判斷數字正負數JavaScript
- MySQL中的show命令MySql
- JavaScript show()JavaScript
- jQuery show()jQuery
- Nginx作為動靜分離、快取與負載均衡初探Nginx快取負載
- Python中負數變成正數常用的方法!Python
- 負數的二進位制表示方法
- Library Hit %: -986.64 負數的問題
- 初探函數語言程式設計函數程式設計
- 函數語言程式設計初探函數程式設計
- MySQL的show engine innodb statusMySql
- jQuery的addClass,children,showjQuery