On Wed, Feb 5, 2020 at 12:25 PM Arya F <arya6000@xxxxxxxxx> wrote:
If I run the database on a server that has enough ram to load all the indexes and tables into ram. And then it would update the index on the HDD every x seconds. Would that work to increase performance dramatically?
Perhaps. Probably not dramatically though. If x seconds (called a checkpoint) is not long enough for the entire index to have been dirtied, then my finding is that writing half of the pages (randomly interspersed) of a file, even in block order, still has the horrid performance of a long sequence of random writes, not the much better performance of a handful of sequential writes. Although this probably depends strongly on your RAID controller and OS version and such, so you should try it for yourself on your own hardware.
Cheers,
Jeff
