Re: I am working on trying to improve a database perfomance in version 9.2 Postgresql database server

 I need some help in trying improve database performance on the server that has 103 databases running in one server. Autovacuum is switched off intentionally , it was causing issues until I implemented a script where I run vacuum analyze every Sunday of the week, I don’t know if it’s enough or not but each database receive about 4000 new transactions a day.  I run VACUUM ANALYZE in each active database that’s on this server. Currents are like when I try to create a new database on the same server using a created command, it takes long to finish, hence takes to restore a database on the server. Sometimes the load average hits about 20 to 30 when there is many things running on the server. It takes about 6 hours for this vacuum to finish running in all these databases every Sunday. Is it necessary to run any REINDEXes.

 

Any advices will help, see some information below:

I am thinking of increasing values on the following parameters in the conf file after some reading ing http://www.postgresql.org/docs/9.2/static/runtime-config-resource.html

 

Parameters:

 

work_mem

shared_buffers

maintenance_work_mem

checkpoint_segments

 Some server config information currently are:

 

1.       data/ directory size is 526G

2.       Total memory  is 24G