Postgres Pro
Downloads
Home   >   mailing lists

Thread: Importing from pg_dump slow, low Disk IO

Importing from pg_dump slow, low Disk IO

From
"Steve Pollard"
Date:
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

Re: Importing from pg_dump slow, low Disk IO

From
"Steve Pollard"
Date:
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)

Re: Importing from pg_dump slow, low Disk IO

From
"Steve Pollard"
Date:
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)

Re: Importing from pg_dump slow, low Disk IO

From
"Martin Fandel"
Date:
Hi,

i'm trying this too :). My Dump (IN) is about 84 minutes. Now
i'm testing how much time takes it with open_sync :). I'm
anxious about the new results :).

best regards,

pingufreak


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

Re: Importing from pg_dump slow, low Disk IO

From
"Martin Fandel"
Date:
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