Sun中国技术社区- Sun Developer Network (SDN) Home

摘要：通过对数据库服务器进行配置和调优，并针对 MySQL 优化 Solaris OS，我们可以最大限度地提升 MySQL 在 Solaris 平台中的性能。本文旨在帮助您定义调优参数并在环境中优化这些参数。

MySQL 是全球最为流行的一种开源数据库。MySQL 数据库的关键优势在于它卓越的性能和可伸缩性，这使它非常适合在企业环境中作为 Web 站点、数据仓库和数据密集型应用程序的后端。

要最大限度地提升 MySQL 在 Solaris 操作系统的性能，数据库服务器的配置和调优以及针对 MySQL 优化 Solaris 操作系统都是至关重要的因素。但是，目前还没有通用的 MySQL 服务器调优参数可应用于所有平台上的所有工作负载；合适的参数取决于具体的工作负载、硬件和操作系统平台以及 MySQL 的使用情况。本文旨在帮助您定义调优参数并在运行环境中对这些参数进行调优。

MySQL 包含 MyISAM、InnoDB、HEAP 和 Berkeley DB（BDB）等存储引擎。InnoDB 和 BDB 存储使用提交、点名、崩溃恢复功能支持原子性、一致性、隔离性和持续性（Atomic，Consistent，Isolation，Durable，ACID）事务，只有 InnoDB 存储使用查询（以默认非锁定一致性读取方式运行）支持行级锁。

InnoDB 存储引擎支持所有四种隔离级别：读操作未提交、读操作已提交、可重复读取和可序列化。InnoDB 也有借助外键约束支持获得称作引用完整性的功能，并且支持快速记录查找使用主键进行的查询。由于这些功能和其他强大的功能和特性，InnoDB 经常用于大型、负载很重的生产系统。本文涵盖了在 Solaris 10 OS 中提高 CPU、内存和磁盘驱动资源的利用率的不同方式。讨论的主题包括使用优化库、使用 Sun Studio 11 软件编译 64 位 MySQL、调优 Solaris UFS 文件系统、以及在 Solaris 平台上为 InnoDB 存储引擎而进行的 MySQL 服务器配置和调优。

MySQL 是一个单进程的多线程应用程序。主线程在所有 MySQL 线程中拥有控制服务器的最高优先级。主线程绝大多数时间处于空闲状态，它每隔 300 毫秒检查一次是否需要执行某项动作，比如说刷新在缓冲池中的 Dirty Block。

除了主线程之外，专用的用户线程集以普通优先级在线程池中运行，用来处理客户机的同步请求。对于每项客户机请求，要创建一个单线程来处理客户机请求，并且一旦结果准备就绪便向每个客户机发送回结果。用户线程集中有一个等待来自控制台输入的单用户线程，以及一组以较低优先级运行用来处理一些后台任务的实用线程。

MySQL 不能随着处理客户机请求的用户线程数量增加而立刻很好地扩展。MySQL 的性能只能随着每个用户线程的增加而有效率地扩展，直到达到性能的峰值点。在此之后，增加用户连接数量将降低 MySQL 性能，因为这时发生了线程的并发竞争。对于用户连接数量可调节的应用程序，您需要决定（对于不同的工作负载）达到峰值性能的最佳用户连接数量。

我们在一个 4 路 UltraSPARC IV 基于处理器的服务器上运行 SysBench CPU 绑定基准测试（1 M 行的数据可以被填写到 InnoDB 数据和索引缓存缓冲区中）。MySQL 性能峰值最多可达 16 个用户连接，性能从 32 个用户连接开始回落，如下图所示。（请注意：结果可能会有所不同。）

结果测试显示，对于 SysBench 工作负载，在 UltraSPARC IV 基于处理器的服务器上的 MySQL 性能峰值可以通过在 1-4 个 CPU 系统上设置 4*CPU 用户连接数量获得。MySQL 性能峰值一直接近线性增加直到 4 个 CPU ，可伸缩性比率从 8 个 CPU 开始回落。下列表格显示使用装有 1-24 个 UltraSPARC IV 处理器的系统进行可伸缩性测试的结果。（请注意：结果可能会有所不同。）

