Re: Probable faq: need some benchmarks of pgsql vr.s mysql

My company is currently hitting a problem with mysql/innodb having really
slow insert performance (we're seeing ~1K rows/second). My boss wants to
go and spend a bunch of money on the Tokutek backend. I'd rather we save
the money and go to postgres instead. We're not heavily invested in mysql
at this point (fixing our queries to switch from mysql to postgres would
take about five minutes). But my boss wants to see some benchmarks.

I've googled around for a while, but all the benchmarks I've found commit
one or more "fatal flaws", which render the benchmark pointless at best:

1) Comparing Postgres to MyISAM. Transactions are not an option for us,
so it doesn't matter if MyISAM is a hundred times faster. I want to
compare Postgres to InnoDB (bonus points for Postgres vr.s Tokutek).

2) Using the default configurations. Be serious- is there any one who
cares the least about performance who uses the default configuration?

3) Using old versions of Postgres. I'd like the survey to at least use
the 8.x series, bonus points for it being 9.x.

4) Not using COPY for inserts. We would, of course, be using the copy
command for inserts.

Here's the thing. I have personally seen postgres 8.1 insert 30K
rows/second, in to a real table, on crappy hardware (single slow IDE
drive, old crappy hardware). I would be shocked if I can't improve on the
InnoDB numbers by at least an order of magnitude. I'm whipping together a
personal benchmark to show this. But I need a "professional looking"
benchmark, with pretty charts and graphs and etc., to back me up.

Help?

Brian

On 10/29/2010 02:58 PM, Brian Hurt wrote:
>
> My company is currently hitting a problem with mysql/innodb having
> really slow insert performance (we're seeing ~1K rows/second). My boss
> wants to go and spend a bunch of money on the Tokutek backend. I'd
> rather we save the money and go to postgres instead. We're not heavily
> invested in mysql at this point (fixing our queries to switch from mysql
> to postgres would take about five minutes). But my boss wants to see
> some benchmarks.

hmm 1k/s sounds very slow for MySQL/innodb if you are batching your
inserts (either multi-value inserts or larger transactions) - is there
anything special to that data(very wide, enourmous number of indexes etc)?

>
> I've googled around for a while, but all the benchmarks I've found
> commit one or more "fatal flaws", which render the benchmark pointless
> at best:
>
> 1) Comparing Postgres to MyISAM. Transactions are not an option for us,
> so it doesn't matter if MyISAM is a hundred times faster. I want to
> compare Postgres to InnoDB (bonus points for Postgres vr.s Tokutek).

never seen a comparison against Tokutek so you might have to benchmark
for yourself.

>
> 2) Using the default configurations. Be serious- is there any one who
> cares the least about performance who uses the default configuration?
>
> 3) Using old versions of Postgres. I'd like the survey to at least use
> the 8.x series, bonus points for it being 9.x.
>
> 4) Not using COPY for inserts. We would, of course, be using the copy
> command for inserts.
>
> Here's the thing. I have personally seen postgres 8.1 insert 30K
> rows/second, in to a real table, on crappy hardware (single slow IDE
> drive, old crappy hardware). I would be shocked if I can't improve on
> the InnoDB numbers by at least an order of magnitude. I'm whipping
> together a personal benchmark to show this. But I need a "professional
> looking" benchmark, with pretty charts and graphs and etc., to back me up.

Not sure what you would consider "professional" - but I did some testing
back in the 8.4 days here:
http://www.kaltenbrunner.cc/blog/index.php?/archives/27-Benchmarking-8.4-Chapter-2bulk-loading.html
- not sure if that actually matches your workload(but I guess you could
easily test yourself if it is that simple to convert your app).

>
> Help?

I don't think advocacy is actually the right list to ask maybe you would
get a wider audience on -performance or -general.

Stefan

