I am working on trying to improve a database perfomance in version 9.2 Postgresql database server - Mailing list pgsql-admin

From Khangelani Gama
Subject I am working on trying to improve a database perfomance in version 9.2 Postgresql database server
Date
Msg-id e0b4ad4d3f67c3f7aaff62f2fdab2536@mail.gmail.com
Whole thread Raw
Responses Re: I am working on trying to improve a database perfomance in version 9.2 Postgresql database server
List pgsql-admin

Hi all

 

 

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

 

 

postgresql.conf:

 

# - Memory -

shared_buffers = 1024MB                 # min 128kB

 

# actively intend to use prepared transactions.

work_mem = 128MB                                # min 64kB

maintenance_work_mem = 512MB            # min 1MB

 

checkpoint_segments = 30                # in logfile segments, min 1, 16MB each

#checkpoint_timeout = 5min              # range 30s-1h

checkpoint_completion_target = 0.9      # checkpoint target duration, 0.0 - 1.0

 

 

From /etc/sysctl.conf file I have:

 

# Controls the maximum shared segment size, in bytes

kernel.shmmax = 68719476736

 

# Controls the maximum number of shared memory segments, in pages

kernel.shmall = 4294967296

 

 

 

 

Thanks

 

 

 

 


CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone
other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability
for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.

pgsql-admin by date:

Previous
From: Shreeyansh Dba
Date:
Subject: Re: Errorlog empty
Next
From: "Rossi, Maria"
Date:
Subject: Re: Errorlog empty