对于用户连接数量可调节的应用程序，可以配置 innodb_thread_concurrency 参数来设置保留在 InnoDB 中的并发线程的最大数量。当您看到在 show innodb status 中的队列里有许多查询请求，您需要增加这个参数的数值。在 MySQL 5.0.8 以下的版本中，设置超过 500 的参数值就可以禁用并发检查，所以在 InnoDB 内部根据需要将存在尽可能多的并发线程来处理服务器内部的不同任务。从 MySQL 5.0.8 版本开始该变量有所变化：将该变量设置为等于或大约 20 将禁用并发检查；从 MySQL 5.0.19 及更高版本开始，该变量又一次发生变化：您需要设置 innodb_thread_concurrency 参数为 0 来禁用并发检查。在一些运行在 Solaris 平台上的工作负载中，当您看到存在大量用户级锁（在 prstat –mL 输出中的 LCK )时，减少该参数值会增加 CPU 的使用效率，进而提高了整体性能。

使用经过优化的时间库最大限度地减少 time(2) 系统调用

在 Solaris 平台上 time(2) 系统调用实际上是将陷阱带入内核来调用开销大且耗时的 gethrestime()。当 MySQL 执行每项查询时，MySQL 在启动时执行 time(2) 系统调用，在结束时计量查询花费的时间。对于一些工作负载，MySQL 能在 time(2) 系统调用上占用系统时间超过 30%，使用了大量的系统 CPU 周期，说明如下：

在 Solaris 平台上，我们可以通过实施更快的 gethrtime(3C) 系统调用而不是调用内核中的gethrestimetime()，来实现对 time(2) 系统调用的优化。如果您的工作负载在 time(2) 系统调用上(您可以通过检查 truss 输出来验证)消耗了的大量系统 CPU 资源，您会觉得通过将 MySQL DB 连接到优化时间库（在使用时间戳记的 C/C++ 系统的性能优化中进行了描述）而对它进行重新编译是很有意义的工作。要进行上述操作，通过在 MySQL 源树主页下的 configure 文件开始处添加 LIBS='-lfasttime' 来实现 gethrtime( 3 C )系统调用，或者您可以设置 LD_PRELOAD（32 位）或 LD_PRELOAD_64（64 位）来指定 libfasttime.so 库的位置。我们运行了 OSDL Database Test 2 (DBT2) 工作负载测试，并通过使用在 8 路 UltraSPARC 系统上进行的 10 个仓库工作负载测试中的优化 time(2) 提升了大约 7% 的 MySQL 性能。

从内存占用量角度里看，在 Solaris 平台上的 malloc 例程运行相当好；但是在 libc 中默认的单线程 malloc 一个接一个处理在队列中的并发内存分配请求，从而减慢了在多线程应用程序中的性能。Solaris OS 提供了几种用来改进多线程应用程序的内存分配性能的 malloc 实现方法，包括 mtmalloc、libumem 和 hoard。然而，可能难以说出哪种 malloc 实现对不同的应用程序最好，因为每种 malloc 实现依赖于应用程序的内存分配模式，以及在不同的 malloc 实现中不同的算法是如何适应于应用程序的内存分配模式的。

MySQL 积极使用 malloc() 和 free() 为不可预期的长字符串分配内存。malloc 为 HEAP 竞争调用块 mysqld 线程。通过使用不同的内存分配器进行计量的结果显示，将 malloc 替换为 mtmalloc 可以显著改进 MySQL DB 的性能。我们看到在 SysBench CPU 绑定的基准测试中性能改进 65%（1 M 行的数据可以填写到 InnoDB 数据和索引缓存缓冲区）。我们在装有 MySQL 5.0.7 的 8 路双核 UltraSPARC IV 基于处理器的系统上运行此项测试。

要使用 mtmalloc，您可以通过在 Solaris 平台上 MySQL 启动脚本中设置 LD_PRELOAD 或 LD_PRELOAD_64 环境变量来预加载 mtmalloc 库。这样您也就不必为连接到 mtmalloc 库而重新构建 MySQL DB 了。

