Implementation and performance of MySQL Binlog pre-allocation

Recently to http://forge.mysql.com/worklog/task.php? The binlog pre-allocation mentioned in id = 4925 is implemented based on percoan5.5.18.

In worklog, the tps is increased by 10 times when sync_binlog = 1. It is indeed possible that no group commit exists.

Implementation ideas:

Unlike worklog, daemon plugin is used for pre-allocation.

1. Create a daemon plugin, which is used to pre-allocate the binlog file and is named x1 ~ XN

Wait for the semaphore when the system starts and the pre-allocated file is created;

2. Add judgment in the new_file_impl function (when binlog needs to be switched). When pre-allocation is Enabled:

--- Pre-allocated file, rename

--- If it does not exist, the daemon plugin will be notified to create the plug-in and directly return it to the normal mode.

3. Add the variable actual_size to record the write position of the active binlog. It will be updated before each signal_update.

4. Modify read_log_event to avoid reading dirty data in pre-allocated files.

Simple performance testing:

The test results show that the performance is not as high as that mentioned in worklog when the group commit exists.

Test 1: Use mysqlslap

Insert only auto-incrementing primary key table
Create table xxx (a int auto_increment, B int, c varchar (100), primary key ());

Insert million data

Mysqlslap -- no-defaults-uroot -- create-schema = zwx -- number-of-queries = 1000000 -- concurrency = 100 -- socket =/u01/mysql/run/mysql. sock -- query = "insert into xxx values (NULL, 2, 'sadasda ')"

1.50 threads
(1). binlog_prealloc_num = 0

Tps = 1000000/149 = 6711

(2). binlog_prealloc_num = 10

Tps = 1000000/82 = 12195

Increase (12195-6711)/6711 = 81.7%

2.100 threads

(1). binlog_prealloc_num = 0

Tps = 1000000/86. 6 = 11547

(2). binlog_prealloc_num = 10

Tps = 1000000/57 = 17543

Increase (17543-11547)/11547 = 51.9%

Test 2: Use sysbench to test update Performance

. /Sysbench -- debug = off -- test = tests/db/update_index.lua -- mysql-user = root -- oltp-tables-count = 5 -- oltp-point-selects = 0 -- oltp-table -size = 1000000 -- num-threads = 50 -- max-requests = 1000000 -- max-time = 7200 -- oltp-auto-inc = off -- mysql-engine-trx = yes -- mysql -table-engine = innodb -- oltp-test-mode = notrx -- oltp-nontrx-mode = update_key -- mysql-socket =/u01/mysql/run/mysql. sock run

1.50 threads
(1). binlog_prealloc_num = 0

Tps = 4520.04

(2). binlog_prealloc_num = 10

Tps = 6720.15

Increase (6720-4520)/4520 = 48.6%

2.100 threads

(1). binlog_prealloc_num = 0

Tps = 5967.17

(2). binlog_prealloc_num = 10

Tps = 7698.61

Increase (7698-5967)/5967 = 29%

-----------------------------------

Improvement is limited. The initial test performance is between sync_binlog = 1 and sync_binlog = 0, but it is better than none. Release the patch after stability.