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
|
# 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();
# Establish local TCP/IP connection (con1,localhost,root,,test,,)
# 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;
# Switch to connection default
# Establish second local TCP/IP connection (con1,localhost,root,,test,,)
# 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;
# Switch to 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 (tcpip)
SELECT EVENT_NAME = 'wait/io/socket/sql/client_connection'
AND (IP LIKE '%127.0.0.1' OR IP = '::1') AS 'Expect 1'
FROM performance_schema.socket_instances
WHERE 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
# 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
# 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>
# Disconnect con1 and con2
# After waiting a bit we should have no differences to socket_instances
# before con1, con2 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
|
