将数据和索引数据保存在内存中 - InnoDB与MyISAM [英] Keeping data plus index-data in memory - InnoDB vs. MyISAM

问题描述

假设一个包含1 GB数据和1 GB索引数据的数据库。

Assume a database consisting of 1 GB of data and 1 GB of index data.

为了最小化磁盘IO，从而最大限度地提高性能，我想为MySQL分配内存包含索引的整个数据集可以保存在RAM中（假设机器有大量RAM）。

To minimize disk IO and hence maximize performance I want to allocate memory to MySQL so that the entire dataset including indexes can be kept in RAM (assume that the machine has RAM in abundance).

InnoDB参数 innodb_buffer_pool_size 用于指定InnoDB用于缓存其表的数据和索引的内存缓冲区的大小。 （注意：内存用于数据 AND 索引。）

The InnoDB parameter innodb_buffer_pool_size is used to specify the size of the memory buffer InnoDB uses to cache data and indexes of its tables. (Note: The memory is used for data AND indexes.)

MyISAM参数 key_buffer_size 用于指定MyISAM用于缓存其表的索引的内存缓冲区的大小。 （注意：内存只用于 ONLY 索引。）

The MyISAM parameter key_buffer_size is used to specify the size of the memory buffer MyISAM uses to cache indexes of its tables. (Note: The memory is used ONLY for indexes.)

如果我想要2 GB数据库（1 GB数据和1 GB索引）以适应InnoDB下的内存，我只需将 innodb_buffer_pool_size 配置为 2GB 。

If I want the 2 GB database (1 GB data and 1 GB index) to fit into memory under InnoDB, I'd simply configure the innodb_buffer_pool_size to be 2GB. The two gigabytes will hold both the data and the index.

但是，当设置MyISAM键 key_buffer_size 到 2GB ，该空间将用于索引，但不用于数据。

However, when setting the MyISAM key key_buffer_size to 2GB that space will be used for the index, but not for the data.

我的问题是：

• MyISAM的数据缓冲区大小

推荐答案

• 没有MyISAM没有通用数据缓存。这在的key_buffer_size描述中有所记录官方文档：这是因为MySQL依靠操作系统来执行文件系统缓存以进行数据读取，因此您必须为文件系统缓存留出一些空间。


• No MyISAM has no general purpose data cache. This is documented in the "key_buffer_size" description from the official documentation: 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.

现代操作系统，尤其是Linux，往往具有非常智能的虚拟内存子系统，它们将频繁访问的文件保存在页面缓存中，

Modern OSes, especially Linux, tend to have very smart virtual memory subsystems that will keep frequently accessed files in the page cache, so disk I/O is kept at a bare minimum when the working set fits in available memory.



• 所以，为了回答你的第二个问题：从不。 / li>
  
• So to answer your second question: never.

对于各种myisam变量，如read_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size等，重要的是不要陷入一些是动态分配的，所以更大的并不总是意味着更快 - 有时甚至更慢 - 看这个

It's important not to fall into "buffer oversizing" too for the various myisam variables such as read_buffer_size, read_rnd_buffer_size, sort_buffer_size, join_buffer_size, etc as some are dynamically allocated, so bigger doesn't always mean faster - and sometimes it can even be slower - see this post on mysqlperformanceblog for a very interesting case.

如果您在posix平台上使用5.1版本，您可以在mysqlperformanceblog上使用-always-better /rel =noreferrer> post 。 ，您可能需要对 myisam_use_mmap 进行基准测试对你的工作负载，它应该通过减少malloc（）调用的数量来帮助高争用案例。

If you're on 5.1 on a posix platform, you might want to benchmark myisam_use_mmap on your workload, it's supposed to help high contention cases by reducing the quantity of malloc() calls.

这篇关于将数据和索引数据保存在内存中 - InnoDB与MyISAM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！