Moin Moin,
today we finally switched eUNIQUE over from PostgreSQL 8.1 to 8.3. The reason for waiting that long was the hope to finde time to port the implementation of the contrib module tsearch2 to the integrated fulltextsearch (based on tsearch2) of PG 8.3. Even though we did not find the time, we switched.
The process was fairly simple because the onboard programs pg_dump and pg_restore are making the PostgreSQL admin’s life easy. So we dropped a note to our customers, that the upgrade will take aprox. one hour. Dumping the databases and restoring them took us about 15 minutes - and there have even been some smaller issues so that we had to drop a database and create it again.
Because there was some time left (wow - 45 minutes ;-) ) we decided to check the configuration of our PostgreSQL cluster. Actually we realized, that we had been lousy dbadmins because the configuration (postgresql.conf) was not optimized for the underlying machine (Intel Xeon CPU X3210 2.13GHz, 8 GB ram).
To enable the PostgreSQL to use higher values, it is needed to raise the shared memory settings in the kernel. So you have to rais the following values:
kernel.shmmax = 2147483648
kernel.shmall = 2147483648
Human readable: 2GB.
Now you are able to raise the following values (not all depend on higher settings for shared memory for sure) in postgresql.conf:
# to make VACUUM happy:
maintenance_work_mem = 480MB
# raised form 0.5 to 0.7 to give the process mor time
checkpoint_completion_target = 0.7
# this is set from 3 to 8 what means there are 128 MB transactionlog befor
# firering a checkpoint
checkpoint_segments = 8
# let PG use a lot of RAM for querys so that there is no need to use the hd
effective_cache_size = 5632MB
# let PG use a lot of RAM for sorting, joins and scans
work_mem = 40MB
# enough needed for big transactions
wal_buffers = 4MB
# how much is used by PG - this is raised a lot because of the 8 GB RAM
shared_buffers = 1920MB
# raised from 100 to 200 - we have a quite good machine ;-)
max_connections = 200
Be sure to backup your postgresql.conf first. The values are based on the values proposed by pgtune.
After restarting the cluster and checking the application we recognized a really fast and good behaviour (much better than before). We had the feeling that Apache is now the bad guy when a page needs to load a little ;-)
Happy we are!