On Tue, Oct 26, 2010 at 7:25 AM, Robert Haas <robertmhaas@xxxxxxxxx> wrote: > On Tue, Oct 26, 2010 at 10:13 AM, Rob Wultsch <wultsch@xxxxxxxxx> 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@xxxxxxxxx -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
