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

From Steve Pollard
Subject Importing from pg_dump slow, low Disk IO
Date
Msg-id 13F3036A953F6A41BC91040310BD692A01FD3B@EXCHANGEHW.au.corp.hostworks.com.au
Whole thread Raw
List pgsql-performance
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

pgsql-performance by date:

Previous
From: "Martin Fandel"
Date:
Subject: Re: Filesystem
Next
From: Kim Bisgaard
Date:
Subject: full outer performance problem