Обсуждение: intel vs amd benchmark for pg server
Hi
I would like to upgrade my production pg server from a machine with
following specs
celeron 900MHz, intel 810 motherboard, 512MB
pc 133 sdram memory,
PGDATA is on 2 ide 7200rpm drives under software raid 1,
linux kernel 2.4.18
to a dual processor x86 machine. Because I have read many
benchmarks about athlon handily beating pentium (e.g. one recent
review I saw had low end duron ~ 1GHz beating pentium 4
2.2GHz) I feel that an amd system will give me better
performance/price. None of the benchmarks I saw/remember
was stressing a database server so I thought I would do a benchmark myself
to confirm indeed that athlon is the better choice.
To my great surprise I have to conclude that intel is the winner.
I am using pg version 7.2.1 for the test below.
The benchmark I am using is the time it takes to vacuum. The production
server 900MHz celeron takes around 90 seconds. Nightly backup dump
from the server makes a 3 GB file.
vacuum command is run on the production
server when server is experiencing least or small load. fsync is off.
no vacuum memory is set i.e whatever the default is.
My workstation specs is amd 1.33GHz, 192MB SDRAM, SIS motherboard,
$PGDATA is on a single 5400rpm drive. I have loaded the
dump file from the production server on this machine and
vacuum takes more than 2 minutes. This surprised me and I remebered
doing a benchmark of kernel compilation when I acquired this box
last year. At that time kernel compilation time scales "more or less" linearly
with cpu speed among all machines I have tested i.e faster processor means faster
compilation. Since then I did make some changes to the box. So I redo
kernel compilation test again on the machies below and
it still it scales "more or less" linearly with cpu speed
All tests below were done on systems other than production server
and minimal number of other processes are running.
postgresql.conf is not changed.
There are two numbers I get from the vacuum test. First one t1
is the vacuum time immediately after the database is restored.
The other t2 is the time for subsequent vacuums.
$ initdb
$ pg_ctl -l log -o "-F" start
$ psql template1 < dump
$ time vacuumdb -z what # <----- get first time t1
$ time vacuumdb -z what
.
. repeat a number of times
.
.
.
$ time vacuumdb -z what # <------ get second time t2
------------
In all systems t1 > t2 but I will only report t2 below
system | motherboard| cpu | memory | PGDATA resides on | t2 (seconds)
------------------------------------------------------------------------------------------------------
1 | SIS | ATHLON 1.33GHz | 192MB pc100 SDRAM | seagate 5400 udma 100 40GB | 133
2 | INTEL | celeron 566MHz | 64MB pc100 SDRAM | maxtor 7200 udma 66 10 GB | 87
2 | INTEL | celeron 566MHz | 64MB pc100 SDRAM | maxtor 5400 udma 66 20 GB | 96
3 | ?hp laptop | duron 1GHZ | 240MB SDRAM | udma 100 20 GB unknown speed| 200
-----------
Notice that kernel compilation time scales "more or less" linearly with cpu speed on
all systems above except I did not do kernel compilation on system 2 on 5400rpm drive
The conclusion is that slower intel cpu vacuums quicker than faster amd cpu.
What do you all think?
k
postgres@vrane.com writes: > The conclusion is that slower intel cpu vacuums quicker than faster > amd cpu. > > What do you all think? I think it probably depends a lot more on your disk system than on your CPU. Use the same drive in two different systems and you *might* have a meaningful comparison. -Doug
A fairer test would be to equip the machines with equivalent disk (rpms and number) and RAM (amount and speed) configurations. SiS motherboards, particularly with on-board video, tend to be lower performance than boards with separate AGP/PCI video. postgres@vrane.com wrote: > Hi > > I would like to upgrade my production pg server from a machine with > following specs > celeron 900MHz, intel 810 motherboard, 512MB > pc 133 sdram memory, > PGDATA is on 2 ide 7200rpm drives under software raid 1, > linux kernel 2.4.18 . . . > My workstation specs is amd 1.33GHz, 192MB SDRAM, SIS motherboard, > $PGDATA is on a single 5400rpm drive. -- P. J. "Josh" Rovero Sonalysts, Inc. Email: rovero@sonalysts.com www.sonalysts.com 215 Parkway North Work: (860)326-3671 or 442-4355 Waterford CT 06385 ***********************************************************************
1. 64MB machines beat 192MB and 240MB machies.
2. On 64 MB machine I tested on 5400rpm drive
and still much faster than athlon machine.
3. On sis machine I am using a separete ati agp card
not the built in one and as a result
i am using all memory.
You would expect AMD machines to be at least
slightly faster because cpu speed ratio are
about 2 or bigger. You might not expect
linear scaling but at least faster you know.
Also as I said kernel compilation time
scales almost linearly with cpu speed
On Fri, Apr 26, 2002 at 09:49:31AM -0400, P.J. Josh Rovero wrote:
> A fairer test would be to equip the machines with equivalent
> disk (rpms and number) and RAM (amount and speed) configurations.
>
> SiS motherboards, particularly with on-board video, tend
> to be lower performance than boards with separate AGP/PCI
> video.
>
> postgres@vrane.com wrote:
> > Hi
> >
> > I would like to upgrade my production pg server from a machine with
> > following specs
> > celeron 900MHz, intel 810 motherboard, 512MB
> > pc 133 sdram memory,
> > PGDATA is on 2 ide 7200rpm drives under software raid 1,
> > linux kernel 2.4.18
> .
> .
> .
> > My workstation specs is amd 1.33GHz, 192MB SDRAM, SIS motherboard,
> > $PGDATA is on a single 5400rpm drive.
>
> --
> P. J. "Josh" Rovero Sonalysts, Inc.
> Email: rovero@sonalysts.com www.sonalysts.com 215 Parkway North
> Work: (860)326-3671 or 442-4355 Waterford CT 06385
> ***********************************************************************
postgres@vrane.com wrote: > > 1. 64MB machines beat 192MB and 240MB machies. > > 2. On 64 MB machine I tested on 5400rpm drive > and still much faster than athlon machine. > > 3. On sis machine I am using a separete ati agp card > not the built in one and as a result > i am using all memory. > > You would expect AMD machines to be at least > slightly faster because cpu speed ratio are > about 2 or bigger. You might not expect > linear scaling but at least faster you know. > > Also as I said kernel compilation time > scales almost linearly with cpu speed Have you also run other tests to check out the non-cpu parts of the two systems? If kernel compiles (reasonably CPU intensive) scale with CPU speed, then the odds are pretty good that it's not the CPU that's slowing down your DB tests. Try timing disk accesses with hdparm, for example - this may be a disk controller issue. I'm assuming you've got postgres configured appropriately for both machines, of course. -- Steve Wampler -- swampler@noao.edu O sibile, si ergo. Fortibus es enaro. Nobile, demis trux. Demis phulla causan dux.
> The benchmark I am using is the time it takes to vacuum. The > production server 900MHz celeron takes around 90 seconds. Nightly > backup dump from the server makes a 3 GB file. Once other system effects are considered (as noted in earlier responses to your note), may I modestly suggest that OSDB willoffer a more interesting benchmark than "the time it takes to vacuum the database?" (For what you want to do, 1. Download the OSDB and 40MB data tar balls from <http://osdb.sf.net> 2. Execute something like the following... $ tar zxvf osdb.tgz $ cd osdb $ tar zxvf data-40mb.tgz $ mv data-40mb data $ ./configure --with-postgresql=/usr/local/pgsql --prefix=./bin $ bin/osdb-pg-emb --postgresql=no_hash_index ) /andy p.s. We're working on a runtime data generator to facilitate testing with much larger datasets, but it's not there yet. --- Andy Riebs, andy.riebs@compaq.com High Performance Technical (w) 603-884-1521, (fax) 603-884-0630 Computing/Linux Group <http://cub.sourceforge.net/> Compaq Computer Corporation (h) ariebs@earthlink.net <http://www.compaq.com/linux> <http://osdb.sourceforge.net/> <http://opensource.compaq.com>
I don't think those tests can conclude much about the CPUs - so many factors are different in your comparisons. My guess it's more to do with drive issues. Have you tried testing the performance of the different drives with dd or some other hdd benchmark? What are the results? Regards, Link. At 09:36 AM 4/26/02 -0400, postgres@vrane.com wrote: >Hi > >I would like to upgrade my production pg server from a machine with >following specs > celeron 900MHz, intel 810 motherboard, 512MB > pc 133 sdram memory, > PGDATA is on 2 ide 7200rpm drives under software raid 1, > linux kernel 2.4.18 > >to a dual processor x86 machine. Because I have read many
Yes but you keep comparing apples and oranges. Your benchmarks don't mean hardly anything CPU speed wise because you've got so many other variants. postgres@vrane.com wrote: >1. 64MB machines beat 192MB and 240MB machies. > >2. On 64 MB machine I tested on 5400rpm drive > and still much faster than athlon machine. > >3. On sis machine I am using a separete ati agp card > not the built in one and as a result > i am using all memory. > >You would expect AMD machines to be at least >slightly faster because cpu speed ratio are >about 2 or bigger. You might not expect >linear scaling but at least faster you know. > >Also as I said kernel compilation time >scales almost linearly with cpu speed > > > >On Fri, Apr 26, 2002 at 09:49:31AM -0400, P.J. Josh Rovero wrote: > >>A fairer test would be to equip the machines with equivalent >>disk (rpms and number) and RAM (amount and speed) configurations. >> >>SiS motherboards, particularly with on-board video, tend >>to be lower performance than boards with separate AGP/PCI >>video. >> >>postgres@vrane.com wrote: >> >>>Hi >>> >>>I would like to upgrade my production pg server from a machine with >>>following specs >>> celeron 900MHz, intel 810 motherboard, 512MB >>> pc 133 sdram memory, >>> PGDATA is on 2 ide 7200rpm drives under software raid 1, >>> linux kernel 2.4.18 >>> >>. >>. >>. >> >>>My workstation specs is amd 1.33GHz, 192MB SDRAM, SIS motherboard, >>>$PGDATA is on a single 5400rpm drive. >>> >>-- >>P. J. "Josh" Rovero Sonalysts, Inc. >>Email: rovero@sonalysts.com www.sonalysts.com 215 Parkway North >>Work: (860)326-3671 or 442-4355 Waterford CT 06385 >>*********************************************************************** >> > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html >
In article <20020426093655.A16995@amd.universe>,
<pgsql-general-owner+M23830@postgresql.org> wrote:
>To my great surprise I have to conclude that intel is the winner.
Does the SIS mother board use VIA chip sets for the harddrive?
If so, that is probably your problem.
As pointed out, DB stuff is IO bound, not CPU. On the other hand, a kernel
build is more CPU bound.
The VIA chip sets, more or less, suck. See various posts by Andre Hedrick
for the Linux view point. I'm betting you'll see similar analysis on the
*BSD sides, though I've never looked.
mrc
--
Mike Castle dalgoda@ix.netcom.com www.netcom.com/~dalgoda/
We are all of us living in the shadow of Manhattan. -- Watchmen
fatal ("You are in a maze of twisty compiler features, all different"); -- gcc
Also, DMA settings on IDE drives can have HUGE effects on performance. If you really want to benchmark the AMD versus Intel CPUs, then get a good PCI SCSI card, a stack of 10k RPM hard drives, and two machines that are about evenly matched otherwise (memory, et.al.) and run something more like pgbench or OSDB tests. Or, just write a .sql file with a few choice, ugly, slow queries from your current system (we all have 'em :-) and run it like so: time psql -e <mytestfile.sql >/dev/null and see what kind of execution times you get. Make sure you are running the exact same dataset (pg_dump and restore a fresh install to be sure.) My experience is that CPU speed is only a small part of what makes a fast postgresql server, and total memory, memory access speed, number of drives, and speed of drive connection are much more important. Having TWO CPUs seems more important that one fast one though. My dual PIII 750 machines are MUCH faster than my single Celeron 1.1Ghz machine, even with nearly identical drive and memory setups. On Fri, 26 Apr 2002, Mike Castle wrote: > In article <20020426093655.A16995@amd.universe>, > <pgsql-general-owner+M23830@postgresql.org> wrote: > >To my great surprise I have to conclude that intel is the winner. > > Does the SIS mother board use VIA chip sets for the harddrive? > > If so, that is probably your problem. > > As pointed out, DB stuff is IO bound, not CPU. On the other hand, a kernel > build is more CPU bound. > > The VIA chip sets, more or less, suck. See various posts by Andre Hedrick > for the Linux view point. I'm betting you'll see similar analysis on the > *BSD sides, though I've never looked. > > mrc >
Hello I am trying to port the Pg perl module that ships with postgres to Novell Netware. I am having Tons of issues with the non-standard Netware implimentation of Perl 5.6.1. Anyways digging through the module source I am not able to determin if the module depends on any postgresql libraries. If there is a dependancy is it on a Os-dependant Sharable object. Or is it just a header file? How does the Module load these depandencies. ... I am very inexperienced with this sort of thing. Anybody out there that worked on the PG module that would be welling to let me pick there brain a little bit? Thanks Jeff Post
On Fri, 26 Apr 2002, Jeff Post wrote:
> Anyways digging through the module source I am
> not able to determin if the module depends on any postgresql libraries.
>
> If there is a dependancy is it on a Os-dependant Sharable object.
Yes:
angelic $ pkg_info -L p5-postgresql
Information for p5-postgresql-1.9.0:
Files:
/usr/pkg/lib/perl5/site_perl/5.6.1/i386-netbsd/Pg.pm
/usr/pkg/lib/perl5/site_perl/5.6.1/i386-netbsd/auto/Pg/.packlist
/usr/pkg/lib/perl5/site_perl/5.6.1/i386-netbsd/auto/Pg/Pg.bs
/usr/pkg/lib/perl5/site_perl/5.6.1/i386-netbsd/auto/Pg/Pg.so
/usr/pkg/lib/perl5/site_perl/5.6.1/i386-netbsd/auto/Pg/autosplit.ix
It uses the Postgres C client library.
It would be interesting to try to write a "type 4" driver for
postgres in perl. I don't know if the speed would be as good, but
I don't see any real reason why it shouldn't be.
cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC
SiS chipsets are generally pretty basic, although some of their more
recent ones are going particularly well.
If you want to do a more genuine cpu comparison, you should use
motherboards with comparable chipsets, so both boards have same
generation SiS (or VIA or ALI) chipsets for Intel/AMD accordingly. Same
memory (type, speed, amount & configuration), same hard drive & same O/S &
Postgres installation.
There are so many issues with motherboards, chipsets, memory
access/latencies, off cpu caches, drive caches, etc... (before we start
looking at the IDE DMA & other drivers), etc that an evaluation
such as yours cannot be said to compare cpu's. It just compares those two
platforms, I don't think you can state from your observations that any one
component is responsible for the performance difference.
Brent Wood
=============================================================================
_-''-_ <Brent Wood> b.wood@niwa.cri.nz Ph: +64(4)386-0300
'o \/ NIWA, Box 14901,
> ) /\ Kilbirnie, Wellington, New Zealand
`-===-' #include <std_disclaimer>
I seem to be having a problem with the postgres utilities chewing a lot of RAM. I've been running PG 7.1 under LinuxPPC on a PowerMac G4. After a few days of use, free memory degrades to almost nothing. I logged memory usage for a while and found that free memory would suddenly drop by 200-300 meg every night at 1:20 AM, right when my nightly maintenance scripts run. I traced through the nightly scripts and found that two things seem to account for the memory loss -- vacuuming the databases, and backing them up. I restarted to free all my RAM back up and tried just those operations. Sure enough, after a vacuum verbose analyze (on about thirty tables totaling probably less than 50 MB of data), free memory dropped by 130MB. Backing up everything on the server took another 110MB. And this memory never gets returned. Is this something to do with shared memory? I've raised the max shared mem and directed postgres to take 128 MB of it. Ipcs says this: ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x0052e2c1 0 postgres 600 127934464 1 0x00000000 1 nobody 600 46084 6 dest ------ Semaphore Arrays -------- key semid owner perms nsems status 0x0052e2c1 0 postgres 600 17 0x0052e2c2 1 postgres 600 17 0x0052e2c3 2 postgres 600 17 0x0052e2c4 3 postgres 600 17 0x00000000 4 nobody 600 1 ------ Message Queues -------- key msqid owner perms used-bytes messages Which looks right to me. Why does my free mem just disappear when I run vacuum and pg_dump? Thanks, steve
Just a general philosophical point. Why the heck do you care about free memory? You are like someone who bought a preformance car but drive it at 30 mph maximum because you don't want to wear out the engine. I would be very worried if there is some free memory. It will mean OS is not doing its job of caching whatever it is supposed to be caching. On Mon, Apr 29, 2002 at 12:35:38AM -0500, Steve Lane wrote: > I seem to be having a problem with the postgres utilities chewing a lot of > RAM. I've been running PG 7.1 under LinuxPPC on a PowerMac G4. After a few > days of use, free memory degrades to almost nothing. I logged memory usage > for a while and found that free memory would suddenly drop by 200-300 meg > every night at 1:20 AM, right when my nightly maintenance scripts run.
Steve Lane <slane@fmpro.com> writes:
> I seem to be having a problem with the postgres utilities chewing a lot of
> RAM. I've been running PG 7.1 under LinuxPPC on a PowerMac G4. After a few
> days of use, free memory degrades to almost nothing.
This is wrong?
On most Unixen, the kernel happily uses all spare RAM to cache
recently-accessed disk blocks. If you've got large amounts of "free"
RAM it only means that (a) you rebooted recently, or (b) you're not
doing much disk access.
> I logged memory usage
> for a while and found that free memory would suddenly drop by 200-300 meg
> every night at 1:20 AM, right when my nightly maintenance scripts run.
> I traced through the nightly scripts and found that two things seem to
> account for the memory loss -- vacuuming the databases, and backing them up.
> I restarted to free all my RAM back up and tried just those operations. Sure
> enough, after a vacuum verbose analyze (on about thirty tables totaling
> probably less than 50 MB of data), free memory dropped by 130MB. Backing up
> everything on the server took another 110MB. And this memory never gets
> returned.
This all sounds like standard, expected, preferable behavior.
As soon as you run programs that need RAM, the kernel will drop those
disk-cache pages and assign the RAM to program space. But as long as
you don't, what else should the kernel do with unused RAM than keep disk
cache in it?
regards, tom lane
On Mon, 29 Apr 2002 00:35:38 -0500 "Steve Lane" <slane@fmpro.com> wrote: > I traced through the nightly scripts and found that two things seem to > account for the memory loss -- vacuuming the databases, and backing them up. > I restarted to free all my RAM back up and tried just those operations. Sure > enough, after a vacuum verbose analyze (on about thirty tables totaling > probably less than 50 MB of data), free memory dropped by 130MB. Backing up > everything on the server took another 110MB. And this memory never gets > returned. AFAICS, there's nothing wrong here. Linux, like any modern OS, will use free RAM to cache I/O. Apparently, when these database maintainence/ backup utilities are executed, a lot of I/O is generated, and Linux decides to use some of the spare RAM to cache it. The "free RAM" number is pretty useless in this regard. As for returning this memory, Linux should return it if any applications actually need it. If you're concerned, run "vmstat 1". If you see any paging under normal load, you've got a problem -- look into upgrading your kernel, you might have an early 2.4 kernel with a defective VM. If you don't see any paging, you're fine -- don't worry about it. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
On 4/29/02 12:53 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > Steve Lane <slane@fmpro.com> writes: >> I seem to be having a problem with the postgres utilities chewing a lot of >> RAM. I've been running PG 7.1 under LinuxPPC on a PowerMac G4. After a few >> days of use, free memory degrades to almost nothing. > > This is wrong? Apparently not :-> Performance on the server seems to slowly degrade over time. I made the naïve assumption that this correlated with free memory. This proceeded from an almost (but not completely) absolute ignorance of how Unix manages memory. I am, suffice to say, educated. > > On most Unixen, the kernel happily uses all spare RAM to cache > recently-accessed disk blocks. If you've got large amounts of "free" > RAM it only means that (a) you rebooted recently, or (b) you're not > doing much disk access. > This all sounds like standard, expected, preferable behavior. > > As soon as you run programs that need RAM, the kernel will drop those > disk-cache pages and assign the RAM to program space. But as long as > you don't, what else should the kernel do with unused RAM than keep disk > cache in it? > OK, I need to a) learn more about memory management and b) dig deeper into my (apparent) performance issues. Thanks for the primer. -- sgl
On 4/29/02 12:53 AM, "Neil Conway" <nconway@klamath.dyndns.org> wrote: > On Mon, 29 Apr 2002 00:35:38 -0500 > "Steve Lane" <slane@fmpro.com> wrote: >> I traced through the nightly scripts and found that two things seem to >> account for the memory loss -- vacuuming the databases, and backing them up. >> I restarted to free all my RAM back up and tried just those operations. Sure >> enough, after a vacuum verbose analyze (on about thirty tables totaling >> probably less than 50 MB of data), free memory dropped by 130MB. Backing up >> everything on the server took another 110MB. And this memory never gets >> returned. > > AFAICS, there's nothing wrong here. Linux, like any modern OS, will use > free RAM to cache I/O. Apparently, when these database maintainence/ > backup utilities are executed, a lot of I/O is generated, and Linux > decides to use some of the spare RAM to cache it. The "free RAM" > number is pretty useless in this regard. > > As for returning this memory, Linux should return it if any > applications actually need it. > > If you're concerned, run "vmstat 1". If you see any paging under > normal load, you've got a problem -- look into upgrading your > kernel, you might have an early 2.4 kernel with a defective > VM. If you don't see any paging, you're fine -- don't worry > about it. Okay, I am both educated and reassured. I need to look further into things like vmstat and figuring out how Postgres is using shared memory. My performance issue involves slow response from Apache (running PHP tied to postgres). So I need to keep looking. Thanks, steve