从MySQL得到最大的性能.docx

1、从MySQL得到最大的性能优化是一项复杂的任务，因为它最终需要对整个系统的理解。当用你的系统/应用的小知识做一些局部优化是可能的时候，你越想让你的系统更优化，你必须知道它也越多。 因此，本章将试图解释并给出优化MySQL的不同方法的一些例子。但是记住总是有某些(逐渐变难)是系统更快的方法留着去做。 10.1 优化概述为了使一个系统更快的最重要部分当然是基本设计。你也需要知道你的系统将做这样的事情，那就是你的瓶颈。 最常见的瓶颈是： 磁盘寻道。磁盘花时间找到一个数据，用在1999年的现代磁盘其平均时间通常小于10ms，因此理论上我们能大约一秒寻道 1000 次。这个时间用新磁盘提高很慢并且很难对

2、一个表优化。优化它的方法是将数据散布在多个磁盘上。 当磁盘在我们需要读数据的正确位置时，磁盘读/写。用1999年的现代，一个磁盘传输类似10-20Mb/s。这必寻道更容易优化，因为你能从多个磁盘并行地读。 CPU周期。当我们读数据进内存时，(或如果它已经在那里)我们需要处理它以达到我们的结果。当我们有相对内存较小的表时，这是最常见的限制因素，但是用小表速度通常不是问题。 内存带宽。当CPU需要超出适合cpu缓存的数据时，缓存带宽就成为内存的一个瓶颈。这是对大多数系统的一个不常见的瓶颈但是你应该知道它。 10.2 系统/编译时和启动参数的调节我们以系统级的东西开始，因为这些决策的某一些很早就做好

3、了。在其他情况下，快速浏览这部分可能就够了，因为它对大收获并不重要，但是有一个关于在这个层次上收获有多大的感觉总是好的。 使用的缺省OS确实重要！为了最大程度地使用多CPU，应该使用Solaris(因为线程工作得确实不错)或Linux(因为2.2本的核心又确实不错的SMP支持)。而且在32位的机器上，Linux缺省有2G的文件大小限制。当新的文件系统被释出时( XFS )，希望这不久被修正。 因为我们没在很多平台上运行生产MySQL，我们忠告你在可能选择它前，测试你打算运行的平台。 其他建议： 如果你有足够的RAM，你能删除所有交换设备。一些操作系统在某些情况下将使用一个SWAP设备，即使你有

4、空闲的内存。 使用-skip-locking的MySQL选项避免外部锁定。注意这将不影响MySQL功能，只要它仅运行在一个服务器上。只要在你运行myisamchk以前，记得要停掉服务器(或锁定相关部分)。在一些系统上这个开关是强制的，因为外部锁定不是在任何情况下都工作。当用MIT-pthreads编译时，-skip-locking选项缺省为打开(on)，因为flock()没在所有的平台上被MIT-pthreads充分支持。唯一的情况是如果你对同一数据运行MySQL服务器(不是客户)，你不能使用-skip-locking之时，否则对没有先清掉(flushing)或先锁定mysqld服务器的表上运

5、行myisamchk。你仍然能使用LOCK TABLES/ UNLOCK TABLES，即使你正在使用-skip-locking。 10.2.1 编译和链接怎样影响MySQL的速度大多数下列测试在Linux上并用MySQL基准进行的，但是它们应该对其他操作系统和工作负载给出一些指示。 当你用-static链接时，你得到最快的可执行文件。使用Unix套接字而非TCP/IP连接一个数据库也可给出好一些的性能。 在Linux上，当用pgcc和-O6编译时，你将得到最快的代码。为了用这些选项编译“sql_yacc.cc”，你需要大约200M内存，因为gcc/pgcc需要很多内存使所有函数嵌入（inli

6、ne）。在配置MySQL时，你也应该设定CXX=gcc以避免包括libstdc+库(它不需要)。 只通过使用一个较好的编译器或较好的编译器选项，在应用中你能得到一个10-30%的加速。如果你自己编译SQL服务器，这特别重要！ 在Intel上，你应该例如使用pgcc或Cygnus CodeFusion编译器得到最大速度。我们已经测试了新的 Fujitsu编译器，但是它是还没足够不出错来优化编译MySQL。 这里是我们做过的一些测量表： 如果你以-O6使用pgcc并且编译任何东西，mysqld服务器是比用gcc快11%（用字符串99的版本)。 如果你动态地链接(没有-static)，结果慢了13%

