speed up restore from dump
Hello all,
I've been tring to speed up the restore operation of my database without
success.
I have a 200MB dump file obtained with 'pg_dumpall --clean --oids'.
After restore is produces a database with one single table (1.000.000)
rows. I have also some indexes on that table. that's it.
It always takes me about 20 minutes to reload the data, whatever
settings I change.
I have so far touched these settings:
- fsync = off
- shared_buffers = 24MB
- temp_buffers = 24Mb
- maintenance_work_mem = 128MB
- full_page_writes = off
- wal_writer_delay = 10000
- checkpoint_segments = 200
- checkpoint_timeout = 1800
- autovacuum = off
I started with a default instalation. first I changed fsync to off, then
I started touching other cfg params. but I always get around 20 minutes
(21, 19, 18)....
Can I expect these 20 minutes to be significantly reduced ?
What other cfg paramenters shoud I touch ?
Can anyone shed some light on this ?
any faster approach to upgrade from 8.1 to 8.3 ?
thank you
Joao
On Thursday 30 October 2008, Joao Ferreira gmail
<joao.miguel.c.ferreira@gmail.com> wrote:
What other cfg paramenters shoud I touch ?
work_mem set to most of your free memory might help. You're probably just
disk-bound, though. What does vmstat say during the restore?
--
Alan
On Thu, 2008-10-30 at 11:39 -0700, Alan Hodgson wrote:
On Thursday 30 October 2008, Joao Ferreira gmail
<joao.miguel.c.ferreira@gmail.com> wrote:What other cfg paramenters shoud I touch ?
work_mem set to most of your free memory might help.
I've raised work_mem to 128MB.
still get the same 20 minutes !
You're probably just
disk-bound, though. What does vmstat say during the restore?
During restore:
# vmstat
procs --------memory------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
3 1 230204 4972 1352 110128 2 1 17 63 24 56 12 2 85 0
#
After restore has finished
# vmstat
procs --------memory-------- ---swap-- ---io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
1 0 246864 59972 2276 186420 2 1 18 63 28 56 12 2 85 0
#
joao
Show quoted text
--
Alan
Import Notes
Resolved by subject fallback
On Thu, Oct 30, 2008 at 07:28:57PM +0000, Joao Ferreira gmail wrote:
On Thu, 2008-10-30 at 11:39 -0700, Alan Hodgson wrote:
You're probably just
disk-bound, though. What does vmstat say during the restore?During restore:
# vmstat
procs --------memory------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
3 1 230204 4972 1352 110128 2 1 17 63 24 56 12 2 85 0After restore has finished
# vmstat
procs --------memory-------- ---swap-- ---io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
1 0 246864 59972 2276 186420 2 1 18 63 28 56 12 2 85 0
From the output you've given it doesn't look as though you left vmstat
running while the processing is running, the first set of numbers it
prints out are rarely representational values for the IO usage. Try
running "vmstat 5" to get output every 5 seconds, you should be able
to see things happening a bit more easily that way. Another tool I'd
recommend is iostat, I tend to invoke it as "iostat -mx 5 /dev/sd?" to
get it to print out values for each individual disk.
Sam
On Thursday 30 October 2008, Joao Ferreira gmail
During restore:
# vmstat
procs --------memory------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
3 1 230204 4972 1352 110128 2 1 17 63 24 56 12 2 85 0
#
Does that machine really have only 256MB of RAM? And it's over 200MB into
swap?
--
Alan
Alan Hodgson wrote:
On Thursday 30 October 2008, Joao Ferreira gmail
During restore:
# vmstat
procs --------memory------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
3 1 230204 4972 1352 110128 2 1 17 63 24 56 12 2 85 0
#Does that machine really have only 256MB of RAM? And it's over 200MB into
swap?
Huh, if that's the case then you should drop maintenance_work_mem a lot
(and not increase work_mem too much either), because having it high
enough that it causes swapping leads to worse performance.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Thu, 2008-10-30 at 13:08 -0700, Alan Hodgson wrote:
On Thursday 30 October 2008, Joao Ferreira gmail
During restore:
# vmstat
procs --------memory------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
3 1 230204 4972 1352 110128 2 1 17 63 24 56 12 2 85 0
#Does that machine really have only 256MB of RAM? And it's over 200MB into
swap?
well..... see for yourself... (360 RAM , 524 SWAP) that's what it is...
it supposed to be somewhat an embedded product...
#
# cat /proc/meminfo
MemTotal: 360392 kB
MemFree: 59548 kB
Buffers: 7392 kB
Cached: 62640 kB
SwapCached: 44724 kB
Active: 247892 kB
Inactive: 29936 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 360392 kB
LowFree: 59548 kB
SwapTotal: 524280 kB
SwapFree: 292532 kB
Dirty: 744 kB
Writeback: 0 kB
AnonPages: 190344 kB
Mapped: 42772 kB
Slab: 13176 kB
SReclaimable: 5116 kB
SUnreclaim: 8060 kB
PageTables: 2728 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
CommitLimit: 704476 kB
Committed_AS: 1035156 kB
VmallocTotal: 524280 kB
VmallocUsed: 4020 kB
VmallocChunk: 520164 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 4096 kB
#
# cat /proc/cpuinfo
processor : 0
vendor_id : CentaurHauls
cpu family : 6
model : 9
model name : VIA Nehemiah
stepping : 8
cpu MHz : 1002.309
cache size : 64 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 1
wp : yes
flags : fpu vme de pse tsc msr cx8 sep mtrr pge cmov pat mmx fxsr sse
up rng rng_en ace ace_en
bogomips : 2009.04
clflush size : 32
Show quoted text
--
Alan
On Thursday 30 October 2008, Joao Ferreira <jmcferreira@critical-links.com>
wrote:
well..... see for yourself... (360 RAM , 524 SWAP) that's what it is...
it supposed to be somewhat an embedded product...
Clearly your hardware is your speed limitation. If you're swapping at all,
anything running on the machine is going to be slow.
--
Alan
On Thu, Oct 30, 2008 at 02:28:38PM -0700, Alan Hodgson wrote:
On Thursday 30 October 2008, Joao Ferreira <jmcferreira@critical-links.com>
wrote:well..... see for yourself... (360 RAM , 524 SWAP) that's what it is...
it supposed to be somewhat an embedded product...Clearly your hardware is your speed limitation. If you're swapping at all,
anything running on the machine is going to be slow.
The vmstat output only showed the odd block going in and out; but
performance is only really going to suffer when it's thrashing. If the
swap in number stays in the double digits for a reasonable amount of
time then you should probably look at what's causing it. Giving memory
back to the system to use for caching the file system can be good, lots
of shared memory can also be good.
Building indexes takes time and IO bandwidth, maybe you could look at
building less of them? I'd be tempted to pull the import script apart
into its constituent parts, i.e. the initial data load, and then all the
constraints/index builds separately. Then run through executing them by
hand and see what you can change to make things more efficient.
Sam
On Friday 31 October 2008 08:07:08 Sam Mason wrote:
On Thu, Oct 30, 2008 at 02:28:38PM -0700, Alan Hodgson wrote:
On Thursday 30 October 2008, Joao Ferreira
<jmcferreira@critical-links.com>wrote:
well..... see for yourself... (360 RAM , 524 SWAP) that's what it is...
it supposed to be somewhat an embedded product...Clearly your hardware is your speed limitation. If you're swapping at
all, anything running on the machine is going to be slow.The vmstat output only showed the odd block going in and out; but
performance is only really going to suffer when it's thrashing. If the
swap in number stays in the double digits for a reasonable amount of
time then you should probably look at what's causing it. Giving memory
back to the system to use for caching the file system can be good, lots
of shared memory can also be good.
well, i think he needs to cut back on the work mem, but i think he might want
to give a little more to wal buffers.
Building indexes takes time and IO bandwidth, maybe you could look at
building less of them? I'd be tempted to pull the import script apart
into its constituent parts, i.e. the initial data load, and then all the
constraints/index builds separately. Then run through executing them by
hand and see what you can change to make things more efficient.
It would be good to know where and when his bottlenecks are... ie. i could see
him being i/o, memory, or cpu bottlenecked depending on where he is in the
restore process.
--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com