Thread: import performance

import performance

From
Chris
Date:
Hi all,

I'm trying to work out why my 8.1 system is slower than my 7.4 system
for importing data.

The import is a lot of "insert into" commands - it's a converted
database from another system so I can't change it to copy commands.


My uncommented config options:


autovacuum = off

bgwriter_all_maxpages = 15
bgwriter_all_percent = 10.0
bgwriter_delay = 2000
bgwriter_lru_maxpages = 10
bgwriter_lru_percent = 5.0

checkpoint_segments = 10

commit_delay = 100000
commit_siblings = 500

temp_buffers = 500

wal_buffers = 16

max_connections = 16

shared_buffers = 256


(I was playing around with the bgwriter stuff to see if it made any
differences, so I could be making it worse).

It's a pretty small machine - 2.6GHz with 512M RAM.

My main concern is 7.4 on a smaller machine with less memory is faster
to import this data.


Suggestions on what I need to do would be fantastic, thanks!

--
Postgresql & php tutorials
http://www.designmagick.com/

Re: import performance

From
Gavin Sherry
Date:
On Tue, 14 Mar 2006, Chris wrote:

> Hi all,
>
> I'm trying to work out why my 8.1 system is slower than my 7.4 system
> for importing data.
>
> The import is a lot of "insert into" commands - it's a converted
> database from another system so I can't change it to copy commands.
>
>
> My uncommented config options:
>
>
> autovacuum = off
>
> bgwriter_all_maxpages = 15
> bgwriter_all_percent = 10.0

The above is a bit high.

> bgwriter_delay = 2000

This too.

> bgwriter_lru_maxpages = 10
> bgwriter_lru_percent = 5.0
>
> checkpoint_segments = 10
>
> commit_delay = 100000
> commit_siblings = 500

Way too high

>
> temp_buffers = 500
>
> wal_buffers = 16

Make this at least 64.

>
> max_connections = 16
>
> shared_buffers = 256

Make this higher too. If this is a dedicated machine with 512 MB of ram,
set it to something like 125000.

You may need to adjust shared memory settings for your operating system.
See the manual for details.

Thanks,

Gavin

Re: import performance

From
"Dave Dutcher"
Date:
[Snip]
> >
> > shared_buffers = 256
>
> Make this higher too. If this is a dedicated machine with 512 MB of
ram,
> set it to something like 125000.
>
> You may need to adjust shared memory settings for your operating
system.
> See the manual for details.
>

Whoa.  Maybe I'm wrong, but isn't each buffer 8192 bytes?  So you are
suggesting that he set his shared buffers to a gigabyte on a machine
with 512 MB of ram?  Or was that just a miscalculation?

Dave


Re: import performance

From
Gavin Sherry
Date:
On Mon, 13 Mar 2006, Dave Dutcher wrote:

> [Snip]
> > >
> > > shared_buffers = 256
> >
> > Make this higher too. If this is a dedicated machine with 512 MB of
> ram,
> > set it to something like 125000.
> >
> > You may need to adjust shared memory settings for your operating
> system.
> > See the manual for details.
> >
>
> Whoa.  Maybe I'm wrong, but isn't each buffer 8192 bytes?  So you are
> suggesting that he set his shared buffers to a gigabyte on a machine
> with 512 MB of ram?  Or was that just a miscalculation?

One to many zeros. Oops.

Gavin

Re: import performance

From
Chris
Date:
Gavin Sherry wrote:
> On Tue, 14 Mar 2006, Chris wrote:
>
>
>>Hi all,
>>
>>I'm trying to work out why my 8.1 system is slower than my 7.4 system
>>for importing data.
>>
>>The import is a lot of "insert into" commands - it's a converted
>>database from another system so I can't change it to copy commands.
>>

<snip>

new config variables...

autovacuum = off

bgwriter_all_maxpages = 15
bgwriter_all_percent = 2.0
bgwriter_delay = 500
bgwriter_lru_maxpages = 10
bgwriter_lru_percent = 5.0

checkpoint_segments = 10
checkpoint_timeout = 300

commit_delay = 10000
commit_siblings = 10

fsync = on

lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'

log_connections = on
log_destination = 'syslog'
log_disconnections = on
log_duration = on
log_statement = 'all'

