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

[4 Jan 2014 15:21] Valeriy Kravchuk
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.
[4 Jan 2014 15:48] MySQL Verification Team
There's another table for it!
http://dev.mysql.com/doc/refman/5.6/en/socket-instances-table.html
[4 Jan 2014 15:50] MySQL Verification Team
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)
[4 Jan 2014 17:11] Valeriy Kravchuk
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.
[6 Jan 2014 5:47] MySQL Verification Team
Hello Valeriy,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[6 Jan 2014 9:14] Marc ALFF
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
[10 Dec 2015 22:34] Paul DuBois
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).