Innodb Log写入方式分析

发表于2010年08月19日由tom_fans

作者/译者：王佳隆　来源:　http://www.mysqlsupport.cn/ 联系方式：enjoylonely#live.cn 转载请注:译者和出处，并且不能用于商业用途，违者必究.

原文URL：http://www.mysqlperformanceblog.com/2010/07/16/analyzing-the-distribution-of-innodb-log-file-writes/

最近我分析了一下Innodb是如何写多个日志的。我这里有个流量比较高的MySQL系统，使用的是Percona XtraDB存储引擎，

我使用strace命令分别跟踪了innodb如何去日志文件的。通常来说，innodb是以512bytes的大小来写入日志的。

关于这个可以参考：Mark Callaghan explained this and some of its performance implications 。那么innodb什么时候情况下会以大于512bytes者小于512bytes的请求写到日志里呢？

首先，我通过lsof命令找出日志的文件描述符(handle).

# lsof -p $(pidof mysqld) | grep ib_log
mysqld  29772 mysql    8uW  REG                8,2   268435456   7143989 /var/lib/mysql/ib_logfile0
mysqld  29772 mysql    9uW  REG                8,2   268435456   7143993 /var/lib/mysql/ib_logfile1

我们可以看到2个日志的handle为8和9，现在我们需要捕获innodb是如何写这2个日志的相关信息。Innodb轮循写日志就是通过这个文件描述符.

The following grabs the write sizes out of 100k calls to pwrite() and aggregates them：

# strace -f -p $(pidof mysqld) -e pwrite -s1 -xx 2>&1 \