max_connections = 16

redirect_stderr = on

shared_buffers = 12500

silent_mode = off

stats_command_string = off

syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

temp_buffers = 500

wal_buffers = 256


I changed a couple of things and restarted postgres before trying again.
Still getting pretty insert times :(

INSERT 0 1
Time: 1251.956 ms
INSERT 0 1
Time: 700.244 ms
INSERT 0 1
Time: 851.254 ms
INSERT 0 1
Time: 407.725 ms
INSERT 0 1
Time: 267.881 ms
INSERT 0 1
Time: 575.834 ms
INSERT 0 1
Time: 371.914 ms
INSERT 0 1


The table schema is bare:

CREATE TABLE ArticleLive_articlepages (
   PageID serial not null,
   ArticleID integer default '0',
   SortOrderID integer default '0',
   Title varchar(100) NOT NULL default '',
   Content text,
   PRIMARY KEY  (PageID)
);

(I know the fields will be lowercased...).

--
Postgresql & php tutorials
http://www.designmagick.com/

Re: import performance

From
Frank Wiles
Date:
On Tue, 14 Mar 2006 12:24:22 +1100
Chris <dmagick@gmail.com> wrote:

> Gavin Sherry wrote:
> > On Tue, 14 Mar 2006, Chris wrote:
> >
> >
> >>Hi all,
> >>
> >>I'm trying to work out why my 8.1 system is slower than my 7.4
> >>system for importing data.
> >>
> >>The import is a lot of "insert into" commands - it's a converted
> >>database from another system so I can't change it to copy commands.

  Are you on the same hardware specifically in your disk subsystem?
  Anything else different about how the two servers are used?

 ---------------------------------
   Frank Wiles <frank@wiles.org>
   http://www.wiles.org
 ---------------------------------


Re: import performance

From
Chris
Date:
Frank Wiles wrote:
> On Tue, 14 Mar 2006 12:24:22 +1100
> Chris <dmagick@gmail.com> wrote:
>
>
>>Gavin Sherry wrote:
>>
>>>On Tue, 14 Mar 2006, Chris wrote:
>>>
>>>
>>>
>>>>Hi all,
>>>>
>>>>I'm trying to work out why my 8.1 system is slower than my 7.4
>>>>system for importing data.
>>>>
>>>>The import is a lot of "insert into" commands - it's a converted
>>>>database from another system so I can't change it to copy commands.
>
>
>   Are you on the same hardware specifically in your disk subsystem?
>   Anything else different about how the two servers are used?

Different hardware.

7.4 is running on a 500MHz computer with 256M compared to 8.1 running on
a 2.6GHz with 512M.

The only notable config variables on that machine (the rest are logging):

commit_delay = 10000

checkpoint_segments = 10
checkpoint_warning = 300

insert times:

Time: 63.756 ms
INSERT 13584074 1
Time: 46.465 ms
INSERT 13584075 1
Time: 70.518 ms
INSERT 13584077 1
Time: 59.864 ms
INSERT 13584078 1
Time: 35.984 ms

Tons of difference :/

--
Postgresql & php tutorials
http://www.designmagick.com/

Re: import performance

From
Tom Lane
Date:
Chris <dmagick@gmail.com> writes:
> Tons of difference :/

Have you checked that the I/O performance is comparable?  It seems
possible that there's something badly misconfigured about the disks
on your new machine.  Benchmarking with "bonnie" or some such would
be useful; also try looking at "iostat 1" output while running the
inserts on both machines.

Also, are the inserts just trivial "insert values (... some constants ...)"
or is there more to it than that?

            regards, tom lane

Re: import performance

From
Frank Wiles
Date:
On Tue, 14 Mar 2006 12:42:21 +1100
Chris <dmagick@gmail.com> wrote:

> Different hardware.
>
> 7.4 is running on a 500MHz computer with 256M compared to 8.1 running
> on a 2.6GHz with 512M.

  Well when it comes to inserts CPU and RAM have almost nothing to do
  with it.  What are the hard disk differences?  Does the old server
  have fast SCSI disk and the new box SATA?  Or the old server was
  on a RAID volume and the new one isn't, etc... those are the sort
  of hardware differences that are important in this particular
  case.

 ---------------------------------
   Frank Wiles <frank@wiles.org>
   http://www.wiles.org
 ---------------------------------


Re: import performance

From
Chris
Date:
Tom Lane wrote:
> Chris <dmagick@gmail.com> writes:
>
>>Tons of difference :/
>
>
> Have you checked that the I/O performance is comparable?  It seems
> possible that there's something badly misconfigured about the disks
> on your new machine.  Benchmarking with "bonnie" or some such would
> be useful; also try looking at "iostat 1" output while running the
> inserts on both machines.

I'll check out bonnie, thanks.

hdparm shows a world of difference (which I can understand) - that being
the old server is a lot slower.

hdparm -t /dev/hda
/dev/hda:
  Timing buffered disk reads:   24 MB in  3.13 seconds =   7.67 MB/sec

hdparm -T /dev/hda
/dev/hda:
  Timing cached reads:   596 MB in  2.00 seconds = 298.00 MB/sec



Newer server:
hdparm -t /dev/hda
/dev/hda:
  Timing buffered disk reads:   70 MB in  3.02 seconds =  23.15 MB/sec

hdparm -T /dev/hda
/dev/hda:
  Timing cached reads:   1512 MB in  2.00 seconds = 754.44 MB/sec

> Also, are the inserts just trivial "insert values (... some constants ...)"
> or is there more to it than that?

Straight inserts, no foreign keys, triggers etc.


The only other thing I can see is the old server is ext2:
/dev/hda4 on / type ext2 (rw,errors=remount-ro)

the new one is ext3:
/dev/hda2 on / type ext3 (rw)


If it's a server issue not a postgres issue I'll keep playing :) I
thought my config was bad but I guess not.

