MySQL之优化服务器设置(三)

优化服务器设置

InnoDB表空间

InnoDB把数据保存在表空间内，本质上是一个由一个或多个磁盘文件组成的虚拟文件系统。InnoDB用表空间实现很多功能，并不只是存储表和索引。它还保存了回滚日志(旧版本行)、插入缓冲(Insert Buffer)、双写缓冲(Doublewrite Buffer),以及其他内部数据结构。配置表空间。通过innodb_data_file_path配置项可以定制表空间文件。这些文件都放在innodb_dta_home_dir指定的目录下。这是一个例子:

innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G

这里再三个为文件中创建了3GB的表空间。有时人们并不清楚可以使用多个文件分散驱动器的负载，像这样:

innodb_data_file_path = /disk1/ibdata1:1G;/disk2/ibdata2:1G;....

在这个例子中，表空间确实放在代表不同驱动器的不同目录中，InnoDB把这些文件首尾相连组合起来。因此，通常这种方式并不能获得太多收益。InnoDB先填满第一个文件，当第一个文件满了再用第二个文件，如此循环；负载并没有按照希望的高性能方式分布。用RAID控制器是分布负载更聪明的方式。为了允许表空间再超过了分配的空间时还能增长，可以像这样配置最后一个文件自动扩展:

...ibdata3:1G:autoextend

默认的行为是创建单个10MB的自动扩展文件。如果让文件可以自动扩展，那么最好给表空间大小设置一个上线，别让它扩展得太大，因为一旦扩展了，就不能收缩回来。例如，下面得例子限制了自动扩展文件最多到2GB:

...ibdata3:1G:autoextend:max:2G

管理一个单独得表空间可能有点麻烦，尤其是如果它是自动扩展的，并且希望回收空间时(因为这个原因，我们建议关闭自动扩展功能，至少设置一个合理的空间范围)。回收空间唯一的方式是导出数据，关闭MySQL,删除所有文件，修改配置，重启，让InnoDB创建新的数据文件，然后导入数据。InnoDB这种表空间管理方式很让人头疼——不能简单地删除文件或者改变大小。如果表空间损坏了，InnoDB会拒绝启动。对日志文件也一样的严格。如果像MyISAM一样随便移动文件，千万要谨慎。
innodb_file_per_table选项让InnoDB为每张表使用一个文件，MySQL4.1和之后的版本都支持。它在数据字典存储为"表明.ibd"的数据。这使得删除一张表时回收空间简单多了，并且可以容易地分散表到不同的磁盘上。然而，把数据放到多个文件，总体来说可能导致更多的空间浪费，因为把单个InnoDB表空间的内部碎片浪费分布到了多个.ibd文件，对于非常小的表，这个问题更大，因为InnoDB的页大小是16KB.即使表只有1KB的数据，仍然需要至少16KB的磁盘空间。即使打开innodb_file_per_table选项，依然需要为回滚日志和其他系统数据创建共享表空间。没有把所有数据存在其中是明智的做法，但最好还是关闭它的自动增长，因为无法在不重新导入全部数据的情况下给共享表空间瘦身。一些人喜欢使用innodb_file_per_table，只是因为特别容易管理，并且可以看到每个表的文件。例如，可以通过查看文件的大小来确认表的大小，这比用SHOW TABLE STATUS来看快多了，这个命令需要执行很多复杂的工作来判断给一个表分配了多少页面。设置innodb_file_per_table也有不好的一面:更差的DROP TABLE性能。这可能足以导致显而易见的服务器端阻塞。因为由如下两个原因:

• 1.删除表需要从文件系统层去掉(删除)文件，这可能在某些文件熊(ext3，说的就是你)上会很慢。可以通过欺骗文件系统来缩短这个过程:把.ibd文件链接欸到一个0字节的文件，然后手动删除这个文件，而不用等待MySQL来做。
• 2.当打开这个选项，每张表都在InnoDB中使用自己的表空间。结果时，移除表空间实际上需要InnoDB锁定和扫描缓冲池，查找术语这个表空间的页面，在一个有庞大的缓冲池的服务器上做这个操作是非常慢的。如果打算删除很多InnoDB表(包括临时表)并且用了innodb_file_per_table，可能会从Percona Server包含的一个修复中获益，他可以让服务器慢慢地清理掉术语被删除的页面。只需要设置innodb_lazy_drop_table这个选项。

什么是最终的建议？我们建议使用innodb_file_per_table并且给共享表空间设置大小范围，这样可以过得舒服点(不用处理那些空间回收的事)。如果遇到人恶化头痛的场景，就像上面说的，考虑用下Percona的那个修复。提醒一下，事实上没有必要把InnoDB文件放在传统的文件系统上，像许多的传统数据库服务器一样，InnoDB提供使用裸设备的选项——例如，一个没有格式化的分区——作为它的存储。然而，今天的文件系统已经可以存放足够大的文件，所以经没有必要使用这个选项。使用裸设备可能提升几个百分点的性能，但是我们不认为这点小提升足以抵消这样做带来的坏处，我们不能直接使用文件管理数据。当把数据存在一个裸设备分区时，不能使用mv、cp或其他任何工具来操作它。最终，这点小的性能收益显然不值得。

innod_file_per_table默认是打开的

行的旧版本和表空间

在一个写压力大的环境下，InnoDB的表空间可能增长得非常大。如果事务保持打开状态很久(即使它们没有做任何事)，并且使用默认得REPETABLE READ事务隔离级别，InnoDB将不能删除旧得行版本，因为没提交得事务依然需要看到它们。InnoDB把旧版本存在共享表空间，所以如果有更多的数据在更新，共享表空间会持续增长。有时这个问题并非是没有提交的事务的原因，也可能是工作负载的问题:清理过程只有一个线程处理，直到最近的MySQL版本才改进，这可能导致清理线程处理速度跟不上旧版本行数增加的速度。
无论发生何种情况，SHOW INNODB STATUS的数据都可以帮助定位问题。查看历史链表的长度会显示了回滚日志的大小，以页为单位。分析TRANSACTIONS部分的第一行和第二行可以证实这个观点，这部分展示了当前事务号以及清理线程完成到了哪个点。如果这个表差距很大，可能有大量的没有清理的事务。这有个例子:

---------
TRANSACTIONS
---------
Trx id counter 0 80157601
Purge done for trx's n:o < 0 80154573 undo n:o < 0 0

事务标识是一个64比特的数字，由两个32比特的数字(在更新版本的InnoDB中这是一个十六进制的数组)组成，所以需要这一点数学计算来计算差距。在这个例子中旧很简单了，因为最高为是0:那么由80 157 601 - 80 154 573 = 3028个"潜在的"没有被清理的事务。我们说"潜在的"，是因为这跟很多没有清理的行是由很大区别的。只有改变了数据的事务才会创建旧版本的行，但是有很多事务并没有修改过数据(相反的，一个事务也可能修改很多行)
如果有个很大的回滚日志并且表空间因此增长得很快，可以强制MySQL减速来使InnoDB的清理线程可以跟得上。这听起来不怎么样，但是没办法。否则，InnoDB将保持数据写入，填充磁盘直到最后磁盘空间饱满，或者表空间大于定义的上线。
为了控制写入速度，可以设置innodb_max_purge_lag变量为一个大于0的只。这个只标识InnoDB开始延迟后面的语句更新数据之前，可以等待被清除的最大的事务数量。你必须直到工作负载以决定一个合理的只。例如事务平均影响1KB的行，并且可以容许表空间里有100MB的未清理行，那么可以设置这个值为100 000.牢记，没有清理的行版本会对所有的查询产生影响，因为它们事实上使得表和索引更大了。如果清理线程确实跟不上，性能可能显著地下降。设置innodb_max_purge_lag变量也会降低性能，但是它的伤害较少。在更新版本的MySQL中，甚至在更早版本的Percona Server和MariaDB，清理过程已经显著地提升了性能，并且从其他内部工作任务中分离出来。甚至可以创建多个专用地清理线程来更快地做这个后台工作。如果可以利用这些特性，会比限制服务器地服务能力要好得多