Debian Codegraph @

mysql-test/suite/perfschema/include/memory_aggregate_setup.inc

raw
1 # Tests for the performance schema
2
3 # =============
4 # DOCUMENTATION
5 # =============
6
7 # Verify how memory stats are aggregated into various tables
8 #
9 # In the thread dimension:
10 # - memory_summary_by_thread_by_event_name
11 # - memory_summary_by_account_by_event_name
12 # - memory_summary_by_user_by_event_name
13 # - memory_summary_by_host_by_event_name
14 #
15 # Globally:
16 # - memory_summary_global_by_event_name
17 #
18 # The tests are written with the following helpers:
19 # - include/memory_aggregate_setup.inc
20 # - include/memory_aggregate_load.inc
21 # - include/memory_aggregate_cleanup.inc
22 #
23 # Helpers are intended to be used as follows.
24 #
25 # A Typical test t/memory_aggregate_xxx.test will consist of:
26 # --source ../include/memory_aggregate_setup.inc
27 # --source ../include/memory_aggregate_load.inc
28 # --source ../include/memory_aggregate_cleanup.inc
29 # and a t/memory_aggregate_xxx-master.opt file
30 #
31 # Naming conventions for t/memory_aggregate_xxx.test are as follows:
32 # t/memory_aggregate_<account><user><host>
33 #
34 # <account> corresponds to different sizing settings for
35 # the variable performance-schema-accounts-size
36 # - (blank): accounts-size sufficient to represent all records
37 # - no_a: accounts-size set to 0
38 #
39 # <user> corresponds to different sizing settings for
40 # the variable performance-schema-users-size
41 # - (blank): users-size sufficient to represent all records
42 # - no_u: users-size set to 0
43 #
44 # <host> corresponds to different sizing settings for
45 # the variable performance-schema-hosts-size
46 # - (blank): hosts-size sufficient to represent all records
47 # - no_h: hosts-size set to 0
48
49 # =========================================
50 # HELPER include/memory_aggregate_setup.inc
51 # =========================================
52
53 --source include/not_embedded.inc
54 --source include/have_perfschema.inc
55 --source include/no_protocol.inc
56 --source ../include/wait_for_pfs_thread_count.inc
57 --source include/not_windows.inc
58
59 --disable_query_log
60
61 set @orig_sql_mode= @@sql_mode;
62 set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
63 grant ALL on *.* to user1@localhost;
64 grant ALL on *.* to user2@localhost;
65 grant ALL on *.* to user3@localhost;
66 grant ALL on *.* to user4@localhost;
67 set sql_mode= @orig_sql_mode;
68
69 flush privileges;
70
71 # Purge old users, hosts, user/host from previous tests
72 truncate table performance_schema.accounts;
73 truncate table performance_schema.users;
74 truncate table performance_schema.hosts;
75
76 # Save the setup
77
78 --disable_warnings
79 drop table if exists test.setup_actors;
80 --enable_warnings
81
82 create table test.setup_actors as
83 select * from performance_schema.setup_actors;
84
85 # Only instrument the user connections
86 truncate table performance_schema.setup_actors;
87 insert into performance_schema.setup_actors
88 set host= 'localhost', user= 'user1', role= '%';
89 insert into performance_schema.setup_actors
90 set host= 'localhost', user= 'user2', role= '%';
91 insert into performance_schema.setup_actors
92 set host= 'localhost', user= 'user3', role= '%';
93 insert into performance_schema.setup_actors
94 set host= 'localhost', user= 'user4', role= '%';
95
96 update performance_schema.threads set instrumented='NO';
97
98 # Only instrument a few events of each kind
99 update performance_schema.setup_instruments set enabled='NO', timed='NO';
100
101 update performance_schema.setup_instruments set enabled='YES', timed='YES'
102 where name in ('memory/sql/user_var_entry::value',
103 'memory/sql/User_level_lock',
104 'memory/sql/Query_cache',
105 'idle');
106
107 # Start from a known clean state, to avoid noise from previous tests
108 flush tables;
109 flush global status;
110 truncate performance_schema.memory_summary_by_thread_by_event_name;
111 truncate performance_schema.memory_summary_by_account_by_event_name;
112 truncate performance_schema.memory_summary_by_user_by_event_name;
113 truncate performance_schema.memory_summary_by_host_by_event_name;
114 truncate performance_schema.memory_summary_global_by_event_name;
115
116 --disable_warnings
117 drop procedure if exists dump_thread;
118 drop procedure if exists dump_one_thread;
119 --enable_warnings
120
121 delimiter $$;
122
123 create procedure dump_thread()
124 begin
125 call dump_one_thread('user1');
126 call dump_one_thread('user2');
127 call dump_one_thread('user3');
128 call dump_one_thread('user4');
129 end
130 $$
131
132 create procedure dump_one_thread(in username varchar(64))
133 begin
134 declare my_thread_id int;
135
136 set my_thread_id = (select thread_id from performance_schema.threads
137 where processlist_user=username LIMIT 1);
138
139 if (my_thread_id is not null) then
140 select username,
141 EVENT_NAME, COUNT_ALLOC, COUNT_FREE,
142 SUM_NUMBER_OF_BYTES_ALLOC, SUM_NUMBER_OF_BYTES_FREE,
143 LOW_COUNT_USED, CURRENT_COUNT_USED, HIGH_COUNT_USED,
144 LOW_NUMBER_OF_BYTES_USED, CURRENT_NUMBER_OF_BYTES_USED, HIGH_NUMBER_OF_BYTES_USED
145 from performance_schema.memory_summary_by_thread_by_event_name
146 where event_name in ('memory/sql/user_var_entry::value',
147 'memory/sql/User_level_lock',
148 'memory/sql/Query_cache')
149 and thread_id = my_thread_id
150 order by event_name;
151 else
152 select username, "not found" as status;
153 end if;
154 end
155 $$
156
157 delimiter ;$$
158
159 prepare dump_memory_account from
160 "select *
161 from performance_schema.memory_summary_by_account_by_event_name
162 where user like \'user%\'
163 and event_name in ('memory/sql/user_var_entry::value',
164 'memory/sql/User_level_lock',
165 'memory/sql/Query_cache')
166 order by user, host, event_name;";
167
168 prepare dump_memory_user from
169 "select *
170 from performance_schema.memory_summary_by_user_by_event_name
171 where user like \'user%\'
172 and event_name in ('memory/sql/user_var_entry::value',
173 'memory/sql/User_level_lock',
174 'memory/sql/Query_cache')
175 order by user, event_name;";
176
177 prepare dump_memory_host from
178 "select *
179 from performance_schema.memory_summary_by_host_by_event_name
180 where host=\'localhost\'
181 and event_name in ('memory/sql/user_var_entry::value',
182 'memory/sql/User_level_lock',
183 'memory/sql/Query_cache')
184 order by host, event_name;";
185
186 prepare dump_memory_global from
187 "select *
188 from performance_schema.memory_summary_global_by_event_name
189 where event_name in ('memory/sql/user_var_entry::value',
190 'memory/sql/User_level_lock',
191 'memory/sql/Query_cache')
192 order by event_name;";
193
194 prepare dump_users from
195 "select * from performance_schema.users where user is not null order by user;";
196
197 prepare dump_hosts from
198 "select * from performance_schema.hosts where host is not null order by host;";
199
200 prepare dump_accounts from
201 "select * from performance_schema.accounts where (user is not null) and (host is not null) order by user, host;";
202
203 --enable_query_log
204
205 # Make sure all the instrumentation is present
206 show global status like "performance_schema_memory_classes_lost";
207
208