> What I'd love to see is a common set of data to work from, and table
> structures...then come up with, again, a common set of "results expected",
> from the database. Then, let the MySQL guys provide their query for the
> results, and let the PostgreSQL guys come up with theirs, and compare
> *that*. Don't compare how to get the results the fastest through MySQL,
> and then how the same query stands up under PostgreSQL...let us give them
> how to get it fastest from PostgreSQL...
> The problem also comes down to common hardware...
> Anyone out there running a machine, and have the time, to do something
> like this?
well, i have a single pentiumII 400 box with a RAID 5 array and 256 MB RAM that
often runs at 0 load during the weekends and i'm the sole admin to it. i'd be
willing to offer the computing resources for the task...
additionally, i have another single pentiumII 400 that is the devel system,
only 128 MB RAM, but i can put it to task whenever i want =) my employers would
probably prefer this, but i'm sure i could command both for the test(s).. in
fact, i'd probalby run the production server as a "real world environment"
(several processes running, etc) and the devel system as a "best case scenario"
(i.e. no other processes running except those absolutely necessary, no network
activity, blah blah blah)...
what i would suggest is this:
1) the MySQL and PostgreSQL people create a set of data that both can live
with. something in the 500MB range. (a happy medium between what i'm willing to
cope with file wise and what would constitute a decent enough test bed). it
should include tables with char/text data, tables with numerical data, tables
with floating point data, etc... and tables w/mixes. it should include tables
with primary keys that are referenced in other tables, to reflect subquery
speeds (does MySQL do subqueries yet? i haven't checked in a while). indicies
should also be agreed upon, so that we're comparing apples to apples.
2) a set of selects/inserts/deletes should be agreed upon. i.e. : select all
data from table X where field1 is less than or equal to Z. then the MySQL and
PostgreSQL hackers should be given a set amount of time to come up with queries
for each (this assumes they are the best ones to formulate these). perhaps
these queries can be put out on the mailing lists for general discussion?
3) i will load the data into each database and run the final selects and
present the findings.
this will leave us with a comparison of how the two databases stack up to each
other where the features are comparable. this means we won't be testing
transactions, operations that would require procedural languages, etc.
a word of procedural languages: although the operations would not REQUIRE them,
perhaps it should be allowed that, if the camp decides it would be more
appropriate (read: speedy) to do the operation using a procedural language then
this should be allowed to reflect the real world abilities of the systems.
this should put it to bed, imo.
also, since we're not in a death-battle competing with each other (MySQL vs
PostgreSQL) as occurs in commercial software worlds, perhaps we should also get
together and put out a COOPERATIVE effort to catalog what each database can/can
not do and how it stacks up to the commercial databases.
this is open source, remember? its about choice. its about the right tool for
the right job. its about a FUDless environment where we get the tools to do
what we NEED to do.
in any case. my offer stands. i'll even volunteer to coordinate the effort
(though i'm not against not coordinating it either =), although i would not be
able to devote the time to building the data set / selects, etc... in fact, if
i were to stand in as "coordinator" then it would probably be best that i
didn't, to keep the tester's hands "clean" as it were.
as coordinator, i'd be willing to collect the final parameters, communicate
with representatives from each side (probably the developers?) and post the
results (after running them, of course :-)
as a final thought, it might be interesting to see how responsive each database
is via PHP scripts. though that would probably be a future test.
ok, that's probably way long enough.. haha.. happy hacking...
Aaron J. Seigo