主页 / dba / 问题 / 11627
Accepted
GoldenNewby
Asked: 2012-01-27 00:28:49 +0800 CST

内存少于 Index_length MyISAM

  • 772

我正在开发一个数据库来存储“时间序列”数据（此时 X 的值为 Y）。行本身非常小且大小固定，主键由两个 smallint 列、1 个 tinyint 列和 1 个 timestamp 列组成。每行的索引长度使用率非常低（大约 12 个字节），但是数据库将用于存储非常大量的数据。

所以问题是服务器最终将拥有比 MySQL 中该表的 index_length 大小更少的物理 RAM。发生这种情况意味着什么？我知道理论上 Linux 可以将内存交换到磁盘，但这是否会重复磁盘使用（因为已经存在一个 .MYI 文件）？无法将整个索引存储在 RAM 中对性能有何影响？对于 RAID 1 中的 SATA II 驱动器，我还能期望低于 10 毫秒的选择吗？

回应第一条评论以获取更多信息

目前我的问题是理论性的而不是实践性的。我正在从事的项目资金充足，从技术上讲我们可以负担 RAM 成本，但我更想知道没有足够的 RAM 来覆盖索引的影响。但无论如何，我还是会尝试回答。

从技术上讲，该项目具有无限 RAM，因此减少它的唯一原因是降低成本。

数据存储在 MyISAM 表中用于“历史”存储目的，但在前 24 小时左右存在于 NDBCluster 中（NDB Cluster 使用的索引 RAM 是 MyISAM 的大约 4 倍）。

我当然可以升级 RAM，但这样做会增加很多复杂性。

索引的MB使用量的答案是2.29MB，但毫无意义。现在我只是在测试数据结构的索引使用情况。2.29MB 包含 155,301 行（每行约 15.5 字节）。

...

所以我真正关心的只有一张桌子。其余的都非常小。表的结构如下：

