mysql配置优化之key_buffer_size

作者: wxfeng 分类: 未分类 发布时间: 2017-08-02 00:00

首先看看mysqld(MySQL 服务器启动命令,加“–verbose –help”显示全部启动选项)中是如何定义key_buffer_size 参数的:

[root@localhost zzx]# mysqld --verbose --help|grep key_buffer_size=
--key_buffer_size=# The size of the buffer used for index blocks for MyISAM

从以上定义可以看出,这个参数是用来设置索引块(Index Blocks)缓存的大小,它被所有线程共享,此参数只适用于MyISAM 存储引擎。MySQL 5.1 以前只允许使用一个系统默认的key_buffer,MySQL 5.1 以后提供了多个key_buffer,可以将指定的表索引缓存入指定的key_buffer,这样可以更小地降低线程之间的竞争。

可以这样建立一个索引缓存:

mysql> set global hot_cache2.key_buffer_size=128*1024;
Query OK, 0 rows affected (0.01 sec)

其中,global 表示对每一个新的连接,此参数都将生效。hot_cache2 是新的key_buffer 名称。

如果需要更改参数值,可以随时进行重建,例如:

mysql> set global hot_cache2.key_buffer_size=200*1024;
Query OK, 0 rows affected (0.00 sec)

然后可以把相关表的索引放到指定的索引缓存中,如下:

mysql> cache index sales,sales2 in hot_cache2;
+---------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+--------------------+----------+----------+
| sakila.sales | assign_to_keycache | status | OK |
| sakila.sales2 | assign_to_keycache | status | OK |
+---------------+--------------------+----------+----------+
2 rows in set (0.04 sec)

要想将索引预装到默认key_buffer 中,可以使用LOAD INDEX INTO CACHE 语句。例如,下面的语句可以预装表sales 的所有索引:

mysql> load index into cache sales ;
+--------------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+--------------+----------+----------+
| sakila.sales | preload_keys | status | OK |
+--------------+--------------+----------+----------+
1 row in set (0.00 sec)

如果需要删除索引缓存,则要使用下面命令:

mysql> set global hot_cache2.key_buffer_size=0;
Query OK, 0 rows affected (0.00 sec)

请注意不能删除默认key_buffer。来看一下实际删除结果:

mysql> show variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size | 8388600 |
+-----------------+---------+
1 row in set (0.00 sec)
mysql> set global key_buffer_size=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------+
| Level | Code | Message |
+---------+------+------------------------------+
| Warning | 1438 | Cannot drop default keycache |
+---------+------+------------------------------+
1 row in set (0.01 sec)

可以看出,虽然提示设置成功,但是有一个warning“Cannot drop default keycache”,提示不能删除默认key_buffer。重新创建一个连接后,参数值果然没有更改:

[zzx@localhost ~]$ mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.41-community-log MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size | 8388600 |
+-----------------+---------+
1 row in set (0.00 sec)

cache index 命令在一个表和key_buffer 之间建立一种联系,但每次服务器重启时key_buffer 中的数据将清空。如果想要每次服务器重启时相应表的索引能自动放到key_buffer中,可以在配置文件中设置init-file 选项来指定包含cache index 语句的文件路径,然后在对应的文件中写入cache index 语句。下面是一个例子:

[zzx@localhost ~]$ more /etc/my.cnf
…
key_buffer_size = 4G
hot_cache.key_buffer_size = 2G
cold_cache.key_buffer_size = 2G
init_file=/path/to/data-directory/mysqld_init.sql
…

每次服务器启动时执行mysqld_init.sql 中的语句,该文件每行应包含一个SQL 语句。下面的例子分配几个表,分别对应hot_cache 和cold_cache:

CACHE INDEX a.t1, a.t2, b.t3 IN hot_cache;
CACHE INDEX a.t4, b.t5, b.t6 IN cold_cache;

21.2.2 table_cache 的设置

在mysqld 中对table_cache 参数的定义如下:

[zzx@localhost ~]$ mysqld –verbose –help|grep table_cache=

–table_cache=# The number of open tables for all threads.

这个参数表示数据库用户打开表的缓存数量。每个连接进来,都会至少打开一个表缓存。因此,table_cache 与max_connections 有关,例如,对于200 个并行运行的连接,应该让表的缓存至少有200×N,这里N 是可以执行的查询的一个联接中表的最大数量。此外,还需要为临时表和文件保留一些额外的文件描述符。

可以通过检查mysqld 的状态变量open_tables 和opened_tables 确定这个参数是否过小,这两个参数的区别是前者表示当前打开的表缓存数,如果执行FLUSH TABLES 操作,则此系统会关闭一些当前没有使用的表缓存而使得此状态值减小;后者表示曾经打开的表缓存数,会一直进行累加,如果执行FLUSH TABLES 操作,值不会减少。下面的例子验证了这个过程。

(1)首先清空表缓存,记录两个状态的值:

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql> show global status like 'open_tables';;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 0 |
+---------------+-------+
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show global status like 'opened_tables';;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 35 |
+---------------+-------+
1 row in set (0.00 sec)

(2)然后,执行一个SQL,对表t 进行查询:

mysql> select count(1) from t;
+----------+
| count(1) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)

(3)接着再查看这两个参数的值:

mysql> show global status like 'open_tables';;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 1 |
+---------------+-------+
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show global status like 'opened_tables';;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 36 |
+---------------+-------+
1 row in set (0.00 sec)

(4)可以发现,两个参数值都因为对表t 的查询而状态加1。这时,再次执行刚才对

表t 的查询:

mysql> select count(1) from t;
+----------+
| count(1) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> show global status like 'open_tables';;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 1 |
+---------------+-------+
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show global status like 'opened_tables';;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 36 |
+---------------+-------+
1 row in set (0.00 sec)

(5)此时这两个参数的值并没有变化,因为表t 的描述符已经在此连接中打开过一次,因此保存在了表缓存中。因此,状态值“open_tables”对于设置table_cache 值有着更有价值的参考。

如果觉得我的文章对您有用,请随意赞赏。您的支持将鼓励我继续创作!

发表评论

电子邮件地址不会被公开。 必填项已用*标注