Hi Adam,
I'm not sure what exactly your refer to with "the system seems to slow down", i.e., what exactly slows down: inserts / appends to the (small?) source tables, or queries over the (large) "Events" merge table that access all data?
As for the "cyclic merge table", if your source tables were per area and month rather than per area and year (e..g., Area1_01_2017), you could "easily" mimic such cyclic merge table by creating a merge table that covers only the last, say, 6 month, and then once a month remove the oldest of them and add a new one. Not sure whether this is what you meant ...
Best, Stefan
----- On Oct 26, 2018, at 12:50 AM, Doherty, Adam adam.doherty@esso.ca wrote:
Hello all:
I am wondering if anyone has a remote table strategy such that “hot data” is stored in a table that is backed by RAM.
I have a table with approximately 1 billion events that grows by approximately 1,000 events a minute.
Table is a merge table of merge tables, the parent merge table has a child merge table for every year of data under those child tables are source tables that make up data for areas.
So
Area1_2017, Area2_2017 -> Events_2017
Area1_2018, Area2_2018 -> Events_2018
Events_2017, Events_2018 -> Events
I am finding that as I add more data the system seems to slow down.
I am wanting to keep a cyclical table of say the last 6 months from the Events table in a table backed solely by RAM
So a query to Recent_Events would remain very fast or at the very least constant.
This should be achievable with tmpfs.
I am currently running 11.29.3 on CentOS 7 on VMware 6.7
Server has 128GB of RAM and 16 cores.
Adam
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list