MySQL 8.0 - InnoDB Dynamic Redo Logs (v2)

Frédéric Descamps
Community Manager
Oracle MySQL
Percona Live - May 2023
MySQL 8.0 : InnoDB
Dynamic Redo Logs
Online Redo Log Capacity

View Slide

Who am I ?
about.me/lefred
Copyright @ 2023 Oracle and/or its affiliates.
2

View Slide

@lefred
MySQL Evangelist
using MySQL since version 3.20
devops believer
living in
h ps://lefred.be
Frédéric Descamps
3

View Slide

InnoDB Redo Logs
What is it ?
4

View Slide

InnoDB Redo Logs
During data modi cation, InnoDB caches the changes in memory (inside InnoDB Bu er
Pool) to achieve be er read and write performance.
The modi cations are also writen to disk in a sequential way (remember the old disks?) on
speci c les called Redo Logs (you can also encounter the name Transaction Logs).
Those logs are used only in case of a crash and InnoDB needs to perform a recovery of all
transactions that have been commi ed.
This process guarantees the durability, the D in ACID.
5

View Slide

InnoDB Checkpointing - Part I
Flushing to Tablespaces
6

View Slide

InnoDB Checkpointing
However at some point, InnoDB will also write the changed pages to disk in the tablespaces
(data les). The process of writing the dirty pages (pages that have been modi ed) to the
tablespaces is known as ushing or checkpointing.
The checkpoint represent the LSN value of the latest changes wri en to the data les.
InnoDB ushes small batches of those dirty pages from the bu er pool, this is why it's
called fuzzy checkpointing.
MySQL does not ush them all at once to avoid heavy process that could disrupt the
normal usage of MySQL.
7

View Slide

InnoDB Checkpointing
However at some point, InnoDB will also write the changed pages to disk in the tablespaces
(data les). The process of writing the dirty pages (pages that have been modi ed) to the
tablespaces is known as ushing or checkpointing.
The checkpoint represent the LSN value of the latest changes wri en to the data les.
InnoDB ushes small batches of those dirty pages from the bu er pool, this is why it's
called fuzzy checkpointing.
MySQL does not ush them all at once to avoid heavy process that could disrupt the
normal usage of MySQL.
A log sequence number (LSN) represents the o set, in bytes, of a log record from the
beginning of a database log le.
7

View Slide

Old Days
Before MySQL 8.0.30
8

View Slide

InnoDB Redo Logs before MySQL 8.0.30
Before MySQL 8.0.30, the InnoDB Redo Logs were con gured using these variables:
innodb_log_ le_size: the size of the les, the default was 48MB and the maximum
could not be bigger than 512GB / innodb_log_ les_in_group
innodb_log_ les_in_group: the number of log les, default and minimum of 2 with a
maximum of 100.
Those variables were not dynamic and required a restart of MySQL Server to modify them.
9

View Slide

InnoDB Redo Logs before MySQL 8.0.30
Before MySQL 8.0.30, the InnoDB Redo Logs were con gured using these variables:
innodb_log_ le_size: the size of the les, the default was 48MB and the maximum
could not be bigger than 512GB / innodb_log_ les_in_group
innodb_log_ les_in_group: the number of log les, default and minimum of 2 with a
maximum of 100.
Those variables were not dynamic and required a restart of MySQL Server to modify them.
[
[fred@dell ~/sandboxes/msb_8_0_28/data
fred@dell ~/sandboxes/msb_8_0_28/data]
] $
$ ls
ls -lh
-lh ib_log*
ib_log*
-rw-r-----.
-rw-r-----. 1
1 fred fred 48M Jun
fred fred 48M Jun 17
17 2022
2022 ib_log le0
ib_log le0
-rw-r-----.
-rw-r-----. 1
1 fred fred 48M Jun
fred fred 48M Jun 17
17 2022
2022 ib_log le1
ib_log le1
9

View Slide

InnoDB Redo Logs before MySQL 8.0.30 (2)
10

View Slide

New Redo Log Architecture
Since MySQL 8.0.30
11

View Slide

New InnoDB Redo Log Architecture
Since MySQL 8.0.30, we don't talk about Redo Log Size anymore, but we talk about
capacity !
The capacity is de ned in a unique variable: innodb_redo_log_capacity (in bytes).
The default is 100MB.
The variable is dynamic, it can be changed at runtime, to set it to 200MB:
SQL
SQL >
> set
set global
global innodb_redo_log_capacity
innodb_redo_log_capacity=
=200
200*
*1024
1024*
*1024
1024;
;
12

View Slide

13

View Slide

14

View Slide

InnoDB Redo Log Home Dir
InnoDB will create 32 redo log les in MySQL's datadir inside the new dedicated folder
#innodb_redo by default.
You can also specify another destination by mo ying (not dynamic) the variable
innodb_log_group_home_dir.
Inside that directory, you will be able to nd two types of les:
#ib_redoXXXX (where XXXX is the le_id, a sequence number): those are the active redo
log les
#ib_redoXXXX_tmp: those are spare redo log les
15

View Slide

InnoDB Redo Log Home Dir (2)
InnoDB tries to maintain about 32 les here, so you don't have to wait long before one of
them becomes useless, as would be the case if you only had two large les.
This way it can reclaim them one by one when you want to resize them.
16

View Slide

InnoDB Redo Log Capacity
The InnoDB Redo Log Capacity can be represented like this:
checkpoint_lsn (Innodb_redo_log_checkpoint_lsn): a LSN point up to which all changes
to the pages are guaranteed to have already been wri en and fsynced back to the
tablespace les - basically, the sill needed portion of redo log starts here.
ushed_to_disk_lsn (Innodb_redo_log_ ushed_to_disk_lsn): the last position in the redo
log that InnoDB has been ushed to disk.
17

View Slide

InnoDB Redo Log Capacity (2)
The InnoDB Redo Log Capacity can be represented like this:
current_lsn (Innodb_redo_log_current_lsn): the last wri en position in the redo log. That
write could still be bu ered inside MySQL processes bu er.
18

View Slide

When InnoDB reaches the end of the 31st le (90%), the log les governor will perform
some cleanup and some active les that are not needed anymore will become the new
spare ones:
When the background thread is not able to remove a log le from the left to put it to the
right, the user transaction will get stuck waiting for REDO bu ers to be wri en to disk.
DBAs get warning in the error log notifying them to increase the InnoDB Redo Log
Capacity.
19

View Slide

