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:
Category:MySQL Server: Information schema Severity:S4 (Feature request)
Version:All OS:Any
Assigned to: Matthew Lord CPU Architecture:Any

[15 Mar 2011 14:27] Stephen Jackson
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:-


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

$SQL = "select * from user";

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



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.
[15 Mar 2011 14:49] Valeriy Kravchuk
Thank you for the feature request.
[14 Apr 2011 20:21] Stephen Jackson
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..?
[15 Apr 2011 3:11] Valeriy Kravchuk
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.
[15 Apr 2011 10:25] Paul Willis
+1 For this feature.

Debugging sleeping connections is something I've spent some time on with our MySQL servers. This would help a lot
[9 Sep 2011 23:40] Timothy Graupmann
+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.
[13 Feb 2012 9:37] Pablo Martinez perez
+1 to this feature

it's very useful.
[26 Jun 2013 14:54] Matthew Lord
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
        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!