Skip site navigation (1) Skip section navigation (2)

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

From: MARK CALLAGHAN <mdcallag(at)gmail(dot)com>
To: Brian Hurt <bhurt(at)spnz(dot)org>
Cc: Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc>, Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-advocacy(at)postgresql(dot)org
Subject: Re: Probable faq: need some benchmarks of pgsql vr.s mysql
Date: 2010-11-01 15:23:35
Message-ID: AANLkTimq718qCu=LzEdVQxe0tey7Hm+KZeH4dXXSfkuz@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-advocacy
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

In response to

pgsql-advocacy by date

Next:From: Martin Farach-ColtonDate: 2010-11-02 14:17:20
Subject: Re: Probable faq: need some benchmarks of pgsql vr.s mysql
Previous:From: Guillaume LelargeDate: 2010-10-31 05:42:57
Subject: Re: [pgsql-advocacy] Tasks for the Google Code-In

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group