mysql优化之十

核心提示：译者：叶金荣（Email:），手册来源：MySQL(和PHP搭配之最佳组合)手册版本 5.0.20，mysql优化之十，出处：http://iMySQL(和PHP搭配之最佳组合).cn，转载请注明译者和出处，可以提交 FLUSH HOSTS 语句或者运行 MySQL(和PHP搭配之最佳组合)admin flush-ho

　　译者：叶金荣（Email:），手册来源：MySQL(和PHP搭配之最佳组合)手册版本 5.0.20，出处：http://iMySQL(和PHP搭配之最佳组合).cn，转载请注明译者和出处，并且不能用于商业用途，违者必究。
7.5.4 编译和连接如何影响MySQL(和PHP搭配之最佳组合)的速度

以下的大部分测试都是在Linux上用MySQL(和PHP搭配之最佳组合)的基准套件来做的，不过它们对其他操作系统以及不同的工作量也是有一定启示的。用 -static 连接的话，MySQL(和PHP搭配之最佳组合)执行速度的速度是最快的。在Linux上，用 pgcc 和 -O3 编译的代码最快。大概需要200MB的内存加上这些选项来编译 `sql_yacc.cc`，因为 gcc/pgcc 需要大量内存来编译所有的内部函数。想要配置MySQL(和PHP搭配之最佳组合)以避免包含 libstdc++ 库就要加上 CXX=gcc 参数，不过这不是必须的。注意，使用某些版本的 pgcc 编译的结果代码只能在奔腾处理器上运行，尽管已经给编译器增加选项想让结果代码可以在所有的x586类型(如AMD)处理器上运行。如果使用比较好的编译器以及编译选项，应用程序大概能提高10-30%的速度。这在自己编译MySQL(和PHP搭配之最佳组合)服务器的时候尤为重要。我们已经测试 Cygnus CodeFusion 和 Fujitsu 编译器了，不过测试它们时，无论哪个都没有完全地解决bug以允许最佳方式编译MySQL(和PHP搭配之最佳组合)。标准的MySQL(和PHP搭配之最佳组合)二进制发布包已经编译支持所有的字符集了。如果想要自己编译MySQL(和PHP搭配之最佳组合)，只需编译支持想要使用的字符集。可以在 configure 时增加 --with-charset 选项来控制。

以下是我们已经做过的一些测量：

* 如果使用 pgcc 编译任何东西时用 -O6 选项，则 MySQL(和PHP搭配之最佳组合)d 服务器会比用 gcc 2.95.2 编译快1%。
* 如果使用动态连接(没用 -static)，则在Linux上结果会慢13%。注意，这仍然可以让你的应用程序客户端动态连接MySQL(和PHP搭配之最佳组合)的库。这对提高服务器性能相当关键。
* 如果做 strip MySQL(和PHP搭配之最佳组合)d 了，则结果会快4%。
* 在同一个主机上连接到服务器，如果使用TCP/IP而不是Unix套接字文件，那么会慢7.5%(在Unix上，如果使用主机名 localhost 连接，MySQL(和PHP搭配之最佳组合)默认使用套接字文件)。
* 用TCP/IP连接时，直接连接本机服务器比连接不同主机上的服务器会慢8-11%，尽管是通过100Mb/s的以太网。
* 运行基准测试时如果采用安全连接(所有的数据都用内部支持的SSL加密)则会比用不加密链接慢55%。
* 编译时增加 --with-debug=full 选项，则大部分查询都会慢20%。一些查询可能会变得很慢；例如，MySQL(和PHP搭配之最佳组合)的基准套件可能会慢35%。如果只增加选项 --with-debug(没有 =full)，则只会慢15%。一个使用 --with-debug=full 选项编译后的 MySQL(和PHP搭配之最佳组合)d 可以在启动时增加 --skip-safemalloc 选项来禁用运行时内存检查。则执行速度就会接近于增加 --with-debug 参数时的情况。
* 在 Sun UltraSPARC-IIe 上，用 Forte 5.0 编译的服务器会比用 gcc 3.2 编译的快4%。
* 在 Sun UltraSPARC-IIe 上，用 Forte 5.0 编译的服务器在32位平台上比在64位平台上快4%。
* 在 UltraSPARC 上用 gcc 2.95.2 编译，增加 -mcpu=v8 -Wa,-xarch=v8plusa 选项的话会快4%。
* 在 Solaris 2.5.1 上，MIT-pthreads 比 Solaris 原始的单处理器线程慢8-12%。在不同的负载或者CPU下，这个差异会更大。
* 在 Linux x86上用 gcc 编译，不增加 帧指针(-fomit-frame-pointer 或 -fomit-frame-pointer -ffixed-ebp)选项 的话会慢1-4%。

