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:
>

PostgreSQL version ?

Do you have all the 103 database in one cluster ? I suspect a DISK IO and
CPU spike. Do you see that ?

Its hard to say straight if there is a need for REINDEXING. Can you let us
know the hardware specifications of the server.

We will need to understand if the server capacity is falling short for the
load being received.

How many active connections you see at the database level ?

> 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
>

We need to know the hardware specifications.

Some server config information currently are:
>
>
>
> 1. data/ directory size is 526G
>
> 2. Total memory is 24G
>

Do you see any over utilization of the memory.

Regards,
Venkata Balaji N