When InnoDB reaches the end of the 31st le (90%), the log les governor will perform
some cleanup and some active les that are not needed anymore will become the new
spare ones:
When the background thread is not able to remove a log le from the left to put it to the
right, the user transaction will get stuck waiting for REDO bu ers to be wri en to disk.
DBAs get warning in the error log notifying them to increase the InnoDB Redo Log
Capacity.
[Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log le.
Consider increasing innodb_redo_log_capacity.
19

View Slide

InnoDB Checkpointing - part II
Details
20

View Slide

InnoDB Checkpointing - recap
So we know that each time data is changed in InnoDB, the page(s) containing the data is
modi ed in memory (in the InnoDB Bu er Pool). The page(s) is (are) noted as dirty.
In case of a sudden crash, we cannot loose all those changes… but the data in memory is
gone !
For this reason, the di erential page data is also wri en (and by default ushed to disk) to
the redo logs. The data in these logs will only be read in case of an InnoDB Recovery.
During that process the modi ed pages will be reconstructed with the modi ed data.
21

View Slide

InnoDB Fuzzy Checkpointing
InnoDB ushes those dirty pages from the Bu er Pool (memory) to the table spaces (disk)
in small batches, step by step. This operation is called Fuzzy Checkpointing.
Once the pages are wri en to the data les on disk (InnoDB tablespaces), the
corresponding entries in the Redo Log are not required anymore.
The position up to which InnoDB has wri en the data to the disk (to the tablespace le) is
represented by the value of Innodb_redo_log_checkpoint_lsn.
InnoDB Checkpointing is adaptive. This means that considering the checkpoint age
(log_lsn_checkpoint_age) InnoDB will decide to ush less or more aggressively.
22

View Slide

InnoDB Fuzzy Checkpointing (2)
For info, log_lsn_checkpoint_age and inndob_redo_log_logical_size are almost
equivalent:
23

View Slide

LSN Checkpoint Age and Redo Log Capacity
MySQL performs this adaptive ushing considering these thresholds:
soft limit for logical capacity: to avoid deadlocks InnoDB doesn't let the user transactions
to use up the whole innodb_redo_log_capacity. Instead it keeps them below soft logical
capacity which is roughly 30/32 of it. When this limitation is exceeded, all user threads
are paused and a message is sent to the error_log.
hard limit for logical capacity: this limitation is never exceeded. If space isn't reclaimed
after 1 second wait when the limit is reached, logs are wri en as much as possible or
crash InnoDB !
24

View Slide

LSN Checkpoint Age and Redo Log Capacity (2)
async ush point (log_max_modi ed_age_async): writes are allowed but page ushing will
be gradually increased to reach the next threshold. This will lead to a drop of
performance. In the code, async ush point can be called adaptive_ ush_min_age. This
is 7/8 of the soft logical capacity. However, in practice, it seems that the adaptive
ushing already starts at innodb_adaptive_ ushing_lwm (by default 10% of soft logical
capacity), and reaches maximum allowed IO capacity already at 82% of the async ush
point.
sync ush point (log_max_modi ed_age_sync): at this point the checkpointer will request
page cleaners to ush as much of dirty pages to get the checkpoint age below this
threshold and will wait for it synchronously. Terrible performance. This is also called
adaptive_ ush_max_age. This is 15/16 of the soft logical capacity.
25

View Slide

aggressive_checkpoint_min_age: this represents 31/32 of soft logical capacity. When this
point is reached, MySQL already asked to InnoDB to ush dirty pages from the Bu er
Pool at full speed.
The checkpointer will not sleep for 1 second between a empting updating checkpoint lsn.
Instead it will request a sync checkpoint as often as possible and will also update
checkpoint_lsn value to the redo log header as soon as possible afterwards.
This is performed to be able to reclaim the space faster. As we are already at the top speed,
this doesn't add any more pressure to the page cleaners.
26

View Slide

27

View Slide

28

View Slide

29

View Slide

30

View Slide

31

View Slide

InnoDB Redo Log
Instrumentation
32

View Slide

Instrumentation - Performance_Schema
The new Redo Log is instrumented in Performance_Schema in the table
innodb_redo_log_ les:
This means there are 5 active redo log les and 27 (32-5) spare ones (_tmp)
Each active redo log le is associated with a particular range of LSN values.
33

View Slide

Instrumentation - Performance_Schema (2)
All the les are also instrumented in Performance_Schema's le instance tables
( le_instances and le_summary_by_instance):
34

View Slide

Instrumentation - Status
There are status variables providing information about the " ushpointing" operations:
35

View Slide

Instrumentation - InnoDB Metrics
Information is also available in InnoDB Metrics:
36

View Slide

When the appropriate InnoDB Metrics are enabled, it's also possible to get an overview of
the Redo Log's usage and see where we are in relation to the soft and hard redo log logical
capacity:
select
select concat
concat(
(variable_value
variable_value,
, " ("
" (",
,
format_bytes
format_bytes(
(variable_value
variable_value)
),
,")"
")")
) innodb_redo_log_logical_size
innodb_redo_log_logical_size,
,
concat
concat(
(round
round(
(count
count*
*8
8/
/7
7)
),
, " ("
" (",
,
format_bytes
format_bytes(
(round
round(
(count
count*
*8
8/
/7
7)
))
),
, ")"
")")
) soft_logical_capacity
soft_logical_capacity,
,
concat
concat(
(round
round(
(@
@@innodb_redo_log_capacity
@innodb_redo_log_capacity*
*29.8
29.8/
/32
32)
),
, " ("
" (",
,
format_bytes
format_bytes(
(round
round(
(@
*29.8
29.8/
/32
32)
))
) ,
,")"
")")
) hard_logical_capacity
hard_logical_capacity,
,
concat
concat(
(@
@innodb_redo_log_capacity,
, " ("
" (",
,
format_bytes
format_bytes(
(@
@innodb_redo_log_capacity)
) ,
,")"
")")
) redo_log_capacity
redo_log_capacity,
,
concat
concat(
(round
round(
(variable_value
variable_value /
/ (
(count
count*
*8
8/
/7
7)
)*
*100
100,
,2
2)
),
, "%"
"%")
) logical_used
logical_used,
,
concat
concat(
(round
round(
(variable_value
variable_value /
/ (
(@
*29.8
29.8/
/32
32)
)*
*100
100,
,2
2)
),
, "%"
"%")
) hard_used
hard_used
from
from performance_schema
performance_schema.
.global_status
global_status
join
join information_schema
information_schema.
.innodb_metrics
innodb_metrics
where
where variable_name
variable_name like
like 'innodb_redo_log_logical_size'
'innodb_redo_log_logical_size'
and
and name
name like
like 'log_max_modi ed_age_async'
'log_max_modi ed_age_async';
;
37

View Slide

When the appropriate InnoDB Metrics are enabled, it's also possible to get an overview of
the Redo Log's usage and see where we are in relation to the soft and hard redo log logical
capacity:
select
select concat
concat(
(variable_value
variable_value,
, " ("
" (",
,
format_bytes
format_bytes(
(variable_value
variable_value)
),
,")"
")")
) innodb_redo_log_logical_size
innodb_redo_log_logical_size,
,
concat
concat(
(round
round(
(count
count*
*8
8/
/7
7)
),
, " ("
" (",
,
format_bytes
format_bytes(
(round
round(
(count
count*
*8
8/
/7
7)
))
),
, ")"
")")
) soft_logical_capacity
soft_logical_capacity,
,
concat
concat(
(round
round(
(@
*29.8
29.8/
/32
32)
),
, " ("
" (",
,
format_bytes
format_bytes(
(round
round(
(@
*29.8
29.8/
/32
32)
))
) ,
,")"
")")
) hard_logical_capacity
hard_logical_capacity,
,
concat
concat(
(@
@innodb_redo_log_capacity,
, " ("
" (",
,
format_bytes
format_bytes(
(@
@innodb_redo_log_capacity)
) ,
,")"
")")
) redo_log_capacity
redo_log_capacity,
,
concat
concat(
(round
round(
(variable_value
variable_value /
/ (
(count
count*
*8
8/
/7
7)
)*
*100
100,
,2
2)
),
, "%"
"%")
) logical_used
logical_used,
,
concat
concat(
(round
round(
(variable_value
variable_value /
/ (
(@
*29.8
29.8/
/32
32)
)*
*100
100,
,2
2)
),
, "%"
"%")
) hard_used
hard_used
from
performance_schema.
.global_status
global_status
join
join information_schema
information_schema.
.innodb_metrics
innodb_metrics
where
where variable_name
variable_name like
like 'innodb_redo_log_logical_size'
'innodb_redo_log_logical_size'
and
and name
name like
like 'log_max_modi ed_age_async'
'log_max_modi ed_age_async';
;
+
+------------------------------+-----------------------+-----------------------+------------------------+--------------+-----------+
------------------------------+-----------------------+-----------------------+------------------------+--------------+-----------+
|
| innodb_redo_log_logical_size
innodb_redo_log_logical_size |
| soft_logical_capacity
soft_logical_capacity |
| hard_logical_capacity
hard_logical_capacity |
| redo_log_capacity
redo_log_capacity |
| logical_used
logical_used |
| hard_used
hard_used |
|
+
+------------------------------+-----------------------+-----------------------+------------------------+--------------+-----------+
------------------------------+-----------------------+-----------------------+------------------------+--------------+-----------+
|
| 13108224
13108224 (
(12.50
12.50 MiB
MiB)
) |
| 92892599
92892599 (
(88.59
88.59 MiB
MiB)
) |
| 97648640
97648640 (
(93.12
93.12 MiB
MiB)
) |
| 104857600
104857600 (
(100.00
100.00 MiB
MiB)
) |
| 14.11
14.11%
% |
| 13.42
13.42%
% |
|
+
+------------------------------+-----------------------+-----------------------+------------------------+--------------+-----------+
------------------------------+-----------------------+-----------------------+------------------------+--------------+-----------+
1
1 row
row in
in set
set (
(0.0008
0.0008 sec
sec)
)
37

View Slide

Enable InnoDB Metrics for lsn checkpoint age
SELECT
SELECT status
status FROM
FROM INFORMATION_SCHEMA
INFORMATION_SCHEMA.
.INNODB_METRICS
INNODB_METRICS WHERE
WHERE NAME
NAME like
like 'log_lsn_checkpoint_age'
'log_lsn_checkpoint_age';
;
+
+----------+
----------+
|
| status
status |
|
+
+----------+
----------+
|
| disabled
disabled |
|
+
+----------+
----------+
SET
SET GLOBAL
GLOBAL innodb_monitor_enable
innodb_monitor_enable=
= 'log_lsn_%'
'log_lsn_%';
;
SELECT
SELECT name
name,
, status
status FROM
FROM INFORMATION_SCHEMA
INFORMATION_SCHEMA.
.INNODB_METRICS
INNODB_METRICS
WHERE
WHERE NAME
NAME like
like 'log_lsn%'
'log_lsn%' and
and NAME
NAME not
not like
like 'log_lsn_buf%'
'log_lsn_buf%';
;
+
+--------------------------------+---------+
--------------------------------+---------+
|
| name
name |
| status
status |
|
+
+--------------------------------+---------+
--------------------------------+---------+
|
| log_lsn_last_ ush
log_lsn_last_ ush |
| enabled
enabled |
|
|
| log_lsn_last_checkpoint
log_lsn_last_checkpoint |
| enabled
enabled |
|
|
| log_lsn_current
log_lsn_current |
| enabled
enabled |
|
|
| log_lsn_archived
log_lsn_archived |
| enabled
enabled |
|
|
| log_lsn_checkpoint_age
log_lsn_checkpoint_age |
| enabled
enabled |
|
+
+--------------------------------+---------+
--------------------------------+---------+
38

View Slide

Recommendations
Not too small, not too big
39

View Slide

Recommendations
It's not recommended to oversize the Redo Log Capacity.
Redo Log les consume disk space and increases the recovery time in case of a restart
(innodb_fast_shutdown=1) or a sudden crash.
And it also slows down restart when innodb_fast_shutdown=0.
40

View Slide

Recommendations (2)
During peak tra c time, you can get an estimation of the required amount for the Redo
Log Capacity by running the query below (all in one single line):
select
select VARIABLE_VALUE
VARIABLE_VALUE from
performance_schema.
.global_status
global_status
where
where VARIABLE_NAME
VARIABLE_NAME=
='Innodb_redo_log_current_lsn'
'Innodb_redo_log_current_lsn' into
into @a
@a;
;select
select sleep
sleep(
(60
60)
)
into
into @garb
@garb ;
;select
VARIABLE_VALUE from
performance_schema.
.global_status
global_status
where
where VARIABLE_NAME
VARIABLE_NAME=
into @b
@b;
;select
select
format_bytes
format_bytes(
(abs
abs(
(@a
@a -
- @b
@b)
))
) per_min
per_min,
, format_bytes
format_bytes(
(abs
abs(
(@a
@a -
- @b
@b)
)*
*60
60)
) per_hour
per_hour;
;
41

View Slide

Recommendations (2)
During peak tra c time, you can get an estimation of the required amount for the Redo
Log Capacity by running the query below (all in one single line):
select
VARIABLE_VALUE from
performance_schema.
.global_status
global_status
where
where VARIABLE_NAME
VARIABLE_NAME=
into @a
@a;
;select
select sleep
sleep(
(60
60)
)
into
into @garb
@garb ;
;select
VARIABLE_VALUE from
performance_schema.
.global_status
global_status
where
where VARIABLE_NAME
VARIABLE_NAME=
into @b
@b;
;select
select
format_bytes
format_bytes(
(abs
abs(
(@a
@a -
- @b
@b)
))
) per_min
per_min,
, format_bytes
format_bytes(
(abs
abs(
(@a
@a -
- @b
@b)
)*
*60
60)
) per_hour
per_hour;
;
+
+-----------+----------+
-----------+----------+
|
| per_min
per_min |
| per_hour
per_hour |
|
+
+-----------+----------+
-----------+----------+
|
| 21.18
21.18 MiB
MiB |
| 1.24
1.24 GiB
GiB |
|
+
+-----------+----------+
-----------+----------+
41

View Slide

InnoDB Redo Log
Consumers
42

View Slide

Redo Log Consumers
We all know that:
InnoDB Redo Logs are only read (consumed) in case of InnoDB Recovery.
43

View Slide

Redo Log Consumers
We all know that:
This is not true !
43

View Slide

Redo Log Consumers
We all know that:
This is not true !
There are other processes able to consume InnoDB redo log les:
threads registering to the redo log consumer service like MEB to create incrementals
backup using only the redo logs. See h ps://dev.mysql.com/doc/mysql-enterprise-
backup/8.0/en/mysqlbackup.incremental.html#meb-creating-incremental-redo-log-
only
MySQL clone's archiver
43

View Slide

Redo Log Consumers (2)
Those processes can slow down the re-use of redo log les (converting them back to spare
redo log les).
These consumers must provide InnoDB with their progress by sending their current LSN.
1. InnoDB Recovery
For InnoDB Recovery, the "checkpointer thread" is consuming the redo logs and share the
current checkpoint lsn.
44

View Slide

2. Threads registering to the redo log consumer service
To register to the redo log service, such thread needs to use the UDF
innodb_redo_log_consumer_register().
As this can cause considerable drop in performance, the BACKUP_ADMIN privilege is required.
To send its progress, such thread needs to use the UDF
innodb_redo_log_consumer_advance(lsn).
To unregister, the thread must be ended (stop the session).
45

View Slide

3. MySQL clone
When a MySQL clone operation is in progress, clone register and share its progress using
an internal API.
This is completely transparent and automated.
46

View Slide

InnoDB Redo Log
Trending Example
47

View Slide

InnoDB Redo Log - Trending
Let's take a look at this graph:
48

View Slide

This graph shows the di erence between the current_lsn and the
lsn_ ushed_to_disk.
If the di erence stays always high then it could mean that the sync
operation to disk is too slow.
However we can see something strange on the graph (pointed with the
red arrow).
The di erence stayed the same for a long time.
This means there was data on the MySQL processes bu ers but it could
not be sent to the redo log les on disk.
49

View Slide

InnoDB Redo Log - Trending (2)
Let's take a look at this second graph:
50

View Slide

This graph shows the amount of active Redo Log les.
We can see that twice we reached 32 active InnoDB Redo Log les.
This means no spare Redo Log les at all.
This should not be possible… unless we have something else also
consuming the Redo Log.
In fact in another session, there was a registered consumer and it did
not advance the lsn.
51

View Slide

We can see that for the same period there was no new lsn.
52

View Slide

Nothing could be wri en.
InnoDB was of course not able to ush any new page as checkpointing
operations (ib_pg_ ush_co & ib_log_checkpt) were blocked waiting for
redo log fsync.
And in MySQL's processlist we can see plenty of insert queries in waiting
for handler commit state.
Pay a ention that if you stay in that state for 600 seconds, InnoDB will
intentionally crash !
53

View Slide

InnoDB Redo Log
Archiving
54

View Slide

Redo Log Archiver
MySQL Enterprise Backup (MEB) can also use Redo Log Archiving to be able to not loose
any redo logs if the backup process it too long.
See h ps://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/meb-redo-log-
archiving.html.
To enable this feature, two steps are necessary:
set globally a directory where those archiving logs can be stored
start the archiving process in a session by calling a dedicated function (automated with
MEB)
55

View Slide

Redo Log Archiver (2)
The global variable used to enable Redo Log Archiving is innodb_redo_log_archive_dirs.
This variables contains labelled directories where the archived redo logs can be stored:
innodb_redo_log_archive_dirs
innodb_redo_log_archive_dirs=
='label1:/backups1;label2:/backups2'
'label1:/backups1;label2:/backups2'
The redo log archiving is started using the function innodb_redo_log_archive_start() and
stopped using innodb_redo_log_archive_stop().
Only users with the INNODB_REDO_LOG_ARCHIVE privilege can call those functions.
56

View Slide

Redo Log Archiver (3) - Example
We need to prepare the lesystem:
$
$ sudo
sudo mkdir
mkdir -p
-p /var/lib/mysql-redo-archive/backup1
/var/lib/mysql-redo-archive/backup1
$
$ sudo
sudo chown
chown mysql.
mysql. -R
-R /var/lib/mysql-redo-archive
/var/lib/mysql-redo-archive
$
$ sudo
sudo chmod
chmod -R
-R 700
700 /var/lib/mysql-redo-archive/
/var/lib/mysql-redo-archive/
57

View Slide

And prepare MySQL:
show
show global
global variables
variables like
like 'innodb_redo_log_ar%'
'innodb_redo_log_ar%'
+
+------------------------------+-------+
------------------------------+-------+
|
| Variable_name
Variable_name |
| Value
Value |
|
+
+------------------------------+-------+
------------------------------+-------+
|
| innodb_redo_log_archive_dirs
innodb_redo_log_archive_dirs |
| |
|
+
+------------------------------+-------+
------------------------------+-------+
set
set persist innodb_redo_log_archive_dirs
persist innodb_redo_log_archive_dirs=
='label1:/var/lib/mysql-redo-archive/'
'label1:/var/lib/mysql-redo-archive/';
;
show
show global
global variables
variables like
like 'innodb_redo_log_ar%'
'innodb_redo_log_ar%' ;
;
+
+------------------------------+-------------------------------------+
------------------------------+-------------------------------------+
|
| Variable_name
Variable_name |
| Value
Value |
|
+
+------------------------------+-------------------------------------+
------------------------------+-------------------------------------+
|
| innodb_redo_log_archive_dirs
innodb_redo_log_archive_dirs |
| label1:
label1:/
/var
var/
/lib
lib/
/mysql
mysql-
-redo
redo-
-archive
archive/
/ |
|
+
+------------------------------+-------------------------------------+
------------------------------+-------------------------------------+
58

View Slide

We can now start the process:
select
select innodb_redo_log_archive_start
innodb_redo_log_archive_start(
('label1'
'label1',
,'backup1'
'backup1')
);
;
+
+---------------------------------------------------+
---------------------------------------------------+
|
| innodb_redo_log_archive_start
('label1'
'label1',
,'backup1'
'backup1')
) |
|
+
+---------------------------------------------------+
---------------------------------------------------+
|
| 0
0 |
|
+
+---------------------------------------------------+
---------------------------------------------------+
59

