MySQL table_cache优化

  categories:mysql资料  tags:,   author:

table_cache 参数设置表高速缓存的数目。每个连接进来,都会至少打开一个表缓存。因此, table_cache 的大小应与 max_connections 的设置有关。例如,对于 200 个并行运行的连接,应该让表的缓存至少有 200 × N ,这里 N 是应用可以执行的查询的一个联接中表的最大数量。此外,还需要为临时表和文件保留一些额外的文件描述符。
当 Mysql 访问一个表时,如果该表在缓存中已经被打开,则可以直接访问缓存;如果还没有被缓存,但是在 Mysql 表缓冲区中还有空间,那么这个表就被打开并放入表缓冲区;如果表缓存满了,则会按照一定的规则将当前未用的表释放,或者临时扩大表缓存来存放,使用表缓存 的好处是可以更快速地访问表中的内容。
执行 flush tables 会清空缓存的内容。一般来说,可以通过查看数据库运 行峰值时间的状态值 Open_tables 和 Opened_tables ,判断是否需要增加 table_cache 的值。其中 open_tables 是当前打开的表的数量, Opened_tables 则是已经打开的表的数量。下面我们的例子显示了这两个状态值的变化情况:
首先,清空表缓存:
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
察看当前的表缓存情况:
mysql> show global status like open%_tables;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables | 0 |
| Opened_tables | 543 |
+—————+——-+
2 rows in set (0.00 sec)
在当前连接访问一个表:
mysql> select count(*) from t1;
+———-+
| count(*) |
+———-+
| 4 |
+———-+
1 row in set (0.03 sec)
mysql> show global status like open%_tables;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables | 1 |
| Opened_tables | 544 |
+—————+——-+
2 rows in set (0.00 sec)
Open_tables和opened_tables都增加1。
再访问一个其他表,可以看到两个参数都增加。
mysql> select count(*) from t2;
+———-+
| count(*) |
+———-+
| 1 |
+———-+
1 row in set (0.06 sec)
mysql> show global status like open%_tables;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables | 2 |
| Opened_tables | 545 |
+—————+——-+
2 rows in set (0.00 sec)
再访问表t1的时候:
mysql> select * from t1;
+——+
| id |
+——+
| 2 |
| 1 |
| 1 |
| 3 |
+——+
4 rows in set (0.02 sec)
mysql> show global status like open%_tables;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables | 2 |
| Opened_tables | 545 |
+—————+——-+
2 rows in set (0.00 sec)
两个参数都没有变化,因为该表已经在表缓存中打开了,没有重复打开。
如 果发现 open_tables 接近 table_cache 的时候,并且 Opened_tables 这个值在逐步增加,那就说明可能 table_cache 设置的偏小,经常需要将缓存的表清出,将新的表放入缓存,这时可以考虑增加这个参数的大小来改善访问的效率

 

table_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。
www.itcankao.cn
首先是MyISAM:
从官方网站上面看,每个线程会独自持有一个数据文件的文件描述符,而索引文件的文件描述符是公用的。当table cache不够用的时候,MySQL会采用LRU算法踢掉最长时间没有使用的表。如果table_cache设置过小,MySQL就会反复打开、关闭 frm文件,造成一定的性能损失。那么,table_cache设置是不是越大越好呢?从table_cache negative scalability 这篇文章的测试可以看出,如果table_cache设置过大,MySQL将会消耗很多CPU去做 table cache的算法运算(具体是哪个算法目前不清楚,有可能是LRU)。因此table_cache的值一定要设置合理,没事多看一看 opened_tables参数,如果一直增长的话,就需要适当增加table_cache的值了。

接着是InnoDB:
InnoDB的元数据管理是放在共享表空间里面做的,所以获取表的结构不需要去反复解析frm文件,这是比MyISAM强的地方。即使 table_cache设置过小,对于InnoDB的影响也是很小的,因为它根本不需要反复打开、关闭frm文件去获取元数据。  根据How innodb_open_files affects performance这篇文章的测试可以看出,table_cache和  innodb_open_files的大小对InnoDB效率的影响比较小。但是在InnoDB crash的情况下, innodb_open_files设置过小会影响recovery的效率。所以用InnoDB的时候还是把 innodb_open_files放大一些比较合适。