MySQL(和PHP搭配之最佳组合) AB提供的Linux上的MySQL(和PHP搭配之最佳组合)二进制发布包通常用 pgcc 编译。不过我们又重新使用 gcc 来编译了，因为用 pgcc 编译的代码不能在AMD上运行。我们会一直使用 gcc 来编译直到 pgcc 的这个bug解决了。与此同时，如果你有非AMD机器，就可以用 pgcc 来编译以使MySQL(和PHP搭配之最佳组合)更快。标准的MySQL(和PHP搭配之最佳组合) Linux二进制包是静态连接的，这使得它更快且移植性更好。

7.5.5 MySQL(和PHP搭配之最佳组合) 如何使用内存

以下展示了 MySQL(和PHP搭配之最佳组合)d 服务器使用内存的几种方法。可适用的是，和使用内存相关的系统变量名已经给出了：

* 索引缓冲(key_buffer_size 变量)在所有的线程间是共享的；服务器所需的其他缓冲已经分配好了。详情请看"7.5.2 Tuning Server Parameters"。
* 每个连接使用的一些线程特有的空间：
o 一个堆栈(默认是64KB，thread_stack 变量)。
o 一个连接缓冲(net_buffer_length 变量)。
o 一个结果缓冲(net_buffer_length 变量)。
连接缓冲和结果缓冲可根据需要动态扩大到 max_allowed_packet。一个查询正在执行时，当前查询语句的一份拷贝也需要为之分配内存。
* 所有的线程共享一样的基本内存。
* 只有压缩后的 ISAM 和 MyISAM 表才是内存映射的。这是因为32位的内存只有4GB的内存空间，对大部分大表都不够。当64位寻址的操作系统更普遍时，我们会普遍支持内存映射的。
* 每个需要顺序扫描数据表的请求都需要分配一个读缓冲(read_buffer_size 变量)。
* 如果以"随机"顺序读取记录(例如，排序之后)，一个随机-读缓冲就必须分配以避免磁盘搜索(read_rnd_buffer_size 变量)。
* 所有的表连接都在一个步骤中完成，甚至大部分连接可以不使用临时表就能完成。大部分临时表都是基于内存(HEAP 表)的。记录长度很长(所有的字段长度之和)或者包含有 BLOB 字段的临时表都会存储在磁盘中。在MySQL(和PHP搭配之最佳组合) 3.23.2之前有一个问题是，当一个内部的内存表大小超过 tmp_table_size 后，就会产生一个 The table tbl_name is full 错误。在MySQL(和PHP搭配之最佳组合) 3.23.2后，这会自动处理，在必须的时候把内存表转换成基于磁盘存储的 MyISAM 表。想要在旧版本的MySQL(和PHP搭配之最佳组合)也能正常运行，可以给 MySQL(和PHP搭配之最佳组合)d 设置 tmp_table_size 选项以增加临时表大小，或者在客户端程序中设置 SQL_BIG_TABLES 选项。详情请看"14.5.3.1 SET Syntax"。在MySQL(和PHP搭配之最佳组合) 3.20中，临时表的大小最多为 record_buffer*16；如果你使用这个版本，就必须加大 record_buffer 的值。可以在使用 --big-tables 选项启动 MySQL(和PHP搭配之最佳组合)d 来允许将临时表保存在磁盘中。不过这会影响很多查询编译。
* 大部分需要做排序的请求都会分配排序缓冲，且视结果集大小分配0到2个临时文件。详情请看"A.4.4 Where MySQL(和PHP搭配之最佳组合) Stores Temporary Files"。
* 几乎所有的解析和计算在本地内存存储中已经做完了。无需为小的条目耗费内存，因此通常能避免比较慢的内存分配以及释放。内存只分配给不可预料的大字符串；这由 malloc() 和 free() 来完成。
* 每个打开着的 MyISAM 和 ISAM 表，索引文件只打开一次，而数据文件则是每次为同时运行的线程打开一次。在每个当前的线程中，表结构，每个字段的结构，需要分配大小 3 * N 的缓冲(N 是记录的最大长度，不计算 BLOB 字段)。BLOB 字段需要比 BLOB 数据长度多出来5到8字节。MyISAM 和 ISAM 存储引擎维护一个额外的缓冲字段供内部使用。
* 对于每个有 BLOB 字段的表，专门为其扩展一个动态缓冲来读取大 BLOB 数据。读表的时候，就会分配一个以最大长的 BLOB 数据长度的缓冲。
* 所有的使用中的表句柄都缓存起来并且被当作FIFO来管理。默认地，这个缓存有64个表目。如果有一个表同时被两个运行着的线程使用了，那么在缓存中包含了该表的2个表目。详情请看"7.4.8 How MySQL(和PHP搭配之最佳组合) Opens and Closes Tables"。
* FLUSH TABLES 语句或 MySQL(和PHP搭配之最佳组合)admin flush-tables 命令会关闭所有非使用中的表，并且也会在线程结束后关闭那些使用中的表。这对释放使用中的内存很有效。

