Обсуждение: MySQL+InnoDB vs. PostgreSQL test?

Folks,

I've had requests from a couple of businesses to see results of infomal MySQL
+InnoDB vs. PostgreSQL tests.    I know that we don't have the setup to do
full formal benchmarking, but surely someone in our community has gone
head-to-head on your own application?

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Josh,

I evaluated MySQL + InnoDB briefly for a project, once.  I didn't get
very far because of some severe limitations in MySQL.

I had to import all of the data from an existing database (MS SQL).
One of the tables was about 8 million rows, 10 fields, and had 5
indexes.  I found it quite impossible to import into MySQL.  I would
import the data into a table with no indexes, then perform a bunch of
manipulation on it (I wasn't just converting from MS SQL, but also
needed to alter quite a bit of the structure).  After the manipulation,
I would drop some columns and build the indexes.  It took MySQL over 4
days to do this!

What I found out was that any DDL changes to a table in MySQL actually
does this:  create a new table, copy all of the data over, then drop
the old table and rename the new one.  Whenever I added a new index,
MySQL would go through the process of rebuilding each previous index.
Same thing when adding or dropping columns.

I could not find a way to import all of the data in a reasonable amount
of time.  For comparison, it took less that 45 minutes to import all of
the data in to PostgreSQL (that's ALL of the data, not just that one
table).

