Debian Codegraph @

mysql-test/suite/innodb/t/create_drop_temp_table_perf_1.test

raw
1 #
2 # WL#6469: Optimizing CREATE/DROP performance for temporary tables
3 #
4
5 --source include/no_valgrind_without_big.inc
6
7 #########################################################################
8 # #
9 # Will test following scenarios: #
10 # 1. Vary innodb_change_buffering value for temp-table #
11 #########################################################################
12
13
14 #-------------------------------------------------------------
15 #
16 # create test-bed.
17 #
18 let $change_buffer_value = `select @@global.innodb_change_buffering`;
19 set global innodb_file_per_table = 0;
20
21 #-------------------------------------------------------------
22 #
23 # 1. Vary innodb_change_buffering value
24 #
25 --disable_warnings
26 DROP TABLE IF EXISTS t1,t2;
27 --enable_warnings
28 select @@global.innodb_change_buffering;
29 create temporary table t1
30 (keyc int, c1 char(100), c2 char(100),
31 primary key(keyc)) engine = innodb;
32 create index idx on t1(keyc, c1(20));
33 delimiter |;
34 create procedure populate_t1(IN `rows` INT)
35 begin
36 declare i int default 1;
37 while (i <= `rows`) DO
38 insert into t1 values (i, 'a', 'b');
39 set i = i + 1;
40 end while;
41 end|
42 delimiter ;|
43 set autocommit=0;
44 select count(*) from t1;
45 call populate_t1(1000);
46 select count(*) from t1;
47 select * from t1 limit 10;
48 commit;
49 update t1 set keyc = -1 * keyc where keyc%50 = 0;
50 select count(*) from t1 where keyc < 0;
51 drop table t1;
52
53 # change innodb_change_buffering value to none
54 set @@global.innodb_change_buffering = none ;
55 select @@global.innodb_change_buffering;
56
57 create temporary table t1
58 (keyc int, c1 char(100), c2 char(100),
59 primary key(keyc)) engine = innodb;
60 create index idx on t1(keyc, c1(20));
61 set autocommit=0;
62 select count(*) from t1;
63 CALL populate_t1(1000);
64 select count(*) from t1;
65 select * from t1 limit 10;
66 commit;
67 update t1 set keyc = -1 * keyc where keyc%50 = 0;
68 select count(*) from t1 where keyc < 0;
69 drop table t1;
70 drop procedure if exists populate_t1;
71 eval set global innodb_change_buffering = $change_buffer_value;
72
73 #-------------------------------------------------------------
74 #
75 # remove test-bed.
76 #
77 set global innodb_file_per_table = default;