Re: BBU Cache vs. spindles

On Tue, Oct 26, 2010 at 7:25 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Oct 26, 2010 at 10:13 AM, Rob Wultsch <wultsch(at)gmail(dot)com> wrote:
>> The double write buffer is one of the few areas where InnoDB does more
>> IO (in the form of fsynch's) than PG. InnoDB also has fuzzy
>> checkpoints (which help to keep dirty pages in memory longer),
>> buffering of writing out changes to secondary indexes, and recently
>> tunable page level compression.
>
> Baron Schwartz was talking to me about this at Surge.  I don't really
> understand how the fuzzy checkpoint stuff works, and I haven't been
> able to find a good description of it anywhere.  How does it keep
> dirty pages in memory longer?  Details on the other things you mention
> would be interesting to hear, too.

For checkpoint behavior:
http://books.google.com/books?id=S_yHERPRZScC&pg=PA606&lpg=PA606&dq=fuzzy+checkpoint&source=bl&ots=JJrzRUKBGh&sig=UOMPsRy5E-YDgjAFkaSVn3dps_M&hl=en&ei=_k8yTOfeHYzZnAepyumLBA&sa=X&oi=book_result&ct=result&resnum=8&ved=0CEYQ6AEwBw#v=onepage&q=fuzzy%20checkpoint&f=false

I would think that best case behavior "sharp" checkpoints with a large
checkpoint_completion_target would have behavior similar to a fuzzy
checkpoint.

Insert (for innodb 1.1+ evidently there is also does delete and purge)
buffering:
http://dev.mysql.com/doc/refman/5.5/en/innodb-insert-buffering.html

For a recent ~800GB db I had to restore, the insert buffer saved 92%
of io needed for secondary indexes.

Compression:
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-compression-internals.html

For many workloads 50% compression results in negligible impact to
performance. For certain workloads compression can help performance.
Please note that InnoDB also has non-tunable toast like feature.

>> Given that InnoDB is not shipping its logs across the wire, I don't
>> think many users would really care if it used the double writer or
>> full page writes approach to the redo log (other than the fact that
>> the log files would be bigger). PG on the other hand *is* pushing its
>> logs over the wire...
>
> So how is InnoDB doing replication?  Is there a second log just for that?
>

The other log is the "binary log" and it is one of the biggest
problems with MySQL. Running MySQL in such a way that the binary log
stays in sync with the InnoDB redo has a very significant impact on
performance.
http://www.mysqlperformanceblog.com/2010/10/23/mysql-limitations-part-2-the-binary-log/
http://mysqlha.blogspot.com/2010/09/mysql-versus-mongodb-update-performance.html
(check out the pretty graph)

If you are going to West you should considering heading over to the
Facebook office on Tuesday as the MySQL team is having something of an
open house:
http://www.facebook.com/event.php?eid=160712450628622

Mark Callaghan from the Facebook MySQL Engineering (and several
members of their ops team, for that matter) team understands InnoDB
dramatically better than I do.

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