1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
|
# Take a snapshot of SOCKET_INSTANCES
CREATE TEMPORARY TABLE my_socket_instances AS
SELECT * FROM performance_schema.socket_instances;
# Get thread id of the default connection
SELECT THREAD_ID INTO @thread_id
FROM performance_schema.threads
WHERE PROCESSLIST_ID = CONNECTION_ID();
connect con1,$my_localhost,root,,test,,$MASTER_MYPORT;
# Store the thread id of connection 1 (tcp/ip)
SELECT THREAD_ID INTO @thread_id
FROM performance_schema.threads
WHERE PROCESSLIST_ID = CONNECTION_ID();
# Store the port of connection 1 (tcp/ip)
SELECT PORT INTO @port
FROM performance_schema.socket_instances
WHERE THREAD_ID = @thread_id;
connection default;
connect con2,$my_localhost,root,,test,,$MASTER_MYPORT;
# Store the thread_id of connection 2 (tcp/ip)
SELECT THREAD_ID INTO @thread_id
FROM performance_schema.threads
WHERE PROCESSLIST_ID = CONNECTION_ID();
# Store the port of connection 2 (tcp/ip)
SELECT PORT INTO @port
FROM performance_schema.socket_instances
WHERE THREAD_ID = @thread_id;
connection default;
connect con3,localhost,root,,test,,;
# Store the thread id of connection 3 (unix domain)
SELECT THREAD_ID INTO @thread_id
FROM performance_schema.threads
WHERE PROCESSLIST_ID = CONNECTION_ID();
# Store the port of connection 3 (unix domain)
SELECT PORT INTO @port
FROM performance_schema.socket_instances
WHERE THREAD_ID = @thread_id;
connection default;
# EVENT_NAME is the "wait/io/socket/*" instrument identifier.
SELECT COUNT(*) = 0 AS "Expect 1"
FROM performance_schema.socket_instances
WHERE EVENT_NAME NOT LIKE 'wait/io/socket/%';
Expect 1
1
# OBJECT_INSTANCE_BEGIN is an arbitrary identifier, guaranteed to be unique.
SELECT COUNT(*) = COUNT(DISTINCT OBJECT_INSTANCE_BEGIN) AS "Expect 1"
FROM performance_schema.socket_instances;
Expect 1
1
# SOCKET_ID is the internal file handle assigned to the socket.
SELECT COUNT(*) = COUNT(DISTINCT SOCKET_ID) AS "Expect 1"
FROM performance_schema.socket_instances;
Expect 1
1
# Characteristics per our thread
# There must be only one entry with our thread_id
SELECT COUNT(*) = 1 AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE THREAD_ID = @thread_id;
Expect 1
1
# TCP/IP connections should have a unique port number
# Connection 1 (tcp/ip)
SELECT COUNT(*) = 1 AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE PORT = @port;
Expect 1
1
# Connection 2 (tcp/ip)
SELECT COUNT(*) = 1 AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE PORT = @port;
Expect 1
1
# Check the content for the default connection (unix domain)
SELECT COUNT(*) = 1 as 'Expect 1'
FROM performance_schema.socket_instances
WHERE EVENT_NAME = 'wait/io/socket/sql/client_connection'
AND PORT = 0 AND THREAD_ID = @thread_id;
Expect 1
1
# Characteristics of 'server_tcpip_socket' entry
# Server listening socket, TCP/IP
# There is only one entry with 'wait/io/socket/sql/server_tcpip_socket'.
# It shares the same thread id as 'wait/io/socket/sql/server_unix_socket'.
SELECT COUNT(*) = 1 AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE EVENT_NAME = 'wait/io/socket/sql/server_tcpip_socket';
Expect 1
1
# Get the 'server_tcpip_socket' thread id
SELECT THREAD_ID INTO @thread_id
FROM performance_schema.socket_instances
WHERE EVENT_NAME = 'wait/io/socket/sql/server_tcpip_socket';
# Check the content.
SELECT THREAD_ID = @thread_id
AND (IP = '0.0.0.0' OR IP = '::')
AND PORT = @port
AND STATE = 'ACTIVE' AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE EVENT_NAME = 'wait/io/socket/sql/server_tcpip_socket';
Expect 1
1
# Characteristics of 'server_unix_socket' entry
# Server listening socket, unix domain (socket file)
# There is only one entry with 'wait/io/socket/sql/server_unix_socket'.
# It shares the same thread id as 'wait/io/socket/sql/server_tcpip_socket'.
SELECT COUNT(*) = 1 AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE EVENT_NAME = 'wait/io/socket/sql/server_unix_socket';
Expect 1
1
# Get the 'server_unix_socket' thread id
SELECT THREAD_ID INTO @thread_id
FROM performance_schema.socket_instances
WHERE EVENT_NAME = 'wait/io/socket/sql/server_unix_socket';
# Check the content.
SELECT THREAD_ID = @thread_id
AND IP = ''
AND PORT = 0
AND STATE = 'ACTIVE' AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE EVENT_NAME = 'wait/io/socket/sql/server_unix_socket';
Expect 1
1
# Server listening sockets (TCP and Unix) are handled on the same thread
SELECT COUNT(*) = 2 AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE THREAD_ID = @thread_id;
Expect 1
1
SELECT COUNT(*) = 2 AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE THREAD_ID = @thread_id;
Expect 1
1
#Compare server listener socket thread ids
select @match_thread_id;
@match_thread_id
1
# Check content for client connection 1 (tcpip)
SELECT EVENT_NAME = 'wait/io/socket/sql/client_connection'
AND (IP LIKE '%127.0.0.1' OR IP = '::1')
AND PORT = @port
AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE THREAD_ID = @thread_id;
Expect 1
1
# Characteristics of entries with THREAD_ID of con1
# There is only one entry with this id.
SELECT COUNT(*) = 1 AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE THREAD_ID = @thread_id;
Expect 1
1
# Check content for client connection 2 (tcpip)
SELECT EVENT_NAME = 'wait/io/socket/sql/client_connection'
AND (IP LIKE '%127.0.0.1' OR IP = '::1')
AND PORT = @port
AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE THREAD_ID = @thread_id;
Expect 1
1
# Characteristics of entries with THREAD_ID of con2
# There is only one entry with this id.
SELECT COUNT(*) = 1 AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE THREAD_ID = @thread_id;
Expect 1
1
# Check the content for client connection 3 (unix domain).
SELECT EVENT_NAME = 'wait/io/socket/sql/client_connection'
AND IP = ''
AND PORT = 0
AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE THREAD_ID = @thread_id;
Expect 1
1
# Characteristics of entries with THREAD_ID of con3
# There is only one entry with this id.
SELECT COUNT(*) = 1 AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE THREAD_ID = @thread_id;
Expect 1
1
# Show differences to socket_instances before con1, con2 and con3 connecting
SELECT EVENT_NAME, IP
FROM performance_schema.socket_instances
WHERE (EVENT_NAME,OBJECT_INSTANCE_BEGIN,THREAD_ID,SOCKET_ID,IP,PORT,STATE)
NOT IN (SELECT EVENT_NAME,OBJECT_INSTANCE_BEGIN,THREAD_ID,SOCKET_ID,IP,PORT,STATE
FROM my_socket_instances)
ORDER BY THREAD_ID;
EVENT_NAME IP
wait/io/socket/sql/client_connection <LOCALHOST>
wait/io/socket/sql/client_connection <LOCALHOST>
wait/io/socket/sql/client_connection
connection default;
connection con1;
disconnect con1;
connection con2;
disconnect con2;
connection con3;
disconnect con3;
connection default;
# After waiting a bit we should have no differences to socket_instances
# before con1, con2, con3 connecting.
SELECT *
FROM performance_schema.socket_instances
WHERE (EVENT_NAME,OBJECT_INSTANCE_BEGIN,THREAD_ID,SOCKET_ID,IP,PORT,STATE)
NOT IN (SELECT EVENT_NAME,OBJECT_INSTANCE_BEGIN,THREAD_ID,SOCKET_ID,IP,PORT,STATE
FROM my_socket_instances)
ORDER BY THREAD_ID;
EVENT_NAME OBJECT_INSTANCE_BEGIN THREAD_ID SOCKET_ID IP PORT STATE
|