CREATE  TABLE IF NOT EXISTS `monitor`.`result` (
  `server` SMALLINT UNSIGNED NOT NULL ,
  `ref_id` SMALLINT UNSIGNED NOT NULL ,
  `request` TINYINT UNSIGNED NOT NULL ,
  `recorded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `resolution` TINYINT NOT NULL ,
  `value` MEDIUMINT UNSIGNED NOT NULL ,
  PRIMARY KEY (`server`, `request`, `recorded`, `ref_id`) )
ENGINE = MyISAM

“ref_id”列存在的原因是为了缩小数据集所指的范围，使其超出服务器级别。因此，例如，我们可能拥有有关服务器上用户或设备的统计信息。

为什么我需要这么多内存

上表似乎不会使用那么多 RAM，而且在大多数实际情况下，它不会。我想存储尽可能多的数据。我知道我可以存储更少的数据，但我希望数据的分辨率尽可能高。磁盘空间很便宜，所以我什至不关心这个成本，但另一方面 RAM 可能会变得昂贵。尽管商业模式使得不必在任何程度上担心 RAM 成为可能，但我还是希望尽可能降低成本。

换句话说，我想为每个受监控的服务器每分钟最多存储 100 个统计信息。您可以看到在一千台服务器中行数迅速变大（100×1000×1044×365 = 38,106,000,000）。一千台服务器每年的预算是 120,000 美元（大量 RAM），但关键是要降低成本。

细化问题

我非常感谢到目前为止提供的答案，所以我会更具体一些，以更具体地解决我的问题。

到目前为止的答案让我认为我需要自己简单地做一些基准测试，看看会产生什么结果（为你开发！）。所以在这一点上，我真正面临的“问题”是 RAM 使用量将不可避免地达到数百 GB。

问题 1 ) 因此，如果我决定采用将大量数据放入 RAM 的方法，则需要将其分布在一堆服务器上。我已经使用 NDBCluster 执行此操作，但 NDBCluster 使用几乎 3 倍的 RAM 来存储相同的数据（15 字节对大约 48 字节）。在服务器集群的 RAM 中存储那么多数据的公认方法是什么？我应该实施一些应用程序级系统来与一堆 MyISAM 服务器集成吗？

问题 2）MyISAM 是数据库引擎的正确选择吗？我用 InnoDB 测试了一下，它似乎比 MyISAM 使用更多的 RAM 来处理索引。非 MySQL 解决方案呢？

问题 3 ) 将索引存储在磁盘上是否值得？那时我什至不应该创建一个索引，如果它无论如何都不会在 RAM 中（我非常怀疑）。

问题 4 ) 如果我选择不将数据放入 RAM 的方式，该项目推荐使用哪种磁盘设置？SSD 的 RAID？

问题 5 ) 不在索引中包含值和分辨率列是否值得？假设索引在磁盘上而不是在 RAM 中，我们在谈论浪费多少 CPU 时间？

非常感谢您的任何建议，一旦回答了这些问题（如果可能），我一定会选择一个答案

mysql myisam

2 个回答

  1. Best Answer

    观察 #1

    monitor.result如果索引页面必须经历两件事，性能影响应该很快就会变得明显：

    • 经验 1) 交换构成 MyISAM Ke​​y Cache 的 RAM
    • 体验 2) 轮转进出 MyISAM Ke​​y Cache（大小由key_buffer_size决定）

    经验 1 几乎是不可避免的。至于经验 #2，它可能会导致在面对最近对其他 MyISAM 表的查询时，所需的索引页被从 MyISAM 密钥缓存中清除。那些需要的索引页更多的是通过查询对应的表来取回。这两种体验加在一起可能会导致在相对较小的表上进行比预期慢的查询。

    monitor.result但是，您可以通过创建专用的 MyISAM 键缓存分配索引来最小化或抵消交换的任何不良影响。它将是一个仅包含来自monitor.result.

    你是怎样做的 ？？？

    首先，回想一下您提到的索引使用量monitor.result是 2.29MB。您可以创建该大小的专用密钥缓存，并留有少量净空，比如 2.5MB。让我们像这样创建该密钥缓存：

    SET GLOBAL monitor_result_private_cache.key_buffer_size = 1024 * 512 * 5;
CACHE INDEX monitor.result IN monitor_result_private_cache;
LOAD INDEX INTO CACHE monitor.result;

    这将执行以下步骤：

    1. 创建密钥缓存
    2. 使用LOAD INDEX INTO CACHE将键缓存分配给 MyISAM 表
    3. 将分配的 MyISAM 表的索引页加载到其对应的缓存中

    它可以方便地防止该表的索引页离开缓存。如果 INSERTs intomonitor.result将内容增加到 2.5MB 以上，唯一会留下的表索引页。您必须选择足够的空间来容纳许多 INSERT 到monitor.result.

    观察#2

    我还注意到您列出的索引monitor.result：

    PRIMARY KEY (`server`, `request`, `recorded`, `ref_id`)

    如果您的任何查询monitor.result类似于以下内容：

    SELECT resoultion,value FROM monitor.result
WHERE server = 200 AND refid = 50 AND ... ;

    您可以通过重新排序 PRIMARY KEY 来加快查询速度

    CREATE  TABLE IF NOT EXISTS `monitor`.`result` (            
  `server` SMALLINT UNSIGNED NOT NULL ,            
  `ref_id` SMALLINT UNSIGNED NOT NULL ,            
  `request` TINYINT UNSIGNED NOT NULL ,            
  `recorded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,            
  `resolution` TINYINT NOT NULL ,            
  `value` MEDIUMINT UNSIGNED NOT NULL ,            
  PRIMARY KEY (`server`, `ref_id`, `request`, `recorded`) )            
