Re: odd variances in count(*) times

I have two systems running 8.2beta1 getting strange difference of
results in count(*). Query that illistrates the difference is
count(*). this is a synthetic test i use to measure a sytems's cpu
performance.

System A:
2.2 ghz p4 northwood, HT
win xp
vanilla sata (1 disk)

System B:
amd 64 3700+
linux cent/os 4.4 32 bit
4 raptors, raid 5, 3ware

explain analyze select 5000!;
A: 2.4 seconds
B: 1.8 seconds

explain analyze select count(*) from generate_series(1,500000);
A: 0.85 seconds
B: 4.94 seconds

Both systems have a freshly minted database. By all resepcts I would
expect B to outperform A on most cpu bound tests with a faster
processor and linux kernel. memory is not an issue here, varying the
size of the count(*) does not effect the results, A is always 5x
faster than B. the only two variables i see are cpu and o/s.

Also tested on pg 8.1, results are same except pg 8.2 is about 10%
faster on both systems for count(*). (yay!) :-)

anybody think of anything obvious? should i profile? (windows mingw
profiling sucks)

merlin

* Merlin Moncure (mmoncure(at)gmail(dot)com) wrote:
> explain analyze select 5000!;
> A: 2.4 seconds
> B: 1.8 seconds
>
> explain analyze select count(*) from generate_series(1,500000);
> A: 0.85 seconds
> B: 4.94 seconds

Try w/o the explain analyze. It adds quite a bit of overhead and that
might be inconsistant between the systems (mainly it may have to do with
the gettimeofday() calls being implemented differently between Windows
and Linux..).

Thanks,

Stephen

On 10/9/06, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Merlin Moncure (mmoncure(at)gmail(dot)com) wrote:
> > explain analyze select 5000!;
> > A: 2.4 seconds
> > B: 1.8 seconds
> >
> > explain analyze select count(*) from generate_series(1,500000);
> > A: 0.85 seconds
> > B: 4.94 seconds
>
> Try w/o the explain analyze. It adds quite a bit of overhead and that
> might be inconsistant between the systems (mainly it may have to do with
> the gettimeofday() calls being implemented differently between Windows
> and Linux..).

that was it. amd system now drop to .3 seconds, windows .6. (doing
time foo > psql -c bar > file). thanks...

merlin

On Mon, Oct 09, 2006 at 02:41:07PM -0400, Merlin Moncure wrote:
> that was it. amd system now drop to .3 seconds, windows .6. (doing
> time foo > psql -c bar > file). thanks...

What you want is probably \timing in psql, by the way. :-)

/* Steinar */
--
Homepage: http://www.sesse.net/

On Mon, Oct 09, 2006 at 02:41:07PM -0400, Merlin Moncure wrote:
> On 10/9/06, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> >* Merlin Moncure (mmoncure(at)gmail(dot)com) wrote:
> >> explain analyze select 5000!;
> >> A: 2.4 seconds
> >> B: 1.8 seconds
> >>
> >> explain analyze select count(*) from generate_series(1,500000);
> >> A: 0.85 seconds
> >> B: 4.94 seconds
> >
> >Try w/o the explain analyze. It adds quite a bit of overhead and that
> >might be inconsistant between the systems (mainly it may have to do with
> >the gettimeofday() calls being implemented differently between Windows
> >and Linux..).
>
> that was it. amd system now drop to .3 seconds, windows .6. (doing
> time foo > psql -c bar > file). thanks...

You can also turn timing on in psql.

And FWIW, RAID5 generally isn't a good idea for databases.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On 10/10/06, Jim C. Nasby <jim(at)nasby(dot)net> wrote:
> > >Try w/o the explain analyze. It adds quite a bit of overhead and that
> > >might be inconsistant between the systems (mainly it may have to do with
> > >the gettimeofday() calls being implemented differently between Windows
> > >and Linux..).
> >
> > that was it. amd system now drop to .3 seconds, windows .6. (doing
> > time foo > psql -c bar > file). thanks...
>
> You can also turn timing on in psql.
>
> And FWIW, RAID5 generally isn't a good idea for databases.

thats just our development box here at the office. production system
runs on something much more extravagent :).

melrin