[Mysql] 关于 Innodb 和 NDB 的一些事

作为一个准 PHPer，今天必须要写的第一句话一定是： “Happy birthday to PHP ” ~ 第十五个年头了，PHP 发展至今仍然能占据最流行语言 TOP4 的位置，确实令不少人惊异不已，但是这就是 PHP 独特的魅力，真心希望他能更好的发展下去，期待 PHP6 给我们带来惊喜；顺便查了一下 PHP 的好伙伴 Mysql 的生日，让我吃惊的是今年他居然已经 31 岁了，呵呵~ 真是老当益壮啊~ 好吧，今天就来说说 Mysql 的一些 “八卦” 吧~

 

对于目前的 Mysql 的境遇来说，真可谓是命运多舛，但是他的功能之强大家应该都深有体会了吧，其实我一直认为 PHP 能有今天的地位，Mysql 这位 “老大哥” 的支持绝对功不可没~ 而 Innodb 应该算是 Mysql Engine 庞大的家族中最令人称道的主力军了吧，目前估计 80% 的 Mysql 服务器应该都在用，支持事务是 Innodb 的最大特点，其他的事就先不介绍了。以下收集了 Innodb 配置参数的详细解释，我想应该对大家比较有帮助：

 

innodb_data_home_dir	这是InnoDB表的目录共用设置。如果没有在 my.cnf 进行设置，InnoDB 将使用MySQL的 datadir 目录为缺省目录。如果设定一个空字串,可以在 innodb_data_file_path 中设定绝对路径。
innodb_data_file_path	单独指定数据文件的路径与大小。数据文件的完整路径由 innodb_data_home_dir 与这里所设定值的组合。 文件大小以 MB 单位指定。因此在文件大小指定后必有“M”。 InnoDB 也支持缩写“G”， 1G = 1024M。从 3.23.44 开始，在那些支持大文件的操作系统上可以设置数据文件大小大于 4 GB。而在另一些操作系统上数据文件必须小于 2 GB。数据文件大小总和至少要达到 10 MB。在 MySQL-3.23 中这个参数必须在 my.cnf 中明确指定。在 MySQL-4.0.2 以及更新版本中则不需如此，系统会默认在 MySQL 的 datadir 目录下创建一个 16 MB 自扩充(auto-extending)的数据文件 ibdata1。你同样可以使用一个 原生磁盘分区(RAW raw disk partitions(raw devices)) 作为数据文件， 如何在 my.cnf 中详细指定它们请查看第 12.1 节。
innodb_mirrored_log_groups	为了保护数据而设置的日志文件组的拷贝数目，默认设置为 1。在 my.cnf 中以数字格式设置。
innodb_log_group_home_dir	InnoDB 日志文件的路径。必须与 innodb_log_arch_dir 设置相同值。 如果没有明确指定将默认在 MySQL 的 datadir 目录下建立两个 5 MB 大小的 ib_logfile... 文件。
innodb_log_files_in_group	日志组中的日志文件数目。InnoDB 以环型方式(circular fashion)写入文件。数值 3 被推荐使用。在 my.cnf 中以数字格式设置。
innodb_log_file_size	日志组中的每个日志文件的大小(单位 MB)。如果 n 是日志组中日志文件的数目，那么理想的数值为 1M 至下面设置的缓冲池(buffer pool)大小的 1/n。较大的值，可以减少刷新缓冲池的次数，从而减少磁盘 I/O。但是大的日志文件意味着在崩溃时需要更长的时间来恢复数据。 日志文件总和必须小于 2 GB，3.23.55 和 4.0.9 以上为小于 4 GB。在 my.cnf 中以数字格式设置。
innodb_log_buffer_size	InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。 因此，如果有大的事务处理，设置大的日志缓冲可以减少磁盘I/O。 在 my.cnf 中以数字格式设置。
innodb_flush_log_at_trx_commit	通常设置为 1，意味着在事务提交前日志已被写入磁盘， 事务可以运行更长以及服务崩溃后的修复能力。如果你愿意减弱这个安全，或你运行的是比较小的事务处理，可以将它设置为 0 ，以减少写日志文件的磁盘 I/O。这个选项默认设置为 0。
innodb_log_arch_dir	The directory where fully written log files would be archived if we used log archiving. 这里设置的参数必须与 innodb_log_group_home_dir 相同。 从 4.0.6 开始，可以忽略这个参数。
innodb_log_archive	这 个值通常设为 0。 既然从备份中恢复(recovery)适合于 MySQL 使用它自己的 log files，因而通常不再需要 archive InnoDB log files。这个选项默认设置为 0。
innodb_buffer_pool_size	InnoDB 用来高速缓冲数据和索引内存缓冲大小。 更大的设置可以使访问数据时减少磁盘 I/O。在一个专用的数据库服务器上可以将它设置为物理内存的 80 %。 不要将它设置太大，因为物理内存的使用竞争可能会影响操作系统的页面调用。在 my.cnf 中以数字格式设置。
innodb_additional_mem_pool_size	InnoDB 用来存储数据字典(data dictionary)信息和其它内部数据结构(internal data structures)的存储器组合(memory pool)大小。理想的值为 2M，如果有更多的表你就需要在这里重新分配。如果 InnoDB 用尽这个池中的所有内存，它将从操作系统中分配内存，并将错误信息写入 MySQL 的错误日志中。在 my.cnf 中以数字格式设置。
innodb_file_io_threads	InnoDB 中的文件 I/O 线程。 通常设置为 4，但是在 Windows 下可以设定一个更大的值以提高磁盘 I/O。在 my.cnf 中以数字格式设置。
innodb_lock_wait_timeout	在回滚(rooled back)之前，InnoDB 事务将等待超时的时间(单位 秒)。InnoDB 会自动检查自身在锁定表与事务回滚时的事务死锁。如果使用 LOCK TABLES 命令，或在同一个事务中使用其它事务安全型表处理器(transaction safe table handlers than InnoDB)，那么可能会发生一个 InnoDB 无法注意到的死锁。在这种情况下超时将用来解决这个问题。这个参数的默认值为 50 秒。在 my.cnf 中以数字格式设置。
innodb_flush_method	这个参数仅仅与 Unix 相关。这个参数默认值为 fdatasync。 另一个设置项为 O_DSYNC。这仅仅影响日志文件的转储，在 Unix 下以 fsync 转储数据。InnoDB 版本从 3.23.40b 开始，在 Unix 下指定 fdatasync 为使用 fsync 方式、指定 O_DSYNC 为使用 O_SYNC 方式。由于这在某些 Unix 环境下还有些问题所以在 'data' versions 并没有被使用。
innodb_force_recovery	警告：此参数只能在你希望从一个被损坏的数据库中转储 (dump)数据的紧急情况下使用！ 可能设置的值范围为 1 - 6。查看下面的章节 'Forcing recovery' 以了解这个参数的具体含义。参数设置大于 0 的值代表着 InnoDB 防止用户修改数据的安全度。从 3.23.44 开始，这个参数可用。在 my.cnf 中以数字格式设置。
innodb_fast_shutdown	InnoDB 缺少在关闭之前清空插入缓冲。这个操作可能需要几分钟，在极端的情况下可以需要几个小时。如果这个参数据设置为 1 ，InnoDB 将跳过这个过程而直接关闭。从 3.23.44 和 4.0.1 开始，此参数可用。从 3.23.50 开始，此参数的默认值为 1。
innodb_thread_concurrency	InnoDB 会试图将 InnoDB 服务的使用的操作系统进程小于或等于这里所设定的数值。此参数默认值为 8。如果计算机系统性能较低或 innodb_monitor 显示有很多线程等侍信号，应该将这个值设小一点。如果你的计算机系统有很我的处理器与磁盘系统，则可以将这个值设高一点以充分利用你的系统资源。建议设值 为处理器数目+ 磁盘数目。 从 3.23.44 和 4.0.1 开始，此参数可用。在 my.cnf 中以数字格式设置。

 

