2

I'm currently a startup and using non server grade NVMEs/SSD on my database server. My motherboard , CPU , DDR (ECC) etc are server grade except for the flash part.

I'm looking for ways to reduce the amount of writes on my Postgresql Database to preserve the lifespan of Flash Drives hopefully to reduce the failure rate.

My requirements

  • Write speed (Low Priority)
  • High Read speed & low latency (High Priority)
  • Low Writes (High Priority)

Questions

  • What kind of storage format do you recommend for a non-raided ubuntu system - ext4/xfs
  • What are the configuration settings I should look into
  • Any form of settings would improve usage of ram over flash drives
  • Should I reduce maintenance / vacuum frequency.
  • Block Size for Disk.
  • Separate disk for WAL ?

"This question needs to be more focused. It is not currently accepting answers." You know, if you close questions like this , stack exchange will only have newbies questions and won't attract the real professionals.

This question and issue is real, TBW gets burn hard and fast with the default configuration and there isn't much discussion on this. If you can't recover this , i will just migrate the content and build another stack exchange.

Improve this question
5
  • Hi and welcome to the forum! If your drives are modern, then you'll likely be drawing your pension before your drives fail! I would urge you to get a RAID setup (with a battery backed cache) - even a simple one and use that if your server data is important to you! That way, even if a drive fails, you just replace it and you're grand! Of course, this involves money, but only you know how much your data (and a good night's sleep :-) ) is worth to you! See here.
    – Vérace
    Commented Mar 17, 2021 at 18:53
  • @Vérace hi mate , nowadays it's rarely MLC. The best you get is TLC , mostly are QLC and because of that, the disk doesn't last forever anymore. I used to run a crypto currency mining farm, the hardware that died the fastest was SSD. I saw you wrote battery backed cache raid, I didn't know there was such a thing.
    – JohnDotOwl
    Commented Mar 17, 2021 at 22:36
  • See here - it would appear this poster's TLC disk should last ~ 20 years with his usage pattern - you say that fast writes aren't your priority and that the write load won't be very heavy - which is right up an SSD's alley - reads don't wear them down (unlike HDD's). A 2 disk mirrored arrangement might be best (consider RTO & RPO)- the chances of two disks failing at once should be vanishingly small - but, if your writes aren't huge, maybe a nightly cloud backup also (belt and braces) +1 learnt more about SSDs!
    – Vérace
    Commented Mar 18, 2021 at 18:22
  • Whoever who closed this post, I think you're gonna kill the whole forum at this rate it's going
    – JohnDotOwl
    Commented Mar 29, 2021 at 6:39
  • Hi. If you look into the help centre under Closed Questions you can find the following explanation: "Needs more focus - If your question has many valid answers (but no way to determine which, if any, are correct), then it probably needs to be more focused to be successful in our format.". This summary included the explanation: "This question currently includes multiple questions in one. It should focus on one problem only." This sums up how the community at the time felt about your question.
    – John K. N.
    Commented May 18, 2022 at 13:52

1 Answer 1

Reset to default
1

To reduce the number of checkpoints as much as possible, increase max_wal_size and checkpoint_timeout. The price is longer recovery time after a crash.

Make sure that shared_buffers is not too low, so that there is no memory pressure that forces dirty pages out to disk between checkpoints. You could experiment with setting bgwriter_lru_maxpages = 0, so that the background worker does not proactively clean out pages between checkpoints.

Set wal_compression = on so that full page writes are compressed (at the expense of CPU time).

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.