| grep ‘pwrite([89],’ |head -n 100000 \

| awk ‘{writes[$5]++}END{for(w in writes){print w, ” “, writes[w]}}’

本来我可以写一个更好的脚本来捕获信息，但是下面的信息对我来说已经足够了。

bytes        count
512           44067
1024         30740
1536         15221
2048         7094
2560         1810
3072         570
3584         219
4096         112
4608         39
5120         23
5632         16
6144         15
6656         5
7168         3
7680         8
8192         2
8704         2
9216         1
9728         2
10240       1
10752       2
11264       1
11776       1
14848       1
15360       1
15872       2
16384       4
16896       4
17408       2
17920       2
18432       2
18944       8
19456       7
19968       4
20480       4
21504       1
22016       2
24064       1
40960       1

总的来说，大致有3/4是以512和1024字节写入的。那么这到底什么意思呢？这里有很多有趣的而且复杂的东西需要我们去研究。

1. 我们可以看到，大部分的写入都小于4K。但是我们知道操作系统的page大小是4K的。如果要写入的page不在cache中，那么这个page最开始需要读出，然后再修改，最后再写回到磁盘。vadim曾经过做做一些测试，如果要性能最好，那么日志要在OS的cache中。

2.这台MySQL的innodb_flush_log_at_trx_commit设置是2.这就说明每个事务都会有一个日志写入请求，这个和设置为1是一样的。但是如果设置成0，那么写入的方法就大不相同—这个时候写入请求会累计到一定程度，然后一起写入。

3.那么如果说log buffer小于一个事务要写的日志大小怎么办？这是另外一个需要研究的主题了。目前我还不清楚。

4.从上面的信息来看，是否可以很容易知道log buffer大小应该给多少呢？最大的写入小于40K，这好像可以说明分配64K给log buffer就已经足够了。这是真的吗？我们需要去测试才可以知道。peter以前和我谈过这个问题，log buffer背后的机制其实是很复杂的，到底log buffer需要保留多少空间给写操作。这些都需要更深入的研究。但是有一点可以确认，即使最大的写入操作不是很大的情况下，如果log buffer设置太小，性能肯定是不好的，而且会造成而外的锁。这个问题或许我们同样需要去研究。

最后，研究innodb是如何写日志的很容易，但是事实上innodb redo log机制是很复杂的，有时候我们很难说去猜想应该是什么样的，而应该去更深入的研究才可以知道的更多。也许我们可以按照上面这种步骤去研究不同LOG buufer大小，不同的日志参数设置，以及不同的服务器负载的情况下innodb到底是如何来写入日志的。

参考地址：http://mysqlha.blogspot.com/2009/06/buffered-versus-direct-io-for-innodb.html

Innodb 表和索引结构

发表于2010年01月20日由吴炳锡

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究.

表的结构：

对于MySQL把有的存储引擎都是把表结构的定义存放到.frm文件中。但对于Innodb表同时有一个内部的字典存放到表空间中。所以对于Innodb表不能单纯的移动.frm在不同的MySQL事例下。对于Innodb引擎的表，如果MySQL 删除相应的表或数据库，同时会删除相应的.frm及在表空间的相应的字典信息。在.frm文件只是用来定义表的结构，Innodb把数据和索引都存放到了表空间中。

聚集索引和次要索引：

每一个Innodb表都有一个聚集索引，这个聚信索引和行数据存在一起。

可以用来做聚集索引的列：

如果有声明了主建(primary key)，则这个列可以做为表的主建。
如果没有声明主建，MySQL会用一个唯一索引(UNIQUE)而且是不为空的列做为主建，成为该表的聚信索引。
如果没声明主建，同时也没合适的唯一的索引，Innodb内部会产生一个隐藏的聚集索引:RowID。这个RowID在Innodb的多版本中曾提到过。这个RowID是在插入时产生，并且是自增的增加。所以也是按顺序增长存放。

由于聚集索引和行数据存放一起(在同一个数据页中)，所以利用聚集索引访问数据行时，非常的快，同一个数据页在访问索引时，已经把页加载到Buffer中，在访问数据时，等于了一个顺序IO的访问（内存中完成）。大多数情况下索引和数据都不在一块（MyISAM，数据和索引存到不同的文件中），而聚集索引是有结构的通常是按顺序存放，同时和数据存放在一起，利用索引索引访问大表的数据可以节省许多IO。

对于Innodb次要的索引会包含聚信索引，查询在使用次要索引时，找到聚集索引信息，然后利用聚集索引信息访问行。所以，如果聚集索引过长，会造成空间浪费严重。另外，如果对表或是区间进行Count操作的话，大多数情况较短的次要索引比基于聚集索引快。对于Innodb的聚集索引选择，尽量选择比较短的列做为聚集索引列，是一个好的设计习惯。

索引的物理结构：

Innodb的索引以B-tree的形式存到各个叶点上。索引叶点页的大小默认为16K，当有什么的索引插入叶点时，该叶点至少会保留1/16的空闲空间，用于将来该叶点的索引更新或是插入。

对于顺序写入的索引（无论是递增或是递减，顺序的就行），索引叶点可以达到15/16满。如果是随机的索引写入行为，叶点只会达到1/2到15/16满。当叶点填充在1/2以下满，或是被删除到1/2下满时，Innodb会缩短索引树，试图释放该叶点，该叶点可以被继续写入数据。

设计中的Tips：

因为Innodb表的数据是依赖于聚集索引顺序存放，同时聚集索引和数据一块存储，普通索引也需要存放一份聚集索引。所以对于聚集索引的设计尽量按顺序写入，必免数据分页，行迁移等对性能影响的现象。另外聚集索引要设计的尽可能短。从设计上必须锁的时间，大量随机IO的出现。
如对于监控（或是股票类的信息）可以利用时间和类型构成聚集索引，让相关性高的数据尽可能位到一块。以便读取时可以利用顺序IO读取到相应的数据。最好的情况，相关性高的数据在一个Page上，这样读取的效果更好。基于Innodb聚集索引的特性，在设计上也需要考虑利用一下优势，必免其不好的一方面从而达到最佳性能。

Innodb 多版本实现

发表于2010年01月5日由吴炳锡

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究

Innodb是一个多版本的存储引擎，它可以把旧的行信息存到表空间中。这些旧的行信息存储到Innodb称为的回滚段的表空间中。

Innodb为实现多版本，Innodb在每一行添加了三个列。一个6字节的DB_TRX_ID字段用来表示事务的Insert或是Update操作，对于Delete操作实际上也并不在直接删除，只是用一个Bit位去标识行被删除。另外，每行包括7字节的DB_ROLL_PTR字段，称为回滚指针（roll pointer）。这个回滚指针指向回滚段（undo segment）中的回滚记录。如果行被更新，那么回滚段中记录的信息足以使update操作加到update操作之间。最后还有一个6字节的DB_ROW_ID字段，该字段包含新行的Row Id，这个字段只在Insert操作时单纯的增加。DB_ROW_ID是需要一个互诉锁的才能产生。Innodb产生的clustered index包括Row ID.从另一方面来说，除了Clustered Index索引外，其它的索引不会包含Row Id.

Innodb利用回滚段保存的信息完成事务的回滚。同样是为了读取早的版本行信息，通过回滚段中的信息达到一致性读。

回滚段中的回滚日值可以区分为insert和update日值。Insert的回滚日值只需要一个事务回的信息，记录Insert的操作的事务就行，该回滚信息在事务提交后被丢弃。Update的回滚日值不但用来撤销事务，同时也为一致性读服务。在事务操作中，别的Session可以通过Update的回滚段信息达构建早期的版本，从来达到一致性读。

对于Innodb的多版本是为达到一致性读，我们在使用Innodb时要养成一个习惯：要规律的提交我们的事务。另一方面，对于Innodb的回滚段中Update的回滚日值不能随着事务的提交而被丢弃，所以回滚段有可能增长很大，填满所有的表空间。

回滚段的需求的物理大小通常比Insert和Update的行小的多，所以我们可以根据Insert，Update行的并发量来估算分配回滚段的大小。

在Innodb的多版本设计中，Delete语句并不是直接物理的从数据中立即删除相应的行，只是做一个Bit位的标识。另外当Innodb删除相应的原行和行的索引信息时，回滚段中此行的Update回滚日值才会被清除。对于Delete操作的的删除我们称为静化（purge）,这个操作是很快速的，正常情况下静化在SQL语句后按一定的顺序去执行删除操作。

假设一个场景：当一个用户用小的批量插入和删除行操作在一个表，它有可能会造成静化操作的进程落后，从而造成表的增长的很大很大，使的磁盘的工作效率比较低了。这样就会出现恶劣的情况既使表只有该表只有10M的数据，也有可能使表空间增长到10G，当然里面有很多是即将过静化掉的行(dead row).基于这个原因，静化进程会造成新的行操作和分配资源的性能瓶颈。所以也要关注一下innodb_max_purge_lag这个参数的设定是不是合适。

Innodb 文件表空间结构

发表于2009年12月15日由吴炳锡

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

      Innodb的表空间是在配置文件中定义（说是表空间有时觉的有点羞愧，和Oracle比真的差太远了），这里简单列一下表空间里的基本概念及表的分配情况。
       表空间是在配置文件中定义的几个文件简单的耦合起来，在使用中互不可少（少一个就面临DB完蛋的危险）。对于共享表空间无法确定表所在的表空间上。
      独立表空间可以做到每个表有自已的表空间（羞一下）。
       针对共享表空间，表空间中包括：回滚段,段（segment），区域（extent),数据页(page size)在表空间的体现为：
