mysql cluster 運用disk data storage 的測試

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的大小要合理計算,根據具體應用設置大小

Tags: