MySQL：性能调优（CPU的选择，内存、磁盘的选择、RAID类型等一些简略的工具介绍）

asdsInnoDB 存储引擎的性能问题：

ddsdss①、选择合适的CPU ddsdss②、内存的重要性 ddsdss③、硬盘对数据库性能的影响 ddsdss④、合理地设置RAID ddsdss⑤、操作系统的选择也很重要 ddsdss⑥、不同文件系统对数据库的影响 ddsdss⑦、选择合适的基准测试工具

asdsadasdasdasdsadasdasdasdsadassdasdsadasdasdsadasdsadassadasdas————《MySQL技术内幕INNODB存储引擎》

ssdss当前数据库的应用类型一般分为两大类：OLTP、OLAP。OLAP 多用在数据仓库或数据集市中，一般需要执行复杂的SQL 语旬来进行查询； OLTP 多用在日常的事物处理应用中，如银行交易、在线商品交易、Blog 、网络游戏等应用。相对于OLAP,数据库的容量较小。

ssdssInnoDB 存储引擎一般都应用于OLTP 的数据库应用：

ssddsss①、用户操作的并发量大

ssddsss②、事务处理的时间一般比较短

ssddsss③、查询的语句较为简单，一般都走索引

ssddsss④、复杂的查询较少

ssddsss OLTP 的数据库应用本身对CPU 的要求并不是很高，因为复杂的查询可能需要执行比较、排序、连接等非常耗CPU 的操作，这些操作在OLTP 的数据库应用中较少发生。因此，可以说 OLAP 是CPU 密集型的操作，而OLTP 是IO 密集型的操作。建议在采购设备时，将更多的注意力放在提高IO 的配置上。

ssddsss注 1：从InnoDB 存储引擎的设计架构上来看，其主要的后台操作都是在一个单独的master thread 中完成的， 因此并不能很好地支持多核的应用。当然，开源社区已经通过多种方法来改变这种局面，而InnoDB 1.0 版本在各种测试下已经显示出对多核CPU 的处理性能的支持有了极大的提高，而InnoDB 1.2 版本又支持多个purge 线程，以及将刷新操作从master thread 中分离出来。因此，若用户的CPU 支持多核， InnoDB 的版本应该选择1.1 或更高版本。另外，如果CPU 是多核的，可以通过修改参数 innodb_read_io_threads 和innodb_ write_io_threads 来增大IO 的线程，这样也能更充分有效地利用CPU的多核性能。

ssddsss注 2：在当前的MySQL 数据库版本中， 一条SQL 查询语句只能在一个CPU 中工作，并不支持多C PU 的处理。OLTP 的数据库应用操作一般都很简单，因此对OLTP 应用的影响并不是很大。但是，多个CPU 或多核CPU 对处理大并发最的请求还是会有帮助。

ssdss内存的大小是最能直接反映数据库的性能。InnoDB 存储引擎既缓存数据，又缓存索引，并且将它们缓存于一个很大的缓冲池中，即 InnoDB Buffer Pool 。因此，内存的大小直接影响了数据库的性能。

ssdss注 3 ：在上述测试中，数据和索引总大小为18GB, 然后将缓冲池的大小分别设为2GB 、4GB 、6GB 、8GB 、10GB 、12GB 、14GB 、16GB 、18GB 、20GB 、22GB, 再进行sysbench的测试。可以发现，随着缓冲池的增大，测试结果TPS (Transaction Per Second) 会线性增长。当缓冲池增大到20GB 和22GB 时，数据库的性能有了极大的提高，因为这时缓冲池的大小已经大于数据文件本身的大小，所有对数据文件的操作都可以在内存中进行。因此这时的性能应该是最优的，再调大缓冲池并不能再提高数据库的性能。

ssdss如何判断当前数据库的内存是否已经达到瓶颈了呢？可以通过查看当前服务器的状态，比较物理磁盘的读取和内存读取的比例来判断缓冲池的命中率，通常InnoDB 存储引擎的缓冲池的命中率不应该小于99%,

ss大撒大撒大撒sadsaddsseg：mysql>SHOW GLOBAL STAUTS LIKE’innodb%read%’ \G; ssdss即使缓冲池的大小已经大于数据库文件的大小，这也并不意味着没有磁盘操作。数据库的缓冲池只是一个用来存放热点的区域，后台的线程还负责将脏页异步地写入到磁盘。此外，每次事务提交时还需要将日志写入重做日志文件。(预读的概念可看)

ssdss当前大多数数据库使用的都是传统的机械硬盘。机械硬盘的技术目前巳非常成熟，在服务器领域一般使用SAS 或SATA 接口的硬盘。服务器机械硬盘开始向小型化转型，目前大部分使用2.5 寸的SAS 机械硬盘。

ssdss机械硬盘有两个重要的指标： 一个是寻道时间，另一个是转速。当前服务器机械硬盘的寻道时间已经能够达到3ms, 转速为15 000RPM (rotate per minute) 。传统机械硬盘最大的问题在于读写磁头，读写磁头的设计使硬盘可以不再像磁带一样，只能进行顺序访问，而是可以随机访问。但是，机械硬盘的访问需要耗费长时间的磁头旋转和定位来查找，因此顺序访问的速度要远高于随机访问。 传统关系数据库的很多设计也都是在尽量充分地利用顺序访问的特性。

ssdss固态硬盘，更准确地说是基于闪存的固态硬盘，是近几年出现的一种新的存储设备，其内部由闪存(Flash Memory) 组成。因为闪存的低延迟性、低功耗，以及防震性，闪存设备已在移动设备上得到了广泛的应用。企业级应用一般使用固态硬盘，通过并联多块闪存来进一步提高数据传输的吞吐量。传统的存储服务提供商EMC 公司已经开始提供基于闪存的固态硬盘的TB 级别存储解决方案。数据库厂商Oracle 公司最近也开始提供绑定固态硬盘的Exadata 服务器。

