postgres memory allocation tuning 2
- by pstanton
i've got a Ubuntu Linux system with 12Gb memory most of which (at least 10Gb) can be allocated solely to postgres. the system also has a 6 disk 15k SCSI RAID 10 setup.
The process i'm trying to optimise is twofold.
firstly a single threaded, single connection will do many inserts into 2-4 tables linked by foreign key.
secondly many different complex queries are run against the resulting data, using group by extensively. this part especially needs to be optimised.
i have four of these processes running at once in order to make use of the quad core CPU, therefore there will generally be no more than 5 concurrent connections (1 spare for admin tasks).
what configuration changes to the default Postgres config would you recommend?
I'm looking for the optimum values for things like work_mem, shared_buffers etc.
relevant doco
thanks!
