MySQL Bugs: #71289: PERFORMANCE_SCHEMA.THREADS table misses port in PROCESSLIST

Bug #71289	PERFORMANCE_SCHEMA.THREADS table misses port in PROCESSLIST_HOST column
Submitted:	4 Jan 2014 15:21	Modified:	10 Dec 2015 22:34
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6.15	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	performance_schema, threads

Description:
Manual mentions many times that performance_schema.threads table is a better way to check process information, like in http://dev.mysql.com/doc/refman/5.6/en/processlist-table.html:

"Process information is also available from the performance_schema.threads table. However, access to threads does not require a mutex and has minimal impact on server performance. INFORMATION_SCHEMA.PROCESSLIST and SHOW PROCESSLIST have negative performance consequences ..."

The table obviously has columns that should provide the same information as corresponding columns in the SHOW PROCESSLIST output or INFORMATION_SCHEMA.PROCESSLIST table. But one of this columns, PROCESSLIST_HOST, does NOT contain client port for TCP/IP connections.

How to repeat:
mysql> select * from information_schema.processlist where id=connection_id()\G
*************************** 1. row ***************************
     ID: 4
   USER: root
   HOST: localhost:60416
     DB: test
COMMAND: Query
   TIME: 0
  STATE: executing
   INFO: select * from information_schema.processlist where id=connection_id()
1 row in set (0.01 sec)

mysql> show full processlist\G
*************************** 1. row ***************************
     Id: 4
   User: root
   Host: localhost:60416
     db: test
Command: Query
   Time: 0
  State: init
   Info: show full processlist
1 row in set (0.00 sec)

In both outputs we see port 60416. Now compare to the following: 

mysql> select * from performance_schema.threads where processlist_id=connection_
id()\G
*************************** 1. row ***************************
          THREAD_ID: 25
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 4
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: Sending data
   PROCESSLIST_INFO: select * from performance_schema.threads where processlist_
id=connection_id()
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
1 row in set (0.00 sec)

Here there is no port in the PROCESSLIST_HOST column. This is inconsistent and prevents use of this great new source of information in some cases. 

Suggested fix:
Add client port to the value of the PROCESSLIST_HOST column and/or document this limitation/difference in the meantime.

There's another table for it!
http://dev.mysql.com/doc/refman/5.6/en/socket-instances-table.html

mysql> desc setup_instruments;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| NAME    | varchar(128)     | NO   |     | NULL    |       |
| ENABLED | enum('YES','NO') | NO   |     | NULL    |       |
| TIMED   | enum('YES','NO') | NO   |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from setup_instruments where name like '%socket%';
+----------------------------------------+---------+-------+
| NAME                                   | ENABLED | TIMED |
+----------------------------------------+---------+-------+
| wait/io/socket/sql/server_tcpip_socket | NO      | NO    |
| wait/io/socket/sql/server_unix_socket  | NO      | NO    |
| wait/io/socket/sql/client_connection   | NO      | NO    |
+----------------------------------------+---------+-------+
3 rows in set (0.00 sec)

mysql> update setup_instruments set enabled='YES' where name like '%socket%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> \r
Connection id:    2
Current database: performance_schema

mysql> select * from socket_instances;
+--------------------------------------+-----------------------+-----------+-----------+------------------+-------+--------+
| EVENT_NAME                           | OBJECT_INSTANCE_BEGIN | THREAD_ID | SOCKET_ID | IP               | PORT  | STATE  |
+--------------------------------------+-----------------------+-----------+-----------+------------------+-------+--------+
| wait/io/socket/sql/client_connection |              33034816 |        22 |      2264 | ::ffff:127.0.0.1 | 48937 | ACTIVE |
+--------------------------------------+-----------------------+-----------+-----------+------------------+-------+--------+
1 row in set (0.00 sec)

That's great, but this instrument is not enabled by default. 

Also, in any case, manual should explain how PROCESSLIST_HOST is different from Host column in other sources of processlist information and how to get this missing information.

Hello Valeriy,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

Using the PROCESSLIST_HOST column to report localhost:60416 is just wrong, so this is not going to change.

In my opinion, there are two distinct issues here.

1)

PROCESSLIST_HOST in table performance_schema.threads is not equivalent to HOST in PROCESSLIST, and this needs to be clarified in the documentation.

Using this bug report, bug#71289, to fix the doc

2)

The PORT information is missing from table performance_schema.threads.

This is a valid feature request, recorded as bug#71305 PERFORMANCE_SCHEMA.THREADS table, add a PORT column

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

Updated http://dev.mysql.com/doc/refman/5.6/en/threads-table.html
with instructions for getting port information (based on Shane's information).