pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedodd variances in count(*) times
6+ messages / 4 participants
[nested] [flat]
* odd variances in count(*) times
@ 2006-10-09 18:17 Merlin Moncure <mmoncure@gmail.com>
2006-10-09 18:30 ` Re: odd variances in count(*) times Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 6+ messages in thread
From: Merlin Moncure @ 2006-10-09 18:17 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: odd variances in count(*) times
2006-10-09 18:17 odd variances in count(*) times Merlin Moncure <mmoncure@gmail.com>
@ 2006-10-09 18:30 ` Stephen Frost <sfrost@snowman.net>
2006-10-09 18:41 ` Re: odd variances in count(*) times Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Stephen Frost @ 2006-10-09 18:30 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
* 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
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: odd variances in count(*) times
2006-10-09 18:17 odd variances in count(*) times Merlin Moncure <mmoncure@gmail.com>
2006-10-09 18:30 ` Re: odd variances in count(*) times Stephen Frost <sfrost@snowman.net>
@ 2006-10-09 18:41 ` Merlin Moncure <mmoncure@gmail.com>
2006-10-09 18:45 ` Re: odd variances in count(*) times Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-10-09 21:23 ` Re: odd variances in count(*) times Jim C. Nasby <jim@nasby.net>
0 siblings, 2 replies; 6+ messages in thread
From: Merlin Moncure @ 2006-10-09 18:41 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: odd variances in count(*) times
2006-10-09 18:17 odd variances in count(*) times Merlin Moncure <mmoncure@gmail.com>
2006-10-09 18:30 ` Re: odd variances in count(*) times Stephen Frost <sfrost@snowman.net>
2006-10-09 18:41 ` Re: odd variances in count(*) times Merlin Moncure <mmoncure@gmail.com>
@ 2006-10-09 18:45 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
1 sibling, 0 replies; 6+ messages in thread
From: Steinar H. Gunderson @ 2006-10-09 18:45 UTC (permalink / raw)
To: pgsql-performance
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: odd variances in count(*) times
2006-10-09 18:17 odd variances in count(*) times Merlin Moncure <mmoncure@gmail.com>
2006-10-09 18:30 ` Re: odd variances in count(*) times Stephen Frost <sfrost@snowman.net>
2006-10-09 18:41 ` Re: odd variances in count(*) times Merlin Moncure <mmoncure@gmail.com>
@ 2006-10-09 21:23 ` Jim C. Nasby <jim@nasby.net>
2006-10-10 06:30 ` Re: odd variances in count(*) times Merlin Moncure <mmoncure@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Jim C. Nasby @ 2006-10-09 21:23 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: odd variances in count(*) times
2006-10-09 18:17 odd variances in count(*) times Merlin Moncure <mmoncure@gmail.com>
2006-10-09 18:30 ` Re: odd variances in count(*) times Stephen Frost <sfrost@snowman.net>
2006-10-09 18:41 ` Re: odd variances in count(*) times Merlin Moncure <mmoncure@gmail.com>
2006-10-09 21:23 ` Re: odd variances in count(*) times Jim C. Nasby <jim@nasby.net>
@ 2006-10-10 06:30 ` Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Merlin Moncure @ 2006-10-10 06:30 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-10-10 06:30 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-09 18:17 odd variances in count(*) times Merlin Moncure <mmoncure@gmail.com>
2006-10-09 18:30 ` Stephen Frost <sfrost@snowman.net>
2006-10-09 18:41 ` Merlin Moncure <mmoncure@gmail.com>
2006-10-09 18:45 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-10-09 21:23 ` Jim C. Nasby <jim@nasby.net>
2006-10-10 06:30 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox