分析数据库

Question

我正在建立一个大型数据库，它将根据输入的数据生成统计报告。

该系统大部分将按以下方式运作：

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

这基本上是我们现有系统的第二版。

现在，我们使用的是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 )根本就不是一种选择--尽管我希望Oracle这样做。

关于MyISAM对Innodb的注意:我们运行Innodb，而对于我们来说，我们发现它要慢得多，大约慢3-4倍。但是，我们也比MySQL更新得多，坦率地说，我不确定我们是否为Innodb适当地调优了db。

我们运行在一个高度正常运行的环境中--电池备份、失效网络连接、备份发电机、完全冗余系统等等。因此，对MyISAM的完整性问题进行了权衡，并被认为是可以接受的。

关于5.1:我听说了5.1的稳定性问题。一般来说，我认为最近(在过去12个月内)的任何一款软件都不是稳定的。5.1中更新的功能集太大了，不能放弃，因为有机会重新设计这个项目。

关于PostgreSQL问题，对于我们来说，不带where子句的COUNT(*)是非常罕见的情况。我不认为这是个问题。COPY几乎不像loading那样灵活，但是中间加载表将修复这个问题。我最关心的是缺少INSERT忽略。我们经常在构建一些处理表时使用它，这样我们就可以避免将多个记录放入两次，然后不得不在最后完成一个庞大的组，只为了删除一些陷阱。我认为它的使用很少，以致于它的缺乏是可以容忍的。

Answer 1

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

在大的1600万张记录表上运行简单的聚合，我注意到的第一件事是它对可用RAM的数量是多么的敏感。RAM的增加允许一年的聚合，而不需要进行连续的表扫描。

如果您决定使用PostgreSQL，我强烈建议重新调整配置文件，因为它倾向于使用最保守的设置(这样它就可以在内存很少的系统上运行)。调优需要一些时间，也许需要几个小时，但是一旦您将其调整到一个可以接受的响应点，只需设置它并忘记它。

一旦您完成了服务器端的调优(这都是关于内存的，惊喜！)你会把注意力转到索引上。索引和查询规划也需要一些努力，但一旦设置好，您就会发现它是有效的。部分索引是一个很好的功能来隔离那些记录中有“边缘-大小写”数据，我强烈推荐这个特性，如果您正在寻找异常的类似数据海洋。

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

Answer 2

在我的实际经验中，我不得不说，postgresql的性能从7.x/8.0跃升到8.1 (对于我们的用例来说，速度快了2x-3x )，从8.1提高到8.2，改进较小，但仍然值得注意。我不知道8.2和8.3之间的改进，但我希望性能也会有所提高，我还没有测试过它。

关于索引，我建议删除这些索引，只有在用您的数据填充数据库之后才能再次创建它们，这样做要快得多。

进一步改善你的postgresql设置的垃圾，有这么多的好处。默认设置现在至少是合理的，在8.2倍前pg被优化为在pda上运行。

在某些情况下，特别是如果您有复杂的查询，它可以帮助禁用您的设置中的嵌套循环，这迫使pg对您的查询使用更好的执行方法。

啊，是的，我说过你应该选择postgresql吗？

(另一种选择是firebird，它不太灵活，但根据我的经验，在某些情况下，它的性能要比mysql和postgresql好得多)

Answer 3

根据我的经验，对于非常简单的查询，Inodb要快得多，对于更复杂的查询，pg要快得多。Myisam在检索方面可能甚至比Innodb还要快，但在索引/索引修复方面可能更慢。

这些主要是varchar字段，您是否用char(n)索引来索引它们？

你能让其中一些正常化吗？重写将花费您的时间，但可能会节省后续查询的时间，因为您的行大小将减少，因此一次将更多的行插入内存。

编辑：

好的，那么您有两个问题，每天的查询时间和更新历史记录，对吗？

至于第二点:在我的经验中，mysql myism在重新索引方面很差。在您每天的记录大小(0.5到100万条记录，以及相当宽的(非规范化平面输入)记录)上，我发现重写表比插入和等待重新索引和相应的磁盘敲击要快。

因此，这可能有帮助，也可能没有帮助：

create new_table select * from old_table ;

复制表，但不复制索引。

然后像往常一样插入新记录。然后在新表上创建索引，等待一段时间。删除旧表，并将新表重命名为旧表。

编辑:作为对第四条评论的回应:我不知道MyIsam总是那么糟糕。我知道，在我的特殊情况下，我对复制表然后添加索引的速度如此之快感到震惊。当它发生时，我正在做一些类似于您正在做的事情，将大型非规范化平面文件复制到数据库中，然后重命名数据。但这是一件轶事，不是数据。;)

(我还发现总体InnoDb更快，因为我做的插入和查询一样多。数据库使用的一个非常特殊的情况。)

注意，使用select .*，b.value作为foo联接进行复制.也比更新a.foo = b.value快.联接，如下所示，因为更新是对索引列的更新。