7、。注意你仍能使用一个动态连接的MySQL库。只有服务器对性能是关键的。 如果你使用TCP/IP而非Unix套接字，结果慢7.5%。 在一个Sun SPARCstation 10上，gcc2.7.3是比Sun Pro C+ 4.2快13%。 在Solaris 2.5.1上，在单个处理器上MIT-pthreads比带原生线程的Solaris慢8-12%。以更多的负载/cpus，差别应该变得更大。 由TcX提供的MySQL-Linux的分发用pgcc编译并静态链接。 10.2.2 磁盘问题 正如前面所述，磁盘寻道是一个性能的大瓶颈。当数据开始增长以致缓存变得不可能时，这个问题变得越来越明显。对大数据

8、库，在那你或多或少地要随机存取数据，你可以依靠你将至少需要一次磁盘寻道来读取并且几次磁盘寻道写入。为了使这个问题最小化，使用有低寻道时间的磁盘。 为了增加可用磁盘轴的数量(并且从而减少寻道开销)，符号联接文件到不同磁盘或分割磁盘是可能的。 使用符号连接 这意味着你将索引/数据文件符号从正常的数据目录链接到其他磁盘(那也可以被分割的)。这使得寻道和读取时间更好(如果磁盘不用于其他事情)。见10.2.2.1 使用数据库和表的符号链接。 分割 分割意味着你有许多磁盘并把第一块放在第一个磁盘上，在第二块放在第二个磁盘上，并且第 n块在第(n mod number_of_disks)磁盘上，等等。这意味

9、着，如果你的正常数据大小于分割大小(或完美地排列过)，你将得到较好一些的性能。注意，分割是否很依赖于OS和分割大小。因此用不同的分割大小测试你的应用程序。见10.8 使用你自己的基准。注意对分割的速度差异很依赖于参数，取决于你如何分割参数和磁盘数量，你可以得出以数量级的不同。注意你必须选择为随机或顺序存取优化。 为了可靠，你可能想要使用袭击RAID 0+1(分割+镜像)，但是在这种情况下，你将需要2*N个驱动器来保存N个驱动器的数据。如果你有钱，这可能是最好的选择！然而你也可能必须投资一些卷管理软件投资以高效地处理它。 一个好选择是让稍重要的数据(它能再生)上存在RAID 0磁盘上，而将确实重

10、要的数据(像主机信息和日志文件)存在一个RAID 0+1或RAID N磁盘上。如果因为更新奇偶位你有许多写入，RAID N可能是一个问题。 你也可以对数据库使用的文件系统设置参数。一个容易的改变是以noatime选项挂装文件系统。这是它跳过更新在inode中的最后访问时间，而且这将避免一些磁盘寻道。人皮面具10.2.2.1 为数据库和表使用符号链接你可以从数据库目录移动表和数据库到别处，并且用链接到新地点的符号代替它们。你可能想要这样做，例如，转移一个数据库到有更多空闲空间的一个文件系统。 如果MySQL注意到一个表是一个符号链接，它将解析符号链接并且使用其实际指向的表，它可工作在支持real