On Fri, Oct 29, 2010 at 5:58 AM, Brian Hurt <bhurt(at)spnz(dot)org> wrote:
>
> My company is currently hitting a problem with mysql/innodb having really
> slow insert performance (we're seeing ~1K rows/second).  My boss wants to go
> and spend a bunch of money on the Tokutek backend.  I'd rather we save the
> money and go to postgres instead.  We're not heavily invested in mysql at
> this point (fixing our queries to switch from mysql to postgres would take
> about five minutes).  But my boss wants to see some benchmarks.
>
> I've googled around for a while, but all the benchmarks I've found commit
> one or more "fatal flaws", which render the benchmark pointless at best:
>
> 1) Comparing Postgres to MyISAM.  Transactions are not an option for us, so
> it doesn't matter if MyISAM is a hundred times faster.  I want to compare
> Postgres to InnoDB (bonus points for Postgres vr.s Tokutek).
>
> 2) Using the default configurations.  Be serious- is there any one who cares
> the least about performance who uses the default configuration?
>
> 3) Using old versions of Postgres.  I'd like the survey to at least use the
> 8.x series, bonus points for it being 9.x.
>
> 4) Not using COPY for inserts.  We would, of course, be using the copy
> command for inserts.
>
> Here's the thing.  I have personally seen postgres 8.1 insert 30K
> rows/second, in to a real table, on crappy hardware (single slow IDE drive,
> old crappy hardware).  I would be shocked if I can't improve on the InnoDB
> numbers by at least an order of magnitude.  I'm whipping together a personal
> benchmark to show this.  But I need a "professional looking" benchmark, with
> pretty charts and graphs and etc., to back me up.
>
> Help?
>
> Brian

If you post your conf I can give you pointers on InnoDB performance.
Please also post a hardware profile (including ram and IO hardware).
One way or another this is the right first step for a comparison with
PG.

As for tokutek, I think they are probably the wave of the future. I
have suggested that they create a pg product.

--
Rob Wultsch
wultsch(at)gmail(dot)com

Brian,

> My company is currently hitting a problem with mysql/innodb having
> really slow insert performance (we're seeing ~1K rows/second).

As others have said, there's clearly something wrong with your setup
and/or application which would likely affect PostgreSQL as well. I can
name several ways in which PostgreSQL is better than MySQL/InnoDB, but
insert performance isn't one of them.

>My boss
> wants to go and spend a bunch of money on the Tokutek backend.

I'm sure that Tokutek would be thrilled to have a customer (they're very
new). Again, though, insert performance isn't Tokutek's specialty; the
arguments for using them is that (a) they're not owned by oracle, and
(b) the "fractal indexes", which are cool.

> I've googled around for a while, but all the benchmarks I've found
> commit one or more "fatal flaws", which render the benchmark pointless
> at best:

Doing real benchmarks is an involved, expensive process, and both DBMSes
are fast-moving targets which need to be benchmarked every year. I've
talked with Percona about doing some MySQL vs. PostgreSQL head-to-heads,
but in the absence of specific funding that's unlikely to be completed.
Besides, we both really want to do head-to-heads with MSSQL & Oracle,
not each other.

> Here's the thing. I have personally seen postgres 8.1 insert 30K
> rows/second, in to a real table, on crappy hardware (single slow IDE
> drive, old crappy hardware). I would be shocked if I can't improve on
> the InnoDB numbers by at least an order of magnitude. I'm whipping
> together a personal benchmark to show this. But I need a "professional
> looking" benchmark, with pretty charts and graphs and etc., to back me up.

Don't have anything like that, I'm afraid. Last real benchmark was
SpecJAppserver from 2007 (which at the time was 90% of comparable Oracle
performance), which has long been superceded.

However, given that what you want to know about is insert performance,
it seems like it would be easy enough to mock up your own comparison.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

Brian,

Rob pointed me to a benchmark of MySQL vs. Tokutek which is interesting
to your case:

http://www.mysqlperformanceblog.com/2009/04/28/detailed-review-of-tokutek-storage-engine/

It's also interesting to me because apparently InnoDB *does* have an
issue with large numbers of inserts to an already-large table, which we
don't have (I don't think we do, anyway).

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Fri, 2010-10-29 at 13:53 -0700, Josh Berkus wrote:
> Brian,
>
> Rob pointed me to a benchmark of MySQL vs. Tokutek which is interesting
> to your case:
>
> http://www.mysqlperformanceblog.com/2009/04/28/detailed-review-of-tokutek-storage-engine/
>
> It's also interesting to me because apparently InnoDB *does* have an
> issue with large numbers of inserts to an already-large table, which we
> don't have (I don't think we do, anyway).