ssdssRAID (Redundant Array of Independent Disks, 独立磁盘冗余数组）的基本思想就是把多个相对便宜的硬盘组合起来，成为一个磁盘数组，使性能达到甚至超过一个价格昂贵、容量巨大的硬盘。由于将多个硬盘组合成为一个逻辑扇区， RAID 看起来就像一个单独的硬盘或逻辑存储单元，因此操作系统只会把它当作一个硬盘。

ssdssRAID的作用：

ssddsss①、增强数据集成度ssddsss②、增强容错功能ssddsss③、增加处理量或容量

ssdss根据不同磁盘的组合方式，常见的RAID 组合方式可分为 RAID 0 、RAID 1 、RAIDS 、RAID 10 和 RAID 50 等。

ssdssRAID 0：将多个磁盘合并成一个大的磁盘，不会有冗余，并行I/O, 速度最快。RAID 0 亦称为带区集，它将多个磁盘并列起来，使之成为一个大磁盘。 在存放数据时，其将数据按磁盘的个数进行分段，同时将这些数据写进这些盘中。 所以，在所有的级别中， RAID 0 的速度是最快的。但是RAID 0 没有冗余功能，如果一个磁盘（物理）损坏，则所有的数据都会丢失。 ssdss注 4 ：理论上， 多磁盘的效能就等于（单一磁盘效能） X (磁盘数），但实际上受限于总线1/0 瓶颈及其他因素的影响， RAID 效能会随边际递减。也就是说，假设一个磁盘的效能是50MB/s, 两个磁盘的RAIDO 效能约96MB/s, 三个磁盘的RAIDO 也许是130MB/s 而不是150MB/s 。

ssdssRAID 1：两组以上的N 个磁盘相互作为镜像，在一些多线程操作系统中能有很好的读取速度，但写入速度略有降低。除非拥有相同数据的主磁盘与镜像同时损坏，否则只要一个磁盘正常即可维持运作，可靠性最高。

ssdss注 5 ：RAID 1 就是镜像，其原理为在主硬盘上存放数据的同时也在镜像硬盘上写相同的数据。当主硬盘（物理）损坏时，镜像硬盘则代替主硬盘的工作。因为有镜像硬盘做数据备份，所以RAID 1 的数据安全性在所有的RAID 级别上来说是最好的。但是，无论用多少磁盘作为RAID 1,仅算一个磁盘的容量，是所有RAID 中磁盘利用率最低的一个级别。

ssdssRAID 5：是一种存储性能、数据安全和存储成本兼顾的存储解决方案。它使用的是Disk Striping (硬盘分区）技术。RAID 5 至少需要三个硬盘， RAID 5 不对存储的数据进行备份，而是把数据和相对应的奇偶校验信息存储到组成RAID 5 的各个磁盘上，并且奇偶校验信息和相对应的数据分别存储于不同的磁盘上。当RAID 5 的一个磁盘数据发生损坏后，利用剩下的数据和相应的奇偶校验信息去恢复被损坏的数据。 ssdss注 6 ：RAID 5 可以理解为是RAID 0 和RAID 1 的折中方案。RAID 5 可以为系统提供数据安全保障，但保障程度要比镜像低而磁盘空间利用率要比镜像高。RAID 5 具有和RAID 0 相近似的数据读取速度，只是多了一个奇偶校验信息，写入数据的速度相当慢，若使用Write Back 可以让性能改善不少。同时，由于多个数据对应一个奇偶校验信息， RAID 5 的磁盘空间利用率要比RAID 1 高，存储成本相对较低。

ssdssRAID 10 和RAID 01：RAID 10 是先镜像再分区数据，将所有硬盘分为两组，视为RAID 0 的最低组合，然后将这两组各自视为RAID 1 运作。RAID 10 有着不错的读取速度，而且拥有比 RAID 10 更高的数据保护性。RAID 01 则与RAID 10 的程序相反，先分区再将数据镜射到两组硬盘。RAID 01 将所有的硬盘分为两组，变成 RAID 1 的最低组合，而将两组硬盘各自视为RAID 0 运作。 ssdss注 7 ：RAID 01 比 RAID 10 有着更快的读写速度，不过也多了一些会让整个硬盘组停止运转的几率，因为只要同一组的硬盘全部损毁， RAID 01 就会停止运作，而RAID 10 可以在牺牲RAID 0 的优势下正常运作。RAID 10 巧妙地利用了RAID 0 的速度及RAID 1 的安全（保护）两种特性，它的缺点是需要较多的硬盘，因为至少必须拥有四个以上的偶数硬盘才能使用。

ssdssRAID50 ：也被称为镜像阵列条带，由至少六块硬盘组成，像RAID 0 一样，数据被分区成条带，在同一时间内向多块磁盘写入；像 RAID 5 一样，也是以数据的校验位来保证数据的安全，且校验条带均匀分布在各个磁盘上，其目的在于提高RAID 5 的读写性能。 ssdss注 8 ：对于数据库应用来说， RAID 10 是最好的选择，它同时兼顾了RAID 1 和RAID 0 的特性。但是，当一个磁盘失效时，性能可能会受到很大的影响，因为条带(strip) 会成为瓶颈。我曾在生产环境下遇到过的情况是，两台负载基本相同的数据库，一台正常的服务器磁盘 IO 负载为20% 左右，而另一台服务器 IO 负载却高达90% 。

ssdssRAID Write Back 功能是指 RAID 控制器能够将写入的数据放入自身的缓存中，并把它们安排到后面再执行。这样做的好处是，不用等待物理磁盘实际写入的完成，因此写入变得更快了。对于数据库来说，这显得十分重要。例如，对重做日志的写入，在将 sync_binlog 设为1 的情况下二进制日志的写入、脏页的刷新等都可以使性能得到明显的提升。

ssdss但是，当操作系统或数据库关机时， Write Back 功能可能会破坏数据库的数据。这是由于已经写入的数据库可能还在RAID 卡的缓存中，数据可能并没有完全写入磁盘，而这时故障发生了。为了解决这个问题，目前大部分的硬件RAID 卡都提供了电池备份单元(BBU, Battery Backup Unit), 因此可以放心地开启Write Back 的功能。不过我发现每台服务器的出厂设置都不相同，应该将RAID 设置要求告知服务器提供商，开启一些认为需要的参数。

ssdss注 9 ：如果没有启用Write Back 功能，那么在RAID 卡设置中显示的就是Write Through 。 Write Through 没有缓冲写入，因此写入性能可能不是很好，但它却是最安全的写入。即使用户开启了Write Back 功能， RAID 卡也可能只是在Write Through 模式下工作。这是因为安全使用Write Back 的前提是RAID 卡有电池备份单元。为了确保电池的有效性， RAID 卡会定期检查电池状态，并在电池电量不足时对其进行充电，在充电的这段时间内会将Write Back 功能切换为最为安全的Write Through 。用户可以在没有电池备份单元的情况下强制启用Write Back 功能，也可以在电池充电时强制使用Write Back 功能，只是写入是不安全的。用户应该非常确信这点，否则不应该在没有电池备份单元的情况下启用Write Back 。

ssdss对RAID 卡进行配置可以在服务器启动时进入一个类似于 BIOS 的配置界面，然后再对其进行各种设置。此外，很多厂商都开发了各种操作系统下的软件对RAID 进行配置，如果用户使用的是LSI 公司生产提供的RAID 卡，则可以使用 MegaCLI 工具来进行配置。MegaCLI 为多个操作系统提供了支持，对Windows 操作系统还提供了GUI 界面的配置环境，因此相对来说比较简单。这里主要介绍命令行下MegaCLI 的使用，在Windows 下同样可以使用命令MegaCLI.exe 。

ssdss Linux 是 MySQL 数据库服务器中最常使用的操作系统。与其他操作系统不同的是Linux 有着众多的发行版本，每个用户的偏好可能不尽相同。然而在将Linux 操作系统作为数据库服务器时需要 考虑更多的是操作系统的稳定性，而不是新特性。

ssdss Windows 操作系统在MySQL 数据库应用中也非常普及。也有公司喜欢在开发环境下使用Windows 版本的MySQL 数据库，而在正式生产环境下选择使用Linux 操作系统。这本身没有什么问题，但问题通常存在于文件系统大小写敏感对应用程序的影响。在Windows 操作系统下表名不区分大小写，而Linux 操作系统却是大小写敏感的，这点在开发阶段需要特别注意。

ssdss每个操作系统都默认支持一种文件系统并推荐用户使用，如Windows 默认支持 NTFS, Solaris 默认支持ZFS 。而对于Linux 这样的操作系统，不同发行版本默认支持的文件系统各不相同，有的默认支待EXT3, 有的是ReiserFS, 有的是EXT4, 有的是 XFS 。

ssdss虽然不同特性的文件系统有很多，但是在实际使用过程中从未感觉到文件系统的性能差异有多大。网上有多个关于XFS 文件系统的“神话“，认为其是多么地适合数据库应用，性能较之EXT3 有极大的提升。但是在实际测试和使用后发现，它的性能和EXT3 在整体上没有大的差距。因此， DBA 首先应该把更多的注意力放到数据库上，而不是纠结于文件系统。

ssdss基准测试工具可以用来对数据库或操作系统调优后的性能进行对比。MySQL 数据库本身提供了一些比较优秀的工具，这里将介绍另外两款更为优秀和常用的基准测试工具： sysbench 和mysql-tpcc 。

ssdsssysbench 是一个模块化的、跨平台的多线程基准测试工具，主要用于测试各种不同系统参数下的数据库负载情况。它主要包括以下几种测试方式：

ssdss①、CPU 性能 ssdss②、磁盘IO 性能 ssdss③、调度程序性能

ssdss④、内存分配及传输速度 ssdss⑤、POSIX 线程性能 ssdss⑥、数据库OLTP 基准测试

ssdsssysbench 的数据库OLTP 测试支持MySQL 、PostgreSQL 和Oracle 。目前sysbench主要用于Linux 操作系统，开源社区已经将sysbench 移植到Windows, 并支持对Microsoft SQL Server 数据库的测试。

ssdssTPC ( Transaction Processing Performance Council, 事务处理性能协会）是一个用来评价大型数据库系统软硬件性能的非盈利组织。TPC-C 是TPC 协会制定的，用来测试典型的复杂OLTP (在线事务处理）系统的性能。目前在学术界和工业界普遍采用TPC-C来评价OLTP 应用的性能。