而接下来介绍一下新的出场嘉宾 “NDB ”，之所以要介绍这个生力军的原因有两个：第一个，对于目前信息爆炸的时代，Mysql 最重要的集群领域是由他来开拓的，而目前他也不孚众望，获得了大家越来越多的关注。第二个，他功能不弱于 Innodb，以目前的情况看起来他极有可能是 Innodb 的未来接班人 （虽然性能方面还有待改进） ~

 

好了，废话不多说，让我们来看看石头在本机上对 NDB 的一些研究成果：

 

1、架设环境：

 

类似于 Oracle 的集群，NDB Cluster 也包含 “节点” 的概念：数据节点(ndbd)，管理节点(mgmd)，服务节点(mysqld)；他们的启动顺序是：mgmd ---> ndbd ---> mysqld。由于客观原因，石头只能先在本机上建立这个模拟的集群环境，但是多机的配置基本也是一样的，具体的步骤如下：

 

首先我们下载最新的：mysql-cluster-gpl-7.1.3-linux-x86_64-glibc23.tar.gz ，解压后安装到 /usr/local/mysql 下，按照 INSTALL-BINARY 中正常的步骤安装，此时应该已近建立 (mysql:mysql) 用户与组，下面会用到。然后尝试使用 support-files/mysql.server start 进行启动，若成功则此步骤完成~

 

