大容量数据聚合
我需要聚合传入数据并将结果聚合保存到一个表中。下面是数据的样本。
id,time
1967,2014-04-12 00:42:55+00
1967,2014-04-12 00:42:51+00
1972,2014-04-12 00:42:51+00
1972,2014-04-12 00:42:49+00
1972,2014-04-12 00:42:40+00我需要做一个简单的聚合：
SELECT id, MIN(time),MAX(time),COUNT(id) FROM xx GROUP BY id我试过几件事：
每次输入一段数据时直接更新一个表。当然太慢了。
将输入的数据保存到临时表中，并定期运行与上面类似的聚合查询，然后将结果保存到表中。这种方法更好，但随着容量的增加，对我的磁盘的影响太大了。
我目前的方法是一个守护进程程序，它读取传入的数据，将其聚合到内存中，然后根据某些时间标准将结果保存到表中。这种方法的效果要好得多，但仍然不能达到我想要的程度。
我正在使用Postgres 9.1在一个4磁盘RAID 10系统的15k SCSI驱动器和32 10的RAM。没有磁盘分区，WAL和所有东西都在一个磁盘上。我意识到这是一个很大的表现。硬件更改在这一点上是有问题的。这个系统跟不上我需要做的事情。目前，我需要每秒编写数千个这样的聚合，我将选择的解决方案必须扩展到更多。
我一直在探索分布式数据库选项，我喜欢Cassandra背后的想法。然而，由于我主要是一名软件开发人员，所以我没有跟上所有选项的步伐。移动到云中的分布式数据库似乎是下一步的好选择。能够按min、max和count列进行排序将是理想的，但如果有必要，我可以在应用层中这样做。
另一个重要的注意事项是，这些数据汇总的结果实际上很少需要，而且仅供人类使用。其中一小部分将被实际观察到。我尝试实时地生成聚合(通过查询具有原始数据的表)，但最后对大型表(100m+行)进行了顺序扫描。如果聚合这么多数据，并且没有看到其中的大部分数据，那就太荒谬了，我很乐意听到一种根据请求生成这些数据的方法。我的SQL技能是基本的，可能我缺少一些有用的技术。
下面是postgres配置的一些相关部分：
shared_buffers = 22000MB
temp_buffers = 256MB
work_mem = 896MB
maintenance_work_mem = 16MB
fsync = off
wal_buffers = 64MB
checkpoint_segments = 128
checkpoint_timeout = 60min
checkpoint_completion_target = 0.9
random_page_cost = 2.0
effective_cache_size = 8192MB
default_statistics_target = 200
autovacuum_max_workers = 6
autovacuum_naptime = 15s
autovacuum_vacuum_threshold = 25
autovacuum_analyze_threshold = 10
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000 所以我的问题是：
这些任务的postgres配置如何？用我目前的硬件来看这个任务的限制是什么？是否有适合我需要的分布式数据库？
回答 2
Database Administration用户
发布于 2014-04-24 09:19:22
我会将原始数据写入一个没有索引或约束的非常基本的表，甚至没有主键。
如果可以，可以一次插入多个行，这比单行插入要快。
如果你有能力在灾难性的事件中丢失一些数据，那就让它成为一个未登录表，这样更快。
如果您能够在灾难性事件中松散一些数据，并且所有的插入都可以在一个会话中运行，那么将其设置为一个临时表，这就更快了。不过，可能不可能。
每次选择一个新的分区(每天？)并在适当的时机将旧的分区聚合到永久的存储中。
Database Administration用户
发布于 2014-04-29 11:12:57
关于Postgres配置的一些注释：
Shared_buffers可以在PostgreSQL 9.3上设置得更高，而不必重新编译内核。您是否必须重新编译内核才能将设置设置得如此高？如果您的shared_buffers已经设置了这么高的内存，那么几乎所有内存都用于work_mem是否安全？根据正在处理的用户和表的数量，设置值会被乘以。8表8表=16倍指定的数额或与之接近的数额。这个设置为你的内存的2/3，我担心这将远远超出它的意图。1MB的wal_buffers似乎是大型系统的标准。您确定64‘t在这一点上不会影响perfs吗？effective_cache_size应该设置为可用内存的2/3。22000 MB8GB限制了Postgres所能使用的内存总量。
不管设置是什么，限制始终是磁盘的速度和上面软件层的智能。尽可能多地在RAM中聚合并按建议将其转储到未记录的临时表中可能是最快的方法。我会转储机械磁盘并切换到PCI-e SSD卡。较新的SSD的访问时间以纳秒为单位，在机械磁盘上为毫秒。在PCI卡上嵌入的RAID SSD的传输速率可以达到每秒多千兆字节，而在SCSI上可以达到100-300 vs /S的范围。对于这样一项要求很高的任务，我是不会少考虑的。
至于分布式DB，考虑到您似乎只有一个数据源和一个目的地，我怀疑您是否能够更快地进行聚合。如果有多个客户端每秒发送数千个If，那么云可以帮助减少地理差异和互连延迟，但我不指望它。我会直接联系亚马逊并把孩子给他们。在这种水平上，没有其他人会有足够的脊梁或技术资源来帮助你。
只有我的2c。
https://dba.stackexchange.com/questions/63787
复制相似问题
腾讯云开发者
