Skip to content

Commit f7be327

Browse files
marcalffmarcalff
committed
Bug#11763728 - 56475: MISSING THREAD STATES IN PERFORMANCE_SCHEMA.THREADS / SHOW PROFILE
Before this fix, any thread waiting for a specific condition to happen, which corresponds to a call to THD::enter_cond(), would: - set THD::proc_info member directly - ignore instrumentation apis such as set_thd_proc_info and THD_STAGE_INFO. As a result, conditions involving THD::enter_cond() would be: - captured in SHOW PROCESSLIST - captured in table INFORMATION_SCHEMA.PROCESSLIST - missing in SHOW PROFILES - missing in table INFORMATION_SCHEMA.PROFILING - missing in table performance_schema.threads; - missing in table performance_schema.events_stages_current; - missing in table performance_schema.events_stages_history; - missing in table performance_schema.events_stages_history_long; This fix changes the THD::enter_cond() and THD::exit_cond() apis, to include informations about the calling code such as: - the caller function name - the caller file name - the caller line number and to include an instrumented stage name parameter, "PSI_stage_info". The structure PSI_stage_info is used instead of a plain "const char *msg", because PSI_stage_info contains more semantic, and can be used to instrument both: - the SHOW PROFILE code, which needs a plain string - the performance schema stages, which needs a pre registered stage key. With this change, the implementation of THD::enter_cond() can now maintain coherence between all available instrumentations: - the SHOW PROCESSLIST implementation (thd->proc_info) - the SHOW PROFILES implementation (thd->profiling) - the performance schema stages implementation (MYSQL_SET_STAGE) This is done in a new THD::enter_stage() method. Changing the THD::enter_cond() and THD:exit_cond() apis caused mechanical adjustments to many places in the server, and forced callers to explicitely declare stages to the instrumentation. The number of instrumented stages in the server has increased, so the max number of stages has been bumped from 100 to 150, causing adjustments to many performance schema test result files.
1 parent a17ea7b commit f7be327

100 files changed

Lines changed: 1072 additions & 225 deletions

File tree

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