然后，建立管理节点配置文件 etc/mgmd.cnf：

 

# Options affecting ndbd processes on all data nodes: [NDBD DEFAULT] NoOfReplicas=1 # Number of replicas DataMemory=80M # How much memory to allocate for data storage IndexMemory=18M # How much memory to allocate for index storage # For DataMemory and IndexMemory, we have used the # default values. Since the "world" database takes up # only about 500KB, this should be more than enough for # this example Cluster setup. # TCP/IP options: [TCP DEFAULT] #portnumber=2202 # This the default; however, you can use any # port that is free for all the hosts in cluster # Note: It is recommended beginning with MySQL 5.0 that # you do not specify the portnumber at all and simply allow # the default value to be used instead # Management process options: [NDB_MGMD] hostname=127.0.0.1 # Hostname or IP address of MGM node ,mgmd's hostname datadir=/usr/local/mysql/datamgmd # Directory for MGM node logfiles PortNumber=2203 #default value: 1186 # Options for data node "A": [NDBD] hostname=127.0.0.1 # Hostname or IP address ,allow to access datadir=/usr/local/mysql/datamgmd # Directory for this data node's datafiles # Options for data node "B": [NDBD] hostname=127.0.0.1 # Hostname or IP address datadir=/usr/local/mysql/datamgmd # Directory for this data node's datafiles [MYSQLD] hostname=127.0.0.1 # Hostname or IP address # (additional mysqld connections can be # specified for this node for various # purposes such as running ndb_restore)

 

接着你可以尝试启动管理节点：bin/ndb_mgmd -f etc/mgmd.cnf，然后可以使用命令：bin/ndb_mgm 127.0.0.1 2203 登录管理节点进行查看，可以看到每个节点的状态 （可使用 ndb_mgm> show 命令查看），当然现在应该都是 “not connected” 状态。

 

接下来建立数据节点 etc/ndbd1.cnf （另外一个节点 etc/ndbd2.cnf 类似）：

 

[mysqld] datadir=/usr/local/mysql/datandbd/1 skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M ndbcluster ndb-connectstring=127.0.0.1:2203 [mysql_cluster] ndb-connectstring=127.0.0.1:2203 // 连接mgmd服务的ip与端口

 

启动可以使用命令：bin/ndbd --defaults-file=ndbd1.cnf --initial （注意第一次需要加上 --initial 参数 ），然后另外一个节点也类似的启动起来，到这里如果你刷新 mgmd 的状态，你可以看到两个节点已经连接上了，并可以显示出他们的状态。

 

大家可以注意到，datadir 可能还没有建立，除了默认的 data/ 目录，我们还建立 datamgmd/ 和 datandbd/1、datandbd/2 目录来存储以上节点的 runtime 文件和 log，这里别忘记了使：