表空间由默认16k的数据页面（page)组成，每64个连续的页面组成一个区域（extent,Oracle里熟悉的一个东东）。对于表空间的“文件(file)”在Innodb中被称为段(segment)。回滚段(rollback segment）是一个特殊的例子，实际上rollback segment包含了多个段。对于Innodb表的索引都被分配成两个段：一个是为了 B-tree 的无叶结点(non-leaf nodes)，另一个是为了叶结点(leaf nodes)。
这是为了达到包含数据的叶结点的更好的顺序(sequentiality for the leaf nodes)。
       当表空间中的一个段增长时，InnoDB 为它个别地分配最初的 32 个页面。之后 InnoDB 再分配段的整个区域(extents)。InnoDB 会以每次 4 个区域(extents)来增加一个大段以确保数据的良好顺序。
         表空间中的某些页面包含其它页面的位图(bitmaps)，所以在 InnoDB 表空间内的一些区域(extents)不能以一个整体分配给段，而只能作为个体页面。
          当发出一个查询 SHOW TABLE STATUS FROM … LIKE … 来询问表空间的剩余空间时，InnoDB 将报告表空间中所有空闲区域(extents)中确实可用的部分。InnoDB 通常会保留一些区域用于 clean-up 和其它的内部目的；这些保留的区域并不包含在剩余可用空间中。

当从一个表中删除数据时，InnoDB 将收缩 B-tree 中相应的索引。这是依赖于释放个别的页面或区域(extents)以让其他用户使用剩余空间的删除模式。移除(drop)一个表或删除所有记录可以保证释放空间给其他用户，但是删除记录行只有在事务回滚或 consistent read 后并不需要时才会被物理的移除

对于独立表空间也是存一样的概念和行为，唯一区别就是每个表的数据存到指定的表空间中，rollback segment不和数据的segment在一个竞争。使用独立表空间的一个好处就是可以使数据分布相对于磁盘上更连续一点。

更改Innodb 数据页大小优化MySQL

发表于2009年12月13日由吴炳锡

     作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。
         我们知道Innodb的数据页是16K,而且是一个硬性的规定，系统里没更改的办法，希望将来MySQL也能也Oracle一样支持多种数据页的大小。
但实际应用中有时16K显的有点大了，特别是很多业务在Oracle或是SQL SERVER运行的挺好的情况下迁到了MySQL上发现IO增长太明显的情况下，
就会想到更改数据页大小了。
　　实际上innodb的数据页大小也是可以更改的，只是需要在源码层去更改，然后重新rebuild一下MySQL.
    更改办法：
    (以MySQL-5.1.38源码为例）
    位置在storage/innobase/include/univ.i ，在univ.i中查找：UNIV_PAGE_SIZE

/*
   DATABASE VERSION CONTROL
   ========================
*/

/* The universal page size of the database */
#define UNIV_PAGE_SIZE          (2 * 8192) /* NOTE! Currently, this has to be a
     power of 2 */
/* The 2-logarithm of UNIV_PAGE_SIZE: */
#define UNIV_PAGE_SIZE_SHIFT 14

/* Maximum number of parallel threads in a parallelized operation */
#define UNIV_MAX_PARALLELISM 32

UNIV_PAGE_SIZE就是数据页大小，默认的是16K. 后面的备注里标明，该值是可以设置必须为2的次方。对于该值可以设置成4k,8k,16k,32K,64K，在大也没意义了。
同时更改了UNIV_PAGE_SIZE后需要更改 UNIV_PAGE_SIZE_SHIFT 该值是2的多少次方为UNIV_PAGE_SIZE，所以设置数据页分别情况如下：

#define UNIV_PAGE_SIZE_SHIFT 12  if UNIV_PAGE_SIZ=4K
#define UNIV_PAGE_SIZE_SHIFT 13  if UNIV_PAGE_SIZ=8K
#define UNIV_PAGE_SIZE_SHIFT 15  if UNIV_PAGE_SIZ=32K

例子：
　更改innodb的数据页为8K,相应修改为：

/*
   DATABASE VERSION CONTROL
   ========================
*/

/* The universal page size of the database */
#define UNIV_PAGE_SIZE          8192   /* NOTE! Currently, this has to be a
     power of 2 */
/* The 2-logarithm of UNIV_PAGE_SIZE: */
#define UNIV_PAGE_SIZE_SHIFT 13

/* Maximum number of parallel threads in a parallelized operation */
#define UNIV_MAX_PARALLELISM 32

重新编译，然后测试测试，再测试。Good luck!

Innodb如何使用内存

发表于2009年12月12日由吴炳锡

　作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

来源：http://www.mysqlperformanceblog.com/2006/05/30/innodb-memory-usage/

译这个文章的目的：
　　最近经常被问起Innodb是如何使用内存的。该问题早已被原MySQL公司的Vadim论证过。我这里译一下他的文章供大家参考。
开始：
　　这里有许多关于Innodb如何使用内存的问题。我这里将会以innodb启动时的分配情况做一个解释。一些重要的概念：
　　NBLOCKS＝Innodb_buffer_pool有多个页(block)＝innodb_buffer_pool_size/16384(16k)
　OS_THREADS= if ( innodb_buffer_pool_size >= 1000Mb) = 50000
　　else if (innodb_buffer_pool_size >= 8Mb) = 10000
　　 else = 1000 (该值只用在*nixes系统上，对于Windows有一点小的区别计算OS_THREADS)

所以Innodb 使用的内存包括：
　innodb_buffer_pool_size
    innodb_additional_mem_pool_size
    innodb_log_buffer_size
    adaptive index hash ,size (innodb buffer 索引管理区)= innodb_buffer_pool_size/64
    system dictionary hash,size(innodb内部字典区) = 6 * innodb_buffer_pool_size/512
    memory for sync_array,size(用于Innodb内部syncronzation的开销)=OS_THREAD * 512
    memory for os_event,size(用于innodb内存的syncronzation的开销)=OS_THREAD * 216
    memory for locking system(内存的锁管理系统),size = 5 * 4 *NBBLOCKS

最终得到innodb内存使用的计算公式为：
     Innodb_buffer_pool_size + innodb_log_buffer_size + innodb_additional_mem_pool_size + 812/16384 * innodb_buffer_pool_size + OS_THREADS * 368
对于812/16384 * Innodb_buffer_pool_size 可以简单的用 innodb_buffer_pool_size / 20　计算，

对于OS_THREADS * 368　　
OS_THREADS * 368 = 17.5 MB if innodb_buffer_pool_size > 1000MB
OS_THREADS * 368 = 3.5 MB if innodb_buffer_pool_size > 8MB

举一个例子：
　　如果你的innodb_buffer_pool_size有1500MB,innodb_additional_mem_pool_size =20 MB,innodb_log_buffer_size = 8M,
　　Innodb 将会向系统申请内存为= 1500M + 20M + 8M + 1500/20 M +17.5 = 1620.5M

　　根据以上的条件可以算出Innodb最根本最需要多少内存，这样对于服务器的内存使用也可以有一个规划了。

Innodb共享表空间VS独立表空间

发表于2009年10月12日由吴炳锡

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式：select unhex(’777562696E67786940676D61696C2E636F6D’); 载请注明作/译者和出处，并且不能用于商业用途，违者必究。

在使用Innodb引擎时将要面对两种表空间的管理选择的问题，Innodb有两种管理表空间的方法：

1．共享表空间（也可以拆分成多个小的表空间）

2．独立表空间每一个表有一个独立的表空间。

我个人推荐使用独立表空间。在性能和运维上独立表空间比共享的表空间有很多优势。下面我将分别说明一下两种表空间管理的特点。

共享表空间：

优点：

可以放表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同步的文件上）。数据和文件放在一起方便管理。

缺点：

所有的数据和索引存放到一个文件中以为着将有一个很常大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。

我们知道共享表空间管理会出现表空间分配后不能回缩的问题，当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了。我们存在磁盘监控时，也许就报警不断了，但实际上MySQL还可以运行良好。另外，当磁盘上占用较多时性能也不是太好。

这种情况处理只能是是建一个新的Slave从主库上Dump出来，然后在Dump到从库中，动作较大。

对于InnoDB Hot Backup备份的操作（或是直接冷备），每次需要CP的文件比较大。如果现在有180G的表空间，但实际数据只有50多G，那么我们将面对每次需要拷180G的数据。

这种方式也许mysqldump是一个好的处理方式了。

独立表空间：

在配置文件（my.cnf）中设置： innodb_file_per_table

优点：

1．每个表都有自已独立的表空间。

2．每个表的数据和索引都会存在自已的表空间中。

3．可以实现单表在不同的数据库中移动。

4．空间可以回收（除drop table操作处，表空不能自已回收）

a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。

b) 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

c) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