ENGINE = MyISAM

    或添加 UNIQUE 索引

    CREATE  TABLE IF NOT EXISTS `monitor`.`result` (             
  `server` SMALLINT UNSIGNED NOT NULL ,             
  `ref_id` SMALLINT UNSIGNED NOT NULL ,             
  `request` TINYINT UNSIGNED NOT NULL ,             
  `recorded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,             
  `resolution` TINYINT NOT NULL ,             
  `value` MEDIUMINT UNSIGNED NOT NULL ,             
  PRIMARY KEY (`server`, `request`, `recorded`, `ref_id`),
  UNIQUE KEY uniqndx1 (`server`, `ref_id`, `request`, `recorded`)
ENGINE = MyISAM

    如果添加 UNIQUE 索引，则必须相应地加倍专用 keycache。

    观察 #3

    您提到了 SATA 驱动器。归档、低更新历史数据的不错选择。SATA 驱动器上具有专用密钥缓存的任何 MyISAM 表都不应受到索引查找的困扰，但驱动器的数据检索时间将取决于您进行基准测试，以查看您是否可以忍受运行时间。

    这是一个替代方案：

    创建一个包含每一列的索引

    CREATE  TABLE IF NOT EXISTS `monitor`.`result` (            
  `server` SMALLINT UNSIGNED NOT NULL ,            
  `ref_id` SMALLINT UNSIGNED NOT NULL ,            
  `request` TINYINT UNSIGNED NOT NULL ,            
  `recorded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,            
  `resolution` TINYINT NOT NULL ,            
  `value` MEDIUMINT UNSIGNED NOT NULL ,            
  PRIMARY KEY (`server`, `ref_id`, `request`, `recorded`, `resolution`, `value`) )            
ENGINE = MyISAM

    这是做什么的？它提供严格从索引中检索整行的数据。将其与专用的密钥缓存相结合，您基本上可以在 RAM 中拥有整个表。所有查询都将由索引完成并且永远不会触及表，无论表是在 SAS、SATA、SSD 还是石头上。

    更新 2012-01-26 18:18 EDT

    问题 1：您可能想研究一下 memcached。我相信有一个带有 memcached 插件的 InnoDB 版本。至少，我是这么听说的。

    问题 2：InnoDB 用于事务表。如果您有存档数据，压缩的 MyISAM 表应该可以满足要求。事实上，您可以查看ARCHIVE 存储引擎。

    问题3：在磁盘上存储索引（MyISAM、InnoDB）始终是标准的，不能更改。您已使用特殊命令或运行特殊查询来预加载缓存。

    问题四：RAID-10 高写，SSD 高读。注意你的磁盘表面温度！！！

    问题5：如果表只是为了保存历史信息，就没必要矫枉过正。只要是很少读的表，就不需要特别考虑缓存。

    • 3

  2. 我认为 MySQL 的key cache 文档提示了您对超过分配的 RAM 数量的索引的期望：

    要控制键缓存的大小，请使用 key_buffer_size 系统变量。如果此变量设置为零，则不使用密钥缓存。如果 key_buffer_size 值太小而无法分配最小数量的块缓冲区 (8)，则也不会使用密钥缓存。

    当键缓存不可用时，仅使用操作系统提供的本机文件系统缓冲来访问索引文件。

    我假设 MySQL 足够聪明，知道 .MYI 文件的大小，并且它不适合内存；它甚至不会尝试。当您访问索引时，您将从磁盘读取，但它不会在某处的 SWAP 上创建重复副本。

    因此，您的读取速度只会与您的驱动器允许的一样快。如果发现你的 SATA II 驱动器对于这个表来说不够快，一个选择是将它变成一个分区，并将索引文件放在一些更快的驱动器（如 SSD）上。

    从create table文档中，您可以看到这是可能的：

    partition_definition:
  PARTITION partition_name
    **snip**
    [INDEX DIRECTORY [=] 'index_dir']
    **snip**

    我个人从来没有尝试过这个，因为它的费用很高，但你提到你有足够的资金。

    您可以通过将索引文件加载到 1GB 并设置key_buffer_size为 500MB 或其他大小，然后敲击读取请求以使磁盘得到利用来估计性能影响。

    • 2

相关问题