发现当前一个配置问题 (mysql +linux 在线系统)
show global status like open%tables%;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables | 966 |
| Opened_tables | 2919 |
+—————+——-+
2 rows in set
根据这些天潜心研究 (下面是有关知识)
table_cache指示表高速缓存的大小。当Mysql访问一个表时,如果在Mysql表缓冲区中还有空间,那么这个表就被打开并放入表缓冲区,这样做的好处是可以更快速地访问表中的内容。一般来说,可以通过查看数据库运行峰值时间的状态值Open_tables和Opened_tables,用以判断是否需要增加table_cache的值
show global status like open%tables%;查看打开表的情况
Open_tables表示打开表的数量,Opened_tables表示打开过的表数量,如果Opened_tables数量过大,说明配置中table_cache(5.1.3之后这个值叫做table_open_cache)值可能太小。
比较适合的值:
Open_tables / Opened_tables * 100% >= 85%
Open_tables / table_cache * 100% <= 95%
当前我设置这个在线db的 table_cache=1024
由此引发一个问题
966/2919 仅仅为33% 这个是明显有问题的。 (这个系统是主机 有从机器从他那里取数据)
请问我究竟该如何设置这个值 table_cache=??

——————————————————————————–
参数优化基于一个前提,就是在我们的数据库中通常都使用InnoDB表,而不使用MyISAM表。在优化MySQL时,有两个配置参数是最重要的,即table_cache和key_buffer_size。
table_cache
table_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。如果你发现 open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失
table_cache – 64
open_tables – 64
opened-tables – 431
uptime – 1662790 (measured in seconds)
虽然open_tables已经等于table_cache,但是相对于服务器运行时间来说,opened_tables的值也非常低。因此,增加table_cache的值应该用处不大。
目前从找的材料看 没有必要修改

——————————————————————————–
mysql> SHOW STATUS LIKE key_read%;
+——————-+————+
| Variable_name | Value |
+——————-+————+
| Key_read_requests | 1430416782 |
| Key_reads | 269031 |
+——————-+————+
2 rows in set
[3:42:39 PM] bruce: 总的内存需求公式是:global buffer + connections* buffer per connection.
global buffer包括:key_buffer_size & innodb_buffer_size
buffer per connection:一般按照4M计算(最差情况),包括:read_buffer, sort_buffer, thread stack,等等。
[3:47:04 PM] bruce: key_buffer_size只对MyISAM表起作用,
key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。一般我们设为 16M,实际上稍微大一点的站点 这个数字是远远不够的,通过检查状态值Key_read_requests和 Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。 或者如果你装了phpmyadmin 可以通过服务器运行状态看到,笔者推荐用phpmyadmin管理mysql,以下的状态值都是本人通过phpmyadmin获得的实例分析:
这个服务器已经运行了20天

key_buffer_size – 128M
key_read_requests – 650759289
key_reads – 79112

mysql查询tables cache的最大数:
show variables like table_cache;

mysql查询当前系统已经打开的描述符命令:
show status like open_tables;

一、调整table cache
table_cache 参数设置表高速缓存的数目。每个连接进来,都会至少打开一个表缓存。因此, table_cache 的大小应与 max_connections 的设置有关。例如,对于 200 个并行运行的连接,应该让表的缓存至少有 200 × N ,这里 N 是应用可以执行的查询的一个联接中表的最大数量。此外,还需要为临时表和文件保留一些额外的文件描述符。
当 Mysql 访问一个表时,如果该表在缓存中已经被打开,则可以直接访问缓存;如果还没有被缓存,但是在 Mysql 表缓冲区中还有空间,那么这个表就被打开并放入表缓冲区;如果表缓存满了,则会按照一定的规则将当前未用的表释放,或者临时扩大表缓存来存放,使用表缓存的好处是可以更快速地访问表中的内容。
执行 flush tables 会清空缓存的内容。一般来说,可以通过查看数据库运行峰值时间的状态值 Open_tables 和 Opened_tables ,判断是否需要增加 table_cache 的值(其中 open_tables 是当前打开的表的数量, Opened_tables 则是已经打开的表的数量)。即如果open_tables接近table_cache的时候,并且Opened_tables这个值在逐步增加,那就要考虑增加这个值的大小了。还有就是Table_locks_waited比较高的时候,也需要增加table_cache。
二、调整系统和数据库打开文件数目限制
table_cache加大后碰到文件描述符不够用的问题。这个时候就需要考虑提高系统和数据库的打开文件数目限制了。
1,显示当前系统的文件限制
# cat /proc/sys/fs/file-max
2,通过phpmyadmin查询mysql配置参数(既open files limit)。
一般情况下,mysql 5的open files limit会自动根据table cache调整。open files limit 系统默认调整为table cache的2倍多一点。

 

 



快乐成长 每天进步一点点      京ICP备18032580号-1