缺点：

单表增加过大，如超过100个G。

对于单表增长过大的问题，如果使用共享表空间可以把文件分开，但有同样有一个问题，如果访问的范围过大同样会访问多个文件，一样会比较慢。对于独立表空间也有一个解决办法是：使用分区表，也可以把那个大的表空间移动到别的空间上然后做一个连接。其实从性能上出发，当一个表超过100个G有可能响应也是较慢了，对于独立表空间还容易发现问题早做处理。

备份：

InnoDB Hot Backup（冷备）的表空间cp不会面对很多无用的copy了。而且利用innodb hot backup及表空间的管理命令可以实现单现移动。

监控：

可以更好从系统上监控数据的大小，每个表的大小。

另外推荐使用独立表空间的原因：

从性能上对比共享表空间和独立表空间：

共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。这里也有一个TIPS当启用独立表空间时，请合理调整一下：innodb_open_files 。

从Linux系统处理上出发：

文件系统fsync一大片更新数据，对系统io冲击较大。若分隔成多个小数据fsync，能够减少对读的影响。同时从mysql代码，发现mysql保证两次fsync之间至少有20ms的sleep，这样的话，若将一次fsync变成多次小数据操作，应该能够减少慢查询的比例。所以对于大量更新操作的系统不太适合用共享表空间。

