Description:
I wanted to find out, when a MySQL connection was established (timestamp). But I did not find these information neither in the performance schema nor elsewhere...
I also tried to match the timestamp via O/S means but they do not provide this information.
How to repeat:
SELECT thread_id, processlist_user, thread_os_id
FROM performance_schema.threads
WHERE processlist_user = 'app' OR thread_id = 1
LIMIT 3;
+-----------+------------------+--------------+
| thread_id | processlist_user | thread_os_id |
+-----------+------------------+--------------+
| 1 | NULL | 2055 |
| 760 | app | 170388 |
+-----------+------------------+--------------+
SELECT so.event_name, so.thread_id, so.socket_id, so.ip, so.port, so.state
, th.processlist_user AS user, th.thread_os_id
FROM performance_schema.socket_instances AS so
JOIN performance_schema.threads AS th ON th.thread_id = so.thread_id
WHERE so.event_name LIKE '%client_connection%'
;
+--------------------------------------+-----------+-----------+------------------+-------+--------+------+--------------+
| event_name | thread_id | socket_id | ip | port | state | user | thread_os_id |
+--------------------------------------+-----------+-----------+------------------+-------+--------+------+--------------+
| wait/io/socket/sql/client_connection | 753 | 39 | | 0 | ACTIVE | root | 6114 |
| wait/io/socket/sql/client_connection | 760 | 40 | ::ffff:127.0.0.1 | 56214 | IDLE | app | 170388 |
+--------------------------------------+-----------+-----------+------------------+-------+--------+------+--------------+
system lsof -p 2055 | grep -e IP -e unix
mysqld 2055 mysql 24u IPv6 28909 0t0 TCP *:33060 (LISTEN)
mysqld 2055 mysql 25u unix 0x0000000000000000 0t0 28237 /tmp/mysqlx.sock type=STREAM
mysqld 2055 mysql 26u IPv6 28911 0t0 TCP *:3326 (LISTEN)
mysqld 2055 mysql 28u unix 0x0000000000000000 0t0 28912 /var/run/mysqld/mysql-3326.sock type=STREAM
mysqld 2055 mysql 39u unix 0x0000000000000000 0t0 960023 /var/run/mysqld/mysql-3326.sock type=STREAM
mysqld 2055 mysql 40u IPv6 925118 0t0 TCP localhost:3326->localhost:56214 (ESTABLISHED)
system ls -la /proc/2055/fd | grep socket
lrwx------ 1 mysql mysql 64 May 1 14:57 24 -> socket:[28909]
lrwx------ 1 mysql mysql 64 May 1 14:57 25 -> socket:[28237]
lrwx------ 1 mysql mysql 64 May 1 14:57 26 -> socket:[28911]
lrwx------ 1 mysql mysql 64 May 1 14:57 28 -> socket:[28912]
lrwx------ 1 mysql mysql 64 May 1 17:15 40 -> socket:[925118]
system stat /proc/2055/fd/40
File: 40 -> socket:[925118]
Size: 64 Blocks: 0 IO Block: 1024 symbolic link
Device: 17h/23d Inode: 921492 Links: 1
Access: (0700/lrwx------) Uid: ( 1001/ mysql) Gid: ( 1001/ mysql)
Access: 2023-05-01 17:21:22.407168685 +0200
Modify: 2023-05-01 17:15:04.220973687 +0200
Change: 2023-05-01 17:15:04.220973687 +0200
Birth: -
-> This is wrong!
Suggested fix:
It would be cool to have this information in the P_S threads or socket_instances view.
Description: I wanted to find out, when a MySQL connection was established (timestamp). But I did not find these information neither in the performance schema nor elsewhere... I also tried to match the timestamp via O/S means but they do not provide this information. How to repeat: SELECT thread_id, processlist_user, thread_os_id FROM performance_schema.threads WHERE processlist_user = 'app' OR thread_id = 1 LIMIT 3; +-----------+------------------+--------------+ | thread_id | processlist_user | thread_os_id | +-----------+------------------+--------------+ | 1 | NULL | 2055 | | 760 | app | 170388 | +-----------+------------------+--------------+ SELECT so.event_name, so.thread_id, so.socket_id, so.ip, so.port, so.state , th.processlist_user AS user, th.thread_os_id FROM performance_schema.socket_instances AS so JOIN performance_schema.threads AS th ON th.thread_id = so.thread_id WHERE so.event_name LIKE '%client_connection%' ; +--------------------------------------+-----------+-----------+------------------+-------+--------+------+--------------+ | event_name | thread_id | socket_id | ip | port | state | user | thread_os_id | +--------------------------------------+-----------+-----------+------------------+-------+--------+------+--------------+ | wait/io/socket/sql/client_connection | 753 | 39 | | 0 | ACTIVE | root | 6114 | | wait/io/socket/sql/client_connection | 760 | 40 | ::ffff:127.0.0.1 | 56214 | IDLE | app | 170388 | +--------------------------------------+-----------+-----------+------------------+-------+--------+------+--------------+ system lsof -p 2055 | grep -e IP -e unix mysqld 2055 mysql 24u IPv6 28909 0t0 TCP *:33060 (LISTEN) mysqld 2055 mysql 25u unix 0x0000000000000000 0t0 28237 /tmp/mysqlx.sock type=STREAM mysqld 2055 mysql 26u IPv6 28911 0t0 TCP *:3326 (LISTEN) mysqld 2055 mysql 28u unix 0x0000000000000000 0t0 28912 /var/run/mysqld/mysql-3326.sock type=STREAM mysqld 2055 mysql 39u unix 0x0000000000000000 0t0 960023 /var/run/mysqld/mysql-3326.sock type=STREAM mysqld 2055 mysql 40u IPv6 925118 0t0 TCP localhost:3326->localhost:56214 (ESTABLISHED) system ls -la /proc/2055/fd | grep socket lrwx------ 1 mysql mysql 64 May 1 14:57 24 -> socket:[28909] lrwx------ 1 mysql mysql 64 May 1 14:57 25 -> socket:[28237] lrwx------ 1 mysql mysql 64 May 1 14:57 26 -> socket:[28911] lrwx------ 1 mysql mysql 64 May 1 14:57 28 -> socket:[28912] lrwx------ 1 mysql mysql 64 May 1 17:15 40 -> socket:[925118] system stat /proc/2055/fd/40 File: 40 -> socket:[925118] Size: 64 Blocks: 0 IO Block: 1024 symbolic link Device: 17h/23d Inode: 921492 Links: 1 Access: (0700/lrwx------) Uid: ( 1001/ mysql) Gid: ( 1001/ mysql) Access: 2023-05-01 17:21:22.407168685 +0200 Modify: 2023-05-01 17:15:04.220973687 +0200 Change: 2023-05-01 17:15:04.220973687 +0200 Birth: - -> This is wrong! Suggested fix: It would be cool to have this information in the P_S threads or socket_instances view.