On Mon, 20 Sep 1999, Leon wrote:
> Tom Lane wrote:
> >
> > The Hermit Hacker <scrappy@hub.org> writes:
> > > MySQL: 0.498u 0.150s 0:02.50 25.6% 10+1652k 0+0io 0pf+0w
> > > PgSQL: 0.494u 0.061s 0:19.78 2.7% 10+1532k 0+0io 0pf+0w
> > > From the 'time' numbers, MySQL is running ~17sec faster, but uses up 23%
> > > more CPU to do this...so where is our slowdown?
> >
> > It's gotta be going into I/O, obviously. (I hate profilers that can't
> > count disk accesses...) My guess is that the index scans are losing
> > because they wind up touching too many disk pages. You show
> >
>
> On that particular machine that can be verified easily, I hope.
> (there seems to be enough RAM). You can simply issue 10 to 100 such
> queries in a row. Hopefully after the first query all needed info
> will be in a disk cache, so the rest queries will not draw info from
> disk. That will be a clean experiment.
With the server started as:
${POSTMASTER} -o "-F -o /usr/local/db/pgsql/errout -S 32768" \ -i -p 5432 -D/usr/local/db/pgsql/data -B 256 &
And with me being the only person on that system running against the
PostgreSQL database (ie. I don't believe the SI invalidation stuff comes
into play?), the time to run is the exact same each time:
1st run: 0.488u 0.056s 0:16.34 3.2% 10+1423k 0+0io 0pf+0w
2nd run: 0.500u 0.046s 0:16.34 3.3% 10+1517k 0+0io 0pf+0w
3rd run: 0.496u 0.049s 0:16.33 3.2% 9+1349k 0+0io 0pf+0w
4th run: 0.487u 0.056s 0:16.32 3.2% 14+1376k 0+0io 0pf+0w
Note that the results fed back are *exactly* the same each time...the
data is static, as its purely a test database...
I believe that I have the buffers set "Abnormally high", as well as have
provided more then sufficient sort buffer space...
Using the 'optimized' query, that uses subselects, the runs are similar:
1st run: 0.467u 0.031s 0:08.26 5.9% 15+1345k 0+0io 0pf+0w
2nd run: 0.475u 0.023s 0:08.29 5.9% 15+1384k 0+0io 0pf+0w
3rd run: 0.468u 0.031s 0:08.28 5.9% 10+1325k 0+0io 0pf+0w
4th run: 0.461u 0.031s 0:08.31 5.8% 10+1362k 0+0io 0pf+0w
Time is cut in half, CPU usage goes up a bit...but all runs are pretty
much the same...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org