Needless to say (but I'll say it anyway :-), I didn't get any farther
in my evaluation, there was no point.

One more thing that annoyed me.  If you started a process, such as a
large DDL operation, or heaven forbid, a cartesian join (what?  I never
do that!).  There's no way to cancel it with InnoDB.  You have to wait
for it to finish.  Hitting ctrl+c in their command line tool only kills
the command line tool, the process continues.  Even if you stop the
database and restart it (including with a hard boot), it will pick
right up where it left off and continue.  That proved to be way too
much of a pain for me.

Disclaimer:  I'm not a real MySQL expert, or anything.  There could be
ways of getting around this, but after two weeks of trying, I decided
to give up.  It only took me a few hours to build the requisite
PostgreSQL scripts and I never looked back.

Adam Ruth

On Feb 2, 2004, at 10:21 AM, Josh Berkus wrote:

> Folks,
>
> I've had requests from a couple of businesses to see results of
> infomal MySQL
> +InnoDB vs. PostgreSQL tests.    I know that we don't have the setup
> to do
> full formal benchmarking, but surely someone in our community has gone
> head-to-head on your own application?
>
> --
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

On Mon, 2004-02-02 at 12:21, Josh Berkus wrote:
> Folks,
>
> I've had requests from a couple of businesses to see results of infomal MySQL
> +InnoDB vs. PostgreSQL tests.    I know that we don't have the setup to do
> full formal benchmarking, but surely someone in our community has gone
> head-to-head on your own application?
>

We have the setup to do informal benchmarking via OSDL, but afaik mysql
doesn't conform to any of the dbt benchmarks...

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

> One more thing that annoyed me.  If you started a process, such as a
> large DDL operation, or heaven forbid, a cartesian join (what?  I never
> do that!).

I believe InnoDB also has O(n) rollback time.  eg. if you are rolling
back 100 million row changes, it takes a long, long time.  In PostgreSQL
rolling back is O(1)...

Chris

On Tue, 3 Feb 2004, Christopher Kings-Lynne wrote:

> > One more thing that annoyed me.  If you started a process, such as a
> > large DDL operation, or heaven forbid, a cartesian join (what?  I never
> > do that!).
>
> I believe InnoDB also has O(n) rollback time.  eg. if you are rolling
> back 100 million row changes, it takes a long, long time.  In PostgreSQL
> rolling back is O(1)...

Actually, it takes signifigantly longer to rollback than to roll forward,
so to speak, so that if you inserted for 10,000 rows and it took 5
minutes, it would take upwards of 30 times as long to roll back.

This is from the docs:

http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#InnoDB_tuning

Point 8:

# Beware of big rollbacks of mass inserts: InnoDB uses the insert buffer
to save disk I/O in inserts, but in a corresponding rollback no such
mechanism is used. A disk-bound rollback can take 30 times the time of the
corresponding insert. Killing the database process will not help because
the rollback will start again at the database startup. The only way to get
rid of a runaway rollback is to increase the buffer pool so that the
rollback becomes CPU-bound and runs fast, or delete the whole InnoDB
database.

Josh Berkus wrote:

> Folks,
>
> I've had requests from a couple of businesses to see results of infomal MySQL
> +InnoDB vs. PostgreSQL tests.    I know that we don't have the setup to do
> full formal benchmarking, but surely someone in our community has gone
> head-to-head on your own application?
>

Josh,

how does someone compare an Apache+PHP+MySQL "thing" against something
implemented with half the stuff done in stored procedures and the entire
business model guarded by referential integrity, custom triggers and
whatnot?

Seriously, I am tired of this kind of question. You gotta get bold
enough to stand up in a "meeting" like that, say "guy's, you can ask me
how this compares to Oracle ... but if you're seriously asking me how
this compares to MySQL, call me again when you've done your homework".


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

> Seriously, I am tired of this kind of question. You gotta get bold
> enough to stand up in a "meeting" like that, say "guy's, you can ask me
> how this compares to Oracle ... but if you're seriously asking me how
> this compares to MySQL, call me again when you've done your homework".

Hey at least I noticed that InnoDB has one essential feature we don't:

SELECT ... IN SHARE MODE;

Which does a shared lock on a row as opposed to a write lock, hence
avoiding nasty foreign key deadlocks...

Chris

Chris,

> Hey at least I noticed that InnoDB has one essential feature we don't:
>
> SELECT ... IN SHARE MODE;
>
> Which does a shared lock on a row as opposed to a write lock, hence
> avoiding nasty foreign key deadlocks...

Um, wrong.   We don't lock rows for SELECT.

--
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology     josh@agliodbs.com
    and data management solutions     (415) 565-7293
   for law firms, small businesses      fax 621-2533
    and non-profit organizations.     San Francisco

>>Um, wrong.   We don't lock rows for SELECT.
>
> No, but Chris is correct that we could do with having some kind of
> shared lock facility at the row level.

Out of interest, what is it about this particular task that's so hard?
(Not that I could code it myself).  But surely you can use the same sort
of thing as the FOR UPDATE code path?

Chris

>>Out of interest, what is it about this particular task that's so hard?
>
>
> Keeping track of multiple lockers in a fixed amount of disk space.

Why not look at how InnoDB does it?  Or is that not applicable?

Chris,

> > Which does a shared lock on a row as opposed to a write lock, hence
> > avoiding nasty foreign key deadlocks...
>
> Um, wrong.   We don't lock rows for SELECT.

Unless you meant something else?   Am I not following you?

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

In an attempt to throw the authorities off his trail, JanWieck@Yahoo.com (Jan Wieck) transmitted:
> Josh Berkus wrote:
>> I've had requests from a couple of businesses to see results of
>> infomal MySQL
>> +InnoDB vs. PostgreSQL tests.    I know that we don't have the setup
>> to do full formal benchmarking, but surely someone in our community
>> has gone head-to-head on your own application?
>
> how does someone compare an Apache+PHP+MySQL "thing" against something
> implemented with half the stuff done in stored procedures and the
> entire business model guarded by referential integrity, custom
> triggers and whatnot?
>
> Seriously, I am tired of this kind of question. You gotta get bold
> enough to stand up in a "meeting" like that, say "guy's, you can ask
> me how this compares to Oracle ... but if you're seriously asking me
> how this compares to MySQL, call me again when you've done your
> homework".

Actually, before saying anything in public about their products, check
out what they require for use of their protected trademarks.
<http://www.mysql.com/company/trademark.html>

To wit, they indicate that:

  "The MySQL AB Marks may not be used in a manner or with respect to
  products that will decrease the value of the MySQL AB Marks or
  otherwise impair or damage MySQL AB's brand integrity, reputation or
  goodwill"

It seems to me that presenting a benchmark that did not favor their
product could be quite reasonably considered to be an "impairment" of
their integrity, reputation, or goodwill, and therefore be something
worthy of legal attack.

That certainly wouldn't be new to the database industry; numerous
(most?) database vendors forbid third parties from presenting
benchmarks without their express consent.

It is actually rather surprising that despite having the budget to put
together a "benchmarketing" group, the only results that they are
publishing are multiple years old, with a paucity of InnoDB(tm)
results, and where they only seem to compare it with ancient versions
of "competitors."

And, of course, if "MaxDB(tm)" is their future, replacing the older
storage schemes, the benchmarks should be based on that.  And the
benchmarks that exist there are all based on the R/3 (tm) SD module,
which is spectacularly different from the usual web server work.  (It
looks like that involves throwing a load of BDC sessions at the
server, but I'm guessing, and in any case, it's work SAP AG did...)
--
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://www.ntlug.org/~cbbrowne/nonrdbms.html
"Has anyone ever thought about the fact that in general, the only web
sites that are consistently making money are the ones dealing in
pornography?  This brings new meaning to the term, "obscene
profits". :)"  -- Paul Robinson <postmaster@paul.washington.dc.us>

>>Um, wrong.   We don't lock rows for SELECT.
>
> Unless you meant something else?   Am I not following you?

I mean row level shared read lock.  eg. a lock that says, you can read
but you cannot delete.

It's what postgres needs to alleviate its foreign key trigger deadlock
problems.

Chris

Well, when I prepared my PG presentation I did some testing of MySQL (So
I could be justified in calling it lousy :).   I used the latest release
(4.0.something I think)

I was first bitten by my table type being MyISAM when I thought I set
the default ot InnoDB.  But I decided since my test was going to be
read-only MyISAM should be the best possible choice.  I loaded up a
couple million records and changed my stored procedure into a perl
script [I also decided to use this perl script for testing PG to be
fair].

For one client mysql simply screamed.

Then I decided to see what happens with 20 clients.

MySQL clocked in at 650 seconds.  During this time the machine was VERY
unresponsive.  To be fair, that could be Linux, not MySQL.

PG (7.3.4) clocked in at 220 seconds.  The machine was perfectly fine
during the test -  nice and responsive.

The hardware wasn't much - dual p2-450 running stock RH8. (2x15k 18g
scsi drives for the data volume)

