MonetDB刷新数据在后台最佳策略与活动连接进行查询 [英] MonetDB refresh data in background best strategy with active connections making queries

问题描述

我正在测试MonetDB，并在我的笔记本电脑上查询数百万行时获得惊人的性能。

I'm testing MonetDB and getting an amazing performance while querying millions of rows on my laptop.

我希望在生产中使用数十亿，我需要尽可能频繁地更新数据，比如说每1分钟或5分钟的最坏情况。只需更新现有记录或添加新记录，即可每天安排一次删除。

I expect to work with billions in production and I need to update the data as often as possible, let say each 1 minute or 5 minutes worst case. Just updating existing records or adding new ones, deletion can be scheduled once a day.

我在测试中看到了更新的良好性能，但我有点担心相同的操作超过三倍的四倍多的数据。

I've seen a good performance for the updates on my tests, but i'm a bit worried about same operations over three of four times more data.

关于BULK插入，在5秒内获得了100万行，因此现在还有足够好的性能。我没有尝试删除。

About BULK insert, got 1 million rows in 5 secs, so good enough performance right now as well. I have not tried deletion.

一切正常，除非您在更新数据的同时运行查询，在这种情况下似乎被冻结了很长时间长时间。

Everything works fine unless you run queries at the same time you update the data, in this case all seems to be frozen for a long-long-long time.

那么，在后台更新MonetDB的最好的策略是什么？
感谢

So, what's the best strategy to get MonetDB updated in background? Thanks

推荐答案

您可以在具有相同模式的新表中进行每次加载，然后创建 VIEW ，它们都在一起。查询将在视图上运行，并且删除和重新创建该视图的速度非常快。

You could do each load in a new table with the same schema, then create a VIEW that unions them all together. Queries will run on the view, and dropping and recreating that view is very fast.

但是，最好将现在和以后合并一些较小的表格。例如，每夜作业可以将前一天的所有装入表合并到新表（独立运行，没有问题），然后再次重新创建视图。

However, it would probably be best to merge some of these smaller tables together every now and then. For example, a nightly job could combine all load tables from the previous day(s) into a new table (runs independently, no problem) and then recreate the view again.

或者，您也可以使用 BINARY COPY INTO 加速加载过程。

Alternatively, you could use the BINARY COPY INTO to speed up the loading process in the first place.

这篇关于MonetDB刷新数据在后台最佳策略与活动连接进行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！