11、path()调用的所有系统上(至少Linux和Solaris支持realpath()！在不支持realpath()的系统上，你应该不同时通过真实路径和符号链接访问表！如果你这样做，表在任何更新后将不一致。 MySQL缺省不支持数据库链接。只要你不在数据库之间做一个符号链接，一切将工作正常。假定你在MySQL数据目录下有一个数据库db1，并且做了一个符号链接db2指向db1：shell cd /path/to/datadirshell ln -s db1 db2人皮面具现在，对在db1中的任一表tbl_a，在db2种也好象有一个表tbl_a。如果一个线程更新db1.tbl_a并且另一个线程更新d

12、b2.tbl_a，将有问题。 如果你确实需要这样，你必须改变下列在“mysys/mf_format.c”中的代码： if (!lstat(to,&stat_buff) /* Check if its a symbolic link */ if (S_ISLNK(stat_buff.st_mode) & realpath(to,buff)把代码改变为这样： if (realpath(to,buff) 10.2.3 调节服务器参数你能用这个命令得到mysqld服务器缺省缓冲区大小： shell mysqld -help这个命令生成一张所有mysqld选项和可配置变量的表。输出包括缺省值并且看上去象

13、这样一些东西： Possible variables for option -set-variable (-O) are:back_log current value: 5connect_timeout current value: 5delayed_insert_timeout current value: 300delayed_insert_limit current value: 100delayed_queue_size current value: 1000flush_time current value: 0interactive_timeout current value: 28

14、800join_buffer_size current value: 131072key_buffer_size current value: 1048540lower_case_table_names current value: 0long_query_time current value: 10max_allowed_packet current value: 1048576max_connections current value: 100max_connect_errors current value: 10max_delayed_threads current value: 20m

15、ax_heap_table_size current value: 16777216max_join_size current value: 4294967295max_sort_length current value: 1024max_tmp_tables current value: 32max_write_lock_count current value: 4294967295net_buffer_length current value: 16384query_buffer_size current value: 0record_buffer current value: 13107

16、2sort_buffer current value: 2097116table_cache current value: 64thread_concurrency current value: 10tmp_table_size current value: 1048576thread_stack current value: 131072wait_timeout current value: 28800如果有一个mysqld服务器正在运行，通过执行这个命令，你可以看到它实际上使用的变量的值： shell mysqladmin variables 每个选项在下面描述。对于缓冲区大小、长度和栈大

17、小的值以字节给出，你能用于个后缀“K”或“M” 指出以K字节或兆字节显示值。例如，16M指出16兆字节。后缀字母的大小写没有关系；16M和16m是相同的。 你也可以用命令SHOW STATUS自一个运行的服务器看见一些统计。见7.21 SHOW语法(得到表、列的信息)。 back_log 要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话

18、说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 Unix listen(2)系统调用的手册页应该有更多的细节。检查你的OS文档找出这个变量的最大值。试图设定back_log高于你的操作系统的限制将是无效的。 connect_timeout mysqld服务器在用Bad handshake（糟糕的握手）应答前正在等待一个连接报文的秒数。 delayed_insert_timeout 一个INSERT DELAYED线程应该在终止之前等待INSERT语句的时间。 delayed_insert_limit 在插入delayed_insert_limit

19、行后，INSERT DELAYED处理器将检查是否有任何SELECT语句未执行。如果这样，在继续前执行允许这些语句。 delayed_queue_size 应该为处理INSERT DELAYED分配多大一个队列(以行数)。如果排队满了，任何进行INSERT DELAYED的客户将等待直到队列又有空间了。 flush_time 如果这被设置为非零值，那么每flush_time秒所有表将被关闭(以释放资源和sync到磁盘)。 interactive_timeout 服务器在关上它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_IN

20、TERACTIVE选项的客户。也可见wait_timeout。 join_buffer_size 用于全部联结(join)的缓冲区大小(不是用索引的联结)。缓冲区对2个表间的每个全部联结分配一次缓冲区，当增加索引不可能时，增加该值可得到一个更快的全部联结。（通常得到快速联结的最佳方法是增加索引。） key_buffer_size 索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。记住既然MySQL不缓存读取的数据，你将必须为OS文件系统缓

21、存留下一些空间。为了在写入多个行时得到更多的速度，使用LOCK TABLES。见7.24LOCK TABLES/UNLOCK TABLES语法。 long_query_time 如果一个查询所用时间超过它(以秒计)，Slow_queries记数器将被增加。 max_allowed_packet 一个包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增加到max_allowed_packet个字节。缺省地，该值太小必能捕捉大的(可能错误)包。如果你正在使用大的BLOB列，你必须增加该值。它应该象你想要使用的最大BLOB的那么大。 max_connection

22、s 允许的同时客户的数量。增加该值增加mysqld要求的文件描述符的数量。见下面对文件描述符限制的注释。见18.2.4 Too many connections错误。 max_connect_errors 如果有多于该数量的从一台主机中断的连接，这台主机阻止进一步的连接。你可用FLUSH HOSTS命令疏通一台主机。 max_delayed_threads 不要启动多于的这个数字的线程来处理INSERT DELAYED语句。如果你试图在所有INSERT DELAYED线程在用后向一张新表插入数据，行将被插入，就像DELAYED属性没被指定那样。 max_join_size 可能将要读入多于ma

23、x_join_size个记录的联结将返回一个错误。如果你的用户想要执行没有一个WHERE子句、花很长时间并且返回百万行的联结，设置它。 max_sort_length 在排序BLOB或TEXT值时使用的字节数(每个值仅头max_sort_length个字节被使用；其余的被忽略)。 max_tmp_tables （该选择目前还不做任何事情)。一个客户能同时保持打开的临时表的最大数量。 net_buffer_length 通信缓冲区在查询之间被重置到该大小。通常这不应该被改变，但是如果你有很少的内存，你能将它设置为查询期望的大小。（即，客户发出的SQL语句期望的长度。如果语句超过这个长度，缓冲区自

24、动地被扩大，直到max_allowed_packet个字节。） record_buffer 每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。 sort_buffer 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。见18.5 MySQL在哪儿存储临时文件。 table_cache 为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符，见下面对文件描述符限制的注释。对于表缓存如何工作的信息，见10.2.4 MySQL怎

25、样打开和关闭表。 tmp_table_size 如果一张临时表超出该大小，MySQL产生一个The table tbl_name is full形式的错误，如果你做很多高级GROUP BY查询，增加tmp_table_size值。 thread_stack 每个线程的栈大小。由crash-me测试检测到的许多限制依赖于该值。缺省队一般的操作是足够大了。见10.8 使用你自己的基准。 wait_timeout 服务器在关闭它之前在一个连接上等待行动的秒数。也可见interactive_timeout。 MySQL使用是很具伸缩性的算法，因此你通常能用很少的内存运行或给MySQL更多的被存以得到更

26、好的性能。 如果你有很多内存和很多表并且有一个中等数量的客户，想要最大的性能，你应该一些象这样的东西： shell safe_mysqld -O key_buffer=16M -O table_cache=128 -O sort_buffer=4M -O record_buffer=1M &如果你有较少的内存和大量的连接，使用这样一些东西： shell safe_mysqld -O key_buffer=512k -O sort_buffer=100k -O record_buffer=100k &或甚至： shell safe_mysqld -O key_buffer=512k -O sor

27、t_buffer=16k -O table_cache=32 -O record_buffer=8k -O net_buffer=1K &如果有很多连接，“交换问题”可能发生，除非mysqld已经被配置每个连接使用很少的内存。当然如果你对所有连接有足够的内存，mysqld执行得更好。 注意，如果你改变mysqld的一个选项，它实际上只对服务器的那个例子保持。 为了明白一个参数变化的效果，这样做： shell mysqld -O key_buffer=32m -help保证-help选项是最后一个；否则，命令行上在它之后列出的任何选项的效果将不在反映在输出中。 10.2.4 MySQL怎样打开和

28、关闭数据库表table_cache, max_connections和max_tmp_tables影响服务器保持打开的文件的最大数量。如果你增加这些值的一个或两个，你可以遇到你的操作系统每个进程打开文件描述符的数量上强加的限制。然而，你可以能在许多系统上增加该限制。请教你的OS文档找出如何做这些，因为改变限制的方法各系统有很大的不同。 table_cache与max_connections有关。例如，对于200个打开的连接，你应该让一张表的缓冲至少有200 * n，这里n是一个联结(join)中表的最大数量。 打开表的缓存可以增加到一个table_cache的最大值（缺省为64；这可以用mys

29、qld的-O table_cache=#选项来改变）。一个表绝对不被关闭，除非当缓存满了并且另外一个线程试图打开一个表时或如果你使用mysqladmin refresh或mysqladmin flush-tables。 当表缓存满时，服务器使用下列过程找到一个缓存入口来使用： 不是当前使用的表被释放，以最近最少使用（LRU）顺序。 如果缓存满了并且没有表可以释放，但是一个新表需要打开，缓存必须临时被扩大。 如果缓存处于一个临时扩大状态并且一个表从在用变为不在用状态，它被关闭并从缓存中释放。 对每个并发存取打开一个表。这意味着，如果你让2个线程存取同一个表或在同一个查询中存取表两次(用AS)，表

30、需要被打开两次。任何表的第一次打开占2个文件描述符；表的每一次额外使用仅占一个文件描述符。对于第一次打开的额外描述符用于索引文件；这个描述符在所有线程之间共享。 10.2.5 在同一个数据库中创建大量数据库表的缺点如果你在一个目录中有许多文件，打开、关闭和创建操作将会很慢。如果你执行在许多不同表上的SELECT语句，当表缓存满时，将有一点开销，因为对每个必须打开的表，另外一个必须被关闭。你可以通过使表缓冲更大些来减少这个开销。 10.2.6 为什么有这么多打开的表？当你运行mysqladmin status时，你将看见象这样的一些东西： Uptime: 426 Running threads:

31、 1 Questions: 11082 Reloads: 1 Open tables: 12如果你仅有6个表，这可能有点令人困惑。 MySQL是多线程的，因此它可以同时在同一个表上有许多询问。为了是2个线程在同一个文件上有不同状态的问题减到最小，表由每个并发进程独立地打开。这为数据文件消耗一些内存和一个额外的文件描述符。索引文件描述符在所有线程之间共享。 10.2.7 MySQL怎样使用内存下表指出mysqld服务器使用存储器的一些方式。在应用的地方，给出与存储器使用相关的服务器变量的名字。 关键字缓冲区(变量key_buffer_size)由所有线程分享；当需要时，分配服务器使用的其他缓冲区。见10.2.3 调节服务器参数。 每个连接使用一些线程特定的空间；一个栈(缺省64K，变量thread_stack)、一个连接缓冲区(变量net_buffer_length)和一个结果缓冲区(变量net_buffer_length)。当需要时，连接缓冲区和结果缓冲区动态地被扩大到max_allowed_packet。当一个查询正在运行当前查询的一个拷贝时，也分配字符串。 所有线程共享同一基存储器。 目前还没有什么是内存映射的(除了压缩表，但是那是另外一个的故事)。这是因为4GB的32位存储器空间对最大的数据库来所