mysql Key_buffer_size参数的优化设置

先来看看document对这个参数的解释:

缓存myisam表的索引块大小,可以被所有进程所共享。当设置key_buffer_size,操作系统不会马上分配key_buffer_size设置的值,而是在需要的时候,再分配的。可以设置多个key_buffer,当设置不是默认key_buffer为0时,mysql会把缓存的索引块移到默认的key_buffer中去并删除不再使用的索引块。Myisam表中只能cache索引块,不能cache数据块。

原本描述:

Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.

The maximum allowable setting for key_buffer_size is 4GB on 32-bit platforms. As of MySQL 5.0.52, values larger than 4GB are allowed for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB with a warning). The effective maximum size might be less, depending on your available physical RAM and per-process RAM limits imposed by your operating system or hardware platform. The value of this variable indicates the amount of memory requested. Internally, the server allocates as much memory as possible up to this amount, but the actual allocation might be less.

Increase the value to get better index handling (for all reads and multiple writes) to as much as you can afford. Using a value that is 25% of total memory on a machine that mainly runs MySQL is quite common. However, if you make the value too large (for example, more than 50% of your total memory) your system might start to page and become extremely slow. MySQL relies on the operating system to perform file system caching for data reads, so you must leave some room for the file system cache. Consider also the memory requirements of other storage engines.

1、建立缓存索引 :

mysql> set global key_buffer_1.key_buffer_size=8384512;
Query OK, 0 rows affected (0.01 sec)
mysql> set global key_buffer_2.key_buffer_size=8384512;
Query OK, 0 rows affected (0.01 sec)

2、把指定表放到key buffer中

mysql> cache index t1,t2 in key_buffer_1;
+————+——————–+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————+——————–+———-+———-+
| luoxuan.t1 | assign_to_keycache | status | OK |
| luoxuan.t2 | assign_to_keycache | status | OK |
+————+——————–+———-+———-+
2 rows in set (0.00 sec)

3、预先装载表的索引块

mysql> load index into cache t1,t2;
+————+————–+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————+————–+———-+———-+
| luoxuan.t1 | preload_keys | status | OK |
| luoxuan.t2 | preload_keys | status | OK |
+————+————–+———-+———-+
2 rows in set (0.00 sec)

下面我们来看一下,如果计算命中率及key buffer的使用率

Cache命中率:
100 – ( (Key_reads * 100) / Key_read_requests )

Key buffer的使用率
100 – ( (Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size )在mysql数据库中,mysql key_buffer_size是对MyISAM表性能影响最大的一个参数,下面就将对mysql Key_buffer_size参数的设置进行详细介绍,供您参考。

下面一台以MyISAM为主要存储引擎服务器的配置:

mysql> show variables like 'key_buffer_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| key_buffer_size | 536870912 |
+-----------------+------------+

分配了512MB内存给mysql key_buffer_size,我们再看一下key_buffer_size的使用情况:
mysql> show global status like 'key_read%';

+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_read_requests | 27813678764 |
| Key_reads | 6798830 |
+------------------------+-------------+

一共有27813678764个索引读取请求,有6798830个请求在内存中没有找到直接从硬盘读取索引,计算索引未命中缓存的概率:

key_cache_miss_rate = Key_reads / Key_read_requests * 100%

比如上面的数据,key_cache_miss_rate为0.0244%,4000个索引读取请求才有一个直接读硬盘,已经很BT了,key_cache_miss_rate在0.1%以下都很好(每1000个请求有一个直接读硬盘),如果key_cache_miss_rate在0.01%以下的话,key_buffer_size分配的过多,可以适当减少。
MySQL服务器还提供了key_blocks_*参数:

mysql> show global status like 'key_blocks_u%';
+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_blocks_unused | 0 |
| Key_blocks_used | 413543 |
+------------------------+-------------+

Key_blocks_unused表示未使用的缓存簇(blocks)数,Key_blocks_used表示曾经用到的最大的blocks数,比如这台服务器,所有的缓存都用到了,要么增加key_buffer_size,要么就是过渡索引了,把缓存占满了。比较理想的设置:

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

key_buffer_size设置注意事项
1.单个key_buffer的大小不能超过4G,如果设置超过4G,就有可能遇到下面3个bug:

http://bugs.mysql.com/bug.php?id=29446

http://bugs.mysql.com/bug.php?id=29419

http://bugs.mysql.com/bug.php?id=5731

2.建议key_buffer设置为物理内存的1/4(针对MyISAM引擎),甚至是物理内存的30%~40%,如果key_buffer_size设置太大,系统就会频繁的换页,降低系统性能。因为MySQL使用操作系统的缓存来缓存数据,所以我们得为系统留够足够的内存;在很多情况下数据要比索引大得多。

3.如果机器性能优越,可以设置多个key_buffer,分别让不同的key_buffer来缓存专门的索引

上面只是对"新手"来说的,我们还可以更深入地优化key_buffer_size,使用"show status"来查看"Key_read_requests, Key_reads, Key_write_requests 以及Key_writes ",以调整到更适合你的应用的大小,Key_reads/Key_read_requests的大小正常情况下得小于0.01

参考资料:

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_key_buffer_size

优化mysql之key_buffer_size

key_buffer_size
key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads /key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%'获得)。
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
对于1G内存的机器,如果不使用MyISAM表,推荐值是16M(8-64M)

提升性能的建议:
1.如果opened_tables太大,应该把my.cnf中的table_cache变大
2.如果Key_reads太大,则应该把my.cnf中key_buffer_size变大.可以用Key_reads/Key_read_requests计算出cache失败率
3.如果Handler_read_rnd太大,则你写的SQL语句里很多查询都是要扫描整个表,而没有发挥键的作用
4.如果Threads_created太大,就要增加my.cnf中thread_cache_size的值.可以用Threads_created/Connections计算cache命中率
5.如果Created_tmp_disk_tables太大,就要增加my.cnf中tmp_table_size的值,用基于内存的临时表代替基于磁盘的


MySQL优化小案例:key_buffer_size

key_buffer_size是对MyISAM表性能影响最大的一个参数,下面一台以MyISAM为主要存储引擎服务器的配置:

mysql> SHOW VARIABLES LIKE '%key_buffer_size%';
  
下面查看key_buffer_size的使用情况:

mysql> SHOW GLOBAL STATUS LIKE '%key_read%';
+-------------------+-----------------+
| Variable_name     | Value           |
+-------------------+-----------------+
| Key_read_requests | 2454354135490   |
| Key_reads         | 23490           |
+-------------------+-----------------+
2 rows in set (0.00 sec)
一共有Key_read_requests个索引请求,一共发生了Key_reads次物理IO

Key_reads/Key_read_requests ≈ 0.1%以下比较好。

经过对比,针对我的内存是64G的,所以我把Key_buffer_size设置为2048M,感觉好多了,么有了内存溢出情况。解决了问题。后续有什么情况我们小编继续补充。

(0)

相关推荐

  • 优化mysql之key_buffer_size设置

    key_buffer_size key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度.通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理.比例key_reads /key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE 'key_read%'获得). key_buffer_size只对MyISAM表起作用.

  • mysql优化的重要参数 key_buffer_size table_cache

    MySQL服务器端的参数有很多,但是对于大多数初学者来说,众多的参数往往使得我们不知所措,但是哪些参数是需要我们调整的,哪些对服务器的性能影响最大呢?对于使用Myisam存储引擎来说,主要有key_buffer_size和table_cache两个参数.对于InnoDB引擎来说主要还是以innodb_开始的参数,也很好辨认. 查看MySQL参数,可以使用show variables和show status命令查看,前者查看服务器静态参数,即在数据库启动后不会动态更改的值,比如缓冲区.字符集等.后

  • mysql Key_buffer_size参数的优化设置

    先来看看document对这个参数的解释: 缓存myisam表的索引块大小,可以被所有进程所共享.当设置key_buffer_size,操作系统不会马上分配key_buffer_size设置的值,而是在需要的时候,再分配的.可以设置多个key_buffer,当设置不是默认key_buffer为0时,mysql会把缓存的索引块移到默认的key_buffer中去并删除不再使用的索引块.Myisam表中只能cache索引块,不能cache数据块. 原本描述: Index blocks for MyIS

  • MySQL内存及虚拟内存优化设置参数

    mysql 优化调试命令   1.mysqld --verbose --help 这个命令生成所有mysqld选项和可配置变量的列表 2.通过连接它并执行这个命令,可以看到实际上使用的变量的值: mysql> SHOW VARIABLES; 还可以通过下面的语句看到运行服务器的统计和状态指标: mysql>SHOW STATUS: 使用mysqladmin还可以获得系统变量和状态信息: shell> mysqladmin variables shell> mysqladmin ex

  • MYSQL配置参数优化详解

    MySQL参数优化对于不同的网站,及其在线量,访问量,帖子数量,网络情况,以及机器硬件配置都有关系,优化不可能一次性完成,需要不断的观察以及调试,才有可能得到最佳的效果. 1)连接请求的变量 1.max_connections MySQL的最大连接数,如果服务器的并发连接请求量较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,MySQL回味每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值. 数值过小经常会出现ERROR 1

  • MySQL MyISAM 优化设置点滴

    最近在配置mysql服务器需要用到的一些设置,经过测试发现比较不错的配置方案,亮点在最后啊 先说一点问题:   Mysql中的InnoDB和MyISAM是在使用MySQL中最常用的两个表类型,各有优缺点.两种类型最主要的差别就是 InnoDB 支持事务处理与外键和行级锁.而MyISAM不支持.所以Myisam往往就容易被人认为只适合在小项目中使用.但是从数据库需求角度讲,要求99.9%的稳定性,方便的扩展性和高可用性来说的话,那MyISAM绝对应该是首选.MyISAM类型的表强调的是性能,其执行

  • mysql myisam 优化设置设置

    myisam_max_[extra]_sort_file_size足够大 delay_key_write减少io,提高写入性能 bulk_insert_buffer_size concurrent_insert 设置为2 read_rnd_buffer_size random scan 使用 read_buffer_size 顺序扫描表使用 key cache 的三种方式 key cache 预加载 SET GLOBAL hot_cache.key_buffer_size=16m SET BLO

  • MySQL核心参数优化文件my.ini实现

    目录 一.数据库服务器配置 二.CPU的优化 三.内存的优化 四.IO的优化 五.连接的优化 六.数据一致性的优化 一.数据库服务器配置 CPU:48C内存:128GDISK:3.2TSSD 二.CPU的优化 innodb_thread_concurrency=32表示SQL经过解析后,允许同时有32个线程去innodb引擎取数据,如果超过32个,则需要排队:值太大会产生热点数据,global锁争用严重,影响性能 三.内存的优化 query_cache_type=0query_cache_siz

  • MySQL 4G内存服务器配置优化

    公司网站访问量越来越大(日均超10万PV),MySQL自然成为瓶颈,关于 MySQL 的优化,最基本的是 MySQL 系统参数的优化. MySQL对于web架构性能的影响最大,也是关键的核心部分.MySQL的设置是否合理优化,直接影响到web的速度和承载量!同时,MySQL也是优化难度最大的一个部分,不但需要理解一些MySQL专业知识,同时还需要长时间的观察统计并且根据经验进行判断,然后设置合理的参数. 下面我们了解一下MySQL优化的一些基础,MySQL自身(my.cnf)的优化. 我们介绍一

  • MySQL配置文件my.cnf优化详解(mysql5.5)

    MySQL 5.5.13 参数说明: [client] character-set-server = utf8 port = 3306 socket = /data/mysql/3306/mysql.sock [mysqld] character-set-server = utf8 user = mysql port = 3306 socket = /data/mysql/3306/mysql.sock basedir = /usr/local/webserver/mysql datadir =

  • 对MySQL配置参数 my.ini/my.cnf的详细解析

    以下的文章主要描述的是对MySQL配置参数 my.ini/my.cnf的详细解析,我们主要是以实例演示的方式来对MySQL配置参数 my.ini/my.cnf的实际操作步骤进行说明,以下就是相关内容的具体描述. 1.获取当前配置参数 要优化MySQL配置参数,首先要了解当前的配置参数以及运行情况.使用下列命令可以获得目前服务器使用的配置参数: 复制代码 代码如下: mysqld –verbose –help mysqladmin variables extended-status –u root

  • Ubuntu中MySQL的参数文件my.cnf示例详析

    前言 对于MySQL的理解,我认为很多性能优化工作.主从主主复制都是在调整参数,来适应不同时期不同数量级的数据. 故,理解透彻my.cnf里的参数是永恒的话题:只有理解透彻了参数设置,才能在某些方面对数据库进行调优. 前几天刚接手一个MySQL数据,操作系统为Ubuntu 16.04.5 LTS,  数据库版本为5.7.23-0ubuntu0.16.04.1(APT方式安装的MySQL).这个操作系统下的MySQL的配置文件my.cnf很多地方都让人有点不适应(跟之前的MySQL环境有些出入,之

随机推荐