如果是 32 位 MySQL，通过设置 LD_PRELOAD 预加载 mtmalloc，如下所示：

如果是 64 位 MySQL，设置 LD_PRELOAD_64 预加载 mtmalloc，如下所示：

为了应用 libmtmalloc，已经使用 "--with-mysqld-libs=-lmtmalloc" 配置标志构建由 MySQL 释放的用于 Solaris 操作系统的 MySQL 5.0 GA 二进制码。这样一来，您将不必设置 LD_PRELOAD 或 LD_PRELOAD_64 环境变量而使用 mtmalloc。

Solaris OS 是一个完整的 64 位计算环境，也为 32 位应用程序提供了二进制兼容性，因此 64 位和 32 位 MySQLs 都可以在 Solaris 平台上良好地运行。与 32 位 MySQL 相比，64 位 MySQL 能够解决在 MySQL 内部的数据缓存、代码缓存、元数据缓存的更多内存，从而减少磁盘的输入/ 输出（I/O）。此外，在 64 位计算环境中借助更大的 CPU 操作，64 位 MySQL 要比 32 位 MySQL 操作更快。下列表格描述了在基于 8 路 UltraSPARC IV 的服务器上进行的 SysBench CPU 绑定测试（1 M 行的数据可以填写到 InnoDB 数据和索引缓存缓冲区）中的性能数据。（注意：结果可能会有所不同。）

要在 Solaris 平台上使用 Sun Studio 11 发行版构建 64 位 MySQL，我们使用编译器标志和配置选项，如下所示：

要比较使用不同编译器发行版本（Sun Studio 10 和 Sun Studio 11）构建的 MySQL 数据库性能，我们使用 DBT2 测试套件。工作负载描述一个经营大量仓库和相关销售区的零件批发供货商。该项任务涉及只读和更新密集型混合事务，包括输入和传递订单、记录付款、检查订单状态和监测仓库存储水平。九个表格包括 Warehouse、 District、 Item、Stock、 Customer、 Order、 New order、 Order-line 和 History，其内容依仓库数量比例决定（除了 Item 表格）。

我们在 Solaris 10 操作系统（安装在 4 个双核 2200-MHz AMD Opteron 基于处理器的 Sun Fire x64 服务器上）上使用 10 个仓库数据库运行 DBT2 测试。在本测试案例中，大多数数据库查询被缓存入 innodb 缓冲区中。由于大多数的 CPU 时间被消耗在处理查询操作上，因此系统在测试期间是 CPU 绑定的。测试标准是吞吐量大小，也就是每分钟内发生的新订单交易。正如在下列表格中所显示的测试结果数据，利用 Sun Studio 11 构建的 MySQL 性能比使用 Sun Studio 10 软件的构建的 MySQL 性能提高13％。（注意：结果可能会有所不同。）

与基于 64 位体系结构系统中的 Sun Studio 10 相比，除了 Sun Studio 11 提供的 MySQL 数据库优化的性能改进之外，Sun Studio 11 在 UltraSPARC 基于处理器系统中还提供了多核和芯片多线程（Chip multithreading，CMT）优化。Sun Studio 11 发行版还包括一个高级的图形调试器工具，用于在 MySQL 内部轻松设置断点、检查变量、导航调用堆栈和调试多线程代码。Sun Studio 11 软件中的复杂性能分析工具提供了附加数据空间。该附加数据空间分析在 UltraSPARC 基于处理器的系统上的评估能力。评估内容为与应用程序内存参考相关的性能开销。有关 Sun Studio 11 调试器和性能分析器工具的新特性，请参阅用户指南。

Sun Studio 11 软件还捆绑了可以并行编译 MySQL 源代码的 dmake 工具。与在多处理器系统上的 make 相比，dmake 可以显著改进编译性能。在装有 T1 处理器的 8 核 Sun Fire T2000 系统上使用 dmake -j 64 只需 9 分钟就可编译 MySQL 源代码 -- 这比使用花费 29 分钟进行编译的 make 快 3 倍还多。

