Re: [HACKERS] All things equal, we are still alot slower then MySQL?

On Sun, 19 Sep 1999, Thomas Lockhart wrote:

> > 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
> > >From the 'time' numbers, MySQL is running ~17sec faster, but uses up 23%
> > more CPU to do this...so where is our slowdown?
> 
> I don't remember if you gave details on the sizes of tables, but in
> any case I'm going to guess that you are spending almost all of your
> time in the optimizer. Try manipulating the parameters to force the
> genetic optimizer and see if it helps. Lots of quals but only two
> tables gives you a non-optimal case for the default exhaustive
> optimizer.

With default GEQO == 11 relations:0.506u 0.045s 0:19.51 2.7%      10+1596k 0+0io 0pf+0w
With GEQO == 2 relations:0.522u 0.032s 0:19.47 2.8%      9+1385k 0+0io 0pf+0w

If I use that big SUBSELECT that I posted earlier, with GEQO==2:0.005u 0.020s 0:07.84 0.2%      120+486k 0+0io 0pf+0w
And with GEQO==11:0.008u 0.016s 0:07.83 0.1%      144+556k 0+0io 0pf+0w

So, going with one large SELECT call with two SUBSELECTs in it cuts off
12secs, but its a web application, and we're still talking 5 seconds
response slower...and alot less CPU being used, which is nice...

But I'm trying to compare apples->apples as much as possible, and MySQL
won't allow us to do that large SUBSELECT call...gives errors, so I'm
guessing its unsupported...

Other ideas, or am I stuck with accepting 7secs?  (Realizing that as each
new release comes out, that 7secs tends to have a habit of dropping with
all the optimizations and cleans up we do to the server itself)  If so,
then I'm going to have to spend time trying to fix the tables themselves
before delving into switching over to PostgreSQL...which hurts :(
Okay, table sizes for the data are:

aecCategory == 1170
Table    = aeccategory
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| ppid                             | varchar() not null default ''    |     6 |
| pid                              | varchar() not null default ''    |     6 |
| id                               | varchar() not null default ''    |     6 |
| name                             | varchar() not null default ''    |   255 |
| description                      | varchar()                        |   255 |
| url                              | varchar()                        |   255 |
| comidsrc                         | int4                             |     4 |
| datelast                         | timestamp                        |     4 |
+----------------------------------+----------------------------------+-------+
Indices:  aeccategory_id         aeccategory_primary

aecEntMain == 16560
Table    = aecentmain
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| id                               | varchar() not null default ''    |     6 |
| mid                              | char() not null default ''       |     2 |
| name                             | varchar() not null default ''    |   200 |
| description                      | text                             |   var |
| url                              | varchar()                        |   255 |
| street                           | varchar()                        |   255 |
| city                             | varchar()                        |   255 |
| state                            | varchar()                        |   255 |
| postal                           | varchar()                        |   255 |
| country                          | varchar()                        |   255 |
| servarea                         | varchar()                        |   255 |
| business                         | varchar()                        |   255 |
| representation                   | varchar()                        |   255 |
| status                           | varchar()                        |   255 |
| datecreate                       | varchar()                        |    14 |
| whocreate                        | varchar()                        |   255 |
| datelast                         | timestamp                        |     4 |
| wholast                          | varchar()                        |   255 |
+----------------------------------+----------------------------------+-------+
Indices:  aecentmain_entityname         aecentmain_primary

aecWebEntry == 58316
Table    = aecwebentry
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| indid                            | varchar() not null default ''    |     6 |
| divid                            | varchar() not null default ''    |     6 |
| catid                            | varchar() not null default ''    |     6 |
| id                               | varchar() not null default ''    |     6 |
| mid                              | char() not null default ''       |     2 |
| webdetid                         | int4                             |     4 |
| status                           | varchar()                        |   255 |
| datecreate                       | varchar()                        |    14 |
| whocreate                        | varchar()                        |   255 |
| datelast                         | timestamp                        |     4 |
| wholast                          | varchar()                        |   255 |
+----------------------------------+----------------------------------+-------+
Index:    aecwebentry_primary

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org