View Slide

We can now start the process:
select
select innodb_redo_log_archive_start
('label1'
'label1',
,'backup1'
'backup1')
);
;
+
+---------------------------------------------------+
---------------------------------------------------+
|
('label1'
'label1',
,'backup1'
'backup1')
) |
|
+
+---------------------------------------------------+
---------------------------------------------------+
|
| 0
0 |
|
+
+---------------------------------------------------+
---------------------------------------------------+
On the lesystem we can see:
$
$ sudo
sudo ls
ls -lh
-lh /var/lib/mysql-redo-archive/backup1
/var/lib/mysql-redo-archive/backup1
total
total 1
1.2G
.2G
-r--r-----.
-r--r-----. 1
1 mysql mysql
mysql mysql 1
1.2G May
.2G May 8
8 17
17:38 archive.17f6a975-e2b4-11ec-b714-c8cb9e32df8e.000001.log
:38 archive.17f6a975-e2b4-11ec-b714-c8cb9e32df8e.000001.log
59

View Slide

Redo Log Archiver (6)
We have the possibility to check if redo log archiving is running:
select
select *
* from
performance_schema.
. le_instances
le_instances
where
where event_name
event_name like
like '%::redo_log_archive_ le'
'%::redo_log_archive_ le'\G
\G
*
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
* 1.
1. row
row *
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
*
FILE_NAME:
FILE_NAME: /
/var
var/
/lib
lib/
/mysql
mysql-
-redo
redo-
-archive
archive/
/backup1
backup1/
/archive
archive.
.17
17f6a975
f6a975-
-e2b4
e2b4-
-11
11ec
ec-
-b714
b714-
-c8cb9e32df8e
c8cb9e32df8e.
.000001.
000001.log
log
EVENT_NAME: wait
EVENT_NAME: wait/
/io
io/
/ le
le/
/innodb
innodb/
/meb::redo_log_archive_ le
meb::redo_log_archive_ le
OPEN_COUNT:
OPEN_COUNT: 1
1
select
select thread_id
thread_id,
, name
name,
, type
type from
performance_schema.
.threads
threads where
where name
name like
like '%redo_log_archive%'
'%redo_log_archive%';
;
+
+-----------+-----------------------------------------------------+------------+
-----------+-----------------------------------------------------+------------+
|
| thread_id
thread_id |
| name
name |
| type
type |
|
+
+-----------+-----------------------------------------------------+------------+
-----------+-----------------------------------------------------+------------+
|
| 70
70 |
| thread
thread/
/innodb
innodb/
/meb::redo_log_archive_consumer_thread
meb::redo_log_archive_consumer_thread |
| BACKGROUND
BACKGROUND |
|
+
+-----------+-----------------------------------------------------+------------+
-----------+-----------------------------------------------------+------------+
60

