mysql cluster 运用disk data storage 的测试

时间：2007-04-18 来源：wysilly

今天做了一个mysql cluster中用disk data storage的测试,通过对比,确实能够大幅减少内存的使用. 百万条小记录.测试过程如下. 1.未使用disk data storage前.测试脚本文件nodiskdata.test --disable_warnings
drop table if exists t1,t2,t3;
--enable_warnings
create table t1(id1 int not null auto_increment primary key, t char(12));
create table t2(id2 int not null, t char(12));
create table t3(id3 int not null, t char(12), index(id3));
disable_query_log;
let $1 = 100;
while ($1)
{
let $2 =100 ;
eval insert into t1(t) values ('$1');
while ($2)
   {
     eval insert into t2(id2,t) values ($1,'$2');
     let $3 = 100;
     while ($3)
     {
       eval insert into t3(id3,t) values ($1,'$2');
       dec $3;
     }
     dec $2;
   }
dec $1;
}
enable_query_log; 运行 #mysqltest < nodiskdata.test #ndb_mgm -e "all dump 1000"
#tail /var/lib/mysql-cluster/ndb_1_cluster.log 看到 Data usage increased to 83%(2133 32K pages of total 2560) Index usage is 61%(1894 8K pages of total 3104) 2.用了disk data storage后.测试脚本文件diskdata.test --disable_warnings
drop table if exists t1,t2,t3;
--enable_warnings
create logfile group lg_1
        add undofile 'undo_1.dat'
        initial_size 32M
        undo_buffer_size 8M;
create tablespace ts_1
        add datafile 'data_1.dat'
        use logfile group lg_1
        initial_size 64M;
create table t1(id1 int not null auto_increment primary key, t char(12)) tablespace ts_1 storage disk;
create table t2(id2 int not null, t char(12)) tablespace ts_1 storage disk;
create table t3(id3 int not null, t char(12), index(id3)) tablespace ts_1 storage disk;
disable_query_log;
let $1 = 100;
while ($1)
{
let $2 =100 ;
eval insert into t1(t) values ('$1');
while ($2)
   {
     eval insert into t2(id2,t) values ($1,'$2');
     let $3 = 100;
     while ($3)
     {
       eval insert into t3(id3,t) values ($1,'$2');
       dec $3;
     }
     dec $2;
   }
dec $1;
}
enable_query_log; 运行 #mysqltest < diskdata.test #ndb_mgm -e "all dump 1000"
#tail /var/lib/mysql-cluster/ndb_1_cluster.log 看到 Data usage is 74%(1896 32K pages of total 2560)
Index usage is 61%(1894 8K pages of total 3104) 3.从我所建的表中,索引列占用的内存很多,所以data usage下降不明显,但是仔细计算,可以发现非索引列确实达到了移出内存,使用磁盘的目的. 4.最后,logfile,tablespace的大小要合理计算,根据具体应用设置大小.