MySQL Variables key_buffer_size 数据库 参数变量解释及正确配置使用
2023-06-13 09:11:25 时间
本站中文解释
key_buffer_size is the size
of the buffer used for index blocks. The key buffer is also
known as the key cache. The minimum permissible setting is 0, but you cannot set
key_buffer_size to 0
dynamically. A setting of 0 drops the key cache, which is not
permitted at runtime. Setting
key_buffer_size to 0 is
permitted only at startup, in which case the key cache is not
initialized. Changing the
key_buffer_size setting at
runtime from a value of 0 to a permitted non-zero value
initializes the key cache. key_buffer_size can be
increased or decreased only in increments or multiples of 4096
bytes. Increasing or decreasing the setting by a nonconforming
value produces a warning and truncates the setting to a
conforming value. The maximum permissible setting for
key_buffer_size is
4GB−1 on 32-bit platforms. Larger values are permitted
for 64-bit platforms. 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. You can increase the value to get better index handling for
all reads and multiple writes; on a system whose primary
function is to run MySQL using the
MyISAM storage engine, 25% of the
machine s total memory is an acceptable value for this
variable. However, you should be aware that, if you make the
value too large (for example, more than 50% of the
machine s total memory), your system might start to page
and become extremely slow. This is because 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.
You should also consider the memory requirements of any other
storage engines that you may be using in addition to
MyISAM. For even more speed when writing many rows at the same time,
use LOCK TABLES. See
Section 8.2.4.1, “Optimizing INSERT Statements”. You can check the performance of the key buffer by issuing a
SHOW STATUS statement and
examining the
Key_read_requests,
Key_reads,
Key_write_requests, and
Key_writes status variables.
(See Section 13.7.5, “SHOW Statements”.) The
Key_reads/Key_read_requests ratio should
normally be less than 0.01. The
Key_writes/Key_write_requests ratio is
usually near 1 if you are using mostly updates and deletes,
but might be much smaller if you tend to do updates that
affect many rows at the same time or if you are using the
DELAY_KEY_WRITE table option. The fraction of the key buffer in use can be determined using
key_buffer_size in
conjunction with the
Key_blocks_unused status
variable and the buffer block size, which is available from
the key_cache_block_size
system variable:
buffer is allocated internally for administrative structures.
Factors that influence the amount of overhead for these
structures include block size and pointer size. As block size
increases, the percentage of the key buffer lost to overhead
tends to decrease. Larger blocks results in a smaller number
of read operations (because more keys are obtained per read),
but conversely an increase in reads of keys that are not
examined (if not all keys in a block are relevant to a query). It is possible to create multiple MyISAM
key caches. The size limit of 4GB applies to each cache
individually, not as a group. See
Section 8.10.2, “The MyISAM Key Cache”.
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 MySQL Variables key_buffer_size 数据库 参数变量解释及正确配置使用
key_buffer_size 是 MySQL 服务器用于缓存索引页的关键变量。 MySQL将数据表索引缓存到 Key_buffer 中以提高数据检索的性能。如果你主要使用MySQL进行数据检索操作,比如读取大量数据库行,就要做出调优。
可以通过以下三种方法设置 key_buffer_size:
1.通过my.cnf文件:如果在MySQL的配置文件(一般是my.cnf)中未定义key_buffer_size参数,MySQL就会使用默认值(一般是8MB);
2.通过MySQL客户端:用户也可以使用MySQL客户端连接上MySQL服务,然后使用以下命令设置key_buffer_size:
SET GLOBAL key_buffer_size = N;
3.通过控制台:除此之外,也可以在命令行控制台中直接使用:
mysqld -–key_buffer_size=N -–v
官方英文解释 key_buffer_sizekey_buffer_size is the size
of the buffer used for index blocks. The key buffer is also
known as the key cache. The minimum permissible setting is 0, but you cannot set
key_buffer_size to 0
dynamically. A setting of 0 drops the key cache, which is not
permitted at runtime. Setting
key_buffer_size to 0 is
permitted only at startup, in which case the key cache is not
initialized. Changing the
key_buffer_size setting at
runtime from a value of 0 to a permitted non-zero value
initializes the key cache. key_buffer_size can be
increased or decreased only in increments or multiples of 4096
bytes. Increasing or decreasing the setting by a nonconforming
value produces a warning and truncates the setting to a
conforming value. The maximum permissible setting for
key_buffer_size is
4GB−1 on 32-bit platforms. Larger values are permitted
for 64-bit platforms. 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. You can increase the value to get better index handling for
all reads and multiple writes; on a system whose primary
function is to run MySQL using the
MyISAM storage engine, 25% of the
machine s total memory is an acceptable value for this
variable. However, you should be aware that, if you make the
value too large (for example, more than 50% of the
machine s total memory), your system might start to page
and become extremely slow. This is because 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.
You should also consider the memory requirements of any other
storage engines that you may be using in addition to
MyISAM. For even more speed when writing many rows at the same time,
use LOCK TABLES. See
Section 8.2.4.1, “Optimizing INSERT Statements”. You can check the performance of the key buffer by issuing a
SHOW STATUS statement and
examining the
Key_read_requests,
Key_reads,
Key_write_requests, and
Key_writes status variables.
(See Section 13.7.5, “SHOW Statements”.) The
Key_reads/Key_read_requests ratio should
normally be less than 0.01. The
Key_writes/Key_write_requests ratio is
usually near 1 if you are using mostly updates and deletes,
but might be much smaller if you tend to do updates that
affect many rows at the same time or if you are using the
DELAY_KEY_WRITE table option. The fraction of the key buffer in use can be determined using
key_buffer_size in
conjunction with the
Key_blocks_unused status
variable and the buffer block size, which is available from
the key_cache_block_size
system variable:
1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)This value is an approximation because some space in the key
buffer is allocated internally for administrative structures.
Factors that influence the amount of overhead for these
structures include block size and pointer size. As block size
increases, the percentage of the key buffer lost to overhead
tends to decrease. Larger blocks results in a smaller number
of read operations (because more keys are obtained per read),
but conversely an increase in reads of keys that are not
examined (if not all keys in a block are relevant to a query). It is possible to create multiple MyISAM
key caches. The size limit of 4GB applies to each cache
individually, not as a group. See
Section 8.10.2, “The MyISAM Key Cache”.
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 MySQL Variables key_buffer_size 数据库 参数变量解释及正确配置使用
相关文章
- MySql数据库基础之分组查询详解
- 获取MySQL系统当前时间详解数据库
- 库使用MVC模式构建MySQL数据库连接(mvc连接mysql数据)
- MySQL Variables datadir 数据库 参数变量解释及正确配置使用
- MySQL Variables ssl_key 数据库 参数变量解释及正确配置使用
- MySQL数据表导出实战:从不熟练到熟练(mysql导出表数据)
- MySQL中降序排序应用实例(mysql降序排序)
- 之间的区别MySQL vs. MySQLi: 理解两者的差异(mysql和mysqli)
- MySQL异地备份解决方案:简单可靠的工具(mysql异地备份工具)
- 简单MySQL数据库连接方式探寻(mysql数据库连接方式)
- MySQL表中如何增加列(mysql表增加列)
- 「全面掌握!」MySQL数据库语句大全,应有尽有!(数据库mysql语句大全)
- MySQL 数据库重新同步(mysql重新同步)
- MySQL企业数据监控:保障企业运行安全(mysql企业监控)
- 【群晖:实现 MySQL 数据管理功能】(群晖mysql)
- 管理极致的MySQL库存管理实践(mysql库存)
- MySQL自然连接:实现数据库最佳性能(mysql自然连接)
- 了解MySQL数据库复制:复制方法和优势(mysql数据库复制)
- 如何配置LANMP环境中的MySQL数据库?(lanmpmysql)
- MySQL主从数据库恢复:一步一步迈出成功的步伐(mysql 主从修复)
- Mysql在X64环境下的安装配置(mysql+x64+安装)
- MySQL数据库连接池及连接数配置(mysql连接数配置)
- 使用MySQL实现数据倒序排列的方法(mysql中倒序怎么)
- MySQL中如何使用WS(mysql中ws怎么用)
- C语言连接MySQL轻松实现数据库交互(c sql连接mysql)
- CMD下搭建MySQL环境,让你拥有更多能力(cmd环境mysql)
- API技术连接MySQL数据库的最佳方法(api如何连接mysql)
- 7zip压缩的MySQL数据库极致效率的安装运行(5.7zip版mysql)
- MySQL 56专业强大的数据库解决方案(5.6mysql数据库)
- 探索MySQL XP最高版本,提升数据库性能与效率(mysql xp最高版本)
- MySQL用户登录失败,如何解决(mysql不能登录用户)
- MySQL建立约束失败,解决方法探究(mysql不能建约束)
- 解决方案MySQL数据库不支持TOP命令的替代方法(mysql 不支持top)
- 如何用workbench导出mysql数据库关系图