mysql集群采用磁盘文件存储表

Love The Way You Lie 2022-09-19 00:21 150阅读 0赞

MySQL Cluster 5.1的一个最受期待的特性是集群已支持基于磁盘的数据存取。NDB存储引擎大大增强了MySQL Cluster的性能，该引擎之前是一个100％的内存数据库引擎。这种引擎对可以运行在内存里的数据库来说是极好的选择，现在基于磁盘的存取数据支持使MySQL 5.1集群拓展了数据库规模，使MySQL集群用户有能力创建更大的数据库而且能对其有效的管理。

对于必须具备很高可用性，但不苛求像基于内存数据存储那样的高性能特性的数据，利用磁盘存取的方式将是最好的选择。另外，那些由于操作系统或硬件条件所致的硬性局限，可以考虑移植基于内存存取的库到磁盘存取的库当中，或者在支持磁盘存取数据的MySQL Cluster上开发新的应用。

本文主要关注MySQL Cluster中创建、移植和维护基于磁盘存取的数据表。请切记对基于磁盘存取数据的支持已经在5.1.6bata 版中正式推出。

# 1. 概念 #

MySQL Cluster采用一系列的Disk Data objects来实现磁盘表。   
        Tablespaces：作用是作为其他Disk Data objects的容器。   
        Undo log files：存储事务进行回滚需要的信息，一个或者多个undo log files组成一个log files group，最后，该log file group关联到一个tablespaces。   
        Data files：作用是存储表中的数据，data file直接关联到tablespaces。   
        在每一个数据节点上undo log files和data files都是实际的文件，默认的，存放在ndb\_node\_id\_fs文件夹下，该路径是在MySQL Cluster的config.ini中用DataDir指定的，node\_id是data node的node ID。可以用绝对路径或者相对路径指定undo log或者data file的路径。tablespaces和log file group则不是实际的文件。

# 2. 创建步骤 #

MySQL Cluster创建一个磁盘表需要包含以下几步：   
        1、创建一个log file group，将一个或者多个undo log files关联到它上面(undo log file也叫做 undofile)。注意，undo log file只是在创建磁盘表的时候才需要，创建ndb的内存表的时候不需要。

2、创建一个tablespaces，关联一个log file group和一个或者多个data files到上面。

3、使用该tablespaces创建一个磁盘表存储数据。

下面做一个例子，创建一个基于内存存取的表

通常创建NDB表的时候，只要发出如下的语句，表和索引都会在主存里建立起来，并且已处于就绪状态。

CREATE TABLE table1 (
    col1 int, 
    col2 int, 
    col3 int, 
    col4 int,
    PRIMARY KEY(col1), 
    index(col1,col2)) ENGINE=ndb;

上面语句所建表的属性如下：

col1 和col2 列驻留内存，因为它们是索引的一部分，这一点即使在使用基于磁盘的存储的MySQL 5.1里面也有同样的限制，原因是NDB索引仍然必须全部驻留于内存里。

Col3列和col4列也驻留于内存，不过我们在5.1里面可以改成磁盘存储，通过在MySQL Cluster定义一个表空间，然后把这些列指向表空间实现。

创建一个日志文件组和表空间

如前所述，利用MySQL Cluster的磁盘数据存取新特性时，我们首先进行一些初始步骤准备。这些步骤包括创建日志文件组和表空间。

创建日志文件组时会在每个数据节点上为存储UNDO日志而新建一个文件。（请注意在5.1版中，只能支持一个logfile 组，然而，从理论上讲，以后一个无限制undofiles数量的特性将被支持的。Undo缓冲区的缺省值是8MB。）

CREATE LOGFILE GROUP logfg1 ADD UNDOFILE 'undofile1.dat' 
    INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=ndb;

该语句可能会出错，出现ERROR 1064 (42000)语法错误的信息，原因是字符集的问题，先执行set character\_set\_client=latin1，在执行创建LOGFILE语句。

接下来我们将在每个数据节点上创建一个文件来存储基于磁盘存取表的分区。我们在表空间里面建的表都与一个logfile 组相关联。

CREATE TABLESPACE tsp1 ADD DATAFILE 'datafile1.dat' USE 
    LOGFILE GROUP logfg1 INITIAL_SIZE 12M  ENGINE=ndb;

由于我们已经创建了logfile组和表空间，现在就可以创建第一个基于磁盘存取的表了。