# chown -R mysql:mysql datamgmd/ datandbd/

# chmod -R +w datamgmd/ datandbd/

来给他们附权哦~

 

最后就是配置具体的 mysql 服务节点了，修改 etc/my.cnf （加入 ndb cluster 配置）：

 

[mysqld] port = 3306 socket = /usr/local/mysql/data/mysql.sock datadir = /usr/local/mysql/data/ # ndb settings ndbcluster ndb-connectstring=127.0.0.1:2203 skip-name-resolve skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M innodb_additional_mem_pool_size = 32M innodb_buffer_pool_size = 256M innodb_data_file_path = ibdata1:30M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 0 innodb_flush_log_at_trx_commit = 0 innodb_log_buffer_size = 16M innodb_log_file_size = 64M innodb_log_files_in_group = 3 innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 1

 

重启 mysql.server restart 之后可以看到管理节点中的所有节点都可以看到状态了，登录到 mysql 服务器上，使用：show variables like 'have%'; 命令查看 mysql 参数如下：

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| have_community_features | YES      |
| have_compress           | YES      |
| have_crypt              | YES      |
| have_csv                | YES      |
| have_dynamic_loading    | YES      |
| have_geometry           | YES      |
| have_innodb             | YES      |
| have_ndbcluster         | YES      |
| have_openssl            | DISABLED |
| have_partitioning       | YES      |
| have_query_cache        | YES      |
| have_rtree_keys         | YES      |
| have_ssl                | DISABLED |
| have_symlink            | YES      |
+-------------------------+----------+

 

这就说明安装成功了，接下来你就可以开始正式的 Mysql 集群管理之旅了~

 

2、功能对比：

 

基于目前对 NDB 和 Innodb 的测试看来，NDB 和 Innodb 的使用基本是一样的，ACID （原子性、一致性、隔离性、持久性）实现方式也基本相同，这里简单提一下 Mysql 中的几个隔离级别：

 

1. READ UNCOMMITTED：最低级别的隔离，通常又称为dirty read，它允许一个事务读取还没commit的数据，这样可能会提高性能，但是dirty read可能不是我们想要的 2. READ COMMITTED：在一个事务中只允许已经commit的记录可见，如果session中select还在查询中，另一session此时insert一条记录，则新添加的数据不可见 3. REPEATABLE READ：在一个事务开始后，其他session对数据库的修改在本事务中不可见，直到本事务commit或rollback。在一个事务中重复select的结果一样，除非本事务中update数据库 4. SERIALIZABLE：最高级别的隔离，只允许事务串行执行。为了达到此目的，数据库会锁住每行已经读取的记录，其他session不能修改数据直到前一事务结束，事务 commit或取消时才释放锁

 

具体的使用可以参考：http://hideto.javaeye.com/blog/195275 ，总结的相当不错~

 

但是他们之间有一个很重要的区别：在同一个 transaction 中，如果某一部的执行出错，Innodb 会继续的往下执行，而 NDB 则会马上回滚到最初状态（类似于 SqlServer 中的 SET XACT_ABORT ON 的状态），如果不注意这一点的话，在一些极端情况下，系统将出现数据不一致的情况，所以还是需要大家多多注意哦！

 

测试用例如下（在 test 库中进行测试）：

 

[Innodb 用例]

mysql> create table test_innodb (i int not null primary key) engine innodb;

mysql> begin;

mysql> insert into test_innodb values(1);

mysql> insert into test_innodb values(1); # 主键重复出错

mysql> commit;

mysql> select * from test_innodb;

+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

 

[NDB 用例]

mysql> create table test_ndb (i int not null primary key) engine ndbcluster;

mysql> begin;

mysql> insert into test_ndb values(1);

mysql> insert into test_ndb values(1); # 主键重复出错

mysql> commit;

mysql> select * from test_ndb;

Empty set (0.00 sec)

 

累啊，写了这么多~ 今天就到这里吧~ 后面有时间在添加内容~

To be continued ...