Re: Configuration Advice

On Thu, 2007-01-18 at 04:24, Arjen van der Meijden wrote:
> On 18-1-2007 0:37 Adam Rich wrote:
> > 4) Complex queries that might take advantage of the MySQL "Query Cache"
> > since the base data never changes
>
> Have you ever compared MySQL's performance with complex queries to
> PostgreSQL's? I once had a query which would operate on a recordlist and
> see whether there were any gaps larger than 1 between consecutive
> primary keys.
>
> Normally that information isn't very usefull, but this time it was.
> Since the data was in MySQL I tried several variations of queries in
> MySQL... After ten minutes or so I gave up waiting, but left my last
> version running. In the mean time I dumped the data, reloaded the data
> in PostgreSQL and ran some testqueries there. I came up with a query
> that took only 0.5 second on Postgres pretty soon. The query on MySQL
> still wasn't finished...

I have had similar experiences in the past. Conversely, I've had
similar things happen the other way around. The biggest difference? If
I report something like that happening in postgresql, it's easier to get
a fix or workaround, and if it's a code bug, the fix is usually released
as a patch within a day or two. With MySQL, if it's a common problem,
then I can find it on the internet with google, otherwise it might take
a while to get a good workaround / fix. And if it's a bug, it might
take much longer to get a working patch.

> In my experience it is (even with the 5.0 release) easier to get good
> performance from complex queries in postgresql.

Agreed. For data warehousing / OLAP stuff, postgresql is generally
better than mysql.

> Besides that, mysql rewrites the entire table for most table-altering
> statements you do (including indexes). For small tables that's no issue,
> but if you somehow can't add all your indexes in a single statement to a
> table you'll be waiting a long time more for new indexes than with
> postgresql. And that situation isn't so unusual if you think of a query
> which needs an index that isn't there yet. Apart from the fact that it
> doesn't have functional indexes and such.

Note that this applies to the myisam table type. innodb works quite
differently. It is more like pgsql in behaviour, and is an mvcc storage
engine. Like all storage engine, it's a collection of compromises.
Some areas it's better than pgsql, some areas worse. Sadly, it lives
under the hood of a database that can do some pretty stupid things, like
ignore column level constraint definitions without telling you.

> Long story short: MySQL still isn't the best performer when looking at
> the more complex queries.

agreed. And those are the queries that REALLY kick your ass. Or your
server's ass, I guess.