pre-alloc binlog file may help improve performance

[Weixiang Zhai]

someone had realized  this feature based on MySQL5.1 two years ago( http://lists.mysql.com/commits/113309 ) and there was a worklog here: http://forge.mysql.com/worklog/task.php?id=4925. From test result, performance improved greatly

Recently I had tried to backport this patch to Percona Server 5.5.24 but use another realization: using a daemon plugin to pre-alloc binlog file

my test: (using mysqlslap, ext3 filesystem)

set sync_binlog       = 1

create a simple table:

     create table xxx (a int auto_increment, b int, c varchar(50), primary key(a))

mysqlslap --no-defaults -uxx --create-schema=test --number-of-queries=2000000 --concurrency=$i  -S $sock  --query="insert into xxx values  (NULL,2,     'sadasda')"

threads              with binlog-prealloc                    Original version

10                               152s                                               455s                          

30                               104s                                               216s

50                                97s                                                164s

70                                98s                                                143s

90                                98s                                                132s

110                              101s                                              127s

130                              102s                                              124s

150                              104s                                              123s

The patch is just used to test the performance improvement

cmake option:

   -DWITH_BINLOG_PREALLOC=ON

then install plugin:

set binlog_prealloc = 1;

install plugin binlog_prealloc soname 'libbinlog_prealloc.so';

flush logs;

from test result, it seems this feature can help improve performance.

[Laurynas Biveinis]

Weixiang -

> someone had realized this feature based on MySQL5.1 two years
> ago( http://lists.mysql.com/commits/113309 ) and there was a worklog
> here: http://forge.mysql.com/worklog/task.php?id=4925. From test result,
> performance improved greatly
>
> Recently I had tried to backport this patch to Percona Server 5.5.24 but use

Thanks for your suggestion. Indeed it looks like a nice performance improvement.

For further discussion on including this in Percona Server, I have
created a blueprint on Launchpad at

https://blueprints.launchpad.net/percona-server/+spec/preallocate-binlogs

Thanks again,
--
Laurynas
www.percona.com