Debian Codegraph @

mysql-test/suite/ndb/t/ndb_optimize_table.test

raw
1 -- source include/have_ndb.inc
2
3 CREATE TABLE t1 (
4 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
5 attr1 VARCHAR(1024),
6 attr2 VARBINARY(1024),
7 attr3 TEXT
8 ) ROW_FORMAT=DYNAMIC ENGINE=ndbcluster CHARACTER SET latin1;
9
10 CREATE UNIQUE INDEX ui on t1(pk,attr1,attr2);
11
12 #
13 # insert 10000 records into table, turn up autoincrement_prefetch_sz
14 # to avoid that a new autoincrement value has to be fetched for
15 # every insert
16 #
17 set @val = "0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF";
18 set @val2 = 0x0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF;
19 let $1=1000;
20 set ndb_autoincrement_prefetch_sz = 256;
21 disable_query_log;
22 while ($1)
23 {
24 insert into t1(attr1, attr2, attr3)
25 values (@val, @val2, @val),
26 (@val, @val2, @val),
27 (@val, @val2, @val),
28 (@val, @val2, @val),
29 (@val, @val2, @val),
30 (@val, @val2, @val),
31 (@val, @val2, @val),
32 (@val, @val2, @val),
33 (@val, @val2, @val),
34 (@val, @val2, @val);
35 dec $1;
36 }
37 enable_query_log;
38
39 # Check that table contains 10000 rows
40 select count(*) from t1;
41
42 # Turn off caching for information_schema, otherwise values like
43 # the 'data_length' value will be cached and only updated
44 # after ANALYZE TABLE
45 SET SESSION information_schema_stats_expiry=0;
46
47 # Read data lenght before deletes
48 let $data_length0= `select data_length from information_schema.tables
49 where table_name = 't1'`;
50 #
51 # delete some rows
52 #
53 delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
54 delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
55 delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
56 delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
57 delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
58 delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
59 delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
60 delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
61 delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
62 select count(*) from t1;
63 select count(*) from t1 where mod(pk, 100) < 75;
64
65 # remember data_length before optimize
66 let $data_length= `select data_length from information_schema.tables
67 where table_name = 't1'`;
68
69 set ndb_optimization_delay = 0;
70 optimize table t1;
71
72 # read data_length after optimize
73 let $data_length2= `select data_length from information_schema.tables
74 where table_name = 't1'`;
75
76 # calculate optimization level
77 let $opt_level = `select 100*($data_length-$data_length2)/$data_length`;
78
79 # check that optimization level is greater than 30
80 if (`select $opt_level < 30`)
81 {
82 echo data_length0: $data_length0;
83 echo data_length: $data_length;
84 echo data_length: $data_length2;
85 echo opt_level: $opt_level;
86 die Too little optimization achieved;
87 }
88 echo optimize worked ok!;
89
90 drop table t1;
91 SET SESSION information_schema_stats_expiry=default;