文件系统集群大小对系统性能能产生重大影响 -- 特别当 MySQL 运行的工作负载使用的数据库比系统内存更大时影响更突出。在 Solaris 平台上，UFS 文件系统集群大小（maxcontig 参数）默认设置为 128 。在 Solaris 10 操作系统 SPARC 平台（x86/x64）上的文件系统块大小为 8 KB。您可以使用在系统文件上的 mkfs –F 或 fstyp –v 命令得到 maxcontig 和 bsize 的值。即使在随机 I/O 中，这也将触发预先读取整个文件系统集群长度(128*8 KB), 从而使磁盘饱和并显著降低性能。

解决问题的一种方式是减少 maxcontig 参数的值，那么磁盘 I/O 的传输大小就和 DB 块大小相匹配了。可以在文件系统中使用 tunefs –a maxcontig# 命令修改 maxcontig 的值。这种解决方案的缺点是可能影响从客户机上运行大型顺序 I/O 的其他工作负载的性能。

另一个解决方案是通过使用 --forcedirectio 选项挂装文件系统而启用文件系统的 Direct I/O，因为文件系统的 Direct I/O 自动禁用预先读取 MySQL 有自己的数据和缓存缓冲区，使用 Direct I/O 可以禁用文件系统缓冲区从而节省消耗在双重缓冲方面的 CPU 周期。下列表格显示了在 Sun Fire V65x 服务器上使用 SysBench I/O 绑定测试（100 M 行的数据不能填写到 InnoDB 数据和索引缓存缓冲区）而得到的性能数据。该项测试采用默认的 maxcontig 值、设置maxcontig 的值为 5（磁盘传输率大小为 5*4 KB），并使用 Direct I/O 来进行性能对比。（请注意：结果可能会有所不同。）

MySQL 不能直接访问磁盘；相反它读取数据到内部缓冲区缓存，读取／写入块，并刷新更改存回磁盘。如果服务器请求在缓存中可得的数据，数据可以被立刻处理。否则，操作系统将请求数据从磁盘中加载。缓存越大，越容易避免磁盘访问。对于绝大多数工作负载来说，8 MB 的默认值太小了。当您看到 %b（磁盘利用率）超过 60％、在 iostat –xnt 5 跟踪输出中 svc_t（响应时间）超过 35 毫秒、在 show innodb status 输出中的 FILE IO 部分中出现较高的读取量时，您就需要增加该数值了。

但是您不应将 innodb_buffer_pool_size 参数值设置太高，以避免那些没有足够多的 RAM 而运行的其他进程进行开销巨大的分页操作，因为这样将显著降低性能。由于 MySQL 进程的内存占用量只有大约 2 MB 到 3 MB，对于在单一专用 MySQL 进程上运行的系统将 innodb_buffer_pool_size 参数值设置达到内存的 70％和 80％比较好。

InnoDB 在后台中大约每秒钟刷新一次事务日志到磁盘。当每项事务提交时日志以默认的方式刷新到磁盘。在 MySQL、操作系统或硬盘崩溃的情况下，避免事务丢失的安全方式是使用 innodb_flush_log_at_trx_commit = 1 模块。

对于许多运行短小事务量的工作负载，您可以通过给 innodb_flush_log_at_trx_commit 参数设置不同的值来减少磁盘写入操作。

当该参数设置为 0 时，当每项事务提交时就没有日志刷新。此种做法能减少磁盘输入/输出从而改进性能,但是如果 MySQL 崩溃,事务可能丢失。

当该参数值设置为 2 时，每项事务提交时日志刷新到操作系统缓存（文件系统缓存）中，而不是刷新到磁盘中。这样还能减少了磁盘输入/输出，而且比该参数值设置为 0 时的执行速度有点慢；但是在 MySQL 发生崩溃的情况下没有事务丢失（尽管操作系统或硬盘发生崩溃的情况下可能出现事务丢失）。

