mysql优化之连接优化

Posted by Money Talks on 2012/02/23 |

第一篇 序章
第二篇 连接优化
第三篇 索引优化
第四篇 查询优化
第五篇 到实战中去

连接优化

连接优化主要指客户端连接数据库以及数据库为响应客户端的请求而打开数据表和索引的过程中涉及到的参数调整。原文可以参考这里或者这里。(原文链接 http://ddbiz.com/?p=950)
尽管不同的mysql发行版本的编译和链接方式也会影响到客户端的链接请求，但是由于我的系统多是直接安装mysql的发行包，且很少会做改动，因此关于手动编译mysql的以达到优化的目的的方面，此处无法聊及，或许日后会有机会涉足其中。

根据MySQL如何使用内存中的说法，下面的参数会影响到客户端的每个请求：

1. open-files-limit

   命令行参数: –open-files-limit=#
   ini/cnf参数: open-files-limit
   mysql 变量: open_files_limit
   全局变量，不可动态调整，取值范围 0到65535。
   open_files_limit指mysql能够打开的文件句柄数。该值不足时，会引发 Too many open files错误。具体需要多少个文件句柄，还需要根据 max_connections 和 table_open_cache来计算。
   一个有趣的现象是，在我的64bit linux中， –open-files-limit或者–open_files_limit可以设置超过 64k，如：
   

   open-files-limit可能受到操作系统的限制，比如linux中，/proc/sys/fs/file-max，就限制了系统最大能够开启的文件句柄数目。像oracle在linux的安装运行要求，对最低要求就是要超过 64k. 可以通过修改/etc/sysctl.conf，增加或者修改 fs.file-max=#来增加系统最大打开值，别忘了修改完了，用 sysctl -p 来启用新值(以上操作为centos/rhel)。
   在linux中，还有一个参数可能会限制系统最大打开文件数值，就是/etc/security/limits.conf
   
   具体如何修改其值，请参考系统文档

   受如下参数影响: 受系统限制
   将影响如下参数: max_connections table_open_cache
   调整触发条件: 当系统出现 Too many open files 时需要调整此参数。

2. thread_stack

   命令行参数: –thread_stack=#
   ini/cnf参数: thread_stack
   mysql 变量: thread_stack
   全局变量，不可动态调整。
   32bit系统中默认为192k, 64bit系统中默认为256k. 先谈及thread_stack是因为他对下面要讲的max_connections有关键影响因素。

   thread_stack 对应于操作系统层面中的stack size，windows中的默认线程的stack size为1M, linux根据版本不同会有变化，一般在8m或者10m。在我的几个Centos 5.x/6.x中，默认的stack size 都是10M(这要比windows高出10倍)

   ulimit -s
   10240

   stack size在32bit的OS中是一个很重要的参数，减少一个线程的stack size可以增加线程数，比如从10m减少到64k。但是在64bit的Linux（内核版本>= 2.6.x）中，如果允许 /proc/sys/vm/overcommit_memory，stack size或许没那么重要了。

   受如下参数影响: 无
   将影响如下参数: max_connections
   调整触发条件: max_connections 已经达到当前系统允许的最大值。

3. max_connections

   命令行参数: –max_connections 或者 –max-connections
   ini/cnf定义: max_connections
   mysql 变量: max_connections
   全局变量，可动态调整

   MySQL数据库允许的并发连接数。
   对于一个访问者众多(pv值很高)的网站来说，有时可能会发生 : Too many connections 的错误。可以考虑增加此值。对于MySQL来说，能够支持的最大的并发连接数，取决于很多因素，包括：

   1. 操作系统线程模型、操作系统版本(参见 thread_size)
   2. 可用的内存数量
   3. 每个连接的内存使用量/工作负载(参见 thread_size)
   4. 预期的服务器响应时间

   在内存允许的情况下,32bit windows可以支持最大2000左右的并发请求（因为单进程最大支持的内存为2G，而默认的一个线程需要资源为1MB），64bit windows 也可以根据内存计算得出可支持的线程数。（关于windows中可用线程估算，可以参考Mark Russinovich的文章Pushing the Limits of Windows: Processes and Threads，或者参考微软的一篇简述(进程地址空间)）。
   而Linux中的因素可能更复杂，不过 stack_size 依然如同windows中一样，是制约线程数的一个重要因素，最大线程数在Liunx下也有默认值，cat /proc/sys/kernel/threads-max, 当不调整这个值时，MySQL的max_connections应该远小于它。

   在实际应用中，可支持的并发数将会远小于理论值，因为每个线程不可能只是空连接一下就断开。线程工作时的CPU/内存损耗，会降低整个系统的可用资源调配。对于MySQL来说，其提供了一个可以调整stack size的参数: thread_stack.
   mysql 的 max_connections * thread_stack 应小于可用内存；根据mysql的官方文档(doc5.5)，linux(或者solaris)下，可以支持500到1000个并发连接，如果每个连接的工作很小，且服务器内存很大的话，那么可以支持到10k的链接。在windows下，则有一个 (open tables*2+open connection) < 2048的限制。因此：

   受如下参数影响: thread_stack table_open_cache open_file_limit
   将影响如下参数: 无
   调整触发条件: 当threads_connected（show status like ‘threads_connected’） 接近 max_connections 时，应该采取行动提高并发数.

4. thread_cache_size

   命令行参数: –thread_cache_size
   ini/cnf定义: thread_cache_size
   mysql 变量: thread_cache_size
   全局变量，可动态调整, 默认值0，最大值16k

   mysql使用此参数限定数据库服务中，缓存多少个线程共客户端使用。 如果服务器每秒都有几百个新的连接出现，那么这个值应该挑高一些。通过评测connections和threads_created判定是否需要增加thread_cache_size.
   mysql> show status like ‘%connections%’; 输出
   Connections 尝试连接请求（包括不能成功建立连接的请求）
   Max_used_connections 最大并发连接数量

   mysql> show status like ‘threads_c%’; 输出
   threads_cached 当前缓存线程数
   threads_connected 当前连接数
   thread_created 线程创建数

   当连接缓存的利用率( thread cache hit =(connections – threads_created)/connections*100%） 的值较低时，表明mysql需要创建更多的线程（线程缓存不够了）来接受客户端请求。

   受如下参数影响: 无
   将影响如下参数: 无
   调整触发条件: 当thread cache hit较低时，应该采取行动提高thread_cache_size此值.

5. table_open_cache/table_cache

   命令行参数: –table-open-cache
   ini/cnf定义: table_open_cache
   mysql 变量: table_open_cache
   全局变量，可动态调整, 默认值400，最大值512k

   mysql打开表的描述符，被缓存在table_open_cache中，table_open_cache >= max_connections * 2，这是因为有些表打开时，需要两个文件符，如myisam表，另外还有index、临时表等的文件符。自链接的查询语句，会额外再多开启一个该表的文件符。

   一个针对性的设置是，找到和数据库有关的所有最复杂的查询语句（包括自链接,left/right/outer join，以及group 等统计语句）查看这些链接将打开多少数据表，设定此值为 N，则

   table_open_cache > max_connections * N

   受如下参数影响: max_connections open_file_limit
   将影响如下参数: max_connections
   调整触发条件: 当opened_tables（show status like ‘opened_tables’）值很大时，应该采取行动提高table_open_cache此值.

6. net_buffer_length

   命令行参数: –net_buffer_length
   ini/cnf定义: net_buffer_length
   mysql 变量: thread_cache_size
   全局变量，可动态调整, 默认值16k，范围1k到1m.

   客户端连接时的缓冲和结果缓冲, 可以动态调整（自动调整，意味着 set net_buffer_length=xxx是无效的）到最大max_allowed_packet的大小。每个SQL语句结束后，这个值会恢复到初始值。内存不足时–这个情况也很少了，毕竟现在内存这么便宜–或者并发连接很大时，可以适当的缩小这个初始值，比如1k.

   受如下参数影响: max_allowed_packet
   将影响如下参数: 无
   调整触发条件: 如果要装载/导入/导出大量数据时；查询结果中包含大的数据字段时，如TEXT,BLOB等

7. max_allowed_packet

   命令行参数: –max_allowed_packet
   ini/cnf定义: max_allowed_packet
   mysql 变量: max_allowed_packet
   全局变量，可动态调整, 默认值1m，范围1k到1g.

   客户端和服务端的max_allowed_packet需要一致，或者客户端的max_allowed_packet要大于服务端的max_allowed_packet。

   受如下参数影响: 无
   将影响如下参数: 无
   调整触发条件: 如果要装载/导入/导出大量数据时；查询结果中包含大的数据字段时，如TEXT,BLOB等

   Q:max_allowed_packet和net_buffer_length会影响load data infile吗？
   A:No

8. wait_timeout

   命令行参数: –wait_timeout
   ini/cnf定义: wait_timeout
   mysql 变量: wait_timeout
   全局变量，可动态调整, 默认值8小时，范围1秒到31536000.
   wait_timeout定义了一个已连接的客户端在不进行任何查询动作时最常的空闲时间。
   注意：对已经建立的连接将不产生影响。
   可以通过 show processlist 来查看当前数据库连接的状况，如：
   [singlepic id=96 w=320 h=240 float=none]

   受如下参数影响:
   将影响如下参数: max_connections
   调整触发条件: 短链接、高并发的系统应用中.

上一篇 序章 下一篇 索引优化