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 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
54 --source include/no_protocol.inc
55 --source ../include/wait_for_pfs_thread_count.inc
56
57 --disable_query_log
58
59 create user user1@localhost, user2@localhost,
60 user3@localhost, user4@localhost;
61 grant ALL on *.* to user1@localhost;
62 grant ALL on *.* to user2@localhost;
63 grant ALL on *.* to user3@localhost;
64 grant ALL on *.* to user4@localhost;
65
66 flush privileges;
67
68 # Purge old users, hosts, user/host from previous tests
69 truncate table performance_schema.accounts;
70 truncate table performance_schema.users;
71 truncate table performance_schema.hosts;
72
73 # Save the setup
74
75 --disable_warnings
76 drop table if exists test.setup_actors;
77 --enable_warnings
78
79 create table test.setup_actors as
80 select * from performance_schema.setup_actors;
81
82 # Only instrument the user connections
83 truncate table performance_schema.setup_actors;
84 insert into performance_schema.setup_actors
85 set host= 'localhost', user= 'user1', `role`= '%';
86 insert into performance_schema.setup_actors
87 set host= 'localhost', user= 'user2', `role`= '%';
88 insert into performance_schema.setup_actors
89 set host= 'localhost', user= 'user3', `role`= '%';
90 insert into performance_schema.setup_actors
91 set host= 'localhost', user= 'user4', `role`= '%';
92
93 update performance_schema.threads set instrumented='NO';
94
95 # Only instrument a few events of each kind
96 update performance_schema.setup_instruments set enabled='NO', timed='NO';
97
98 update performance_schema.setup_instruments set enabled='YES', timed='YES'
99 where name in ('memory/sql/user_var_entry::value',
100 'idle');
101
102 # Start from a known clean state, to avoid noise from previous tests
103 flush tables;
104 flush status;
105 truncate performance_schema.memory_summary_by_thread_by_event_name;
106 truncate performance_schema.memory_summary_by_account_by_event_name;
107 truncate performance_schema.memory_summary_by_user_by_event_name;
108 truncate performance_schema.memory_summary_by_host_by_event_name;
109 truncate performance_schema.memory_summary_global_by_event_name;
110
111 --disable_warnings
112 drop procedure if exists dump_thread;
113 drop procedure if exists dump_one_thread;
114 --enable_warnings
115
116 delimiter $$;
117
118 create procedure dump_thread()
119 begin
120 call dump_one_thread('user1');
121 call dump_one_thread('user2');
122 call dump_one_thread('user3');
123 call dump_one_thread('user4');
124 end
125 $$
126
127 create procedure dump_one_thread(in username varchar(64))
128 begin
129 declare my_thread_id int;
130
131 set my_thread_id = (select thread_id from performance_schema.threads
132 where processlist_user=username LIMIT 1);
133
134 if (my_thread_id is not null) then
135 select username,
136 EVENT_NAME, COUNT_ALLOC, COUNT_FREE,
137 SUM_NUMBER_OF_BYTES_ALLOC, SUM_NUMBER_OF_BYTES_FREE,
138 LOW_COUNT_USED, CURRENT_COUNT_USED, HIGH_COUNT_USED,
139 LOW_NUMBER_OF_BYTES_USED, CURRENT_NUMBER_OF_BYTES_USED, HIGH_NUMBER_OF_BYTES_USED
140 from performance_schema.memory_summary_by_thread_by_event_name
141 where event_name in ('memory/sql/user_var_entry::value')
142 and thread_id = my_thread_id
143 order by event_name;
144 else
145 select username, "not found" as status;
146 end if;
147 end
148 $$
149
150 delimiter ;$$
151
152 prepare dump_memory_account from
153 "select *
154 from performance_schema.memory_summary_by_account_by_event_name
155 where user like \'user%\'
156 and event_name in ('memory/sql/user_var_entry::value')
157 order by user, host, event_name;";
158
159 prepare dump_memory_user from
160 "select *
161 from performance_schema.memory_summary_by_user_by_event_name
162 where user like \'user%\'
163 and event_name in ('memory/sql/user_var_entry::value')
164 order by user, event_name;";
165
166 prepare dump_memory_host from
167 "select *
168 from performance_schema.memory_summary_by_host_by_event_name
169 where host=\'localhost\'
170 and event_name in ('memory/sql/user_var_entry::value')
171 order by host, event_name;";
172
173 prepare dump_memory_global from
174 "select *
175 from performance_schema.memory_summary_global_by_event_name
176 where event_name in ('memory/sql/user_var_entry::value')
177 order by event_name;";
178
179 prepare dump_users from
180 "select USER, CURRENT_CONNECTIONS, TOTAL_CONNECTIONS from performance_schema.users where user is not null order by user;";
181
182 prepare dump_hosts from
183 "select HOST, CURRENT_CONNECTIONS, TOTAL_CONNECTIONS from performance_schema.hosts where host is not null order by host;";
184
185 prepare dump_accounts from
186 "select USER, HOST, CURRENT_CONNECTIONS, TOTAL_CONNECTIONS from performance_schema.accounts where (user is not null) and (host is not null) order by user, host;";
187
188 --enable_query_log
189
190 # Make sure all the instrumentation is present
191 show global status like "performance_schema_memory_classes_lost";
192
193