View Slide

InnoDB Redo Log
Functions
61

View Slide

Redo Log Functions
These are the UDF functions releated to MySQL InnoDB Redo Logs (as in 8.0.33)
:
select
select udf_name
udf_name from
performance_schema.
.user_de ned_functions
user_de ned_functions where
where udf_name
udf_name like
like 'innodb_redo%'
'innodb_redo%';
;
+
+-------------------------------------+
-------------------------------------+
|
| udf_name
udf_name |
|
+
+-------------------------------------+
-------------------------------------+
|
| innodb_redo_log_sharp_checkpoint
innodb_redo_log_sharp_checkpoint |
|
|
| innodb_redo_log_consumer_unregister
innodb_redo_log_consumer_unregister |
|
|
| innodb_redo_log_consumer_register
innodb_redo_log_consumer_register |
|
|
| innodb_redo_log_archive_ ush
innodb_redo_log_archive_ ush |
|
|
| innodb_redo_log_archive_stop
innodb_redo_log_archive_stop |
|
|
innodb_redo_log_archive_start |
|
|
| innodb_redo_log_consumer_advance
innodb_redo_log_consumer_advance |
|
+
+-------------------------------------+
-------------------------------------+
7
7 rows
rows in
in set
set (
(0.0003
0.0003 sec
sec)
)
62

View Slide

Redo Log Functions (2)
And in MySQL 8.0 it is also possible to disable completely the InnoDB Redo Logs:
ALTER
ALTER INSTANCE
INSTANCE [
[ENABLE
ENABLE|
|DISABLE
DISABLE]
] INNODB
INNODB REDO_LOG
REDO_LOG;
;
This is unsafe (no durability) but e cient for initial data load.
63

View Slide

Share your
❤ to MySQL
#mysql
Join our slack channel!
bit.ly/mysql-slack
64

View Slide

MySQL 8.0 DBA Certi cation
65

View Slide

MySQL 8.0 Developer Certi cation
66

View Slide

Questions ?
67

View Slide

MySQL 8.0 - InnoDB Dynamic Redo Logs (v2)

MySQL 8.0 - InnoDB Dynamic Redo Logs (v2)

lefred

More Decks by lefred

Featured

Transcript