Then I decided to try the "beloved" InnoDB.

Well.. after it sat for a few hours at 100% cpu loading the data I
killed it off and gave up on InnoDB.. I am interested in the numbers.
Perhaps I'll fire it up again someday and let it finish loading.

Remember -  you cannot judge mysql by since connection performance - you
can't beat it.  But just add up the concurrency and watch the cookies
tumble

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

On Tue, 3 Feb 2004 16:02:00 +0200
"Rigmor Ukuhe" <rigmor.ukuhe@finestmedia.com> wrote:

> > script [I also decided to use this perl script for testing PG to be
> > fair].
> >
> > For one client mysql simply screamed.
> >
>
> If already have test case set up, you could inform us, from where
> Postgres starts to beat MySql. Because if with 5 clients it still
> "screams" then i would give it a try in case of that kind of
> requirements.
>

I just checked (to see about restarting the innodb test) and it appears
that it'll take a bit of work to get the machine up and running.
I don't have time right now to do further testing.

However, you could try it out.

Not sure at what point it will topple, in my case it didn't matter if it
ran good with 5 clients as I'll always have many more clients than 5.


--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

In an attempt to throw the authorities off his trail, JanWieck@Yahoo.com (Jan Wieck) transmitted: 

Josh Berkus wrote:   

I've had requests from a couple of businesses to see results of
infomal MySQL
+InnoDB vs. PostgreSQL tests.    I know that we don't have the setup
to do full formal benchmarking, but surely someone in our community
has gone head-to-head on your own application?     

how does someone compare an Apache+PHP+MySQL "thing" against something
implemented with half the stuff done in stored procedures and the
entire business model guarded by referential integrity, custom
triggers and whatnot?

Seriously, I am tired of this kind of question. You gotta get bold
enough to stand up in a "meeting" like that, say "guy's, you can ask
me how this compares to Oracle ... but if you're seriously asking me
how this compares to MySQL, call me again when you've done your
homework".   

Actually, before saying anything in public about their products, check
out what they require for use of their protected trademarks.
<http://www.mysql.com/company/trademark.html>

To wit, they indicate that:
 "The MySQL AB Marks may not be used in a manner or with respect to products that will decrease the value of the MySQL AB Marks or otherwise impair or damage MySQL AB's brand integrity, reputation or goodwill"

It seems to me that presenting a benchmark that did not favor their
product could be quite reasonably considered to be an "impairment" of
their integrity, reputation, or goodwill, and therefore be something
worthy of legal attack. 