ps 和其他系统程序都可以报告 MySQL(和PHP搭配之最佳组合)d 使用了大量内存。这个可能是因为在不同内存地址上的线程堆栈导致。例如，Solaris下的 ps 会把堆栈中未使用的内存也认为是已使用了。可以通过 swap -s 命令来检查可用的交换内存。我们已经用好几个内存泄漏监测器来测试了 MySQL(和PHP搭配之最佳组合)d(包括商业的和开源的)，应该不会存在内存泄漏。
7.5.6 MySQL(和PHP搭配之最佳组合) 如何使用 DNS

当一个新的客户端连接到 MySQL(和PHP搭配之最佳组合)d 时，它就会产生一个新的线程来处理。这个线程首先检查客户主机名是否在主机缓存中。如果没有，它就尝试解析这个主机名：

* 如果操作系统支持线程安全的 gethostbyaddr_r() 和 gethostbyname_r() 调用，就用他们来解析。
* 如果操作系统不支持上述两个的线程安全调用，那么线程就县锁定一个mutex，然后再调用它们。这时，其他线程便无法解析不在主机缓存中主机名，直到第一个线程释放了mutex锁。

在 MySQL(和PHP搭配之最佳组合)d 启动时使用 --skip-name-resolve 选项就能禁用DNS解析。不过，这样的话就不能在MySQL(和PHP搭配之最佳组合)的授权表中使用主机名了而只能用ip格式。如果DNS解析很慢且有很多主机，那么可以用 --skip-name-resolve 选项禁用DNS解析或者重新编译 MySQL(和PHP搭配之最佳组合)d 时增加 HOST_CACHE_SIZE 的定义值(默认是 128)来提高性能。启动服务器时使用 --skip-name-resolve 选项就能禁用主机缓存。想要清除主机缓存，可以提交 FLUSH HOSTS 语句或者运行 MySQL(和PHP搭配之最佳组合)admin flush-hosts 命令。想要彻底禁用TCP/IP连接，在启动服务器时使用 --skip-networking 选项即可。