Thread: import 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/
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
[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
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
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/
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 ---------------------------------
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/
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
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 ---------------------------------
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/
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
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/