mysql优化之连接优化（open-files-limit与table_open_cache）
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/f新增如下⾏：
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配置⽂件myf中新增下⾯的⾏
open_files_limit =65535
innodb_open_files=65535
innodb_open_files:
This variable is relevant only if you use multiple InnoDB tablespaces. It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is10. The default val
ue is300if innodb_file_per_table
The file descriptors used for .ibd files are for InnoDB tables only. They are independent of those specified by the --open-files-limit server option, and do not affect the operation of the table cache.
open_files_limit ：
更改为 mysqld 的可⽤的⽂件描述符数量。你应该尝试增加此选项的值，如果 mysqld 给你出的错误太多打开的⽂件。mysqld 使⽤选项值保留与 setrlimit() 的描述符。在内部，此选项的最⼤值是最⼤的⽆符号的整数值，但实际最⼤值是依赖于平台。mysqld 可能尝试分配更多请求的描述符（如果可⽤），使⽤ max_connections 和 table_open_cache 的值来估计是否将需要更多的描述符数量。
在 Unix 上，设置的值不能⼩于 ulimit-n。
然后重启你的MySQL⼀般问题就解决了。
2. 背后的问题
上⾯的办法⼀般就能解决问题了。不过在实践中发现，在myf中设置的参数open_files_limit值是⽆效的，即MySQL启动后open_files_limit始终以OS的⽂件描述符为准。（版
本MySQL5.1.45 RHEL5.4）
mysql> show global variables like "%open_files_limit%";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+
1 row in set (0.0
2 sec)
那myf参数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/f
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535
看到参数open_files_limit确实没有作⽤（已经实验了很多次了）。limit -n 20000也没有⽤，直接⽤ f值
连接优化
根据中的说法，下⾯的参数会影响到客户端的每个请求：
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来计算。
2.
⼀个有趣的现象是，在我的64bit linux中， –open-files-limit或者–open_files_limit可以设置超过 64k，如：
open-files-limit可能受到操作系统的限制，⽐如linux中，/proc/sys/fs/file-max，就限制了系统最⼤能够开启的⽂件句柄数⽬。像oracle在linux的安装运⾏要求，对最低要求就是要超过 64k. 可以通过修改/f，增加或者修改 fs.file-max=#来增加系统最⼤打开值，别忘了修改完了，⽤ sysctl -p 来启⽤新值(以上操作为centos/rhel)。
在linux中，还有⼀个参数可能会限制系统最⼤打开⽂件数值，就是/etc/f
具体如何修改其值，请参考系统⽂档
受如下参数影响: 受系统限制
将影响如下参数: max_connections table_open_cache
调整触发条件: 当系统出现 Too many open files 时需要调整此参数。
3. 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 已经达到当前系统允许的最⼤值。
4. max_connections
命令⾏参数: –max_connections 或者 –max-connections
ini/cnf定义: max_connections
mysql 变量: max_connections
全局变量，可动态调整
MySQL数据库允许的并发连接数。
对于⼀个访问者众多(pv值很⾼)的⽹站来说，有时可能会发⽣ : Too many connections 的错误。可以考虑增加此值。对于MySQL来说，能够⽀持的最⼤的并发连接数，取决于很多因素，包括：
a. 操作系统线程模型、操作系统版本(参见 )
b. 可⽤的内存数量
c. 每个连接的内存使⽤量/⼯作负载(参见 )
d. 预期的服务器响应时间
在内存允许的情况下,32bit windows可以⽀持最⼤2000左右的并发请求（因为单进程最⼤⽀持的内存为2G，⽽默认的⼀个线程需要资源为1MB），64bit windows 也可以根据内存计算得出可⽀持的线程数。（关于windows中可⽤线程估算，可以参考Mark Russinovich的⽂章，或者参考微软的⼀篇）。
⽽Linux中的因素可能更复杂，不过 stack_size 依然如同windows中⼀样，是制约线程数的⼀个重要因
素，最⼤线程数在Liunx下也有默认值，cat /proc/sys/kernel/threads-max, 当不调整这个值时，MySQL的max_connections应该远⼩于它。
在实际应⽤中，可⽀持的并发数将会远⼩于理论值，因为每个线程不可能只是空连接⼀下就断开。线程⼯作时的CPU/内存损耗，会降低整个系统的可⽤资源调配。对于MySQL来说，其提供了⼀个可以调整stack size的参数: .
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 时，应该采取⾏动提⾼并发数.
5. 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 variables like "%connections%";
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| max_connections | 151 |
| max_user_connections | 0 |
+----------------------+-------+
2 rows in set (0.02 sec)
mysql> show status like ‘threads_c%’; 输出
threads_cached 当前缓存线程数
threads_connected 当前连接数
thread_created 线程创建数
mysql> show status like "%threads_c%";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 1 |
+-------------------+-------+
3 rows in set (0.03 sec)
当连接缓存的利⽤率( thread cache hit =(connections – threads_created)/connections*100%）的值较低时，表明mysql需要创建更多的线程（线程缓存不够了）来接受客户端请求。
受如下参数影响: ⽆
将影响如下参数: ⽆
调整触发条件: 当thread cache hit较低时，应该采取⾏动提⾼thread_cache_size此值.
6. 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此值.
7. net_buffer_length
命令⾏参数: –net_buffer_length
ini/cnf定义: net_buffer_length
mysql 变量: thread_cache_size
全局变量，可动态调整, 默认值16k，范围1k到1m.
客户端连接时的缓冲和结果缓冲, 可以动态调整（⾃动调整，意味着 set net_buffer_length=xxx是⽆效的）到最⼤的⼤⼩。每个SQL语句结束后，这个值会恢复到初始值。
内存不⾜时–这个情况也很少了，毕竟现在内存这么便宜–或者并发连接很⼤时，可以适当的缩⼩这个初始值，⽐如1k.
受如下参数影响: max_allowed_packet
将影响如下参数: ⽆
调整触发条件: 如果要装载/导⼊/导出⼤量数据时；查询结果中包含⼤的数据字段时，如TEXT,BLOB等
8. 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
9. wait_timeout
命令⾏参数: –wait_timeout
ini/cnf定义: wait_timeout
mysql 变量: wait_timeout
全局变量，可动态调整, 默认值8⼩时，范围1秒到31536000.
wait_timeout定义了⼀个已连接的客户端在不进⾏任何查询动作时最常的空闲时间。
注意：对已经建⽴的连接将不产⽣影响。
可以通过 show processlist 来查看当前数据库连接的状况，如：mysql操作官方文档
受如下参数影响:
将影响如下参数: max_connections
调整触发条件: 短链接、⾼并发的系统应⽤中.
《open/close table on mysql》
我们知道mysql是⼀个⽀持多线程的数据库，尤其在innodb存储引擎出现后，对mysql的事务，并发，锁⽀持得到了极⼤提⾼。在⾼并发的访问的应⽤场景中，应⽤端⼤量并发的进程发问数据库，⽽数据库中的数据表在磁盘上以数据⽂件存放，在unix，linux的系统调⽤中，是依赖于⽂件描述符的。不同的os对⽂件描述符的限制不同（⾮Unix/linux 操作系统⽆⽂件描述符概念，在windows中称作⽂件句柄），如在linux中/etc/f配置⽂件中设置他们的⽂件描述符极限。
在了解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版本中为，其默认值为64，官⽅⽂档中对该参数的解释为：
The number of open tables for all threads. Increasing this value increases the number of file descript
ors that requires.
所有threads打开表的数量，增加这个参数需要在mysqld启动的时候增加；
第⼀个问题：？
在官⽅⽂档中描述的很清晰了：
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%’;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论