1
# Take a snapshot of SOCKET_INSTANCES
2
CREATE TEMPORARY TABLE my_socket_instances AS
3
SELECT * FROM performance_schema.socket_instances;
4
# Get thread id of the default connection
5
SELECT THREAD_ID INTO @thread_id
6
FROM performance_schema.threads
7
WHERE PROCESSLIST_ID = CONNECTION_ID();
8
# Establish local TCP/IP connection (con1,localhost,root,,test,,)
9
# Store the thread id of connection 1 (tcp/ip)
10
SELECT THREAD_ID INTO @thread_id
11
FROM performance_schema.threads
12
WHERE PROCESSLIST_ID = CONNECTION_ID();
13
# Store the port of connection 1 (tcp/ip)
14
SELECT PORT INTO @port
15
FROM performance_schema.socket_instances
16
WHERE THREAD_ID = @thread_id;
17
# Switch to connection default
18
# Establish second local TCP/IP connection (con1,localhost,root,,test,,)
19
# Store the thread_id of connection 2 (tcp/ip)
20
SELECT THREAD_ID INTO @thread_id
21
FROM performance_schema.threads
22
WHERE PROCESSLIST_ID = CONNECTION_ID();
23
# Store the port of connection 2 (tcp/ip)
24
SELECT PORT INTO @port
25
FROM performance_schema.socket_instances
26
WHERE THREAD_ID = @thread_id;
27
# Switch to connection default
28
# Establish local unix domain connection (con3,localhost,root,,test,,)
29
# Store the thread id of connection 3 (unix domain)
30
SELECT THREAD_ID INTO @thread_id
31
FROM performance_schema.threads
32
WHERE PROCESSLIST_ID = CONNECTION_ID();
33
# Store the port of connection 3 (unix domain)
34
SELECT PORT INTO @port
35
FROM performance_schema.socket_instances
36
WHERE THREAD_ID = @thread_id;
37
# Switch to connection default
38
# EVENT_NAME is the "wait/io/socket/*" instrument identifier.
39
SELECT COUNT(*) = 0 AS "Expect 1"
40
FROM performance_schema.socket_instances
41
WHERE EVENT_NAME NOT LIKE 'wait/io/socket/%';
44
# OBJECT_INSTANCE_BEGIN is an arbitrary identifier, guaranteed to be unique.
45
SELECT COUNT(*) = COUNT(DISTINCT OBJECT_INSTANCE_BEGIN) AS "Expect 1"
46
FROM performance_schema.socket_instances;
49
# SOCKET_ID is the internal file handle assigned to the socket.
50
SELECT COUNT(*) = COUNT(DISTINCT SOCKET_ID) AS "Expect 1"
51
FROM performance_schema.socket_instances;
54
# Characteristics per our thread
55
# There must be only one entry with our thread_id
56
SELECT COUNT(*) = 1 AS 'Expect 1'
57
FROM performance_schema.socket_instances
58
WHERE THREAD_ID = @thread_id;
61
# TCP/IP connections should have a unique port number
62
# Connection 1 (tcp/ip)
63
SELECT COUNT(*) = 1 AS 'Expect 1'
64
FROM performance_schema.socket_instances
68
# Connection 2 (tcp/ip)
69
SELECT COUNT(*) = 1 AS 'Expect 1'
70
FROM performance_schema.socket_instances
74
# Check the content for the default connection (unix domain)
75
SELECT COUNT(*) = 1 as 'Expect 1'
76
FROM performance_schema.socket_instances
77
WHERE EVENT_NAME = 'wait/io/socket/sql/client_connection'
78
AND PORT = 0 AND THREAD_ID = @thread_id;
81
# Characteristics of 'server_tcpip_socket' entry
82
# Server listening socket, TCP/IP
83
# There is only one entry with 'wait/io/socket/sql/server_tcpip_socket'.
84
# It shares the same thread id as 'wait/io/socket/sql/server_unix_socket'.
85
SELECT COUNT(*) = 1 AS 'Expect 1'
86
FROM performance_schema.socket_instances
87
WHERE EVENT_NAME = 'wait/io/socket/sql/server_tcpip_socket';
90
# Get the 'server_tcpip_socket' thread id
91
SELECT THREAD_ID INTO @thread_id
92
FROM performance_schema.socket_instances
93
WHERE EVENT_NAME = 'wait/io/socket/sql/server_tcpip_socket';
95
SELECT THREAD_ID = @thread_id
96
AND (IP = '0.0.0.0' OR IP = '::')
98
AND STATE = 'ACTIVE' AS 'Expect 1'
99
FROM performance_schema.socket_instances
100
WHERE EVENT_NAME = 'wait/io/socket/sql/server_tcpip_socket';
103
# Characteristics of 'server_unix_socket' entry
104
# Server listening socket, unix domain (socket file)
105
# There is only one entry with 'wait/io/socket/sql/server_unix_socket'.
106
# It shares the same thread id as 'wait/io/socket/sql/server_tcpip_socket'.
107
SELECT COUNT(*) = 1 AS 'Expect 1'
108
FROM performance_schema.socket_instances
109
WHERE EVENT_NAME = 'wait/io/socket/sql/server_unix_socket';
112
# Get the 'server_unix_socket' thread id
113
SELECT THREAD_ID INTO @thread_id
114
FROM performance_schema.socket_instances
115
WHERE EVENT_NAME = 'wait/io/socket/sql/server_unix_socket';
117
SELECT THREAD_ID = @thread_id
120
AND STATE = 'ACTIVE' AS 'Expect 1'
121
FROM performance_schema.socket_instances
122
WHERE EVENT_NAME = 'wait/io/socket/sql/server_unix_socket';
125
# Server listening sockets (TCP and Unix) are handled on the same thread
126
SELECT COUNT(*) = 2 AS 'Expect 1'
127
FROM performance_schema.socket_instances
128
WHERE THREAD_ID = @thread_id;
131
SELECT COUNT(*) = 2 AS 'Expect 1'
132
FROM performance_schema.socket_instances
133
WHERE THREAD_ID = @thread_id;
136
#Compare server listener socket thread ids
137
select @match_thread_id;
140
# Check content for client connection 1 (tcpip)
141
SELECT EVENT_NAME = 'wait/io/socket/sql/client_connection'
142
AND (IP LIKE '%127.0.0.1' OR IP = '::1')
145
FROM performance_schema.socket_instances
146
WHERE THREAD_ID = @thread_id;
149
# Characteristics of entries with THREAD_ID of con1
150
# There is only one entry with this id.
151
SELECT COUNT(*) = 1 AS 'Expect 1'
152
FROM performance_schema.socket_instances
153
WHERE THREAD_ID = @thread_id;
156
# Check content for client connection 2 (tcpip)
157
SELECT EVENT_NAME = 'wait/io/socket/sql/client_connection'
158
AND (IP LIKE '%127.0.0.1' OR IP = '::1')
161
FROM performance_schema.socket_instances
162
WHERE THREAD_ID = @thread_id;
165
# Characteristics of entries with THREAD_ID of con2
166
# There is only one entry with this id.
167
SELECT COUNT(*) = 1 AS 'Expect 1'
168
FROM performance_schema.socket_instances
169
WHERE THREAD_ID = @thread_id;
172
# Check the content for client connection 3 (unix domain).
173
SELECT EVENT_NAME = 'wait/io/socket/sql/client_connection'
177
FROM performance_schema.socket_instances
178
WHERE THREAD_ID = @thread_id;
181
# Characteristics of entries with THREAD_ID of con3
182
# There is only one entry with this id.
183
SELECT COUNT(*) = 1 AS 'Expect 1'
184
FROM performance_schema.socket_instances
185
WHERE THREAD_ID = @thread_id;
188
# Show differences to socket_instances before con1, con2 and con3 connecting
189
SELECT EVENT_NAME, IP
190
FROM performance_schema.socket_instances
191
WHERE (EVENT_NAME,OBJECT_INSTANCE_BEGIN,THREAD_ID,SOCKET_ID,IP,PORT,STATE)
192
NOT IN (SELECT EVENT_NAME,OBJECT_INSTANCE_BEGIN,THREAD_ID,SOCKET_ID,IP,PORT,STATE
193
FROM my_socket_instances)
196
wait/io/socket/sql/client_connection <LOCALHOST>
197
wait/io/socket/sql/client_connection <LOCALHOST>
198
wait/io/socket/sql/client_connection
199
# Disconnect con1, con2 and con3
200
# After waiting a bit we should have no differences to socket_instances
201
# before con1, con2, con3 connecting.
203
FROM performance_schema.socket_instances
204
WHERE (EVENT_NAME,OBJECT_INSTANCE_BEGIN,THREAD_ID,SOCKET_ID,IP,PORT,STATE)
205
NOT IN (SELECT EVENT_NAME,OBJECT_INSTANCE_BEGIN,THREAD_ID,SOCKET_ID,IP,PORT,STATE
206
FROM my_socket_instances)
208
EVENT_NAME OBJECT_INSTANCE_BEGIN THREAD_ID SOCKET_ID IP PORT STATE