Yea - I'll try to optimize as I had a plan to write to
pgsql.performance for rescue anyway.
I don't know exact hardware specification yet - known facts at the
moment are:
Sun Turgo?? (SPARC) with 32 cores
17GB RAM (1GB for shared buffers)
hdd - ?
OS - Solaris 10 - the system is running in the zone (Solaris
virtualization) - however during test nothing else is utilizing the
machine.
PostgreSQL 8.4.4 64bit
The data set is 9mln rows - about 250 columns
The result database size is ~9GB
Load time ~2h 20min
CPU utilization - 1,2% (half of the one core)
iostat shows writes ~6MB/s, 20% busy
when I run 2 loads in parallel the CPU is split to 2*0,6%, hdd write
~7MB (almost the same)
postgresql.conf changes:
checkpoint_segments - 128
checkpoint_timeout - 30min
shared_buffers - 1GB
maintenance_work_mem - 128MB
does it looks like my HDD is the problem? or maybe the Solaris
virtualization?
what's also interesting - table is empty when I start (by truncate)
but while the COPY is working, I see it grows (by \d+ or
pg_total_relation_size) about 1MB per second
what I'd expect it should grow at checkpoints only, not all the
time - am I wrong?
> On Wed, 2010-08-25 at 12:20 -0400, Eric Comeau wrote:
>>
>> > Without even changing any line of data or code in sql !
>> >
>> > Incredible, isn't it ?
>> >
>>
>> Curious- what postgresql.conf settings did you change to improve it?
> The most obvious would be to turn fsync off, sychronous_commit off,
> increase work_mem, increase checkpoint_timeout, increase wal_segments.
> JD
>>
>>
>>
--
Pozdrowienia,
Wojciech Strzałka