Re: [HACKERS] All things equal, we are still alot slower then MySQL? - Mailing list pgsql-hackers

From The Hermit Hacker
Subject Re: [HACKERS] All things equal, we are still alot slower then MySQL?
Date
Msg-id Pine.BSF.4.10.9909201717011.66830-100000@thelab.hub.org
Whole thread Raw
In response to Re: [HACKERS] All things equal, we are still alot slower then MySQL?  (Leon <leon@udmnet.ru>)
List pgsql-hackers
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 



pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: New TODO detail
Next
From: The Hermit Hacker
Date:
Subject: Re: [HACKERS] All things equal, we are still alot slower then MySQL?