Tom Lane wrote:
Martin Foster <[EMAIL PROTECTED]> writes:
Gaetano Mendola wrote:
change this values in: shared_buffers = 50000 sort_mem = 16084
wal_buffers = 1500
This value of wal_buffers is simply ridiculous.
Instead I think is ridiculous a wal_buffers = 8 ( 64KB ) by default.
There isn't any reason to set wal_buffers higher than the amount of WAL log data that will be generated by a single transaction, because whatever is in the buffers will be flushed at transaction commit. If you are mainly dealing with heavy concurrency then it's the mean time between transaction commits that matters, and that's even less than the average transaction length.
I partially agree with you, tell me how decide that value without even now the typical queries, the tipical load ... nothing. I suggested to OP to keep the wal_buffers so high in order to eliminate one freedom of degree in his performance problems. You can see from following reply,
======================================================================== Gaetano Mendola wrote: Unfortunately there is no a "wizard tuning" for postgres so each one of us have a own "school". The data I gave you are oversized to be sure to achieve improvements. Now you can start to decrease these values ( starting from the wal_buffers ) in order to find the good compromise with your HW. ========================================================================
However wal_buffers = 1500 means ~12 MB that are not so expensive considering a server with 2GB of ram and I think that is a good compromise if you are not starving for RAM.
I had a discussion about how fine tuning a postgres server with a client, my question was: are you planning to have someone that periodically take a look at your server activities in order to use your hardware at the best? Easy answer: No, because when the server is overloaded I will buy a bigger one that is less expensive that pay someone, considering also that shareolders prefer increase the capex that pay salaries ( if the company close the hardware can be selled :-( ).
This is the real world out there.
Regards Gaetano Mendola
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