-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

On Tue, 3 Feb 2004, Joshua D. Drake wrote:

> Christopher Browne wrote:
>
> >In an attempt to throw the authorities off his trail, JanWieck@Yahoo.com (Jan Wieck) transmitted:
> >
> >
> >>Josh Berkus wrote:
> >>
> >>
> >>>I've had requests from a couple of businesses to see results of
> >>>infomal MySQL
> >>>+InnoDB vs. PostgreSQL tests.    I know that we don't have the setup
> >>>to do full formal benchmarking, but surely someone in our community
> >>>has gone head-to-head on your own application?
> >>>
> >>>
> >>how does someone compare an Apache+PHP+MySQL "thing" against something
> >>implemented with half the stuff done in stored procedures and the
> >>entire business model guarded by referential integrity, custom
> >>triggers and whatnot?
> >>
> >>Seriously, I am tired of this kind of question. You gotta get bold
> >>enough to stand up in a "meeting" like that, say "guy's, you can ask
> >>me how this compares to Oracle ... but if you're seriously asking me
> >>how this compares to MySQL, call me again when you've done your
> >>homework".
> >>
> >>
> >
> >Actually, before saying anything in public about their products, check
> >out what they require for use of their protected trademarks.
> ><http://www.mysql.com/company/trademark.html>
> >
> >To wit, they indicate that:
> >
> >  "The MySQL AB Marks may not be used in a manner or with respect to
> >  products that will decrease the value of the MySQL AB Marks or
> >  otherwise impair or damage MySQL AB's brand integrity, reputation or
> >  goodwill"
> >
> >It seems to me that presenting a benchmark that did not favor their
> >product could be quite reasonably considered to be an "impairment" of
> >their integrity, reputation, or goodwill, and therefore be something
> >worthy of legal attack.
> >
> >
> It depends on how it is presented. Basically you just don't offer an
> opinion on the matter.
> For example...
>
> MySQL was 10x slower than PostgreSQL in this test....
>
> Instead you could use something like.
>
> We performed the following test.
>
> MySQL scored this much
> PostgreSQL scored this much

My guess is that what they are saying is that you can't make a program
like:

mysqlhelper

without their permission.

Using their mark in a review is fair use, and the only way they could get
you is if you either failed to attribute it, or had signed a license with
them saying you wouldn't do benchmarks, like how Oracle licenses their
software.

On Tue, 03 Feb 2004 11:46:05 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Jeff <threshar@torgo.978.org> writes:
> > Not sure at what point it will topple, in my case it didn't matter
> > if it ran good with 5 clients as I'll always have many more clients
> > than 5.
>
> I did some idle, very unscientific tests the other day that indicated
> that MySQL insert performance starts to suck with just 2 concurrent
> inserters.  Given a file containing 10000 INSERT commands, a single
> mysql client ran the file in about a second.  So if I feed the file
> simultaneously to two mysqls in two shell windows, it should take
> about two seconds total to do the 20000 inserts, right?  The observed
> times were 13 to 15 seconds.  (I believe this is with a MyISAM table,
> since I just said CREATE TABLE without any options.)
>

MyISAM is well known to suck if you update/insert/delete because it
simply aquires a full table lock when you perform those operations!

InnoDB is supposed to be better at that.

So your results are fairly in line with what you should see.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

Wow, I didn't know that (didn't get far enough to test any rollback).
That's not a good thing.  <facetious>But then again, it's MySQL who
needs rollback anyway?</facetious>

On Feb 2, 2004, at 5:44 PM, Christopher Kings-Lynne wrote:

>> One more thing that annoyed me.  If you started a process, such as a
>> large DDL operation, or heaven forbid, a cartesian join (what?  I
>> never do that!).
>
> I believe InnoDB also has O(n) rollback time.  eg. if you are rolling
> back 100 million row changes, it takes a long, long time.  In
> PostgreSQL rolling back is O(1)...
>
> Chris
>

Josh Berkus <josh@agliodbs.com> writes:
>> Hey at least I noticed that InnoDB has one essential feature we don't:
>> SELECT ... IN SHARE MODE;
>>
>> Which does a shared lock on a row as opposed to a write lock, hence
>> avoiding nasty foreign key deadlocks...

> Um, wrong.   We don't lock rows for SELECT.

No, but Chris is correct that we could do with having some kind of
shared lock facility at the row level.

            regards, tom lane

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> No, but Chris is correct that we could do with having some kind of
>> shared lock facility at the row level.

> Out of interest, what is it about this particular task that's so hard?

Keeping track of multiple lockers in a fixed amount of disk space.

            regards, tom lane

Jeff <threshar@torgo.978.org> writes:
> Not sure at what point it will topple, in my case it didn't matter if it
> ran good with 5 clients as I'll always have many more clients than 5.

I did some idle, very unscientific tests the other day that indicated
that MySQL insert performance starts to suck with just 2 concurrent
inserters.  Given a file containing 10000 INSERT commands, a single
mysql client ran the file in about a second.  So if I feed the file
simultaneously to two mysqls in two shell windows, it should take about
two seconds total to do the 20000 inserts, right?  The observed times
were 13 to 15 seconds.  (I believe this is with a MyISAM table, since
I just said CREATE TABLE without any options.)

It does scream with only one client though ...

            regards, tom lane

Jeff <threshar@torgo.978.org> writes:
> On Tue, 03 Feb 2004 11:46:05 -0500
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I did some idle, very unscientific tests the other day that indicated
>> that MySQL insert performance starts to suck with just 2 concurrent
>> inserters.  Given a file containing 10000 INSERT commands, a single
>> mysql client ran the file in about a second.  So if I feed the file
>> simultaneously to two mysqls in two shell windows, it should take
>> about two seconds total to do the 20000 inserts, right?  The observed
>> times were 13 to 15 seconds.  (I believe this is with a MyISAM table,
>> since I just said CREATE TABLE without any options.)

> MyISAM is well known to suck if you update/insert/delete because it
> simply aquires a full table lock when you perform those operations!

Sure, I wasn't expecting it to actually overlap any operations.  (If you
try the same test with Postgres, the scaling factor is a little better
than linear because we do get some overlap.)  But that shouldn't result
in a factor-of-seven slowdown.  There's something badly wrong with their
low-level locking algorithms I think.

            regards, tom lane

> script [I also decided to use this perl script for testing PG to be
> fair].
>
> For one client mysql simply screamed.
>

If already have test case set up, you could inform us, from where Postgres
starts to beat MySql. Because if with 5 clients it still "screams" then i
would give it a try in case of that kind of requirements.

Rigmor Ukuhe

> Then I decided to see what happens with 20 clients.
>
> MySQL clocked in at 650 seconds.  During this time the machine was VERY
> unresponsive.  To be fair, that could be Linux, not MySQL.
>
> PG (7.3.4) clocked in at 220 seconds.  The machine was perfectly fine
> during the test -  nice and responsive.
>
> The hardware wasn't much - dual p2-450 running stock RH8. (2x15k 18g
> scsi drives for the data volume)
>
> Then I decided to try the "beloved" InnoDB.
>
> Well.. after it sat for a few hours at 100% cpu loading the data I
> killed it off and gave up on InnoDB.. I am interested in the numbers.
> Perhaps I'll fire it up again someday and let it finish loading.
>
> Remember -  you cannot judge mysql by since connection performance - you
> can't beat it.  But just add up the concurrency and watch the cookies
> tumble
>
> --
> Jeff Trout <jeff@jefftrout.com>
> http://www.jefftrout.com/
> http://www.stuarthamm.net/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19.01.2004
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19.01.2004

> Seriously, I am tired of this kind of question. You gotta get bold
> enough to stand up in a "meeting" like that, say "guy's, you can ask me
> how this compares to Oracle ... but if you're seriously asking me how
> this compares to MySQL, call me again when you've done your homework".

Can they call you at the unemployment office?
--
Mike Nolan

Jan Wieck wrote:
> It might not work with the words I used above, but the point I tried to
> make is that the hardest thing you can "sell" is a "no". I mean, not
> just saying "no", but selling it in a way that the customer will not go
> with the next idiot who claims "we can do that".

But you will need some kind of data or reasoning to back up your response,
especially if it is deviating from the conventional wisdom, or from
some familiar system.

Especially in this case, it's not a "no" answer that's being sold...
it's "solution a is better than solution b, even though you might
be more familiar with solution b."

Cheers,
Mark