‎include/my_sys.h‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -228,8 +228,8 @@ extern void (*fatal_error_handler_hook)(uint my_err, const char *str,
228228
extern uint my_file_limit;
229229
extern ulong my_thread_stack_size;
230230

231-
extern const char *(*proc_info_hook)(void *, const char *, const char *,
232-
const char *, const unsigned int);
231+
extern void (*proc_info_hook)(void *, const PSI_stage_info *, PSI_stage_info *,
232+
const char *, const char *, const unsigned int);
233233

234234
#ifdef HAVE_LARGE_PAGES
235235
extern my_bool my_use_large_pages;

‎mysql-test/suite/perfschema/include/stage_cleanup.inc‎

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
1+
# Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved.
2+
#
3+
# This program is free software; you can redistribute it and/or modify
4+
# it under the terms of the GNU General Public License as published by
5+
# the Free Software Foundation; version 2 of the License.
6+
#
7+
# This program is distributed in the hope that it will be useful,
8+
# but WITHOUT ANY WARRANTY; without even the implied warranty of
9+
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10+
# GNU General Public License for more details.
11+
#
12+
# You should have received a copy of the GNU General Public License
13+
# along with this program; if not, write to the Free Software
14+
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
15+
16+
# Tests for the performance schema
17+
18+
# ================================
19+
# HELPER include/stage_cleanup.inc
20+
# ================================
21+
22+
--disable_query_log
23+
revoke all privileges, grant option from user1@localhost;
24+
revoke all privileges, grant option from user2@localhost;
25+
revoke all privileges, grant option from user3@localhost;
26+
revoke all privileges, grant option from user4@localhost;
27+
drop user user1@localhost;
28+
drop user user2@localhost;
29+
drop user user3@localhost;
30+
drop user user4@localhost;
31+
flush privileges;
32+
33+
drop procedure dump_thread;
34+
drop procedure dump_one_thread;
35+
36+
truncate table performance_schema.setup_actors;
37+
insert into performance_schema.setup_actors
38+
select * from test.setup_actors;
39+
drop table test.setup_actors;
40+
drop table test.t1;
41+
42+
update performance_schema.threads set instrumented='YES';
43+
update performance_schema.setup_instruments set enabled='YES', timed='YES';
44+
45+
--enable_query_log
46+

‎mysql-test/suite/perfschema/include/stage_setup.inc‎

Lines changed: 156 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,156 @@
1+
# Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved.
2+
#
3+
# This program is free software; you can redistribute it and/or modify
4+
# it under the terms of the GNU General Public License as published by
5+
# the Free Software Foundation; version 2 of the License.
6+
#
7+
# This program is distributed in the hope that it will be useful,
8+
# but WITHOUT ANY WARRANTY; without even the implied warranty of
9+
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10+
# GNU General Public License for more details.
11+
#
12+
# You should have received a copy of the GNU General Public License
13+
# along with this program; if not, write to the Free Software
14+
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
15+
16+
# Tests for the performance schema
17+
18+
# =============
19+
# DOCUMENTATION
20+
# =============
21+
22+
# Verify critical stages of a statement
23+
#
24+
# The tests are written with the following helpers:
25+
# - include/stage_setup.inc
26+
# - include/stage_cleanup.inc
27+
#
28+
# Helpers are intended to be used as follows.
29+
#
30+
# A Typical test t/stage_xxx.test will consist of:
31+
# --source ../include/stage_setup.inc
32+
# ... test specific payload ...
33+
# --source ../include/stage_cleanup.inc
34+
# and a t/stage_xxx-master.opt file
35+
#
36+
# ==============================
37+
# HELPER include/stage_setup.inc
38+
# ==============================
39+
40+
--source include/not_embedded.inc
41+
--source include/have_perfschema.inc
42+
43+
# The file with expected results fits only to a run without
44+
# ps-protocol/sp-protocol/cursor-protocol/view-protocol.
45+
if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL
46+
+ $VIEW_PROTOCOL > 0`)
47+
{
48+
--skip Test requires: ps-protocol/sp-protocol/cursor-protocol/view-protocol disabled
49+
}
50+
51+
--disable_query_log
52+
53+
grant ALL on *.* to user1@localhost;
54+
grant ALL on *.* to user2@localhost;
55+
grant ALL on *.* to user3@localhost;
56+
grant ALL on *.* to user4@localhost;
57+
58+
flush privileges;
59+
60+
# Save the setup
61+
62+
--disable_warnings
63+
drop table if exists test.setup_actors;
64+
drop table if exists test.t1;
65+
--enable_warnings
66+
67+
create table test.t1(a varchar(64));
68+
69+
create table test.setup_actors as
70+
select * from performance_schema.setup_actors;
71+
72+
# Only instrument the user connections
73+
truncate table performance_schema.setup_actors;
74+
insert into performance_schema.setup_actors
75+
set host= 'localhost', user= 'user1', role= '%';
76+
insert into performance_schema.setup_actors
77+
set host= 'localhost', user= 'user2', role= '%';
78+
insert into performance_schema.setup_actors
79+
set host= 'localhost', user= 'user3', role= '%';
80+
insert into performance_schema.setup_actors
81+
set host= 'localhost', user= 'user4', role= '%';
82+
83+
update performance_schema.threads set instrumented='NO';
84+
85+
# Only instrument a few events of each kind
86+
update performance_schema.setup_instruments set enabled='YES', timed='YES';
87+
88+
# Start from a known clean state, to avoid noise from previous tests
89+
flush tables;
90+
flush status;
91+
truncate performance_schema.events_stages_summary_by_thread_by_event_name;
92+
truncate performance_schema.events_stages_summary_global_by_event_name;
93+
truncate performance_schema.events_stages_history;
94+
truncate performance_schema.events_stages_history_long;
95+
truncate performance_schema.events_statements_summary_by_thread_by_event_name;
96+
truncate performance_schema.events_statements_summary_global_by_event_name;
97+
truncate performance_schema.events_statements_history;
98+
truncate performance_schema.events_statements_history_long;
99+
100+
--disable_warnings
101+
drop procedure if exists dump_thread;
102+
drop procedure if exists dump_one_thread;
103+
--enable_warnings
104+
105+
delimiter $$;
106+
107+
create procedure dump_thread()
108+
begin
109+
call dump_one_thread('user1');
110+
call dump_one_thread('user2');
111+
call dump_one_thread('user3');
112+
call dump_one_thread('user4');
113+
end
114+
$$
115+
116+
create procedure dump_one_thread(in username varchar(64))
117+
begin
118+
declare my_thread_id int;
119+
declare my_statement_id int;
120+
121+
set my_thread_id = (select thread_id from performance_schema.threads
122+
where processlist_user=username);
123+
124+
if (my_thread_id is not null) then
125+
begin
126+
# Dump the current statement for this thread
127+
select username, event_name, sql_text
128+
from performance_schema.events_statements_current
129+
where thread_id = my_thread_id;
130+
131+
# Get the current statement
132+
set my_statement_id = (select event_id from
133+
performance_schema.events_statements_current
134+
where thread_id = my_thread_id);
135+
136+
# Dump the stages for this statement
137+
select username, event_name, nesting_event_type
138+
from performance_schema.events_stages_current
139+
where thread_id = my_thread_id
140+
and nesting_event_id = my_statement_id
141+
order by event_id asc;
142+
select username, event_name, nesting_event_type
143+
from performance_schema.events_stages_history
144+
where thread_id = my_thread_id
145+
and nesting_event_id = my_statement_id
146+
order by event_id asc;
147+
end;
148+
else
149+
select username, "not found" as status;
150+
end if;
151+
end
152+
$$
153+
154+
delimiter ;$$
155+
156+
--enable_query_log

‎mysql-test/suite/perfschema/r/ortho_iter.result‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -109,7 +109,7 @@ performance_schema_max_mutex_classes 200
109109
performance_schema_max_mutex_instances 5000
110110
performance_schema_max_rwlock_classes 30
111111
performance_schema_max_rwlock_instances 5000
112-
performance_schema_max_stage_classes 100
112+
performance_schema_max_stage_classes 150
113113
performance_schema_max_statement_classes 165
114114
performance_schema_max_table_handles 1000
115115
performance_schema_max_table_instances 500

‎mysql-test/suite/perfschema/r/privilege_table_io.result‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -33,7 +33,7 @@ performance_schema_max_mutex_classes 200
3333
performance_schema_max_mutex_instances 5000
3434
performance_schema_max_rwlock_classes 30
3535
performance_schema_max_rwlock_instances 5000
36-
performance_schema_max_stage_classes 100
36+
performance_schema_max_stage_classes 150
3737
performance_schema_max_statement_classes 165
3838
performance_schema_max_table_handles 1000
3939
performance_schema_max_table_instances 500

‎mysql-test/suite/perfschema/r/query_cache.result‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -38,7 +38,7 @@ spins
3838
NULL
3939
select name from performance_schema.setup_instruments order by name limit 1;
4040
name
41-
stage/sql/After create
41+
stage/mysys/Waiting for table level lock
4242
show status like "Qcache_queries_in_cache";
4343
Variable_name Value
4444
Qcache_queries_in_cache 1
@@ -53,7 +53,7 @@ spins
5353
NULL
5454
select name from performance_schema.setup_instruments order by name limit 1;
5555
name
56-
stage/sql/After create
56+
stage/mysys/Waiting for table level lock
5757
show status like "Qcache_queries_in_cache";
5858
Variable_name Value
5959
Qcache_queries_in_cache 1

‎mysql-test/suite/perfschema/r/stage_mdl_function.result‎

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,34 @@
1+
drop function if exists test.f1;
2+
create function test.f1() returns int
3+
return 1;
4+
begin;
5+
select test.f1();
6+
test.f1()
7+
1
8+
drop function test.f1;
9+
call dump_one_thread('user1');
10+
username event_name sql_text
11+
user1 statement/sql/select select test.f1()
12+
username event_name nesting_event_type
13+
username event_name nesting_event_type
14+
user1 stage/sql/System lock STATEMENT
15+
user1 stage/sql/init STATEMENT
16+
user1 stage/sql/optimizing STATEMENT
17+
user1 stage/sql/executing STATEMENT
18+
user1 stage/sql/Opening tables STATEMENT
19+
user1 stage/sql/end STATEMENT
20+
user1 stage/sql/query end STATEMENT
21+
user1 stage/sql/closing tables STATEMENT
22+
user1 stage/sql/freeing items STATEMENT
23+
user1 stage/sql/cleaning up STATEMENT
24+
call dump_one_thread('user2');
25+
username event_name sql_text
26+
user2 statement/sql/drop_function drop function test.f1
27+
username event_name nesting_event_type
28+
user2 stage/sql/Waiting for stored function metadata lock STATEMENT
29+
username event_name nesting_event_type
30+
user2 stage/sql/init STATEMENT
31+
select f1();
32+
f1()
33+
1
34+
commit;

‎mysql-test/suite/perfschema/r/stage_mdl_global.result‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
flush tables with read lock;
2+
insert into test.t1 values (1), (2), (3);
3+
call dump_one_thread('user1');
4+
username event_name sql_text
5+
user1 statement/sql/flush flush tables with read lock
6+
username event_name nesting_event_type
7+
username event_name nesting_event_type
8+
user1 stage/sql/init STATEMENT
9+
user1 stage/sql/Waiting for query cache lock STATEMENT
10+
user1 stage/sql/init STATEMENT
11+
user1 stage/sql/query end STATEMENT
12+
user1 stage/sql/closing tables STATEMENT
13+
user1 stage/sql/freeing items STATEMENT
14+
user1 stage/sql/cleaning up STATEMENT
15+
call dump_one_thread('user2');
16+
username event_name sql_text
17+
user2 statement/sql/insert insert into test.t1 values (1), (2), (3)
18+
username event_name nesting_event_type
19+
user2 stage/sql/Waiting for global read lock STATEMENT
20+
username event_name nesting_event_type
21+
user2 stage/sql/init STATEMENT
22+
user2 stage/sql/checking permissions STATEMENT
23+
user2 stage/sql/Opening tables STATEMENT
24+
unlock tables;

‎mysql-test/suite/perfschema/r/stage_mdl_procedure.result‎

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
drop function if exists test.f1;
2+
drop procedure if exists test.p1;
3+
create function test.f1() returns int
4+
begin
5+
call test.p1();
6+
return 1;
7+
end
8+
$$
9+
create procedure test.p1()
10+
begin end;
11+
begin;
12+
select test.f1();
13+
test.f1()
14+
1
15+
drop procedure test.p1;
16+
call dump_one_thread('user1');
17+
username event_name sql_text
18+
user1 statement/sql/select select test.f1()
19+
username event_name nesting_event_type
20+
username event_name nesting_event_type
21+
user1 stage/sql/executing STATEMENT
22+
user1 stage/sql/Opening tables STATEMENT
23+
user1 stage/sql/query end STATEMENT
24+
user1 stage/sql/closing tables STATEMENT
25+
user1 stage/sql/Opening tables STATEMENT
26+
user1 stage/sql/end STATEMENT
27+
user1 stage/sql/query end STATEMENT
28+
user1 stage/sql/closing tables STATEMENT
29+
user1 stage/sql/freeing items STATEMENT
30+
user1 stage/sql/cleaning up STATEMENT
31+
call dump_one_thread('user2');
32+
username event_name sql_text
33+
user2 statement/sql/drop_procedure drop procedure test.p1
34+
username event_name nesting_event_type
35+
user2 stage/sql/Waiting for stored procedure metadata lock STATEMENT
36+
username event_name nesting_event_type
37+
user2 stage/sql/init STATEMENT
38+
select test.f1();
39+
test.f1()
40+
1
41+
commit;
42+
drop function test.f1;

‎mysql-test/suite/perfschema/r/stage_mdl_table.result‎

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,37 @@
1+
begin;
2+
insert into test.t1 values (1), (2), (3);
3+
commit;
4+
begin;
5+
select * from test.t1 for update;
6+
a
7+
1
8+
2
9+
3
10+
alter table test.t1 add column (b int);
11+
call dump_one_thread('user1');
12+
username event_name sql_text
13+
user1 statement/sql/select select * from test.t1 for update
14+
username event_name nesting_event_type
15+
username event_name nesting_event_type
16+
user1 stage/sql/optimizing STATEMENT
17+
user1 stage/sql/statistics STATEMENT
18+
user1 stage/sql/preparing STATEMENT
19+
user1 stage/sql/executing STATEMENT
20+
user1 stage/sql/Sending data STATEMENT
21+
user1 stage/sql/end STATEMENT
22+
user1 stage/sql/query end STATEMENT
23+
user1 stage/sql/closing tables STATEMENT
24+
user1 stage/sql/freeing items STATEMENT
25+
user1 stage/sql/cleaning up STATEMENT
26+
call dump_one_thread('user2');
27+
username event_name sql_text
28+
user2 statement/sql/alter_table alter table test.t1 add column (b int)
29+
username event_name nesting_event_type
30+
user2 stage/sql/Waiting for table metadata lock STATEMENT
31+
username event_name nesting_event_type
32+
user2 stage/sql/init STATEMENT
33+
user2 stage/sql/checking permissions STATEMENT
34+
user2 stage/sql/checking permissions STATEMENT
35+
user2 stage/sql/init STATEMENT
36+
user2 stage/sql/Opening tables STATEMENT
37+
commit;

0 commit comments

Comments
 (0)