Thanks for all the help.

--
Postgresql & php tutorials
http://www.designmagick.com/

Re: import performance

From
David Lang
Date:
On Tue, 14 Mar 2006, Chris wrote:

> The only other thing I can see is the old server is ext2:
> /dev/hda4 on / type ext2 (rw,errors=remount-ro)
>
> the new one is ext3:
> /dev/hda2 on / type ext3 (rw)

this is actually a fairly significant difference.

with ext3 most of your data actually gets written twice, once to the
journal and a second time to the spot on the disk it's actually going to
live.

in addition there are significant differences in how things are arranged
on disk between the two filesystems, (overridable at mount, but only
changes future new files). the ext3 layout is supposed to be better for a
general purpose filesystem, but I've found common cases (lots of files and
directories) where it's significantly slower, and I think postgres will
fall into those layouts.

try makeing a xfs filesystem for your postgres data and see what sort of
performance you get on it.

David Lang

Re: import performance

From
Chris
Date:
David Lang wrote:
> On Tue, 14 Mar 2006, Chris wrote:
>
>> The only other thing I can see is the old server is ext2:
>> /dev/hda4 on / type ext2 (rw,errors=remount-ro)
>>
>> the new one is ext3:
>> /dev/hda2 on / type ext3 (rw)
>
>
> this is actually a fairly significant difference.
>
> with ext3 most of your data actually gets written twice, once to the
> journal and a second time to the spot on the disk it's actually going to
> live.
>
> in addition there are significant differences in how things are arranged
> on disk between the two filesystems, (overridable at mount, but only
> changes future new files). the ext3 layout is supposed to be better for
> a general purpose filesystem, but I've found common cases (lots of files
> and directories) where it's significantly slower, and I think postgres
> will fall into those layouts.
>
> try makeing a xfs filesystem for your postgres data and see what sort of
> performance you get on it.

Interesting.

To be honest I think I'm just lucky with my really old server. I can't
see any particular tweaks in regards to drives or anything else. I have
another server running postgres 7.4.something and it's as slow as the
8.1 system.

#1 is running 2.4.x kernel - pg 7.4 (debian package) - good performance.
ext2.

#2 is running 2.2.x kernel (I know I know)..  - pg 7.4 (debian package)
- reasonable performance. ext2.

#3 is running 2.6.x kernel - pg 8.1 (fedora package) - reasonable
performance. ext3.

--
Postgresql & php tutorials
http://www.designmagick.com/