Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id C4E23633B36 for ; Mon, 16 Mar 2009 13:12:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 68049-04 for ; Mon, 16 Mar 2009 13:12:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.autorevenue.com (mail.autorevenue.com [208.178.167.106]) by mail.postgresql.org (Postfix) with ESMTP id AE645632876 for ; Mon, 16 Mar 2009 13:11:57 -0300 (ADT) Received: (qmail 30070 invoked from network); 16 Mar 2009 12:17:01 -0400 Received: from unknown (HELO dei26g028534) (msteben@10.10.6.130) by mail.autorevenue.com with SMTP; 16 Mar 2009 12:17:01 -0400 From: "Mark Steben" To: Subject: Performance of archive logging in a PITR restore Date: Mon, 16 Mar 2009 12:11:23 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002D_01C9A630.52DCE8A0" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AcmmUdmYIm/FbqwCR1SYtgYNskBQXg== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 Message-Id: <20090316161202.AE645632876@mail.postgresql.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200903/209 X-Sequence-Number: 33131 This is a multi-part message in MIME format. ------=_NextPart_000_002D_01C9A630.52DCE8A0 Content-Type: text/plain; charset="koi8-r" Content-Transfer-Encoding: quoted-printable First of all, I did pose this question first on the pgsql - admin = mailing list. And I know it is not appreciated to post across multiple mailing lists = so I Apologize in advance. I do not make it a practice to do so but, this = being A performance issue I think I should have inquired on this list first. = Rest Assured I won't double post again. =20 =20 The issue is that during a restore on a remote site, (Postgres 8.2.5)=20 archived logs are taking an average of 35 - 40 seconds apiece to = restore. =20 This is roughly the same speed that they are being archived on the production Site. I compress the logs when I copy them over, then uncompress them During the restore using a cat | gzip -dc command. I don't think=20 The bottleneck is in that command - a log typically is uncompressed and copied In less than 2 seconds when I do this manually. Also when I pass a log That is already uncompressed the performance improves by only about 10 percent. =20 A log compresses (using) gzip down to between 5.5 and 6.5 MB.=20 I have attempted Increases in shared_buffers (250MB to 1500MB). Other relevant (I think) config parameters include: Maintenance_work_mem (300MB) Work_mem (75MB) Wal_buffers (48) =20 Checkpoint_segments (32) Autovacuum (off) =20 =20 ipcs -l =20 ------ Shared Memory Limits -------- max number of segments =3D 4096 max seg size (kbytes) =3D 4194303 max total shared memory (kbytes) =3D 1073741824 min seg size (bytes) =3D 1 =20 ------ Semaphore Limits -------- max number of arrays =3D 128 max semaphores per array =3D 250 max semaphores system wide =3D 32000 max ops per semop call =3D 32 semaphore max value =3D 32767 =20 ------ Messages: Limits -------- max queues system wide =3D 16 max size of message (bytes) =3D 65536 default max size of queue (bytes) =3D 65536 =20 Our database size is about 130 GB. We use tar=20 To backup the file structure. Takes roughly about An hour to xtract the tarball before PITR log recovery Begins. The tarball itself 31GB compressed. =20 Again I apologize for the annoying double posting but I am pretty much out of ideas to make this work. =20 =20 =20 Mark Steben=81Database Administrator=81=20 @utoRevenue-R- "Join the Revenue-tion" 95 Ashley Ave. West Springfield, MA., 01089=20 413-243-4800 x1512 (Phone) =81 413-732-1824 (Fax) @utoRevenue is a registered trademark and a division of Dominion = Enterprises =20 ------=_NextPart_000_002D_01C9A630.52DCE8A0 Content-Type: text/html; charset="koi8-r" Content-Transfer-Encoding: quoted-printable

First of all, I did pose this question first on the = pgsql – admin mailing list.

And I know it is not appreciated to post across = multiple mailing lists so I

Apologize in advance.=9A I do not make it a practice = to do so but, this being

A performance issue I think I should have inquired on = this list first.=9A Rest

Assured I won’t double post again.=9A =

 

The issue is that during a restore on a remote site, = (Postgres 8.2.5)

archived logs are taking an average of 35 – 40 = seconds apiece to restore.=9A

This is roughly the same speed that they are being = archived on the production

Site. I compress the logs when I copy them over, then uncompress them

During the restore using a cat | gzip –dc = command.=9A I don’t think

The bottleneck is in that command – a log = typically is uncompressed and copied

In less than 2 seconds when I do this manually.=9A = Also when I pass a log

That is already uncompressed the performance improves = by only about 10 percent.

 

A log compresses (using) gzip down to between 5.5 and = 6.5 MB.

=9AI have attempted Increases in shared_buffers = (250MB to 1500MB).

=9A=9AOther relevant (I think) config parameters = include:

=9A =9A=9A=9A=9A=9AMaintenance_work_mem = (300MB)

=9A=9A=9A=9A=9A=9A Work_mem = (75MB)

=9A=9A=9A=9A=9A=9A Wal_buffers (48)=9A=9A =

=9A=9A=9A=9A =9A=9ACheckpoint_segments = (32)

=9A=9A=9A=9A =9A=9AAutovacuum = (off)

 

=9A=9A=9A=9A=9A

ipcs -l

 

------ Shared Memory Limits = --------

max number of segments =3D = 4096

max seg size (kbytes) =3D = 4194303

max total shared memory (kbytes) =3D = 1073741824

min seg size (bytes) =3D = 1

 

------ Semaphore Limits = --------

max number of arrays =3D = 128

max semaphores per array =3D = 250

max semaphores system wide =3D = 32000

max ops per semop call =3D = 32

semaphore max value =3D = 32767

 

------ Messages: Limits = --------

max queues system wide =3D = 16

max size of message (bytes) =3D = 65536

default max size of queue (bytes) =3D = 65536

 

Our database size is about 130 GB.=9A We use tar =

To backup the file structure. Takes roughly = about

An hour to xtract the tarball before PITR log = recovery

Begins.=9A The tarball itself 31GB = compressed.

 

Again I apologize for the annoying double posting = but

I am pretty much out of ideas to make this = work.

 

 

 

Mark = Steben=81Database Administrator=81

@utoRevenue­®­ "Join the = Revenue-tion"
95 Ashley Ave. = West Springfield, MA., 01089 
413-243-4800 x1512 (Phone) =81 413-732-1824 (Fax)

@utoRevenue is a = registered trademark and a division of Dominion Enterprises

 

------=_NextPart_000_002D_01C9A630.52DCE8A0--