4

I'm trying to get the fastest possible queries from PostgreSQL, and I'm going to be testing this out but I want to know what kinds of issues could I run in to.

Servers

  • 1X PostgreSQL Master. With all of it's data on a 20GB Ramdisk. (Leaving ~12GB of RAM for OS and programs)
  • 2X PostgreSQL Replica (Hot-Standby). With all of it's data on a RAID 10 of SSDs.

Config

  • Synchronous commit is disabled
  • wal_buffer is set to 16MB
  • wal_writer_delay is 400ms
  • checkpoint_segments is 64
  • shared_buffers is 3GB

Loss of data that has not been committed yet is acceptable in this setup. But once the data is committed after the 400ms then it needs to be able to survive any single machine in this setup failing.

If the master fails that is okay and the last ~400ms is lost which is fine. But one of the other two nodes should then pick up where the master left off; although without the RAM disk.

We want to be able to query and insert data as fast as absolutely possible, and we have contingencies built into our application to handle the master failing. What problems would this configuration cause, or what problems or difficulties might we face?

Any other information that might be needed I can provide.

ECourant
  • 108
  • 1
  • 14
  • 2
    Why bother with the ramdisk? If you make `shared_buffers` big enough, you have the same effect. Or simply let the filesystem use the RAM for caching –  Jun 05 '18 at 13:50
  • Would setting shared buffers to the size of the database basically yield the same results? – ECourant Jun 05 '18 at 13:52
  • You would have worse performance for writes, because the transaction log has to be synced to disk. On the up side, you would be running a supported configuration that is not bound to eat or corrupt your data. – Laurenz Albe Jun 05 '18 at 13:56
  • So to achieve good write performance as well would it be good to have something like a PCI SSD just for the transaction log. And keep the database on the SSDs and keep the shared_buffers as high as possible? – ECourant Jun 05 '18 at 13:58
  • `(Leaving ~12GB of RAM for OS and programs)` You must be running Java... – joop Jun 05 '18 at 13:59
  • This definitely should be in https://dba.stackexchange.com – Juan Carlos Oropeza Jun 05 '18 at 14:01
  • How would I migrate this question there without reposting @JuanCarlosOropeza – ECourant Jun 05 '18 at 14:02
  • moderator will close this and move it when enough votes – Juan Carlos Oropeza Jun 05 '18 at 14:15

0 Answers0