Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

<cut>

Aaron> what i would suggest is this:

Aaron> 1) the MySQL and PostgreSQL people create a set of data that both can live
Aaron> with. something in the 500MB range. (a happy medium between what i'm willing to
Aaron> cope with file wise and what would constitute a decent enough test bed). it
Aaron> should include tables with char/text data, tables with numerical data, tables
Aaron> with floating point data, etc... and tables w/mixes. it should include tables
Aaron> with primary keys that are referenced in other tables, to reflect subquery
Aaron> speeds (does MySQL do subqueries yet? i haven't checked in a while). indicies
Aaron> should also be agreed upon, so that we're comparing apples to apples.

Note that it would be much better to write a benchmark that can be run
against any SQL server.  As the MySQL benchmarks are designed to do
exactly this, I would suggest that you use the MySQL benchmark suit as
a base for this!

You can already run the MySQL benchmarks with tables of 500M without
any problems by just supplying some startup switches.

I don't think the important things is just to optimize some specific
queries; It's much more important to test a lot of different types of
queries.

Aaron> 2) a set of selects/inserts/deletes should be agreed upon. i.e. : select all
Aaron> data from table X where field1 is less than or equal to Z. then the MySQL and
Aaron> PostgreSQL hackers should be given a set amount of time to come up with queries
Aaron> for each (this assumes they are the best ones to formulate these). perhaps
Aaron> these queries can be put out on the mailing lists for general discussion?

I think it's important to using queries that are common to many
applications.

For normal queries there isn't that much to optimize.  There is of
course also the option to add some test to 'solve some problem'.  In
this case one can use different methods to solve the query for
different databases;  Normally these kind of tests are more
interesting than useful as this isn't normally portable between
database servers.

Aaron> 3) i will load the data into each database and run the final selects and
Aaron> present the findings.

Aaron> this will leave us with a comparison of how the two databases stack up to each
Aaron> other where the features are comparable. this means we won't be testing
Aaron> transactions, operations that would require procedural languages, etc.

Aaron> a word of procedural languages: although the operations would not REQUIRE them,
Aaron> perhaps it should be allowed that, if the camp decides it would be more
Aaron> appropriate (read: speedy) to do the operation using a procedural language then
Aaron> this should be allowed to reflect the real world abilities of the systems.
Aaron> this should put it to bed, imo.

One could do this as in the MySQL benchmarks:  If one run the benchmarks
with the '--fast' switch, one is allowed to do 'anything' to make the
test run faster.

Aaron> also, since we're not in a death-battle competing with each other (MySQL vs
Aaron> PostgreSQL) as occurs in commercial software worlds, perhaps we should also get
Aaron> together and put out a COOPERATIVE effort to catalog what each database can/can
Aaron> not do and how it stacks up to the commercial databases.
Aaron> this is open source, remember? its about choice. its about the right tool for
Aaron> the right job. its about a FUDless environment where we get the tools to do
Aaron> what we NEED to do.

This is exactly the aim of the MySQL benchmarks.  (They are GPL)

Aaron> in any case. my offer stands. i'll even volunteer to coordinate the effort
Aaron> (though i'm not against not coordinating it either =), although i would not be
Aaron> able to devote the time to building the data set / selects, etc... in fact, if
Aaron> i were to stand in as "coordinator" then it would probably be best that i
Aaron> didn't, to keep the tester's hands "clean" as it were.

Aaron> as coordinator, i'd be willing to collect the final parameters, communicate
Aaron> with representatives from each side (probably the developers?) and post the
Aaron> results (after running them, of course :-)

In this case;  Can you take a look at the MySQL benchmark suite and
comment on this?

Aaron> as a final thought, it might be interesting to see how responsive each database
Aaron> is via PHP scripts. though that would probably be a future test.

The current tests are done with perl and DBI (just to keep the tests portable).

Aaron> ok, that's probably way long enough.. haha.. happy hacking...

Regards,
Monty