Re: Importing from pg_dump slow, low Disk IO - Mailing list pgsql-performance

Hmmm. In my configuration there are not much more performance:

The Dump-size is 6-7GB on a PIV-3Ghz, 2GB-RAM, 4x10k disks on raid 10
for the db and 2x10k disks raid 1 for the system and the wal-logs.

open_sync:
real    79m1.980s
user    25m25.285s
sys     1m20.112s

fsync:
real    75m23.792s
user    27m3.693s
sys     1m26.538s

best regards,
martin

Am Freitag, den 10.06.2005, 15:33 +0930 schrieb Steve Pollard:
> Hi All,
>
> Not sure if this is correct fix or not, but a bit of research :
> http://archives.postgresql.org/pgsql-hackers/2001-04/msg01129.php
> And offical doco's from postgres :
> http://www.postgresql.org/docs/7.4/static/wal-configuration.html
> Lead me to try :
> wal_sync_method = open_sync
> And this has increased the speed on my Redhat 8 servers my 20X !
>
> Steve
> -----Original Message-----
> From: Steve Pollard
> Sent: Thursday, 9 June 2005 1:27 PM
> To: Steve Pollard; pgsql-performance@postgresql.org
> Subject: RE: [PERFORM] Importing from pg_dump slow, low Disk IO
>
> As a follow up to this ive installed on another test Rehat 8 machine
> with
> 7.3.4 and slow inserts are present, however on another machine with ES3
> the same 15,000 inserts is about 20 times faster, anyone know of a
> change that would effect this, kernel or rehat release ?
>
> Steve
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Steve
> Pollard
> Sent: Wednesday, 8 June 2005 6:39 PM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Importing from pg_dump slow, low Disk IO
>
>
> Hi Everyone,
>
> Im having a performance issue with version 7.3.4 which i first thought
> was Disk IO related, however now it seems like the problem is caused by
> really slow commits, this is running on Redhat 8.
>
> Basically im taking a .sql file with insert of about 15,000 lines and
> <'ing straight into psql DATABASENAME, the Disk writes never gets over
> about 2000 on this machine with a RAID5 SCSI setup, this happens in my
> PROD and DEV environment.
>
> Ive installed the latest version on RedHat ES3 and copied the configs
> across however the inserts are really really fast..
>
> Was there a performce change from 7.3.4 to current to turn of
> autocommits by default or is buffering handled differently ?
>
> I have ruled out Disk IO issues as a siple 'cp' exceeds Disk writes to
> 60000 (using vmstat)
>
> If i do this with a BEGIN; and COMMIT; its really fast, however not
> practical as im setting up a cold-standby server for automation.
>
> Have been trying to debug for a few days now and see nothing.. here is
> some info :
>
> ::::::::::::::
> /proc/sys/kernel/shmall
> ::::::::::::::
> 2097152
> ::::::::::::::
> /proc/sys/kernel/shmmax
> ::::::::::::::
> 134217728
> ::::::::::::::
> /proc/sys/kernel/shmmni
> ::::::::::::::
> 4096
>
>
> shared_buffers = 51200
> max_fsm_relations = 1000
> max_fsm_pages = 10000
> max_locks_per_transaction = 64
> wal_buffers = 64
> effective_cache_size = 65536
>
> MemTotal:      1547608 kB
> MemFree:         47076 kB
> MemShared:           0 kB
> Buffers:        134084 kB
> Cached:        1186596 kB
> SwapCached:        544 kB
> Active:         357048 kB
> ActiveAnon:     105832 kB
> ActiveCache:    251216 kB
> Inact_dirty:    321020 kB
> Inact_laundry:  719492 kB
> Inact_clean:     28956 kB
> Inact_target:   285300 kB
> HighTotal:      655336 kB
> HighFree:         1024 kB
> LowTotal:       892272 kB
> LowFree:         46052 kB
> SwapTotal:     1534056 kB
> SwapFree:      1526460 kB
>
> This is a real doosey for me, please provide any advise possible.
>
> Steve
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq