Store & Forward Memory Buffer and Local Cache Overflow

[lrose]

@DANewton

Could it be that querying and updating this table takes too long.

Almost certainly. Especially if you're seeing the issue escalate as we move further into the month.

@DANewton

When making a tag historical, the only option is what database is used. My understanding is that it’s the default sqlt_data_xxx table. Is there a way to designate the table data will be stored in?

On the gateway, for each historical provider you can configure the data partitioning. Just a caution that being to finite here can be as much of a headache as being to broad, albeit in a different way.

@DANewton

If we’re limited to the generic table, what’s the best way to break up the table?

If you're going to use the historian then you can't really do much. If you can potentially move over to transaction groups then you have more control over how and where things are stored.

@DANewton

Is there a way to optimize this table?

There are ways to optimize the queries. It depends a lot on the database you're using. @pturmel's suggestions 2,3, & 4 are probably the best places to start.

You may also find the discussion here interesting, it isn't about exactly the same thing but definitely has some insight into your issue.

Historian Table Partitioning

I haven’t had to optimize the Historian, as I don’t ever put millions of rows in it. /-: But I’d start with studying all of the indices on the tables to make sure the queries are optimizable and optimization statistics are present. I would make sure there is a BRIN index on the t_stamp column, and a separate b-tree index on the tag id column. Recommended indexing would vary with DB brand, of course. Larger partitions should be tolerable with proper indices. The EXPLAIN ANALYZE … command wi…