From: Mark Steben <msteben@autorevenue.com>
To: pgsql-performance@postgresql.org
Subject: Performance of archive logging in a PITR restore
Date: Mon, 16 Mar 2009 12:11:23 -0400
Message-ID: <20090316161202.AE645632876@mail.postgresql.org> (raw)
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.
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.
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
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.
A log compresses (using) gzip down to between 5.5 and 6.5 MB.
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)
Checkpoint_segments (32)
Autovacuum (off)
ipcs -l
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 4194303
max total shared memory (kbytes) = 1073741824
min seg size (bytes) = 1
------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 32
semaphore max value = 32767
------ Messages: Limits --------
max queues system wide = 16
max size of message (bytes) = 65536
default max size of queue (bytes) = 65536
Our database size is about 130 GB. We use tar
To backup the file structure. Takes roughly about
An hour to xtract the tarball before PITR log recovery
Begins. 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│Database Administrator│
@utoRevenue-R- "Join the Revenue-tion"
95 Ashley Ave. West Springfield, MA., 01089
413-243-4800 x1512 (Phone) │ 413-732-1824 (Fax)
@utoRevenue is a registered trademark and a division of Dominion Enterprises
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-performance@postgresql.org
Cc: msteben@autorevenue.com
Subject: Re: Performance of archive logging in a PITR restore
In-Reply-To: <20090316161202.AE645632876@mail.postgresql.org>
* 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