Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YWLxE-0000ee-4j for pgsql-admin@arkaria.postgresql.org; Fri, 13 Mar 2015 09:32:28 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YWLxD-0008PM-Jh for pgsql-admin@arkaria.postgresql.org; Fri, 13 Mar 2015 09:32:27 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YWLxB-0008O2-Oj for pgsql-admin@postgresql.org; Fri, 13 Mar 2015 09:32:26 +0000 Received: from eu1sys200aog103.obsmtp.com ([207.126.144.115]) by makus.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YWLx7-0007JQ-KS for pgsql-admin@postgresql.org; Fri, 13 Mar 2015 09:32:23 +0000 Received: from mail-qc0-f169.google.com ([209.85.216.169]) (using TLSv1) by eu1sys200aob103.postini.com ([207.126.147.11]) with SMTP ID DSNKVQKun6FswWIVDSpLiEmPoKaJlzTSJT7x@postini.com; Fri, 13 Mar 2015 09:32:21 UTC Received: by qcvp6 with SMTP id p6so25050732qcv.5 for ; Fri, 13 Mar 2015 02:32:14 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:from:mime-version:thread-index:date:message-id :subject:to:content-type; bh=yVgqGPsGbsIVPzdHLEvLX34b6vhG1cEszw9QzNNCOeQ=; b=Q+L22fPDk9xfcJPEFc2RJRugn52cs+/4BFWWRvrqz8fYqHYuj0yRtjhb76Mik9hxqU qF+EDP58ourYRqFkfsLHoWwvbh4mv8e8aroWAEsNCKT/w2Ran4QTh6rIIuiIA62vP/Ui FRp1ZV1+Azk96t3SmjhyPrdole9Q36b0ZQwlIeF/qn1j3+dGD9WnNyyVCTWepO1DgxrB ydbXHlCghyJQey1xV8UtB4oOY90aOYb1QF3iEJIWo9XO7uNP2s/taqOUkobxcavoqpwd fLlJCZE9sSB6BsLMK7gsYK4y6HqE6ATNVKc5CD9P04BOULoEbbR7nu7qWM3rL75raKwh lyBg== X-Gm-Message-State: ALoCoQmZR5sHvAQy2jSFbhZEuHWcNmpv/WegNlPxYiXQQ43xf7ruER1Wmddj++686m/ag2vaKtuyatFubFxRfmwcIcyIwz2D2sdC65oiu55URgdqiTHJ57OryAQAA6+nIBreRa61Qb3p18+ShHvMWX1VT9fJ3cMxNA== X-Received: by 10.140.86.199 with SMTP id p65mr58316303qgd.49.1426239134763; Fri, 13 Mar 2015 02:32:14 -0700 (PDT) X-Received: by 10.140.86.199 with SMTP id p65mr58316296qgd.49.1426239134669; Fri, 13 Mar 2015 02:32:14 -0700 (PDT) From: Khangelani Gama MIME-Version: 1.0 X-Mailer: Microsoft Outlook 14.0 thread-index: AdBdcJPbCF2jv1bWT3Stq0BILsjVCQ== Date: Fri, 13 Mar 2015 11:32:13 +0200 Message-ID: Subject: I am working on trying to improve a database perfomance in version 9.2 Postgresql database server To: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=001a11c150b6fcba8c0511282c62 X-Pg-Spam-Score: -4.2 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --001a11c150b6fcba8c0511282c62 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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=E2=80=99t know if it=E2= =80=99s enough or not but each database receive about 4000 new transactions a day. I run VACUUM ANALYZE in each active database that=E2=80=99s on this server. Curre= nts 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 =3D 1024MB # min 128kB # actively intend to use prepared transactions. work_mem =3D 128MB # min 64kB maintenance_work_mem =3D 512MB # min 1MB checkpoint_segments =3D 30 # in logfile segments, min 1, 16M= B each #checkpoint_timeout =3D 5min # range 30s-1h checkpoint_completion_target =3D 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 =3D 68719476736 # Controls the maximum number of shared memory segments, in pages kernel.shmall =3D 4294967296 Thanks CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addre= ssee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. An= y review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intend= ed addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiari= es distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other tha= n strictly for business purposes. =0D --001a11c150b6fcba8c0511282c62 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Hi all

=C2=A0

=C2=A0

I nee= d some help in trying improve database performance on the server that has 1= 03 databases running in one server. Autovacuum is switched off intentionall= y , it was causing issues until I implemented a script where I run vacuum a= nalyse every Sunday of the week, I don=E2=80=99t know if it=E2=80=99s enoug= h or not but each database receive about 4000 new transactions a day.=C2=A0= I run VACUUM ANALYZE in each active database that=E2=80=99s 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 thi= s vacuum to finish running in all these databases every Sunday. Is it neces= sary to run any REINDEXes.

=C2=A0

Any advices will help, see some information below:

=C2=A0

=C2=A0

=C2=A0

I am thinking of increasing values o= n the following parameters in the conf file after some reading ing =C2=A0http://www.postgresql.org/docs/9.2/static/runtime-config-resource.htm= l

=C2=A0

Para= meters:

=C2=A0

= work_mem

shared_buffers

= maintenance_work_mem

checkpoint_segments

=C2=A0

=C2=A0

=C2=A0<= /p>

=C2=A0

Some server conf= ig information currently are:

=C2=A0

1.=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 da= ta/ directory size is 526G

2.=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 Total memory =C2=A0is 24G

=C2=A0

=C2=A0

postgresql.conf:

=C2=A0

# - Memory -

shared_buffers =3D 10= 24MB=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 # min 128kB

=C2=A0

# actively intend to use prepared transactions.

=

work_mem =3D 128MB=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 # min 64kB

maintenance_work_mem =3D 512MB=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 # min 1MB

=C2=A0

checkpoint_segmen= ts =3D 30=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 # in logfile segments, min 1, 16MB each

#checkpoint_timeout =3D 5min=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 # range 30s-1h

checkpoint_completion_target =3D 0.9=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 # checkpoint target duration, 0.0 - 1.0

= =C2=A0

=C2=A0

From /= etc/sysctl.conf file I have:

=C2=A0

# Controls the maximum shared segment size, in bytes

<= p class=3D"MsoNormal">kernel.shmmax =3D 68719476736

=C2=A0

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

kernel.shmall =3D 4294= 967296

=C2=A0

=C2=A0

=C2=A0

=

=C2=A0<= /span>

Thanks

=C2=A0

=C2=A0

=C2=A0

<= p class=3D"MsoNormal">=C2=A0


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