Re: [PERFORM] import 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/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] import 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] import performance
Tom Lane wrote: Chris <[EMAIL PROTECTED]> 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/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] import performance
On Tue, 14 Mar 2006 12:42:21 +1100 Chris <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> http://www.wiles.org - ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] import performance
Chris <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] import performance
Frank Wiles wrote: On Tue, 14 Mar 2006 12:24:22 +1100 Chris <[EMAIL PROTECTED]> 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 = 1 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/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] import performance
On Tue, 14 Mar 2006 12:24:22 +1100 Chris <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> http://www.wiles.org - ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] import 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. 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 = 1 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/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] import 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] import 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] import 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 = 10 > 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