CREATE TABLE table3 (
    pk_col1 int, 
    fname_col2 VARCHAR(24), 
    lname_col3 VARCHAR(255), 
    ssno_col4 int , 
    PRIMARY KEY (pk_col1), 
    INDEX (pk_col1, ssno_col4))
    TABLESPACE tsp1 STORAGE DISK ENGINE=ndb;

上面所建表的属性如下：

pk\_col1列和ssnp\_col4列被保存到内存，因为它们是索引的一部分，这一点前面已有所述，这是源于整个5.1版本MySQL其索引必须存于内存中。

fname\_col2列和lname\_col3列不属于索引，将被保存到磁盘上。

# 3. 磁盘数据管理与维护 #

如果以后某一时候需要给表空间增加数据文件，我们可以用下面的命令完成：

ALTER TABLESPACE tsp1 ADD DATAFILE 'datafile2.dat' INITIAL_SIZE 16M ENGINE=ndb;

添加另一个undofile到logfile组可以通过下面的方法来做：

ALTER LOGFILE GROUP logfg1 ADD UNDOFILE 'undofile2.dat' 
    INITIAL_SIZE 16M ENGINE=ndb;

你也可以给在磁盘上建好的表添加一个索引，具体过程如下：

CREATE TABLE table3 (
    col1 int, 
    col2 int, 
    col3 int, 
    col4 int, 
    PRIMARY KEY (col1))  
    TABLESPACE tsp1 STORAGE DISK ENGINE=ndb;  
    ALTER TABLE table3 ADD INDEX col1and2_index (col1, col2), ENGINE=ndb;

这两步操作将创建适当的索引，并将相应的索引列移到内存，而把其他的，列存于磁盘上。

可以在INFORMATION\_SCHEMA数据库中的FILES表中查看磁盘表的信息。

SELECT
    TABLE_NAME,
    `ENGINE`,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH,
    MAX_DATA_LENGTH,
    INDEX_LENGTH,
    DATA_FREE
    FROM
    TABLES
    WHERE
    TABLE_NAME LIKE 'disktabl%';

![20131208110956484][]

从上面的截图中看出，测试用了两张表，一张是diskTableInDisk表，存储数据在磁盘上，一张是diskTableInMemory表，存数数据在内存中，两张表的结构完全一样，只是表名称不一样，向两张表中插入8000条相同的数据，内存表占用了大约3.2M的内存空间，磁盘表占用了大约1.5M的内存空间，但是磁盘表还占用了20多兆的磁盘空间。磁盘数据表占用磁盘空间大。

关于表空间和logfile组方面需要注意的是，在表空间和logfile组被删除之前，必须先删除表空间和logfile组中的对象。否则将产生与下面相似的结果：

DROP TABLESPACE tsp1 ENGINE=ndb;  
    ERROR 1507 (HY000): Failed to drop TABLESPACE  DROP LOGFILE GROUP logfg1 ENGINE=ndb;  ERROR 1507 (HY000): Failed to drop LOGFILE GROUP

对基于磁盘的集群结构的管理可以简要概括如下：

1、如果相依赖的表空间存在则logfile组不能被删除

2、如果相依赖的数据文件存在则表空间不能被删除

3、如果表空间中相依赖的表存在，则相应的数据文件就不能从表空间中删除。

因此，在开始上述维护操作之前，我们必须先使用如下命令删除数据表。

DROP TABLE table3;

然后删除和表空间相关联的数据文件。

ALTER TABLESPACE tsp1 DROP DATAFILE 'datafile1.dat' ENGINE=ndb;      
    ALTER TABLESPACE tsp1 DROP DATAFILE 'datafile2.dat' ENGINE=ndb;

最后删除表空间和logfile组

DROP TABLESPACE tsp1 ENGINE=ndb; 
    DROP LOGFILE GROUP logfg1 ENGINE=ndb;

# 4. 移植内存表到磁盘表  #

现在我们来看怎么移植一个内存表到一个磁盘表，首先，我们先创建一个简单的内存表：

CREATE TABLE table4 (
    col1 int(5), 
    col2 int(5), 
    col3 int(5), 
    col4 int(5), 
    PRIMARY KEY(col1), 
    INDEX(col1,col2)) ENGINE=ndb;

所建表的属性如下：

col1和col2列由于是索引，被保存到内存。

col3和col4列因为没有定义与之对应的表空间，因此也被保存于内存中。

如前所述，存于磁盘的数据需要有一个表空间和logfile组，因而先来创建这两项：

CREATE LOGFILE GROUP logfg1 ADD UNDOFILE 'undofile1.dat' INITIAL_SIZE 16M  UNDO_BUFFER_SIZE = 1M ENGINE=ndb;
    CREATE TABLESPACE tsp1 ADD DATAFILE 'datafile1.dat' USE LOGFILE GROUP logfg1 INITIAL_SIZE 12M ENGINE ndb;

现在，执行下面的ALTER命令来移植内存表到利用上面定义的表空间的磁盘表。

ALTER TABLE table4 TABLESPACE tsp1 STORAGE DISK ENGINE=ndb;

移植后的表属性如下：

col1和col2列被保存于内存中，因为他们仍是索引的一部分。

col3和col4列将被保存到磁盘，因为他们不属于索引。

参数

以下是当使用基于磁盘的数据时，你应该添加到集群配置文件config.ini的NDBD部分的一些参数。

DiskPageBufferMemory= X

这个参数定义磁盘数据页缓存大小，每一页分配32K bytes。

SharedPoolMemory= X

这个参数设定能被各种资源所使用的空间大小，目前使用共享池的对象包括：表空间、logfile组、数据文件、undo文件、分区信息、log buffer waiter、log sync waiter、页请求、undo 缓冲。

# 5. 磁盘数据存取的注意点 #

1) 磁盘数据表的变长列将占用固定空间，对于每行来说，也就是占用最大空间。

2) 在磁盘数据表中，TEXT和BLOB的前256个字节存于内存，其他的存放在磁盘上。

3) 磁盘数据表的每一行要在内存里面存放8个字节，以指向存放在磁盘上的数据。所以如果一行要存放在磁盘上数据少于8个字节，还不如都放在内存里。

4) 数据节点重启时\--initial选项并不移除磁盘数据文件，你得手工事先把他们干掉。

5) 在每个CREATE LOGFILE GROUP、 ALTER LOGFILE GROUP、CREATE TABLESPACE or ALTER TABLESPACE 语句里面都必须加上一个ENGINE字段，ENGINE的值可以为NDB 和NDBCLUSTER。

参考

[http://wenku.baidu.com/link?url=be8N19vKcJG6tNpPy96v5G2CoiLwKnu8Bmzz2-0xUEyPXTTJP1eVJPj6hVSVrJ6UN3m61i1WZLjNJwvfHoi7kQS9uT63gI7Q9Vbl2BnRYUi][http_wenku.baidu.com_link_url_be8N19vKcJG6tNpPy96v5G2CoiLwKnu8Bmzz2-0xUEyPXTTJP1eVJPj6hVSVrJ6UN3m61i1WZLjNJwvfHoi7kQS9uT63gI7Q9Vbl2BnRYUi]

[http://www.jb51.net/article/31874.htm][http_www.jb51.net_article_31874.htm]

[http://blog.csdn.net/mchdba/article/details/10544585][http_blog.csdn.net_mchdba_article_details_10544585]

[http://blog.csdn.net/chenxingzhen001/article/details/7592100][http_blog.csdn.net_chenxingzhen001_article_details_7592100]

[20131208110956484]: /images/20220828/50fef22a27974119ba3314c2968c5eee.png
[http_wenku.baidu.com_link_url_be8N19vKcJG6tNpPy96v5G2CoiLwKnu8Bmzz2-0xUEyPXTTJP1eVJPj6hVSVrJ6UN3m61i1WZLjNJwvfHoi7kQS9uT63gI7Q9Vbl2BnRYUi]: http://wenku.baidu.com/link?url=be8N19vKcJG6tNpPy96v5G2CoiLwKnu8Bmzz2-0xUEyPXTTJP1eVJPj6hVSVrJ6UN3m61i1WZLjNJwvfHoi7kQS9uT63gI7Q9Vbl2BnRYUi
[http_www.jb51.net_article_31874.htm]: http://www.jb51.net/article/31874.htm
[http_blog.csdn.net_mchdba_article_details_10544585]: http://blog.csdn.net/mchdba/article/details/10544585
[http_blog.csdn.net_chenxingzhen001_article_details_7592100]: http://blog.csdn.net/chenxingzhen001/article/details/7592100