File: innodb_defrag_concurrent.test

package info (click to toggle)
mariadb-10.1 10.1.45-0%2Bdeb9u1
  • links: PTS, VCS
  • area: main
  • in suites: stretch
  • size: 476,916 kB
  • sloc: cpp: 1,124,656; ansic: 871,843; perl: 52,917; sh: 40,078; pascal: 35,370; javascript: 15,555; yacc: 14,728; ruby: 8,684; xml: 5,377; sql: 3,490; makefile: 2,934; python: 1,970; java: 1,691; asm: 837; lex: 757; php: 22; sed: 16
file content (122 lines) | stat: -rw-r--r-- 3,495 bytes parent folder | download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
--source include/have_innodb.inc
--source include/big_test.inc
--source include/not_valgrind.inc
--source include/not_embedded.inc

--disable_warnings
DROP TABLE if exists t1;
--enable_warnings

--disable_query_log
let $innodb_defragment_n_pages_orig=`select @@innodb_defragment_n_pages`;
let $innodb_defragment_stats_accuracy_orig=`select @@innodb_defragment_stats_accuracy`;
--enable_query_log

select @@global.innodb_stats_persistent;
set global innodb_defragment_stats_accuracy = 80;

# Create table.
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), c INT, KEY second(a, b),KEY third(c)) ENGINE=INNODB;

connect (con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
connect (con2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
connect (con3,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
connect (con4,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);

connection default;

SET @@global.innodb_defragment_n_pages = 20;

let $data_size = 20000;
let $delete_size = 2000;

# Populate table.
let $i = $data_size;
--disable_query_log
while ($i)
{
  eval
    INSERT INTO t1 VALUES ($data_size + 1 - $i, REPEAT('A', 256), $i);
  dec $i;
}
--enable_query_log

select count(*) from t1; 
select count(*) from t1 force index (second);
select count(*) from t1 force index (third);

# Delete some data
--disable_query_log
let $size = $delete_size;
while ($size)
{
    let $j =  100 * $size;
    eval delete from t1 where a between $j - 20 and $j;
    dec $size;
}
--enable_query_log

select count(*) from t1; 
select count(*) from t1 force index (second);
select count(*) from t1 force index (third);

# Above delete will free some pages and insert causes page split and these could cause defrag
select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and  stat_name  in ('n_pages_freed');
select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and  stat_name  in ('n_page_split');
select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and  stat_name  in ('n_leaf_pages_defrag');

connection con1;
--send optimize table t1;

connection default;
--send INSERT INTO t1 VALUES (400000, REPEAT('A', 256),300000);

connection con2;
--send INSERT INTO t1 VALUES (500000, REPEAT('A', 256),400000);

connection con3;
--send DELETE FROM t1 where a between 1 and 100;

connection con4;
--send UPDATE t1 SET c = c + 1 where c between 2000 and 8000;

connection con1;
--disable_result_log
--reap
--enable_result_log

connection con2;
--reap

connection con3;
--reap

connection con4;
--reap

connection default;
--reap

disconnect con1;
disconnect con2;
disconnect con3;
disconnect con4;

optimize table t1;

select count(*) from t1;
select count(*) from t1 force index (second);
select count(*) from t1 force index (third);

# Now pages are freed
select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and  stat_name  in ('n_pages_freed');
select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and  stat_name  in ('n_page_split');
select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and  stat_name  in ('n_leaf_pages_defrag');

drop table t1;

# reset system
--disable_query_log
EVAL SET GLOBAL innodb_defragment_n_pages = $innodb_defragment_n_pages_orig;
EVAL SET GLOBAL innodb_defragment_stats_accuracy = $innodb_defragment_stats_accuracy_orig;
--enable_query_log