Skip to content

Commit 9baeab7

Browse files
AliSQLAliSQL
AliSQL
authored and
AliSQL
committed
[perf] Issue#12 OPTIMIZE INNODB READ-ONLY WORKLOAD
Description: ------------ These path introduces several improvement for InnoDB read-only workload: 1. Remove trx_sys_t::ro_trx_list, all transaction will start with read only mode, and change to rw mode while encountering DML statement. For more details, refer to mysql worklog WL#6047. 2. Read view object is cached for read-only statement if no rw statement happens during two read only statements of the same session. 3. Ignore acquiring lock_sys mutex contention while commiting read only transaction.
1 parent 4a68815 commit 9baeab7

30 files changed

Lines changed: 1193 additions & 394 deletions

‎mysql-test/suite/innodb/r/perf_innodb_ro_trx_list_remove_issue12.result‎

Lines changed: 264 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,264 @@
1+
call mtr.add_suppression("prepared XA transaction");
2+
create table t1 (a int, b int, primary key (a)) engine = innodb;
3+
insert into t1 values (1,2),(2,3);
4+
set session tx_isolation = 'READ-COMMITTED';
5+
begin;
6+
select * from t1 where a = 1;
7+
a b
8+
1 2
9+
insert into t1 values (3,4);
10+
commit;
11+
set session tx_isolation = 'REPEATABLE-READ';
12+
begin;
13+
select * from t1 where a = 3;
14+
a b
15+
3 4
16+
insert into t1 values (4,5);
17+
commit;
18+
set session tx_isolation = 'SERIALIZABLE';
19+
begin;
20+
select * from t1 where a = 4;
21+
a b
22+
4 5
23+
insert into t1 values (5,6);
24+
commit;
25+
set session tx_isolation = 'READ-COMMITTED';
26+
select * from t1 where a = 5;
27+
a b
28+
5 6
29+
update t1 set b = b+1 where a = 5;
30+
set session tx_isolation = 'REPEATABLE-READ';
31+
select * from t1 where a = 5;
32+
a b
33+
5 7
34+
update t1 set b = b+1 where a = 5;
35+
set session tx_isolation = 'SERIALIZABLE';
36+
select * from t1 where a = 5;
37+
a b
38+
5 8
39+
update t1 set b = b+1 where a = 5;
40+
set session tx_isolation = 'READ-COMMITTED';
41+
select * from t1 where a = 4;
42+
a b
43+
4 5
44+
select * from t1 where a = 5;
45+
a b
46+
5 9
47+
set session tx_isolation = 'REPEATABLE-READ';
48+
select * from t1 where a = 4;
49+
a b
50+
4 5
51+
select * from t1 where a = 5;
52+
a b
53+
5 9
54+
set session tx_isolation = 'SERIALIZABLE';
55+
select * from t1 where a = 4;
56+
a b
57+
4 5
58+
select * from t1 where a = 5;
59+
a b
60+
5 9
61+
set session tx_isolation = 'READ-COMMITTED';
62+
set session tx_isolation = 'READ-COMMITTED';
63+
begin;
64+
select * from t1 where a = 5;
65+
a b
66+
5 9
67+
insert into t1 values (6,7);
68+
begin;
69+
select * from t1 where a = 5;
70+
a b
71+
5 9
72+
select * from t1 where a = 6;
73+
a b
74+
commit;
75+
select * from t1 where a = 6;
76+
a b
77+
select * from t1 where a = 5;
78+
a b
79+
5 9
80+
commit;
81+
set session tx_isolation = 'REPEATABLE-READ';
82+
set session tx_isolation = 'REPEATABLE-READ';
83+
begin;
84+
select * from t1 where a = 5;
85+
a b
86+
5 9
87+
insert into t1 values (7,8);
88+
begin;
89+
select * from t1 where a = 5;
90+
a b
91+
5 9
92+
select * from t1 where a = 7;
93+
a b
94+
commit;
95+
select * from t1 where a = 7;
96+
a b
97+
select * from t1 where a = 5;
98+
a b
99+
5 9
100+
commit;
101+
set session tx_isolation = 'READ-COMMITTED';
102+
set session tx_isolation = 'READ-COMMITTED';
103+
begin;
104+
update t1 set b=b+1 where a = 1;
105+
update t1 set b=b+1 where a = 5;
106+
begin;
107+
update t1 set b=b+1 where a = 3;
108+
update t1 set b=b+1 where a = 3;;
109+
update t1 set b=b+1 where a = 5;
110+
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
111+
commit;
112+
commit;
113+
begin;
114+
update t1 set b=b+1 where a = 5;
115+
begin;
116+
update t1 set b=b+1 where a = 1;
117+
update t1 set b=b+1 where a = 3;
118+
update t1 set b=b+1 where a = 3;;
119+
update t1 set b=b+1 where a = 5;
120+
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
121+
commit;
122+
commit;
123+
handler t1 open;
124+
handler t1 read first;
125+
a b
126+
1 4
127+
handler t1 read next;
128+
a b
129+
2 3
130+
handler t1 close;
131+
create table t2 (a int) engine = innodb;
132+
alter table t2 add column b int;
133+
alter table t2 add primary key (a);
134+
alter table t2 add column c int;
135+
alter table t2 add key (c);
136+
rename table t2 to t2_tmp;
137+
rename table t2_tmp to t2;
138+
alter table t2 discard tablespace;
139+
drop table t2;
140+
create temporary table tmp(a int) engine = innodb;
141+
set session tx_read_only = 1;
142+
begin;
143+
insert into tmp values (1);
144+
insert into tmp values (2);
145+
commit;
146+
set session tx_read_only = 0;
147+
drop temporary table tmp;
148+
CREATE TABLE fts_t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) ENGINE = InnoDB;
149+
INSERT INTO fts_t1 VALUES
150+
('MySQL has now support', 'for full-text search'),
151+
('Full-text indexes', 'are called collections'),
152+
('Only MyISAM tables','support collections'),
153+
('Function MATCH ... AGAINST()','is used to do a search'),
154+
('Full-text search in MySQL', 'implements vector space model');
155+
begin;
156+
select * from fts_t1 where MATCH(a,b) AGAINST ("collections");
157+
a b
158+
Full-text indexes are called collections
159+
Only MyISAM tables support collections
160+
select * from fts_t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION);
161+
a b
162+
Only MyISAM tables support collections
163+
Full-text indexes are called collections
164+
MySQL has now support for full-text search
165+
Full-text search in MySQL implements vector space model
166+
commit;
167+
begin;
168+
select * from fts_t1 where MATCH(a,b) AGAINST ("collections");
169+
a b
170+
Full-text indexes are called collections
171+
Only MyISAM tables support collections
172+
delete from fts_t1;
173+
commit;
174+
drop table fts_t1;
175+
xa begin '123';
176+
insert into t1 values (8,9);
177+
xa end '123';
178+
xa prepare '123';
179+
xa commit '123';
180+
begin;
181+
update t1 set b=b+1 where a = 2;
182+
update t1 set b=b+1 where a = 3;
183+
update t1 set b=b+1 where a =4;
184+
begin;
185+
select * from t1 where a =3;
186+
a b
187+
3 6
188+
commit;
189+
commit;
190+
show variables like 'innodb_autoinc_lock_mode';
191+
Variable_name Value
192+
innodb_autoinc_lock_mode 0
193+
create table tt_autoinc(a int auto_increment primary key, b int);
194+
insert into tt_autoinc values (NULL,1);
195+
insert into tt_autoinc values (NULL,1);
196+
begin;
197+
select * from tt_autoinc order by a;
198+
a b
199+
1 1
200+
2 1
201+
insert into tt_autoinc values (NULL,1);
202+
delete from tt_autoinc;
203+
commit;
204+
drop table tt_autoinc;
205+
create database sbs;
206+
use sbs;
207+
create table t1 (a int);
208+
drop database sbs;
209+
use test;
210+
begin;
211+
update t1 set b=b+1 where a = 5;
212+
savepoint first_sp;
213+
update t1 set b=b+1 where a = 6;
214+
rollback to savepoint first_sp;
215+
update t1 set b=b+1 where a = 6;
216+
commit;
217+
begin;
218+
update t1 set b=b+1 where a = 5;
219+
begin;
220+
select * from t1 where a = 3;
221+
a b
222+
3 7
223+
select trx_state from information_schema.innodb_trx;
224+
trx_state
225+
RUNNING
226+
RUNNING
227+
commit;
228+
commit;
229+
set session tx_isolation = 'REPEATABLE-READ';
230+
start transaction with consistent snapshot;
231+
select * from t1 where a = 3;
232+
a b
233+
3 7
234+
update t1 set b=b+1 where a = 3;
235+
commit;
236+
set session tx_isolation = 'READ-COMMITTED';
237+
create table sb_part(a int) engine = innodb partition by hash(a) partitions 5;
238+
rename table sb_part to sb_part_tmp;
239+
rename table sb_part_tmp to sb_part;
240+
drop table sb_part;
241+
show session variables like 'innodb_table_locks';
242+
Variable_name Value
243+
innodb_table_locks ON
244+
set session innodb_table_locks = 1;
245+
set autocommit = 0;
246+
lock tables t1 write;
247+
unlock tables;
248+
set autocommit = 1;
249+
xa begin '123';
250+
update t1 set b=b+1 where a = 2;
251+
xa end '123';
252+
xa prepare '123';
253+
# Crash right after flushing binary log
254+
SET SESSION DEBUG="+d,crash_after_flush_binlog";
255+
BEGIN;
256+
update t1 set b=b+1 where a = 5;
257+
COMMIT;
258+
ERROR HY000: Lost connection to MySQL server during query
259+
# Restart the master server
260+
xa recover;
261+
formatID gtrid_length bqual_length data
262+
1 3 0 123
263+
xa commit '123';
264+
drop table t1;

‎mysql-test/suite/innodb/t/perf_innodb_ro_trx_list_remove_issue12-master.opt‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
--query_cache_size=1M
2+
--query_cache_type=1
3+
--innodb_autoinc_lock_mode=0
4+
--binlog-format=row

0 commit comments

Comments
 (0)