From: | The Hermit Hacker <scrappy(at)hub(dot)org> |
---|---|
To: | Leon <leon(at)udmnet(dot)ru> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] All things equal, we are still alot slower then MySQL? |
Date: | 1999-09-20 20:27:15 |
Message-ID: | Pine.BSF.4.10.9909201717011.66830-100000@thelab.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, 20 Sep 1999, Leon wrote:
> Tom Lane wrote:
> >
> > The Hermit Hacker <scrappy(at)hub(dot)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(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org
From | Date | Subject | |
---|---|---|---|
Next Message | The Hermit Hacker | 1999-09-20 21:16:40 | Re: [HACKERS] All things equal, we are still alot slower then MySQL? |
Previous Message | Bruce Momjian | 1999-09-20 20:06:45 | New TODO detail |