Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I'm using Postgresql-11, with a traditional HDD as the physical storing device.
I need to optimize the performance for writing.
I have changed the wal_buffers from -1 to 512MB, that means that wal_buffers no long use shared_buffers. Am I right?
I want to know whether or not it can improve the writing performance to enlarge shared_buffers. In my mind, it is used only for querying?
The current value of shared_buffers is 2GB, my RAM is 8GB.
I want to change it to 4GB.
thanks!
It is not a very viable strategy in general, but it could work in some specific cases, such as if the total size of the index pages which need to be maintained during bulk inserting is >2GB but <4GB.
Because PostgreSQL doesn't use direct IO, but rather reads pages through the normal kernel calls, that means pages are first read into the filesystem cache before being read into shared_buffers. So in general at 4GB out of 8GB, the two caches will be "double buffering" reducing their effectiveness. If I wanted to go the high-shared_buffers route, I would set it to 6 to 7GB (or 7.5, but not with wal_buffers set so high--which I also have doubts about), not 4GB.