I thought InnoDB still had P/K insert/update issues. I could be cracked
(would have to dig up the article)

JD

>
> --
> -- Josh Berkus
> PostgreSQL Experts Inc.
> http://www.pgexperts.com
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

For the record, the table we're having trouble inserting into is ~100 rows
with ~50 indexes on it. E.F Codd is spinning in his grave. The reason
they went with this design (instead of one that has two tables, each with
3-6 columns, and about that many indexes) is that "joins are slow".
Which they may be on Mysql, I don't know. But this is (unfortunately) a
different battle.

Brian

On Fri, 29 Oct 2010, Josh Berkus wrote:

> Brian,
>
> Rob pointed me to a benchmark of MySQL vs. Tokutek which is interesting
> to your case:
>
> http://www.mysqlperformanceblog.com/2009/04/28/detailed-review-of-tokutek-storage-engine/
>
> It's also interesting to me because apparently InnoDB *does* have an
> issue with large numbers of inserts to an already-large table, which we
> don't have (I don't think we do, anyway).
>
> --
> -- Josh Berkus
> PostgreSQL Experts Inc.
> http://www.pgexperts.com
>
> --
> Sent via pgsql-advocacy mailing list (pgsql-advocacy(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-advocacy
>

On 10/29/2010 11:37 PM, Brian Hurt wrote:
>
> For the record, the table we're having trouble inserting into is ~100
> rows with ~50 indexes on it. E.F Codd is spinning in his grave. The
> reason they went with this design (instead of one that has two tables,
> each with 3-6 columns, and about that many indexes) is that "joins are
> slow". Which they may be on Mysql, I don't know. But this is
> (unfortunately) a different battle.

is that really only 100 rows or are you actually talking about columns?
if the later you will have a very hard time getting reasonable bulk/mass
loading performance in most databases (and also pg) - a table that wide
and with a that ridiculous number of indexes is just bound to be slow.
Now I actually think that the figures you are getting from innodb are
fairly reasonable...

Stefan

On Sat, 30 Oct 2010 15:57:39 +0200, Stefan Kaltenbrunner
<stefan(at)kaltenbrunner(dot)cc> wrote:
> On 10/29/2010 11:37 PM, Brian Hurt wrote:
>>
>> For the record, the table we're having trouble inserting into is ~100
>> rows with ~50 indexes on it. E.F Codd is spinning in his grave. The
>> reason they went with this design (instead of one that has two tables,
>> each with 3-6 columns, and about that many indexes) is that "joins are
>> slow". Which they may be on Mysql, I don't know. But this is
>> (unfortunately) a different battle.

As opposed to the slowness you are having now?

/me smacks your developers for you

JD

--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

On 10/29/10 2:37 PM, Brian Hurt wrote:
>
> For the record, the table we're having trouble inserting into is ~100
> rows with ~50 indexes on it. E.F Codd is spinning in his grave. The
> reason they went with this design (instead of one that has two tables,
> each with 3-6 columns, and about that many indexes) is that "joins are
> slow". Which they may be on Mysql, I don't know. But this is
> (unfortunately) a different battle.

Not sure that that'll be any better on Postgres then. Few DBMSes
optimize for "stupid design". Maybe they should go with a "NoSQL"
database.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Fri, Oct 29, 2010 at 1:25 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> Brian,
>
>
> My company is currently hitting a problem with mysql/innodb having
>> really slow insert performance (we're seeing ~1K rows/second).
>>
>
> As others have said, there's clearly something wrong with your setup and/or
> application which would likely affect PostgreSQL as well. I can name several
> ways in which PostgreSQL is better than MySQL/InnoDB, but insert performance
> isn't one of them.
>
>
> My boss
>> wants to go and spend a bunch of money on the Tokutek backend.
>>
>
> I'm sure that Tokutek would be thrilled to have a customer (they're very
> new). Again, though, insert performance isn't Tokutek's specialty; the
> arguments for using them is that (a) they're not owned by oracle,

<snip>

That seems like a really weak argument, unless Tokutek has come out with a
stand-alone product I'm not aware of. At best you'll still need to deal with
Oracle to get the MySQL bits, at worse Tokutek's technology is as good as it
sounds and they are not an acquisition target for Oracle. (Assuming you
don't want to deal with Oracle, which you're reasoning above implies. Many
people won't care about that though).

>
> > I've googled around for a while, but all the benchmarks I've found
>
>> commit one or more "fatal flaws", which render the benchmark pointless
>> at best:
>>
>
> Doing real benchmarks is an involved, expensive process, and both DBMSes
> are fast-moving targets which need to be benchmarked every year. I've
> talked with Percona about doing some MySQL vs. PostgreSQL head-to-heads, but
> in the absence of specific funding that's unlikely to be completed.
> Besides, we both really want to do head-to-heads with MSSQL & Oracle, not
> each other.
>
>
Actually in this case, "real" benchmarks should be pretty easy. You already
have a running application that you have numbers on. I'd say swap in a copy
of xtradb, a copy of tokutek's tech, and a copy of Postgres (after all, it's
only "5 minutes" to change the queries around), and benchmark all three.
Realistically it would probably take a dedicated week to do it, but you're
probably making a 3 year decision, so this bit of time up front seems like a
good way to go. (And don't forget to publish your results afterwards).

Robert Treat
play: http://www.xzilla.net
work: http://www.omniti.com/is/hiring

On Sat, 30 Oct 2010, Stefan Kaltenbrunner wrote:

> On 10/29/2010 11:37 PM, Brian Hurt wrote:
>>
>> For the record, the table we're having trouble inserting into is ~100
>> rows with ~50 indexes on it. E.F Codd is spinning in his grave. The
>> reason they went with this design (instead of one that has two tables,
>> each with 3-6 columns, and about that many indexes) is that "joins are
>> slow". Which they may be on Mysql, I don't know. But this is
>> (unfortunately) a different battle.
>
> is that really only 100 rows or are you actually talking about columns?

Bleh, I meant columns.

100 rows is nothing.

> if
> the later you will have a very hard time getting reasonable bulk/mass loading
> performance in most databases (and also pg) - a table that wide and with a
> that ridiculous number of indexes is just bound to be slow. Now I actually
> think that the figures you are getting from innodb are fairly reasonable...
>
>
> Stefan
>

Brian

The "insert buffer" in InnoDB accelerates this workload. It buffers
changes in a special b-tree to avoid disk IO during secondary index
maintenance. For my workloads the special b-tree is able to capture
multiple changes to blocks and is likely reduce the IO requirements
for the application. Even without that benefit it allows the server to
absorb workload spikes as the disk reads for secondary index
maintenance are deferred.

This is done for inserts in MySQL 5.1 and for inserts, updates and
deletes in MySQL 5.5. This won't allow InnoDB to match TokuDB in
performance, but it should provide much better throughput than you
would expect from an engine that does update in place.

http://www.google.com/search?hl=en&q=insert+buffer+innodb

On Sat, Oct 30, 2010 at 4:44 PM, Brian Hurt <bhurt(at)spnz(dot)org> wrote:
>
>
> On Sat, 30 Oct 2010, Stefan Kaltenbrunner wrote:
>
>> On 10/29/2010 11:37 PM, Brian Hurt wrote:
>>>
>>> For the record, the table we're having trouble inserting into is ~100
>>> rows with ~50 indexes on it. E.F Codd is spinning in his grave. The
>>> reason they went with this design (instead of one that has two tables,
>>> each with 3-6 columns, and about that many indexes) is that "joins are
>>> slow". Which they may be on Mysql, I don't know. But this is
>>> (unfortunately) a different battle.
>>
>> is that really only 100 rows or are you actually talking about columns?
>
> Bleh, I meant columns.
>
> 100 rows is nothing.
>
>> if the later you will have a very hard time getting reasonable bulk/mass
>> loading performance in most databases (and also pg) - a table that wide and
>> with a that ridiculous number of indexes is just bound to be slow. Now I
>> actually think that the figures you are getting from innodb are fairly
>> reasonable...
>>
>>
>> Stefan
>>
>
> Brian
>
>
> --
> Sent via pgsql-advocacy mailing list (pgsql-advocacy(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-advocacy
>

--
Mark Callaghan
mdcallag(at)gmail(dot)com