MySQL Bugs: #60476: Processlist Provides Limited Information for connections in sleep state

Bug #60476	Processlist Provides Limited Information for connections in sleep state
Submitted:	15 Mar 2011 14:27	Modified:	26 Jun 2013 14:54
Reporter:	Stephen Jackson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S4 (Feature request)
Version:	All	OS:	Any
Assigned to:	Matthew Lord	CPU Architecture:	Any

Description:
The processlist provides very little information for certain thread states (such as sleep state), which makes it very difficult to debug problematic client code.

Adding tracking info in the form of an SQL comment / script name / linenumber etc,  is useless if the INFO column is NULL (as it is in sleep state)

How to repeat:
A simple script that opens a connection and queries the user table:-

<?php

$dbase = new mysqli("localhost", "root", "", "mysql");

$SQL = "select * from user";

$result = $dbase->query($SQL) or die ($dbase->error);

sleep(30);

?>

During this script's execution, open a CLI connection as root and do a "show processlist;"

Note the "NULL" in INFO column for the sleeping connection.

Suggested fix:
Please add "Last Query" to the processlist table. This should be set as soon as the parser is done and not reset until a new query is executed on that connection.

Thank you for the feature request.

Is there some kind of review process that decides which features will be considered for inclusion?...and how much visibility/influence do we get into this process..?

Surely there is a review process, with multiple stages and more than one decision maker. But in case of feature request you have mostly to rely on changes in Status and public comments in the bug report (if any), if you want to track the progress.

You can influence the process to some extent if you submit patch that implements the feature you need. Comments from other users asking for the same feature can sometimes help to increase its internal priority and get it implemented sooner.

+1 For this feature.

Debugging sleeping connections is something I've spent some time on with our MySQL servers. This would help a lot

+1 for this feature request.

I'm trying to find out why something is eating mysql connections, and if this information was available I would know by now.

+1 to this feature

it's very useful.

Hi Stephen,

Thank you for the feature request, and for helping to make MySQL even better!

I'm closing this for now, as this feature has been implemented in 5.6 via performance_schema.

Here's an example VIEW using P_S to get "new and improved" show processlist like information:

mysql> show create view ps_thread_status\G
*************************** 1. row ***************************
                View: ps_thread_status
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ps_thread_status` AS select `pps`.`THREAD_ID` AS `thd_id`,`pps`.`PROCESSLIST_ID` AS `conn_id`,if((`pps`.`NAME` = 'thread/sql/one_connection'),concat(`pps`.`PROCESSLIST_USER`,'@',`pps`.`PROCESSLIST_HOST`),replace(`pps`.`NAME`,'thread/','')) AS `user`,`pps`.`PROCESSLIST_DB` AS `db`,`pps`.`PROCESSLIST_COMMAND` AS `command`,`pps`.`PROCESSLIST_STATE` AS `state`,`pps`.`PROCESSLIST_TIME` AS `time`,`pps`.`PROCESSLIST_INFO` AS `current_statement`,if((`esc`.`TIMER_WAIT` is not null),`esc`.`SQL_TEXT`,NULL) AS `last_statement`,if((`esc`.`TIMER_WAIT` is not null),`esc`.`TIMER_WAIT`,NULL) AS `last_statement_latency`,`esc`.`LOCK_TIME` AS `lock_latency`,`esc`.`ROWS_EXAMINED` AS `rows_examined`,`esc`.`ROWS_SENT` AS `rows_sent`,`esc`.`ROWS_AFFECTED` AS `rows_affected`,`esc`.`CREATED_TMP_TABLES` AS `tmp_tables`,`esc`.`CREATED_TMP_DISK_TABLES` AS `tmp_disk_tables`,if(((`esc`.`NO_GOOD_INDEX_USED` > 0) or (`esc`.`NO_INDEX_USED` > 0)),'YES','NO') AS `full_scan`,`ewc`.`EVENT_NAME` AS `last_wait`,if((isnull(`ewc`.`TIMER_WAIT`) and (`ewc`.`EVENT_NAME` is not null)),'Still Waiting',`ewc`.`TIMER_WAIT`) AS `last_wait_latency`,`ewc`.`SOURCE` AS `source` from ((`performance_schema`.`threads` `pps` left join `performance_schema`.`events_waits_current` `ewc` on((`pps`.`THREAD_ID` = `ewc`.`THREAD_ID`))) left join `performance_schema`.`events_statements_current` `esc` on((`pps`.`THREAD_ID` = `esc`.`THREAD_ID`))) order by `pps`.`PROCESSLIST_TIME` desc,`last_wait_latency` desc
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql> select * from ps_thread_status\G
*************************** 1. row ***************************
                thd_id: 1
               conn_id: NULL
                  user: sql/main
                    db: NULL
               command: NULL
                 state: System lock
                  time: 89723
     current_statement: INTERNAL DDL LOG RECOVER IN PROGRESS
        last_statement: NULL
last_statement_latency: NULL
          lock_latency: NULL
         rows_examined: NULL
             rows_sent: NULL
         rows_affected: NULL
            tmp_tables: NULL
       tmp_disk_tables: NULL
             full_scan: NO
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
*************************** 2. row ***************************
                thd_id: 23
               conn_id: 4
                  user: root@localhost
                    db: test
               command: Sleep
                 state: cleaning up
                  time: 91
     current_statement: NULL
        last_statement: select * from ps_thread_status limit 3
last_statement_latency: 4108829000
          lock_latency: 699000000
         rows_examined: 41
             rows_sent: 3
         rows_affected: 0
            tmp_tables: 1
       tmp_disk_tables: 1
             full_scan: YES
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
....

If you have any additional comments, please let me know.

Thanks again!