| 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; |