How about compressing binlog?
Hi All,
I'm a dba of Tencent Inc. We make extensive use of MySQL as our database in our business.
But in our businesses (mainly are games), there is usually a lot of binlog generated in a short time. For example, a game called "QQ Dancer", it has one million online players at the same time, we use more than 200 machines as its database server, they generate about 1.6T binlog per hour altogether. This is the result of using MIXED mode, it will be even larger in ROW mode, However, we have more than 10 games just like it. Such a large amount of binlog will not only take up disk space, it will also take a lot of network bandwidth, and it is very difficult to make a long-distance backup. We have searched for solution and only found these:
https:/
https:/
I don't know why it hasn't been implemented so long. Given this, we made an idea of compressing a binlog when generating and we have already implemented it.
The solution is as follows:
We added some event types for the compressed edition of event, there are:
QUERY_
WRITE_
UPDATE_
DELETE_
These events inheritance the uncompressed editor events. One of their constructor functions and write function have been overridden for uncompressing and compressing. Anything but this is totally the same. And the format of these events can be described by this picture:
http://
On slave, The IO thread will uncompress and convert them When it receiving the events from the master.
So the SQL and worker threads can be stay unchanged.
We also added two options for this feature: "log_bin_compress " and "log_bin_
if binlog_format == statement {
if log_bin_compress == true and query_len >= log_bin_
create a Query_compresse
else
create a Query_log_event;
}
if binlog_format == row {
if log_bin_compress == true and record_len >= log_bin_
create a Write_rows_
else
create a Write_log_
}
The complete change for Percona 5.6 can be found by:
https:/
We have tested it on some of our games for months, and the result is obvious: the amount of binlog is reduced by 42% ~ 70%. We will be very glad if you can accept our patch.
If you have any other questions, please don't hesitate to reply to me!
Question information
- Language:
- English Edit question
- Status:
- Expired
- Assignee:
- No assignee Edit question
- Last query:
- Last reply: