mysql - 分析数据库

Question

我正在建立一个大型数据库，它将根据传入的数据生成统计报告。
该系统的大部分操作如下:

1. 每天早上将上传大约 400k-500k 行 - 大约 30 列，主要是 varchar(5-30) 和 datetime。它在平面文件形式下约为 60MB，但随着添加合适的索引，它在数据库中急剧增长。
2. 将从当天的数据中生成各种统计数据。
3. 将生成并存储来自这些统计数据的报告。
4. 当前数据集将被复制到分区历史表中。
5. 一整天，最终用户可以查询当前数据集(已复制，未移动)以获取可能不包含常量但包含字段之间关系的信息。
6. 用户可以请求从历史表中进行专门的搜索，但查询将由 DBA 设计。
7. 在第二天上传之前，截断当前数据表。

这实际上是我们现有系统的第 2 版。

现在，我们正在使用 MySQL 5.0 MyISAM 表(Innodb 仅在空间使用上就很吃力)并且在 #6 和 #4 上遭受了很大的痛苦。 #4 目前不是分区表，因为 5.0 不支持它。为了避免将记录插入历史记录所花费的大量时间(数小时和数小时)，我们每天都将数据写入未索引的 history_queue 表，然后在周末最慢的时间将队列写入历史表。问题在于，本周生成的任何历史查询都可能比那时晚几天。我们不能减少历史表上的索引，否则它的查询将变得不可用。

我们肯定会在下一个版本中至少迁移到 MySQL 5.1(如果我们继续使用 MySQL)，但强烈考虑 PostgreSQL。我知道辩论已经进行到死，但我想知道是否有人对这种情况有任何建议。大多数研究都围绕网站使用展开。索引确实是我们对 MySQL 的主要关注点，而且 PostgreSQL 似乎可以通过部分索引和基于函数的索引来帮助我们解决问题。

我已经阅读了数十篇关于两者之间差异的文章，但大多数都过时了。 PostgreSQL 长期以来一直被贴上“更先进，但更慢”的标签——将 MySQL 5.1 与 PostgreSQL 8.3 进行比较时，情况仍然如此，还是现在更平衡了？

商业数据库(Oracle 和 MS SQL)根本不是一个选择——尽管我希望 Oracle 是。

我们在 MyISAM 与 Innodb 上的注意事项:我们正在运行 Innodb，对我们来说，我们发现它慢得多，慢了 3-4 倍。但是，我们对 MySQL 也较新，坦率地说，我不确定我们是否为 Innodb 适当调整了数据库。

我们在正常运行时间非常长的环境中运行 - 电池备份、故障转移网络连接、备用发电机、完全冗余系统等。因此，MyISAM 的完整性问题经过权衡后被认为是可以接受的。

关于 5.1:我听说 5.1 存在稳定性问题。一般来说，我认为任何最近(过去 12 个月内)的软件都不是绝对稳定的。考虑到重新设计项目的机会，5.1 中更新的功能集实在是太多了，不容错过。

关于 PostgreSQL 陷阱:没有任何 where 子句的 COUNT(*) 对我们来说是非常罕见的情况。我不认为这是一个问题。COPY FROM 几乎没有 LOAD DATA INFILE 灵活，但中间加载表可以解决这个问题。我最担心的是缺少 INSERT IGNORE。我们在构建一些处理表时经常使用它，这样我们就可以避免将多条记录放入两次，然后不得不在最后做一个巨大的 GROUP BY 来删除一些重复项。我认为它的使用频率不高，因此可以容忍缺乏它。

Answer 1

我的工作尝试了一个试点项目，以从 ERP 设置中迁移历史数据。数据的大小偏小，只有 60Gbyte，覆盖超过 2100 万行，最大的表有 1600 万行。还有约 1500 万行等待进入管道，但由于其他优先事项，试验已被搁置。该计划是使用 PostgreSQL 的“作业”工具来安排查询，这些查询将每天重新生成适合用于分析的数据。

在 1600 万条记录的大型表上运行简单的聚合，我注意到的第一件事是它对可用 RAM 量的敏感程度。一次增加 RAM 允许一年的聚合，而无需诉诸顺序表扫描。

如果您决定使用 PostgreSQL，我强烈建议您重新调整配置文件，因为它往往带有尽可能最保守的设置(以便它可以在内存很少的系统上运行)。调整需要一点时间，可能需要几个小时，但一旦达到可接受的响应点，只需设置它并忘记它。

完成服务器端调优后(这一切都与内存有关，令人惊讶!)您将把注意力转移到索引上。索引和查询计划也需要一些努力，但一旦设置好，您会发现它是有效的。部分索引是一个很好的功能，用于隔离其中包含“边缘情况”数据的记录，如果您要在大量类似数据中寻找异常(exception)情况，我强烈推荐此功能。

最后，使用表空间功能将数据重新定位到快速驱动器阵列上。