Debian Codegraph @

mysql-test/suite/perfschema/r/processlist.result

raw
1 ##
2 ## Test the Performance Schema-based implementation of SHOW PROCESSLIST.
3 ##
4 ## Test cases:
5 ## 1. Execute the new SHOW [FULL] PROCESSLIST and SELECT on performance_schema.processlist
6 ## 2. Execute the legacy SHOW [FULL] PROCESSLIST and SELECT on information_schema.processlist
7 ## 3. Verify that performance_schema_show_processlist = ON executes the new implementation
8 ## 4. Verify that performance_schema_show_processlist = OFF executes the legacy code path
9 ##
10 ## Results must be manually verified.
11
12 ### Setup ###
13
14 select @@global.performance_schema_show_processlist into @save_processlist;
15
16 create user user1@localhost, user2@localhost,
17 user3@localhost, user4@localhost;
18
19 grant ALL on *.* to user1@localhost;
20 grant ALL on *.* to user2@localhost;
21 grant ALL on *.* to user3@localhost;
22 grant ALL on *.* to user4@localhost;
23
24 flush privileges;
25
26 use test;
27 create table test.t1 (s1 int, s2 int, s3 int, s4 int);
28
29 # Switch to (con0, localhost, root, , )
30
31 insert into test.t1 values(1, 1, 1, 1);
32 insert into test.t1 values(2, 2, 2, 2);
33 insert into test.t1 values(3, 3, 3, 3);
34 insert into test.t1 values(4, 4, 4, 4);
35
36 # Lock test.t1, insert/update/deletes will block
37 lock tables t1 read;
38
39 # Connect (con1, localhost, user1, , )
40 insert into test.t1 values (0, 0, 0, 0);
41
42 # Connect (con2, localhost, user2, , )
43 update test.t1 set s1 = s1 + 1, s2 = s2 + 2, s3 = s3 + 3, s4 = ((s4 + 4) * (s4 + 5)) + 12345 + 67890 + 11111 + 22222 + 33333 + 44444 + 55555 + 99999;;
44
45 # Connect (con3, localhost, user3, , )
46 delete from test.t1 where s1 = 3;
47
48 # Connect (con4, localhost, user4, , )
49 insert into test.t1 values (4, 4, 4, 4);
50
51 # Connection default
52
53 # Wait for queries to appear in the processlist table
54
55
56 ### Execute new SHOW [FULL] PROCESSLIST and SELECT on performance_schema.processlist
57
58 set @@global.performance_schema_show_processlist = on;
59
60 SHOW FULL PROCESSLIST;
61 Id User Host db Command Time State Info
62 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
63 <Id> root <Host> test <Command> <Time> <State> NULL
64 <Id> root <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
65 <Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
66 <Id> user2 <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2, s3 = s3 + 3, s4 = ((s4 + 4) * (s4 + 5)) + 12345 + 67890 + 11111 + 22222 + 33333 + 44444 + 55555 + 99999
67 <Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3
68 <Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4)
69
70 # Performance Schema processlist table
71
72 select * from performance_schema.processlist order by user, id;
73 ID USER HOST DB COMMAND TIME STATE INFO
74 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
75 <Id> root <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
76 <Id> root <Host> test <Command> <Time> <State> NULL
77 <Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
78 <Id> user2 <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2, s3 = s3 + 3, s4 = ((s4 + 4) * (s4 + 5)) + 12345 + 67890 + 11111 + 22222 + 33333 + 44444 + 55555 + 99999
79 <Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3
80 <Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4)
81
82 # Information Schema processlist table
83
84 select * from information_schema.processlist order by user, id;
85 ID USER HOST DB COMMAND TIME STATE INFO
86 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
87 <Id> root <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
88 <Id> root <Host> test <Command> <Time> <State> NULL
89 <Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
90 <Id> user2 <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2, s3 = s3 + 3, s4 = ((s4 + 4) * (s4 + 5)) + 12345 + 67890 + 11111 + 22222 + 33333 + 44444 + 55555 + 99999
91 <Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3
92 <Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4)
93
94
95 ### Execute legacy SHOW [FULL] PROCESSLIST and SELECT on information_schema.processlist
96
97 set @@global.performance_schema_show_processlist = off;
98
99 SHOW FULL PROCESSLIST;
100 Id User Host db Command Time State Info
101 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
102 <Id> root <Host> test <Command> <Time> <State> NULL
103 <Id> root <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
104 <Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
105 <Id> user2 <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2, s3 = s3 + 3, s4 = ((s4 + 4) * (s4 + 5)) + 12345 + 67890 + 11111 + 22222 + 33333 + 44444 + 55555 + 99999
106 <Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3
107 <Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4)
108
109 # Performance Schema processlist table
110
111 select * from performance_schema.processlist order by user, id;
112 ID USER HOST DB COMMAND TIME STATE INFO
113 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
114 <Id> root <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
115 <Id> root <Host> test <Command> <Time> <State> NULL
116 <Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
117 <Id> user2 <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2, s3 = s3 + 3, s4 = ((s4 + 4) * (s4 + 5)) + 12345 + 67890 + 11111 + 22222 + 33333 + 44444 + 55555 + 99999
118 <Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3
119 <Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4)
120
121 # Information Schema processlist table
122
123 select * from information_schema.processlist order by user, id;
124 ID USER HOST DB COMMAND TIME STATE INFO
125 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
126 <Id> root <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
127 <Id> root <Host> test <Command> <Time> <State> NULL
128 <Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
129 <Id> user2 <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2, s3 = s3 + 3, s4 = ((s4 + 4) * (s4 + 5)) + 12345 + 67890 + 11111 + 22222 + 33333 + 44444 + 55555 + 99999
130 <Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3
131 <Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4)
132
133
134 ### Verify feature code path
135
136 # Enable SHOW PROCESSLIST via the Performance Schema
137 set @@global.performance_schema_show_processlist = on;
138
139 # Connection default, send SHOW PROCESSLIST
140 SET DEBUG_SYNC='pfs_show_processlist_performance_schema SIGNAL pfs_processlist_pfs WAIT_FOR continue';
141 SHOW FULL PROCESSLIST;
142
143 # Connection con0
144 SET DEBUG_SYNC='now WAIT_FOR pfs_processlist_pfs';
145 SET DEBUG_SYNC='now SIGNAL continue';
146
147 # Connection default, reap
148 Id User Host db Command Time State Info
149 <Id> event_scheduler <Host> NULL <Command> <Time> <State> <Info>
150 <Id> root <Host> test <Command> <Time> <State> <Info>
151 <Id> root <Host> test <Command> <Time> <State> <Info>
152 <Id> user1 <Host> test <Command> <Time> <State> <Info>
153 <Id> user2 <Host> test <Command> <Time> <State> <Info>
154 <Id> user3 <Host> test <Command> <Time> <State> <Info>
155 <Id> user4 <Host> test <Command> <Time> <State> <Info>
156
157
158 ### Verify legacy code path
159
160 # Enable the legacy SHOW PROCESSLIST
161 set @@global.performance_schema_show_processlist = off;
162
163 # Connection default, send SHOW PROCESSLIST
164 SET DEBUG_SYNC='RESET';
165 SET DEBUG_SYNC='pfs_show_processlist_legacy SIGNAL pfs_processlist_legacy WAIT_FOR continue';
166 SHOW FULL PROCESSLIST;
167
168 # Connection con0
169 SET DEBUG_SYNC='now WAIT_FOR pfs_processlist_legacy';
170 SET DEBUG_SYNC='now SIGNAL continue';
171
172 # Connection default, reap
173 Id User Host db Command Time State Info
174 <Id> event_scheduler <Host> NULL <Command> <Time> <State> <Info>
175 <Id> root <Host> test <Command> <Time> <State> <Info>
176 <Id> root <Host> test <Command> <Time> <State> <Info>
177 <Id> user1 <Host> test <Command> <Time> <State> <Info>
178 <Id> user2 <Host> test <Command> <Time> <State> <Info>
179 <Id> user3 <Host> test <Command> <Time> <State> <Info>
180 <Id> user4 <Host> test <Command> <Time> <State> <Info>
181
182
183 ### Clean up ###
184
185 # Connection con0, unlock test.t1, disconnect
186 unlock tables;
187
188 # Connection con1, reap, disconnect
189 # Connection con2, reap, disconnect
190 # Connection con3, reap, disconnect
191 # Connection con4, reap, disconnect
192
193 # Connection default
194
195 drop table test.t1;
196 drop user user1@localhost;
197 drop user user2@localhost;
198 drop user user3@localhost;
199 drop user user4@localhost;
200
201 set @@global.performance_schema_show_processlist = @save_processlist;