Performance problems when loading data from Network History

[fkondej]

The process becomes very slow when loading data from Network History over time.
I have tested it with the situation when there was an empty database, and I was loading data for the past 15-30 hours.
It starts as expected: 2sec/segment, but after a time (30-60min) it slows down to 15-20sec/segment and then further to 30sec/segment.
The segment processing time does not depend on the size of a segment: I run a test where the first half of segments was big 100k rows a segment, then another half was 2k rows a segment, and a couple of 100k rows a segment at the end. At the begging 100k segments were processed in 1-2sec, but over time they slowed down to 20sec. When segments switched to 2k, the processing time was still ~20sec and over time slowing to 30sec, at the end a couple of 100k segments got processed in the same time ~30sec as the 2k segments.

I have been trying to correlate when the first slowness happens (when times jump from 2sec to 15sec) with the Order table size, but in one run it was 3.5GB, and in another 2.25 GB.

https://vegaprotocol.slack.com/archives/C02KVKMAE82/p1675846017348689

[gordsport]

@fkondej

Having discussed this issue we need to re-test this using at least the recommended hardware for nodes (this covers both a core and data node)

[guoguojin]

We think the problem may be related to the triggers on the database, specifically the order table not being disabled when network history reload is running.

I am going to set up my computer with the same configurations as the new n08.stagnet1.vega.xyz box that @fkondej set up and update data-node to disable those triggers to see if the problems still persist or be alleviated.