Date: Fri, 10 Jun 2005 12:14:36 +0200
From: "Martin Fandel" <martin.fandel@alphyra-evs.de>
To: Steve Pollard <stevep@hostworks.com.au>
Cc: pgsql-performance@postgresql.org
Message-ID: <1118398476.31750.3.camel@fandelm.ecommit.de>
In-Reply-To: <13F3036A953F6A41BC91040310BD692A03EF1B3E@EXCHANGEHW.au.corp.ho>
References: <13F3036A953F6A41BC91040310BD692A03EF1B3E@EXCHANGEHW.au.corp.ho>
Subject: Re: Importing from pg_dump slow, low Disk IO
Mime-Version: 1.0
Content-Type: text/plain
Content-Disposition: inline

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