对于大型事务，当每项事务提交时日志缓冲区被刷新之前，如果您的 innodb_flush_log_at_trx_commit 参数设置为 1，日志可能被加载到日志缓冲区而不是刷新到后台的磁盘中，从而减少了磁盘的输入/输出。如果您看到在运行时的 show innodb status 输出中有大型的日志输入/输出，您或许需要为 innodb_log_buffer_size 参数设置更大的值。对于没有长事务的大多数工作负载，没有必要为日志缓冲区设置较大的值而浪费内存资源。通常设置为 8 MB 和 64 MB 比较合适。

为了实现备份和恢复的目的，在 InnoDB 内部的恢复管理子系统刷新数据库页面和事务操作到日志文件。它还通过从小批量缓冲池中连续刷新数据库页面来实现模糊检查点操作。由于 InnoDB 以循环的方式写入到日志文件，因此如果日志文件已经达到由 innodb_log_file_size 参数设置的配置极限，那么立刻执行检查点操作以便刷新被修改的数据库页面。这样做是为了确保在恢复的情况下所提交的修改页面在日志文件中可用。

我们应选择每项日志的大小，以避免太频繁地执行检查点操作。较大的日志文件减少了检查点中的磁盘输入/输出。当看到在 show innodb status 输出的 BUFFER POOL AND MEMORY 部分中有大型页面写入时，您需要增加这项参数。但是较大的日志文件在服务器崩溃的情况下会增加“重新执行”恢复时间。

除了数据和缓冲区缓存之外，MySQL 版本 4.0.1 和更高版本有一个很好的功能称作查询缓存，用来存储由客户机向数据库服务器发出的相同的 SELECT 查询。在没有反复性的硬解析操作情况下，可能找到并重新执行相同的查询。MySQL 在查询缓存中还存储了查询的结果，从而显著地减少了为来自磁盘或内存缓存的查询创建复杂结果集所带来的开销，既减少了物理 I/O，也减少了逻辑 I/O。对于一些执行来自用户客户机相同查询的应用程序，查询缓存能大大提高响应时间。

query_cache_size 参数用于分配大量内存来缓存执行频繁的查询，并在没有实际查询执行的情况下将结果返回给客户机。query_cache_type 参数以不同的方式启用或禁用查询缓存。要决定如何设置这两个参数，您需要在运行时期间查询 qcache_inserts、qcache_hits 和 qcache_free_memory。qcache_inserts 显示添加到查询缓存的查询数量，qcache_hits 显示在没有实际查询执行的情况下从查询缓存中提取的查询结果的数量，qcache_free_memory 显示没有被使用的查询缓存空闲可用内存量。

如果您发现 qcache_hits 的值与您在运行时的总查询量相比较高，或 qcache_free_memory 值较低，您或许需要相应地增加 query_cache_size 参数的值。否则您要降低 query_cache_size 参数的值，来节省用于其他 MySQL 缓存缓冲区的内存资源。如果在运行时内 qcache_hit 为 0，您需要通过设置 query_cache_type 连同 query_cache_size 的值为 0 来彻底关闭查询缓存。

query_cache_limit 参数用来设置在查询缓存中存储的最大结果集。设置太低的 query_cache_limit 参数值，也会造成在运行时 qcache_hits 比 qcache_insert 的比率低。在这种情况下，您需要增加 query_cache_limit 参数的值，以便在查询缓存中存储大型查询结果集。

对于在 Sun 系统上运行的特定工作负载，MySQL 的杰出性能和可伸缩性可通过调优 MySQL 等存储引擎得到进一步增强。当然，对于各种不同的工作负载，许多变量和调优参数都可会性能造成影响。通过提供一般性指南和实际建议，相信本文可帮助您在 Solaris 平台上优化 InnoDB 的性能。我们非常欢迎您为本文提交反馈意见。

Luojia Chen 是 Sun 公司 Market Development Engineering 机构开源团队的一名软件工程师。她目前负责 MySQL 的采用以及 Sun 最新技术的迁移。她致力提升 MySQL 在 Solaris 平台上的稳定性和扩展性。可以通过电子邮件 luojia.chen@sun.com 与她联系。