>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:
TL> Vivek Khera <khera@kcilink.com> writes:
>> Well, for me the create index part of the restore is what takes about
>> 3x the time for the data load. Total about 4 hours. The dump takes 1
>> hour.
TL> What sort_mem do you use for the restore? Have you tried increasing it?
All tests have these non-default settings:
vacuum_mem = 131702
max_fsm_pages = 1000000
random_page_cost = 2
effective_cache_size = 12760 # `sysctl -n vfs.hibufspace` / BLKSZ
16k pages
30000 shared buffers
The four tests I've run so far are:
checkpoint_segments default
sort_mem 8192
restore time: 15344.57 seconds
checkpoint_segments default
sort_mem 131702
restore time: 15042.00 seconds
checkpoint_segments 50
sort_mem 8192
restore time: 11511.24 seconds
checkpoint_segments 50
sort_mem 131702
restore time: 11287.94 seconds
I have also enabled the extra query/timing logging you requested last
week, and just need to sanitize the table names to prevent any
confidential information from leaking out of the office. I'll send
those along shortly to you directly, as they are pretty large. I
wasn't able to do much work since the 'storm' hit on thursday,
knocking out power to the house until sunday...
Right now I'm running the same above tests with fsync=false to see if
that improves anything. Next test will be Marc's test to disable the
WAL entirely.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/