MySQL: BinLog Assíncrono

Os Binary Logs (também conhecidos apenas por Bin Logs) do MySQL contém todas as alterações feitas nos bancos de dados, e é necessário para executa-las novamente após a restauração de um backup. Se você não pode perder dados, deve ativar os Binary Logs. Os Bin Logs guardam alterações feitas em tabelas de todos os Engines.

Mas se você puder se dar ao luxo de perder um pouco de dados, pode utilizar a gravação assíncrona dos Bin Logs, ao invés no padrão, que solicita uma gravação síncrona ao sistema operacional. Esta perda de dados ocorreria apenas em caso de necessidade de restaurar um backup e aplicar os Bin Logs. Na gravação assíncrona, se o MySQL for desligado incorretamente (por uma falha de energia, por exemplo) algumas gravações podem não estar nos Bin Logs, e portanto não poderão ser reaplicadas no banco de dados após a restauração do backup.

Veja abaixo o impacto de I/O causado pela gravação síncrona (parâmetro sync_binlog em 1), no disco sda, onde são gravados os Bin Logs deste servidor (na última coluna do iostat, a %util).

mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0,01 sec)

mysql> \! iostat -xd 2 10
Linux 3.10.0-514.2.2.el7.x86_64 (Baltazar.localdomain)  09-01-2017      _x86_64_        (4 CPU)

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               2,02     1,99   10,72   39,61  1333,99  1238,27   102,22     2,38   47,35    7,61   58,11  14,04  70,67
sdb               0,58     0,05  127,39   42,79  4471,10   396,98    57,21     0,51    3,00    1,04    8,84   0,42   7,21

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    0,00   16,00     0,00   130,00    16,25     0,26   16,22    0,00   16,22  16,22  25,95
sdb               0,00     0,00 1895,00   19,00 33116,00   143,50    34,75     0,62    0,32    0,32    0,68   0,31  59,25

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    0,50   33,00     2,00   222,00    13,37     0,52   15,55   22,00   15,45  15,55  52,10
sdb               0,00     0,00 2109,00   35,00 36236,00   199,25    33,99     0,66    0,31    0,30    0,51   0,30  63,25

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    3,00   25,00    20,00   308,00    23,43     0,40   13,73    0,17   15,36  14,18  39,70
sdb               0,00     0,00 2071,50   29,50 35670,00   188,00    34,13     0,65    0,31    0,30    0,71   0,30  62,80

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    0,00   28,50     0,00   158,00    11,09     0,52   18,79    0,00   18,79  17,72  50,50
sdb               0,00     0,00 1905,00   30,50 32374,00   156,50    33,61     0,65    0,34    0,33    0,64   0,32  62,40

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    0,00   21,50     0,00   188,00    17,49     0,42   19,74    0,00   19,74  19,70  42,35
sdb               0,00     0,00 1831,50   23,00 32102,00   187,50    34,82     0,64    0,35    0,34    0,85   0,34  62,30

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    0,00   21,50     0,00   148,00    13,77     0,44   19,49    0,00   19,49  20,35  43,75
sdb               0,00     0,00 1610,50   24,00 29178,00   152,00    35,89     0,58    0,36    0,35    0,79   0,33  54,05

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    0,00   34,50     0,00   186,00    10,78     0,66   19,45    0,00   19,45  19,01  65,60
sdb               0,00     0,00 1603,50   39,50 29738,00   204,50    36,45     0,58    0,35    0,35    0,54   0,33  54,45

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    3,00   22,50   108,00   130,00    18,67     0,48   19,20   11,17   20,27  18,08  46,10
sdb               0,00     0,00 1773,50   28,50 30960,00   176,75    34,56     0,55    0,31    0,30    0,63   0,30  53,90

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    0,00   30,00     0,00   202,00    13,47     0,59   19,52    0,00   19,52  19,52  58,55
sdb               0,00     0,00 2566,50   35,50 45616,00   234,50    35,24     0,76    0,29    0,29    0,62   0,28  73,55

mysql>

E veja abaixo a diferença do impacto de I/O ao utilizar a gravação assíncrona.

mysql> set global sync_binlog=0;
Query OK, 0 rows affected (0,00 sec)

mysql> \! iostat -xd 2 10
Linux 3.10.0-514.2.2.el7.x86_64 (Baltazar.localdomain)  09-01-2017      _x86_64_        (4 CPU)

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               2,02     1,99   10,72   39,61  1333,79  1238,11   102,21     2,38   47,35    7,61   58,10  14,04  70,67
sdb               0,58     0,05  127,66   42,79  4475,58   396,95    57,17     0,51    3,00    1,04    8,84   0,42   7,22

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    1,50    0,00     6,00     0,00     8,00     0,02   13,67   13,67    0,00  13,67   2,05
sdb               0,00     0,00 1591,50   19,50 33720,00   139,00    42,03     0,63    0,40    0,37    2,74   0,36  57,70

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    0,00    0,00     0,00     0,00     0,00     0,00    0,00    0,00    0,00   0,00   0,00
sdb               0,00     0,00 1520,00   22,00 34178,00   186,25    44,57     0,59    0,38    0,38    0,80   0,36  55,25

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    2,00    8,50    68,00    38,00    20,19     0,09    8,71    8,50    8,76   8,71   9,15
sdb               0,00     0,00 1733,50   21,00 38480,00   136,50    44,02     0,66    0,38    0,38    0,81   0,36  62,60

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    0,00    0,50     0,00   140,00   560,00     0,00    1,00    0,00    1,00   1,00   0,05
sdb               0,00     0,00 1583,00   20,00 36592,00   121,50    45,81     0,61    0,39    0,38    0,85   0,36  58,05

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    1,00    2,00    16,00   392,00   272,00     0,00    1,17    1,00    1,25   0,67   0,20
sdb               0,00     0,00 1693,50   23,50 28812,00   187,00    33,78     0,58    0,34    0,34    0,64   0,33  56,15

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    0,00    0,00     0,00     0,00     0,00     0,00    0,00    0,00    0,00   0,00   0,00
sdb               0,00     0,00 1808,00   26,50 30418,00   203,50    33,38     0,67    0,37    0,35    1,89   0,34  62,70

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00   12,50    0,50   234,00     2,00    36,31     0,25   18,92   19,68    0,00   5,27   6,85
sdb               0,00     0,00 1853,00   17,00 38906,00   134,75    41,75     0,66    0,35    0,35    0,88   0,34  63,00

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    0,00    1,00     0,00    14,00    28,00     0,02   16,00    0,00   16,00  16,00   1,60
sdb               0,00     0,00 1525,50   20,00 45664,00   110,00    59,24     0,70    0,44    0,43    0,88   0,39  60,75

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    0,00    0,00     0,00     0,00     0,00     0,00    0,00    0,00    0,00   0,00   0,00
sdb               0,00     0,00 1565,00   17,00 46376,00   118,50    58,78     0,71    0,47    0,46    0,85   0,39  61,85

mysql>

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.