MySQL Innodb 引擎优化（二）

发表于2009年05月6日由吴炳锡

机器篇

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

背景：

Innodb 是什么的介绍请参考　MySQL Innodb　引擎优化（一）我有时在讲选择机器上时，好多朋友会觉的MySQL嘛，随便弄一个机器就行了。又不象Oracle之类的安装要求内存必须超过５１２Ｍ什么之类的。但要想让机器跑出一秒中上千Query的效果，机器选择方面还是要待认真对待一下。另一方面做为数据库，到真正的生产中大家绝对要求是稳定高性能了，怎么达到这些要求呢？当然基本建设上一定要跟上，弄一个稳定的高性能的机器是首要条件。当然这里有一个并发力度和数据量总大小和单表大小有很大的关系。例如：并发不大，数量小或是总量比较大，但最大的表没超过１Ｇ，一般的类CMS网站,那对于机器的选择要求真的是不大了；另一方面，如果能预见自已的数据量增长较快，并发强度大，数据增长也快的情况下，选择Ｉｎｎｏｄｂ引擎是一个明智的选择；或是现在的业务已经出现了性能瓶颈进行优化时，也可以比较一下各个引擎的特点。

机器选择包括：

1、硬盘选择

2、内存选择

3、cpu选择

4、系统选择

5、整架构的规划和调整　

　
１、硬盘选择：

空间的大小：这个根据自已数据量，增长情况，项目预算到那个阶段来决定。Raid级别：根据业务情况选择Raid10，Raid5推荐用Raid10对数据库系统Raid10绝对有优势，Ｒａｉｄ５适合读比较多的业务系统。不过在在充许的情况，还是Ｒａｉｄ１０是最佳的选择以空间换时间，从而加快速度。在Raid的选择上，一定要选择好的Ｒａｉｄ卡，大牌子还是可信的。免的出现很多莫名其妙的问题。Ｒａｉｄ卡没电，加电过程机器性能下降严重，数据文件损坏等现象。让项目受到损失。推荐Raid卡缓存在256K-1M的。一定要用烂牌了Ｒａｉｄ卡。成本可能的情况：选择市场上最快的磁盘，根据业务考虑自已的业务的读写比例有所取舍，必竟现在出来了一个ＳＳＤ硬盘，在硬盘的选择上又多了一个选择。最其码建义也是单块的ＳＡＳ硬盘。
2、内存选择：

根据数据量来决定。现在数据库机器内存最少也４个Ｇ了，其码要不要低于这个吧。便于你的机器能在项目中抗一段吧。建义用８Ｇ内存以上。
3、CPU选择：

选择６４位的ＣＰＵ，缓存要大的。Ｉｎｎｏｄｂ对ＣＰＵ的利用比较高。不用担心ＣＰＵ浪费。
4、系统选择:

