Using the exact same data, and the exact same queries (dbi is cool):
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
The main query that appears to be "dog slow" is:
SELECT distinct b.indid, b.divid, b.catid, a.id, a.mid \ FROM aecEntMain a, aecWebEntry b \ WHERE
(a.id=b.idAND a.mid=b.mid) \ AND (a.status like 'active%' and b.status like 'active%') AND (a.status
like'%active:ALL%' and b.status like '%active:ALL%') AND (a.representation like '%:ALL%') AND
(b.indid=?and b.divid=? and b.catid=?)";
Where, unfortunately, getting rid of those LIKE comparisons will be next to
impossible in the short time...
>From the 'time' numbers, MySQL is running ~17sec faster, but uses up 23%
more CPU to do this...so where is our slowdown? Obviously it isn't a lack
of CPU...all else is equal...hardware wise, both are running on the same
machine.
If I get rid of the three lines above that deal with LIKE, the results
are:
MySQL: 0.497u 0.168s 0:01.48 43.9% 9+1519k 0+0io 0pf+0w
PgSQL: 0.504u 0.052s 0:17.81 3.0% 10+1608k 0+0io 0pf+0w
So, blaming things on the LIKE conditions is totally inappropriate...
And looking at the EXPLAIN of the above, I have enough indices:
NOTICE: QUERY PLAN:
Unique (cost=1271.15 rows=5 width=84) -> Sort (cost=1271.15 rows=5 width=84) -> Nested Loop (cost=1271.15
rows=5width=84) -> Index Scan using aecwebentry_primary on aecwebentry b (cost=1269.08 rows=1 width=60)
-> Index Scan using aecentmain_primary on aecentmain a (cost=2.07 rows=16560 width=24)
EXPLAIN
I'm starting the server as:
#!/bin/tcsh
setenv POSTMASTER /usr/local/db/pgsql/bin/postmaster
rm /tmp/.s.P*
${POSTMASTER} -o "-F -o /usr/local/db/pgsql/errout -S 32768" \ -i -p 5432 -D/usr/local/db/pgsql/data -B 256 &
So I think I'm dedicating *more* then enough resources to the server, no?
Again, this data is static...hasn't changed for either database since we
loaded it yesterday...a vacuum analyze has been done on the PostgreSQL
database, but we haven't done anything with the MySQL one (no vacuum, no
special run parameters)
I'm going to be working with this company towards cleaning up the table
structures over the next little while, with an eye towards moving it to
PostgreSQL, but, all things considered equal except for the DB software
itself...how is it that we are *so* much slower?
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
