← Back to team overview

maria-developers team mailing list archive

Re: pre-allocating binlog to speed up sync_binlog=1


Hi Serg, Kristian, all

On 14/12/2010, at 4:08 PM, Sergei Golubchik wrote:
On Dec 14, Arjen Lentz wrote:
Can we adopt/implement http://forge.mysql.com/worklog/task.php?id=4925 in
The benchmark info is in the item, and looks quite interesting.
The author tested it using a separate tool to preallocate the binlog, but it should be straightforward implementing this inside the server, that'd
also be much easier to manage without extra fuss for a DBA.

You mean - before it gets into 5.6 and we merge it the normal way?
Or you mean - with a different implementation (w/o mysqlbinlogalloc) ?

Both ;-)

sync_binlog=1 is a serious problem, note my earlier bugreport on the number of fsyncs a commit requires. While fixing that is not trivial, pre-allocating apparently makes a big difference. So I'd see that as high priority, even just looking in the realm of a number of Open Query clients.

Secondly, I think the implementation with the external mysqlbinlogalloc tool is not nice. I appreciate that writing full a 1GB file takes a long time, but that's not how it needs to work. InnoDB can extend its tablespace with a configurable chunk at a time (8M by default, iirc).

The binlog_preallocate variable should, similar to the InnoDB method, define the size of the next preallocation. I also don't see the point of the =0 ignoring a preallocated file. Since mysqld always starts a new binlog on startup, it does not have to scan an existing file at that stage. For replication, surely it can detect what's going on without having a setting. By having the pre-allocation size configurable, users can choose an amount that works best in their particular environment; with the right filesystem, it could be huge. Otherwise, more modest. Either way advantage can be gained.

Arjen Lentz, Exec.Director @ Open Query (http://openquery.com)
Remote expertise & maintenance for MySQL/MariaDB server environments.

Follow us at http://openquery.com/blog/ & http://twitter.com/openquery