这个也是比较重要的。一定要选择６４位的系统，６４位的ＭｙＳＱＬ。另一方面确认系统的ＩＯ模式，DIRECT模式是一个不错的ＩＯ模式。线程库支持情况。MySQL是一个多线程的ＤＢ，所以对线程库要求比较高，基于这点Linux,Solaris都是一个不错的选择。Linux要选择kernel 2.6.X的内核。再可能的情况下可以禁用ＤＢ服务器的Ｓｗａｐ分区，从而必免系统瞬时不稳定的现象。MySQL大量的查询都是短查询，所以可以禁掉Ｓｗａｐ。如果是线下数据分悉，还是起用Ｓｗａｐ吧。不同系统的分区性能也是不一样的。对于Ｌｉｎｕｘ来说推荐用ＸＦＳ，在数据量大的情况下可以提高性能1.5倍以上。
5、整架构的规划和调整　

另外在选择机器上时，还要考虑到我一次能用多少机器呢？如只有一台，那就尽配置的最强，如果可以上多台，那就可以根据是主库或是从库，读写多少这类来配置不同的机器。如果机器的并发严重，写入量也大的情况下，可以适高的考虑用强的机器做Ｓｌａｖｅ，从而从使Slave能追上Master。

总结：

在机器上的选择上，特别是对ＤＢ应用，一定要选择可靠的机器。在上线使用前一定要深入测试，上线测试，多观查，根据自已的业务情况来选择合适的机器。同样也要本着最低成本投入，最大收益的原则来做。

Mysql Innodb 引擎优化(-)

发表于2009年05月6日由吴炳锡

参数篇

作/译者：吴炳锡，来源：http://imysql.cn/　＆　http://www.mysqlsupport.cn　转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

介绍：　
　 InnoDB给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。　Innodb 的创始人：Heikki Tuuri　Heikki Tuuri在Ｉｎｎｏｄｂ的Ｂｕｇ社区里也是很活跃的，如果遇到Ｂｕｇ也可以直接提到社区，得到作者的解答。

为什么要学习Ｉｎｎｏｄｂ的调优：　　
目前来说：InnoDB是为Mysql处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。在数据量大的网站或是应用中Innodb是倍受青睐的。　　另一方面，在数据库的复制操作中Innodb也是能保证master和slave数据一致有一定的作用。

参数调优内容：
　　1. 内存利用方面
　　2. 日值控制方面
　　3.文件IO分配，空间占用方面
　　4. 其它相关参数

１.内存利用方面：
首先介绍一个Innodb最重要的参数：innodb_buffer_pool_size　　这个参数和MyISAM的key_buffer_size有相似之处，但也是有差别的。这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲。为Innodb加速优化首要参数。　　该参数分配内存的原则：这个参数默认分配只有8M，可以说是非常小的一个值。如果是一个专用ＤＢ服务器，那么他可以占到内存的70%-80%。这个参数不能动态更改，所以分配需多考虑。分配过大，会使Swap占用过多，致使Mysql的查询特慢。如果你的数据比较小，那么可分配是你的数据大小＋１０％左右做为这个参数的值。
例如：数据大小为５０Ｍ,那么给这个值分配innodb_buffer_pool_size＝６４Ｍ
设置方法：innodb_buffer_pool_size=4G这个参数分配值的使用情况可以根据show innodb status\G;中的
———————-BUFFER POOL AND MEMORY———————-
Total memory allocated 4668764894;

去确认使用情况。

第二个：innodb_additional_mem_pool：
作用：用来存放Innodb的内部目录这个值不用分配太大，系统可以自动调。不用设置太高。
通常比较大数据设置16Ｍ够用了，如果表比较多，可以适当的增大。如果这个值自动增加，会在error log有中显示的。

分配原则：用show innodb status\G;去查看运行中的ＤＢ是什么状态
（参考BUFFER POOL AND MEMORY段中），然后可以调整到适当的值。

———————-BUFFER POOL AND MEMORY———————-

Total memory allocated 4668764894; in additional pool allocated 16777216

参考：in additional pool allocated 16777216根据你的参数情况，可以适当的调整。

设置方法：innodb_additional_mem_pool=16M

２.关于日值方面：innodb_log_file_size作用：
指定日值的大小分配原则：几个日值成员大小加起来差不多和你的innodb_buffer_pool_size相等。上限为每个日值上限大小为4G.一般控制在几个ＬＯＧ文件相加大小在２Ｇ以内为佳。具体情况还需要看你的事务大小，数据大小为依据。说明：这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系。设置方法：innodb_log_file_size=256M

innodb_log_files_in_group作用：
指定你有几个日值组。
分配原则：　一般我们可以用２－３个日值组。默认为两个。
设置方法：innodb_log_files_in_group=3

innodb_log_buffer_size：

作用：事务在内存中的缓冲。分配原则：控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。在Ｏｒａｃｌｅ等数据库了解这个，一般最大指定为３Ｍ比较合适。
参考：Innodb_os_log_written(show global status 可以拿到)如果这个值增长过快，可以适当的增加innodb_log_buffer_size另外如果你需要处理大理的ＴＥＸＴ，或是ＢＬＯＢ字段，可以考虑增加这个参数的值。

设置方法：innodb_log_buffer_size=3M

innodb_flush_logs_at_trx_commit
作用：控制事务的提交方式分配原则：这个参数只有３个值，０，１，２请确认一下自已能接受的级别。默认为１，主库请不要更改了。性能更高的可以设置为０或是２，但会丢失一秒钟的事务。说明：这个参数的设置对Ｉｎｎｏｄｂ的性能有很大的影响，所以在这里给多说明一下。当这个值为1时：innodb 的事务LOG在每次提交后写入日值文件，并对日值做刷新到磁盘。这个可以做到不丢任何一个事务。当这个值为2时：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新,在对日志文件的刷新在值为2的情况也每秒发生一次。但需要注意的是，由于进程调用方面的问题，并不能保证每秒１００％的发生。从而在性能上是最快的。但操作系统崩溃或掉电才会删除最后一秒的事务。当这个值为0时：日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。mysqld进程的崩溃会删除崩溃前最后一秒的事务。
从以上分析，当这个值不为１时，可以取得较好的性能，但遇到异常会有损失，所以需要根据自已的情况去衡量。
设置方法：innodb_flush_logs_at_trx_commit=1

3.　文件IO分配，空间占用方面innodb_file_per_table

作用：使每个Innodb的表，有自已独立的表空间。如删除文件后可以回收那部分空间。分配原则：只有使用不使用。但ＤＢ还需要有一个公共的表空间。设置方法：innodb_file_per_table=1
innodb_file_io_threads作用：文件读写ＩＯ数，这个参数只在Windows上起作用。在LINUX上只会等于４设置方法：innodb_file_io_threads=4
innodb_open_files作用：限制Innodb能打开的表的数据。分配原则：如果库里的表特别多的情况，请增加这个。这个值默认是３００。设置方法：innodb_open_files=800 请适当的增加table_cache

4. 其它相关参数这里说明一个比较重要的参数：innodb_flush_method

作用：Ｉｎｎｏｄｂ和系统打交道的一个ＩＯ模型分配原则：Windows不用设置。Ｕｎｉｘ可以设置：fsync() or O_SYNC/O_DSYNC如果系统可以禁止系统的Ｃａｃｈｅ那就把他禁了。Ｌｉｎｕｘ可以选择：O_DIRECT 直接写入磁盘，禁止系统Ｃａｃｈｅ了设置方法：innodb_flush_method=O_DIRECT
innodb_max_dirty_pages_pct 作用：控制Innodb的脏页在缓冲中在那个百分比之下，值在范围1-100,默认为90.这个参数的另一个用处：当Ｉｎｎｏｄｂ的内存分配过大，致使Ｓｗａｐ占用严重时，可以适当的减小调整这个值，使达到Ｓｗａｐ空间释放出来。建义：这个值最大在９０％，最小在１５％。太大，缓存中每次更新需要致换数据页太多，太小，放的数据页太小，更新操作太慢。设置方法：innodb_max_dirty_pages_pct＝90动态更改需要有Ｓｕｐｅｒ权限：set global innodb_max_dirty_pages_pct=50;