mysql优化之连接优化(open-尊龙游戏旗舰厅官网
mysql打开的文件描述符限制
can't open file: '.\test\mytable.frm' (errno: 24) [root@localhost ~]# perror 24 os error code 24: too many open files
这就是mysql的文件描述不够用了。先说解决办法，再说背后的原因吧。
1. 如何解决第一步：设置os参数（如果你有权限的话）：
文件/etc/security/limits.conf新增如下行：
mysql soft nofile 65535 mysql hard nofile 65535
上面的配置，是os限制各个用户能够打开的文件描述符限制（hard soft区别参看man ulimit），新增上面两行，表示mysql用户能够打开65535个文件描述符
（可以使用lsof -u mysql|wc -l查看当前打开了多少个文件描述符）
[root@localhost ~]# lsof -u mysql|wc -l 63
第二步：修改mysql参数：
在mysql配置文件my.cnf中新增下面的行
更改为 mysqld 的可用的文件描述符数量。你应该尝试增加此选项的值，如果 mysqld 给你出的错误太多打开的文件。mysqld 使用选项值保留与 setrlimit() 的描述符。在内部，此选项的最大值是最大的无符号的整数值，但实际最大值是依赖于平台。如果无法分配请求的文件描述符数量，mysqld 向错误日志中写入一个警告。mysqld 可能尝试分配更多请求的描述符 （如果可用），使用 max_connections 和 table_open_cache 的值来估计是否将需要更多的描述符数量。在 unix 上，设置的值不能小于 ulimit-n。
然后重启你的mysql一般问题就解决了。
2. 背后的问题
上面的办法一般就能解决问题了。不过在实践中发现，在my.cnf中设置的参数open_files_limit值是无效的，即mysql启动后open_files_limit始终以os的文件描述符为准。（版本mysql5.1.45 rhel5.4）
那my.cnf参数open_files_limit是否真的是没用呢？接下来会是一篇很长、很蛋疼的关于该问题的研究，如果不是很有时间，不建议看下去。
3. 源代码中如何设置open_files_limit 3.1 实验验证配置文件中配置：open_files_limit = 10000；$ulimit -n 20000；启动数据库，观察：
mysql> show global variables like "%open_files_limit%"; ------------------ ------- | variable_name | value | ------------------ ------- | open_files_limit | 65535 | ------------------ ------- 1 row in set (0.03 sec)/etc/security/limits.conf
* soft nproc 65535 * hard nproc 65535 * soft nofile 65535 * hard nofile 65535
看到参数open_files_limit确实没有作用（已经实验了很多次了）。limit -n 20000也没有用，直接用 limits.conf值
连接优化主要指客户端连接数据库以及数据库为响应客户端的请求而打开数据表和索引的过程中涉及到的参数调整。原文可以参考这里或者这里。(原文链接 http://ddbiz.com/?p=950)
尽管不同的mysql发行版本的编译和链接方式也会影响到客户端的链接请求，但是由于我的系统多是直接安装mysql的发行包，且很少会做改动，因此关于手动编译mysql的以达到优化的目的的方面，此处无法聊及，或许日后会有机会涉足其中。
根据mysql如何使用内存中的说法，下面的参数会影响到客户端的每个请求：
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 时需要调整此参数。
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 已经达到当前系统允许的最大值。
max_connections
命令行参数: –max_connections 或者 –max-connections
ini/cnf定义: max_connections
mysql 变量: max_connections
全局变量，可动态调整
mysql数据库允许的并发连接数。
对于一个访问者众多(pv值很高)的网站来说，有时可能会发生 : too many connections 的错误。可以考虑增加此值。对于mysql来说，能够支持的最大的并发连接数，取决于很多因素，包括：
在内存允许的情况下,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 时，应该采取行动提高并发数.
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此值.
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
eg:mysql> show variables like "%table_open%"; 表文件描述符===》线程描术符===》总打开文件描述符 ---------------------------- ------- | variable_name | value | ---------------------------- ------- | table_open_cache | 2000 | | table_open_cache_instances | 1 | ---------------------------- ------- 2 rows in set (0.02 sec)
受如下参数影响: max_connections open_file_limit
将影响如下参数: max_connections
调整触发条件: 当opened_tables（show status like ‘opened_tables’）值很大时，应该采取行动提高table_open_cache此值.
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等
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
wait_timeout
命令行参数: –wait_timeout
ini/cnf定义: wait_timeout
mysql 变量: wait_timeout
全局变量，可动态调整, 默认值8小时，范围1秒到31536000.
wait_timeout定义了一个已连接的客户端在不进行任何查询动作时最常的空闲时间。
注意：对已经建立的连接将不产生影响。
可以通过 show processlist 来查看当前数据库连接的状况，如：
受如下参数影响:
将影响如下参数: max_connections
调整触发条件: 短链接、高并发的系统应用中.
《open/close table on mysql》
我们知道mysql是一个支持多线程的数据库，尤其在innodb存储引擎出现后，对mysql的事务，并发，锁支持得到了极大提高。在高并发的访问的应用场景中，应用端大量并发的进程发问数据库，而数据库中的数据表在磁盘上以数据文件存放，在unix，linux的系统调用中，是依赖于文件描述符的。不同的os对文件描述符的限制不同（非unix/linux 操作系统无文件描述符概念，在windows中称作文件句柄），如在linux中/etc/security/limits.conf配置文件中设置他们的文件描述符极限。
在了解mysql打开表的过程前，需要了解一些知识：
table cache：对于不同的存储引擎，table cache的作用是不同的，对于myisam表，每一个客户端线程打开任何一个myisam表的数据文件都需要打开一个文件描述符，但如果是索引文件，则可以多个线程共享同一个索引文件的描述符，table cache的主要作用应该用于缓存文件描述符，当有新的请求时不需要重新的打开，使用结束时也不用立即关闭。
对于innodb的存储引擎来说，打开表的方式与myisam是不同：
unlike myisam innodb does not have to keep open file descriptor when table is open – open table is purely logical state and appropriate .ibd file may be open or closed，innodb uses a single, global file descriptor for each .ibd file.
innodb has its own per-table cache, variously called a table definition cache or data dictionary, which you cannot configure.when innodb opens a table, it adds a corresponding object to the data dictionary. each table can take up 4 kb or more of memory(although much less space is required in mysql 5.1). tables are not removed from the data dictionary when they are closed.
在引擎上，innodb把table cache 叫做了数据字典，表的定义都缓存在数据字典中（data dictionary），文件描述符上使用一个global file descriptor来处理每个ibd文件，如果使用的是共享表空间来存储数据，则打开的文件描述符就比较少，但如果使用的是独享表空间方式（innodb_file_per_table=1）则打开的文件描述符则较多。
知道了上面的知识后，来看下面的参数：
table_cache：在mysql 5.1.3版本中为table_open_cache，其默认值为64，官方文档中对该参数的解释为：
the number of open tables for all threads. increasing this value increases the number of file descriptors thatmysqld requires.
所有threads打开表的数量，增加这个参数需要在mysqld启动的时候增加文件描述符；
第一个问题：mysql是如何打开和关闭表的？
在官方文档中描述的很清晰了：
mysql is multi-threaded, so there may be many clients issuing queries for a given table simultaneously. to minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session
mysql在访问一张表的时候，将其放入到cache中，如果数据库中有许多的表，通常将其放入到cache中，对性能的提升带来帮助。
那么在不断的新表打开中，cache被慢慢填满（table_open_cache—-full），如果新打开的表没有在cache中，mysql会将一些没有使用的table清除掉：
(1)
session 1
mysql> show variables like "%table_open%"; ---------------------------- ------- | variable_name | value | ---------------------------- ------- | table_open_cache | 2000 | | table_open_cache_instances | 1 | ---------------------------- -------root@test 10:56:22>set global table_open_cache=2;
query ok, 0 rows affected (0.00 sec)
root@test 11:07:50>flush tables;
query ok, 0 rows affected (0.00 sec)
root@test 11:08:58>show global status like ‘open%table%’;
————————– ——-
| variable_name | value |
————————– ——-
| open_table_definitions | 0 |
| open_tables | 0 |
| opened_table_definitions | 28 |
| opened_tables | 28 |
(2)
sessioin 2：
root@test 10:56:03>select * from t1;
session 3：
root@test 10:56:03>select * from t2;
session 1：
root@test 11:09:17>show global status like ‘open%table%’;
————————– ——-
| variable_name | value|
————————– ——-
| open_table_definitions | 2 |
| open_tables | 2 |
| opened_table_definitions | 30 |
| opened_tables | 30 |
(3)
session 4：
root@test 10:52:22>select * from t1;
session1：
root@test 11:11:08>show global status like ‘open%table%’;
————————– ——-
| variable_name | value |
————————– ——-
| open_table_definitions | 2 |
| open_tables | 2 |
| opened_table_definitions | 30 |
| opened_tables | 30 |
(4)
session5：
root@test 10:52:39>select * from test_1;
session1:
root@test 11:13:03>show global status like ‘open%table%’;
————————– ——-
| variable_name | value |
————————– ——-
| open_table_definitions | 3 |
| open_tables | 2 |
| opened_table_definitions | 31 |
| opened_tables | 31 |
我们可以看到，第一步：session1： 开始cache中执行 flush tables后，open_tables为0，open_table_definitions 为0；
第二步：session2,3：执行两个表的查询，session1中查询open_tables， open_table_definitions 为2；
第三步：session 4:执行session2的查询，session1中查询open_tables， open_table_definitions 没有变，保持2；
第四步：session5：执行新的查询，session中查询open_tables为2，open_table_definitions为3；
从实验上看是满足上述的情况的。
如果没有table能够被释放，cache将会根据需要临时扩展，当有table关闭或者unused，cache将会被释放：
第一步：session1： root@test 11:26:58>flush tables;
root@test 11:33:35>show global status like ‘open%table%’;
————————– ——-
| variable_name | value |
————————– ——-
| open_table_definitions | 0 |
| open_tables | 0 |
| opened_table_definitions | 38 |
| opened_tables | 39 |
第二步：
session2：
root@test 11:10:43>handler t1 open;
session3
root@test 11:10:46>handler t2 open;
第三步：session1
root@test 11:33:41>show global status like ‘open%table%’;
————————– ——-
| variable_name | value |
————————– ——-
| open_table_definitions | 2 |
| open_tables | 2 |
| opened_table_definitions | 40 |
| opened_tables | 41 |
第四步：
session4：
root@test 11:10:49>select * from t3;
第五步：
session1：
root@test 11:34:06>show global status like ‘open%table%’;
————————– ——-
| variable_name | value |
————————– ——-
| open_table_definitions | 3 |
| open_tables | 2 |
| opened_table_definitions | 41 |
| opened_tables | 42 |
第六步：
session5： root@test 11:29:59>handler test_1 open;
第七步：
root@test 11:34:19>show global status like ‘open%table%’;
————————– ——-
| variable_name | value |
————————– ——-
| open_table_definitions | 4 |
| open_tables | 3 |
| opened_table_definitions | 42 |
| opened_tables | 43 |
上面中有两个参数：
open_table_definitions
the number of cached .frm files. this variable was added in mysql 5.1.3.
open_tables
the number of tables that are open.
我们看到在使用handler open打开表的时候，该table不会被mysql清出cache，当cache被填满后，在使用handler open，cache将会被扩展；直到使用handler close关闭后释放。
那table_open_cache设为多少值合理，是不是越大table_open_cache参数设置会带来性能的上的线性提升？当我们的数据库中上千数量的表的时候，查询中有涉及复杂的多表连接，并且同时有多个connection连到mysql中执行这些query，那么就可能很快用完文件描述符cache（table_open_cache），mysql使用lru算法，把最近最少使用的描述符关闭掉，用于存放新的描述符。但是在查找要关闭的描述符中，查找时间会随着cache中的缓存数量增加而增加（o(n),n为cache的items数量），文件打开的时间等于文件关闭的时间，从而导致了性能上的下降。
在官方文档中对table_open_cache参数的设置限于对os的文件描述符的上，当然还一些相应内存开销，通常在设置table_open_cache参数的时候，我们会在业务的高峰时期，检查open_tables的值，如果open_tables的值与table_open_cache的值相等，并且opened_tales的值在不断的增加，这个时候就需要对table_open_cache的值增加了；
set global table_open_cache=m；
root@test 01:25:00>show global status like ‘open%tables’;
————— ———
| variable_name | value |
————— ———
| open_tables | 56 |
| opened_tables | 2139150 |
第二个问题：os文件描述符对该参数的限制，
当我们在调整table_open_cache的时候，还需要考虑一个参数就是os的文件描述符，如果table_open_cache参数设置的很大，mysql有可能用完文件描述符，导致mysql拒绝其他连接请求，这时候就需要根据os的文件描述符限制来设置参数的值。
调整文件描述符：open_files_limit和open-files-limit这两个参数：
官方文档中告诉我们可以通过再mysqld_safe启动中加入open-files-limit参数，mysqld_safe –open-files-limit=n,来改变open_files_limit值；
在配置文件中，我们也可以看到open_files_limit参数，但是如果你设置该参数，重启主机后，该参数的值还是以系统的文件描述符为准，所以在安装mysql的时候，在/etc/security/limits.conf配置好mysql用户对最大打开文件数的限制，建议设置到最大：
mysql soft nofiles 65535
mysql hard nofiles 65535。
一般来讲:
table_open_cache = max_connections * n ,n代表一个查询中最多打开的表数量。
table_definition_cache = 设置足够大。
对于open_files_limit，它代表同一时间点能打开的文件数上限，这里有两篇文章：http://www.orczhou.com/index.php/2010/10/mysql-open-file-limit/，http://blog.csdn.net/fbjwying2/article/details/6323897，里面具体的说了open_files_limit与哪些因素有关。我这里只简单说下我的理解。
open_files_limit的值取决于三个数值:open_files_limit,max_connections*5,10 max_connections table_cache_size*2。真实值是这三个值的最大值。
有一种情况是上面得出的最大值超过了ulimit -n设置的值时，有可能通过show variables看到值却不是真实的。因为运行于os里的进程最终会受到os的影响。所以最好就是设置ulimit -n 与 show variables like 'open_files_limit'里一样。
show global 里的open_files表示当前此时此刻打开文件的数量，如果与open_files_limit非常接近，则就要提高open_files_limit的值。
show global 里的opened_tables表示已经打开表的数量，如果与table_open_cache非常接近，则就要提高table_open_cache的值。但不要一味的增加，还要看当前open_tables的值。
注意:show global 里 open_files 代表当前此时此刻打开的文件数，opened_files代表从服务最近一次启动到现在打开的文件数。
open_tables 代表当前此时此刻打开的表，opened_tables代表从服务最近一次启动到现在打开的表。
opened_tables -----> table_open_cache.
open_files -----> open_files_limit.
出处：http://www.cnblogs.com/xuegang/mysql性能优化
mysql> show variables like "table%";
------------------------- --------
| variable_name | value |
------------------------- --------
| table_definition_cache | 256 |
| table_lock_wait_timeout | 50 |
| table_open_cache | 1024 |
| table_type | innodb |
------------------------- --------
4 rows in set (0.00 sec)
table_open_cache ：mysql每打开一个表，都会读入一些数据到table_open_cache 缓存 中
当mysql在这个缓存中找不到相应的信息时，才会去磁盘上直接读取。
mysql> show status like "open%";
-------------------------- ----------
| variable_name | value |
-------------------------- ----------
| open_files | 66 |
| open_streams | 0 |
| open_table_definitions | 135 |
| open_tables | 302 |
| opened_files | 86399846 |
| opened_table_definitions | 0 |
| opened_tables | 0 |
-------------------------- ----------
7 rows in set (0.00 sec)
open_tables 当前打开的表数目 302 。table_open_cache 1024 。所有现在没必要对缓存进行调整。
mysql> show variables like "thread%";
error 2006 (hy000): mysql server has gone away
no connection. trying to reconnect...
connection id: 81208905
current database: zxx
------------------- ---------------------------
| variable_name | value |
------------------- ---------------------------
| thread_cache_size | 64 |
| thread_handling | one-thread-per-connection |
| thread_stack | 196608 |
------------------- ---------------------------
3 rows in set (0.04 sec)
每当客户端连接到mysql数据库，mysql数据库就会创建以个线程为它服务
但是首先mysql回去thread_cache中寻找可用的线程，找不到才会创建新的线程
当前线程状况：
mysql> show status like "thread%";
------------------- -------
| variable_name | value |
------------------- -------
| threads_cached | 61 |
| threads_connected | 3 |
| threads_created | 2371 |
| threads_running | 3 |
------------------- -------
table_open_cache(5.1以前版本为table_cache)
table_open_cache指定表高速缓存的大小。每当mysql访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容
通过检查峰值时间的状态值open_tables和opened_tables，可以决定是否需要增加table_open_cache的值。如果你发现open_tables等于table_open_cache，并且opened_tables在不断增长，那么你就需要增加table_open_cache的值了
5.max_connections
最大连接数
6.back_bog
back_log值指出在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的tcp/ip连接的侦听队列的大小.
7.thread_cache
mysql里面为了提高客户端请求创建连接过程的性能,提供了一个连接池也就是thread_cache池,将空闲的连接线程放在连接池中,而不是立即销毁.这样的好处就是,当又有一个新的请求的时候,mysql不会立即去创建连接线程,而是先去thread_cache中去查找空闲的连接线程,如果存在则直接使用,不存在才创建新的连接线程.
thread_cache_size
thread_cache中存放的最大连接线程数.在短连接的应用中thread_cache的功效非常明显
thread_stack
每个连接被创建的时候,mysql分配给它的内存.这个值一般认为默认就可以应用于大部分场景了
thread_cache命中率计算
show variables like 'thread%';show status like '%connections%';show status like '%thread%'
公式为：thread_cache_hit=(connections-thread_create)/connections*100%
总结
以上是尊龙游戏旗舰厅官网为你收集整理的mysql优化之连接优化(open-files-limit与table_open_cache）的全部内容，希望文章能够帮你解决所遇到的问题。
- 上一篇： 【micropython】用python
- 下一篇： android studio 中 .9.