92 lines
3.1 KiB
Plaintext
92 lines
3.1 KiB
Plaintext
-- source include/have_ndb.inc
|
|
|
|
CREATE TABLE t1 (
|
|
pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
|
|
attr1 VARCHAR(1024),
|
|
attr2 VARBINARY(1024),
|
|
attr3 TEXT
|
|
) ROW_FORMAT=DYNAMIC ENGINE=ndbcluster CHARACTER SET latin1;
|
|
|
|
CREATE UNIQUE INDEX ui on t1(pk,attr1,attr2);
|
|
|
|
#
|
|
# insert 10000 records into table, turn up autoincrement_prefetch_sz
|
|
# to avoid that a new autoincrement value has to be fetched for
|
|
# every insert
|
|
#
|
|
set @val = "0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF";
|
|
set @val2 = 0x0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF;
|
|
let $1=1000;
|
|
set ndb_autoincrement_prefetch_sz = 256;
|
|
disable_query_log;
|
|
while ($1)
|
|
{
|
|
insert into t1(attr1, attr2, attr3)
|
|
values (@val, @val2, @val),
|
|
(@val, @val2, @val),
|
|
(@val, @val2, @val),
|
|
(@val, @val2, @val),
|
|
(@val, @val2, @val),
|
|
(@val, @val2, @val),
|
|
(@val, @val2, @val),
|
|
(@val, @val2, @val),
|
|
(@val, @val2, @val),
|
|
(@val, @val2, @val);
|
|
dec $1;
|
|
}
|
|
enable_query_log;
|
|
|
|
# Check that table contains 10000 rows
|
|
select count(*) from t1;
|
|
|
|
# Turn off caching for information_schema, otherwise values like
|
|
# the 'data_length' value will be cached and only updated
|
|
# after ANALYZE TABLE
|
|
SET SESSION information_schema_stats_expiry=0;
|
|
|
|
# Read data lenght before deletes
|
|
let $data_length0= `select data_length from information_schema.tables
|
|
where table_name = 't1'`;
|
|
#
|
|
# delete some rows
|
|
#
|
|
delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
|
|
delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
|
|
delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
|
|
delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
|
|
delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
|
|
delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
|
|
delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
|
|
delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
|
|
delete from t1 where mod(pk, 100) < 75 order by pk limit 1000;
|
|
select count(*) from t1;
|
|
select count(*) from t1 where mod(pk, 100) < 75;
|
|
|
|
# remember data_length before optimize
|
|
let $data_length= `select data_length from information_schema.tables
|
|
where table_name = 't1'`;
|
|
|
|
set ndb_optimization_delay = 0;
|
|
optimize table t1;
|
|
|
|
# read data_length after optimize
|
|
let $data_length2= `select data_length from information_schema.tables
|
|
where table_name = 't1'`;
|
|
|
|
# calculate optimization level
|
|
let $opt_level = `select 100*($data_length-$data_length2)/$data_length`;
|
|
|
|
# check that optimization level is greater than 30
|
|
if (`select $opt_level < 30`)
|
|
{
|
|
echo data_length0: $data_length0;
|
|
echo data_length: $data_length;
|
|
echo data_length: $data_length2;
|
|
echo opt_level: $opt_level;
|
|
die Too little optimization achieved;
|
|
}
|
|
echo optimize worked ok!;
|
|
|
|
drop table t1;
|
|
SET SESSION information_schema_stats_expiry=default;
|