Re: fairly current mysql v postgresql comparison need for - Mailing list pgsql-general

> [mailto:pgsql-general-owner@postgresql.org] Namens scott.marlowe
> Verzonden: maandag 24 maart 2003 19:26

> Oh, and another thing.  How do you do hot backups of a MySQL database
> running Innodb tables?  Better get out the checkbook for some closed
> source software.
Please elaborate what you see as a hot backup tool?

Is that, for postgresql, the pg_dump tool? In that case, mysql has the
same. Mysqldump works fine with innodb-tables as well as for myisam
tables.
A really nice thing you'd test is load a very large table into your
innodb (something like 4GB+) and issue a 'delete from testtable', last
time I tried that I spent the rest of the day trying to recover our
innodb-setup... It completely crashed mysql and failed to start
afterwards for a while... Lately it has been fixed 'a bit', your system
probably is able to restart, even though it still crashes or renders
itself unuseable.

Or very cute aswell, set it up with 10GB of innodb-files and load a 6GB
database in it. Then you notice performance is worse than expacted and
you find out a certain index needs to be added... No chance you can do
that on your running system, since mysql needs to create a temporary
duplicate of your table and add the index to that, which of course is
impossible due to missing diskspace. The temporary table is loaded into
the innodb-space aswell and it failes due to lack of space, if you _do_
have enough space it still takes ages to accomplish, copying a 6GB table
isn't a thing database handles very fast...

The same silly behaviour is done when renaming columns or doing any
other changes to the tables. As far as I know, all 'alter table'
statements (and create index appearantly gets translated to that)
require a copy of the entire table and while copying changing the
structure. This is mysql 3.23 knowledge by the way, not 4.0, so maybe
they solved this.

Make a table structure like:
users = uid, uname
questions = qid, qtext
answers = aid, qid, atext
answered_questions = aid, qid, uid

Fill it with a decent amount of answers (please note that it is legal
not to answer a question in this design).
And issue a query like:
SELECT answers.atext,
    answers.aid,
    count(answered_question.aid) AS answercount
FROM
    answers
LEFT JOIN answered_question ON answers.aid = answered_question.aid
WHERE
    answers.qid = 28
GROUP BY answers.aid, answers.atext

In my test (3.23.56 vs 7.3) it results in runtimes like resp: 34.96 vs
23.43 ms.
Postgresql 7.4 dev is even faster with these queries, again I haven't
tested it with 4.0.
These tables are pretty small by the way, 202 questions, 801 answers on
that and 81215 answered_question records, over 40000 userid's, but just
some 3000 different ones had answered these questions.

Most queries where a count/agregate is involved are quite slow in mysql,
I heard.

Another nice test is something where you do something like:
select * from table order by some_indexed_column asc limit 5;
select * from table order by some_indexed_column desc limit 5;

Although mysql probably handles these faster than postgresql it is
unable to sort results backwards as well as it does forward... That can
result in queries over 5 to 10 times slower than the forward version.
Somehow it even uses a file-sort when it has to sort 2 results backward
|:(

I hope this is a bit usefull, it is a short summary of my bad
experiences with mysql ;)

Best regards,

Arjen van der Meijden