mysql cluster 运用disk data storage 的测试

今天做了一个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的大小要合理计算,根据具体应用设置大小.