Thread: 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@gmail.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
Attachment
On 10/9/06, Stephen Frost <sfrost@snowman.net> wrote: > * Merlin Moncure (mmoncure@gmail.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@snowman.net> wrote: > >* Merlin Moncure (mmoncure@gmail.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@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
On 10/10/06, Jim C. Nasby <jim@nasby.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