From: Khangelani Gama <kgama@argility.com>
To: pgsql-admin@postgresql.org
Subject: I am working on trying to improve a database perfomance in version 9.2 Postgresql database server
Date: Fri, 13 Mar 2015 11:32:13 +0200
Message-ID: <e0b4ad4d3f67c3f7aaff62f2fdab2536@mail.gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kgama@argility.com
Subject: Re: I am working on trying to improve a database perfomance in version 9.2 Postgresql database server
In-Reply-To: <e0b4ad4d3f67c3f7aaff62f2fdab2536@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox