pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedimport performance
12+ messages / 6 participants
[nested] [flat]
* import performance
@ 2006-03-14 00:40 Chris <dmagick@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Chris @ 2006-03-14 00:40 UTC (permalink / raw)
To: pgsql-performance
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/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: import performance
@ 2006-03-14 00:48 Gavin Sherry <swm@alcove.com.au>
parent: Chris <dmagick@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Gavin Sherry @ 2006-03-14 00:48 UTC (permalink / raw)
To: Chris <dmagick@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: import performance
@ 2006-03-14 00:57 Dave Dutcher <dave@tridecap.com>
parent: Gavin Sherry <swm@alcove.com.au>
1 sibling, 1 reply; 12+ messages in thread
From: Dave Dutcher @ 2006-03-14 00:57 UTC (permalink / raw)
To: 'Gavin Sherry' <swm@alcove.com.au>; 'Chris' <dmagick@gmail.com>; +Cc: pgsql-performance
[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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: import performance
@ 2006-03-14 01:00 Gavin Sherry <swm@alcove.com.au>
parent: Dave Dutcher <dave@tridecap.com>
0 siblings, 0 replies; 12+ messages in thread
From: Gavin Sherry @ 2006-03-14 01:00 UTC (permalink / raw)
To: Dave Dutcher <dave@tridecap.com>; +Cc: 'Chris' <dmagick@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: import performance
@ 2006-03-14 01:24 Chris <dmagick@gmail.com>
parent: Gavin Sherry <swm@alcove.com.au>
1 sibling, 1 reply; 12+ messages in thread
From: Chris @ 2006-03-14 01:24 UTC (permalink / raw)
To: Gavin Sherry <swm@alcove.com.au>; +Cc: pgsql-performance
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/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: import performance
@ 2006-03-14 01:33 Frank Wiles <frank@wiles.org>
parent: Chris <dmagick@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Frank Wiles @ 2006-03-14 01:33 UTC (permalink / raw)
To: Chris <dmagick@gmail.com>; +Cc: swm@alcove.com.au; pgsql-performance
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
---------------------------------
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: import performance
@ 2006-03-14 01:42 Chris <dmagick@gmail.com>
parent: Frank Wiles <frank@wiles.org>
0 siblings, 2 replies; 12+ messages in thread
From: Chris @ 2006-03-14 01:42 UTC (permalink / raw)
To: Frank Wiles <frank@wiles.org>; +Cc: pgsql-performance
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/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: import performance
@ 2006-03-14 01:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris <dmagick@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2006-03-14 01:51 UTC (permalink / raw)
To: Chris <dmagick@gmail.com>; +Cc: Frank Wiles <frank@wiles.org>; pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: import performance
@ 2006-03-14 01:57 Frank Wiles <frank@wiles.org>
parent: Chris <dmagick@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Frank Wiles @ 2006-03-14 01:57 UTC (permalink / raw)
To: Chris <dmagick@gmail.com>; +Cc: pgsql-performance
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
---------------------------------
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: import performance
@ 2006-03-14 02:27 Chris <dmagick@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Chris @ 2006-03-14 02:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Frank Wiles <frank@wiles.org>; pgsql-performance
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/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: import performance
@ 2006-03-14 06:13 David Lang <david@lang.hm>
parent: Chris <dmagick@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: David Lang @ 2006-03-14 06:13 UTC (permalink / raw)
To: Chris <dmagick@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Frank Wiles <frank@wiles.org>; pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: import performance
@ 2006-03-14 06:28 Chris <dmagick@gmail.com>
parent: David Lang <david@lang.hm>
0 siblings, 0 replies; 12+ messages in thread
From: Chris @ 2006-03-14 06:28 UTC (permalink / raw)
To: David Lang <david@lang.hm>; +Cc: pgsql-performance
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/
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2006-03-14 06:28 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-14 00:40 import performance Chris <dmagick@gmail.com>
2006-03-14 00:48 ` Gavin Sherry <swm@alcove.com.au>
2006-03-14 00:57 ` Dave Dutcher <dave@tridecap.com>
2006-03-14 01:00 ` Gavin Sherry <swm@alcove.com.au>
2006-03-14 01:24 ` Chris <dmagick@gmail.com>
2006-03-14 01:33 ` Frank Wiles <frank@wiles.org>
2006-03-14 01:42 ` Chris <dmagick@gmail.com>
2006-03-14 01:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-14 02:27 ` Chris <dmagick@gmail.com>
2006-03-14 06:13 ` David Lang <david@lang.hm>
2006-03-14 06:28 ` Chris <dmagick@gmail.com>
2006-03-14 01:57 ` Frank Wiles <frank@wiles.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox