Обсуждение: 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

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

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
>

> 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.

> 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

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

>>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

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

>>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

>>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/

> 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

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/

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/

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

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

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 #

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
>

> 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

Mike Nolan wrote:
>> 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?

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".

If the customer has a stupid idea, like envisioning an enterprise
solution based on ImSOL, there is no way you will be able to deliver it.
Paying customer or not, you will fail if you bow to their "strategic"
decisions and ignore knowing that the stuff they want to use just
doesn't fit.

That is absolutely not ImSOL specific. If someone comes to me and asks
for a HA scenario with zero transaction loss during failover, we can
discuss a little if this is really what he needs or not, but if he needs
that, the solution will be Oracle or DB2, for sure I will not claim that
PostgreSQL can do that, because it cannot.


Jan

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