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

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

@lefred MySQL Evangelist using MySQL since version 3.20 devops believer living in h ps://lefred.be Frédéric Descamps Copyright @ 2023 Oracle and/or its affiliates. 3

InnoDB Redo Logs What is it ? Copyright @ 2023 Oracle and/or its affiliates. 4

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. Copyright @ 2023 Oracle and/or its affiliates. 5

InnoDB Checkpointing - Part I Flushing to Tablespaces Copyright @ 2023 Oracle and/or its affiliates. 6

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. Copyright @ 2023 Oracle and/or its affiliates. 7

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. Copyright @ 2023 Oracle and/or its affiliates. A log sequence number (LSN) represents the o set, in bytes, of a log record from the beginning of a database log le. 7

Old Days Before MySQL 8.0.30 Copyright @ 2023 Oracle and/or its affiliates. 8

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. Copyright @ 2023 Oracle and/or its affiliates. 9

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 Copyright @ 2023 Oracle and/or its affiliates. 9

InnoDB Redo Logs before MySQL 8.0.30 (2) Copyright @ 2023 Oracle and/or its affiliates. 10

New Redo Log Architecture Since MySQL 8.0.30 Copyright @ 2023 Oracle and/or its affiliates. 11

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; ; Copyright @ 2023 Oracle and/or its affiliates. 12

New InnoDB Redo Log Architecture Copyright @ 2023 Oracle and/or its affiliates. 13

New InnoDB Redo Log Architecture Copyright @ 2023 Oracle and/or its affiliates. 14

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 Copyright @ 2023 Oracle and/or its affiliates. 15

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. Copyright @ 2023 Oracle and/or its affiliates. 16

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. Copyright @ 2023 Oracle and/or its affiliates. 17

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. Copyright @ 2023 Oracle and/or its affiliates. 18

InnoDB Redo Log Capacity (3) 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. Copyright @ 2023 Oracle and/or its affiliates. 19

InnoDB Redo Log Capacity (3) 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. Copyright @ 2023 Oracle and/or its affiliates. [Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log le. Consider increasing innodb_redo_log_capacity. 19

InnoDB Checkpointing - part II Details Copyright @ 2023 Oracle and/or its affiliates. 20

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. Copyright @ 2023 Oracle and/or its affiliates. 21

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. Copyright @ 2023 Oracle and/or its affiliates. 22

InnoDB Fuzzy Checkpointing (2) For info, log_lsn_checkpoint_age and inndob_redo_log_logical_size are almost equivalent: Copyright @ 2023 Oracle and/or its affiliates. 23

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 ! Copyright @ 2023 Oracle and/or its affiliates. 24

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. Copyright @ 2023 Oracle and/or its affiliates. 25

LSN Checkpoint Age and Redo Log Capacity (3) 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. Copyright @ 2023 Oracle and/or its affiliates. 26

LSN Checkpoint Age and Redo Log Capacity (4) Copyright @ 2023 Oracle and/or its affiliates. 27

LSN Checkpoint Age and Redo Log Capacity (4) Copyright @ 2023 Oracle and/or its affiliates. 28

LSN Checkpoint Age and Redo Log Capacity (4) Copyright @ 2023 Oracle and/or its affiliates. 29

LSN Checkpoint Age and Redo Log Capacity (4) Copyright @ 2023 Oracle and/or its affiliates. 30

LSN Checkpoint Age and Redo Log Capacity (4) Copyright @ 2023 Oracle and/or its affiliates. 31

InnoDB Redo Log Instrumentation Copyright @ 2023 Oracle and/or its affiliates. 32

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. Copyright @ 2023 Oracle and/or its affiliates. 33

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. Copyright @ 2023 Oracle and/or its affiliates. 33

Instrumentation - Performance_Schema (2) All the les are also instrumented in Performance_Schema's le instance tables ( le_instances and le_summary_by_instance): Copyright @ 2023 Oracle and/or its affiliates. 34

Instrumentation - Status There are status variables providing information about the " ushpointing" operations: Copyright @ 2023 Oracle and/or its affiliates. 35

Instrumentation - InnoDB Metrics Information is also available in InnoDB Metrics: Copyright @ 2023 Oracle and/or its affiliates. 36

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( (@ @@innodb_redo_log_capacity @innodb_redo_log_capacity* *29.8 29.8/ /32 32) )) ) , ,")" ")") ) hard_logical_capacity hard_logical_capacity, , concat concat( (@ @@innodb_redo_log_capacity @innodb_redo_log_capacity, , " (" " (", , format_bytes format_bytes( (@ @@innodb_redo_log_capacity @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 / / ( (@ @@innodb_redo_log_capacity @innodb_redo_log_capacity* *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'; ; Copyright @ 2023 Oracle and/or its affiliates. 37

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( (@ @@innodb_redo_log_capacity @innodb_redo_log_capacity* *29.8 29.8/ /32 32) )) ) , ,")" ")") ) hard_logical_capacity hard_logical_capacity, , concat concat( (@ @@innodb_redo_log_capacity @innodb_redo_log_capacity, , " (" " (", , format_bytes format_bytes( (@ @@innodb_redo_log_capacity @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 / / ( (@ @@innodb_redo_log_capacity @innodb_redo_log_capacity* *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'; ; Copyright @ 2023 Oracle and/or its affiliates. + +------------------------------+-----------------------+-----------------------+------------------------+--------------+-----------+ ------------------------------+-----------------------+-----------------------+------------------------+--------------+-----------+ | | 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

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 | | + +--------------------------------+---------+ --------------------------------+---------+ Copyright @ 2023 Oracle and/or its affiliates. 38

Recommendations Not too small, not too big Copyright @ 2023 Oracle and/or its affiliates. 39

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. Copyright @ 2023 Oracle and/or its affiliates. 40

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 from performance_schema 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 select VARIABLE_VALUE VARIABLE_VALUE from from performance_schema performance_schema. .global_status global_status where where VARIABLE_NAME VARIABLE_NAME= ='Innodb_redo_log_current_lsn' 'Innodb_redo_log_current_lsn' into 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; ; Copyright @ 2023 Oracle and/or its affiliates. 41

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 from performance_schema 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 select VARIABLE_VALUE VARIABLE_VALUE from from performance_schema performance_schema. .global_status global_status where where VARIABLE_NAME VARIABLE_NAME= ='Innodb_redo_log_current_lsn' 'Innodb_redo_log_current_lsn' into 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 | | + +-----------+----------+ -----------+----------+ Copyright @ 2023 Oracle and/or its affiliates. 41

InnoDB Redo Log Consumers Copyright @ 2023 Oracle and/or its affiliates. 42

Redo Log Consumers We all know that: InnoDB Redo Logs are only read (consumed) in case of InnoDB Recovery. Copyright @ 2023 Oracle and/or its affiliates. 43

Redo Log Consumers We all know that: InnoDB Redo Logs are only read (consumed) in case of InnoDB Recovery. This is not true ! Copyright @ 2023 Oracle and/or its affiliates. 43

Redo Log Consumers We all know that: InnoDB Redo Logs are only read (consumed) in case of InnoDB Recovery. 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 Copyright @ 2023 Oracle and/or its affiliates. 43

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. Copyright @ 2023 Oracle and/or its affiliates. 44

Redo Log Consumers (3) 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). Copyright @ 2023 Oracle and/or its affiliates. 45

Redo Log Consumers (3) 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. Copyright @ 2023 Oracle and/or its affiliates. 46

InnoDB Redo Log Trending Example Copyright @ 2023 Oracle and/or its affiliates. 47

InnoDB Redo Log - Trending Let's take a look at this graph: Copyright @ 2023 Oracle and/or its affiliates. 48

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. InnoDB Redo Log - Trending Copyright @ 2023 Oracle and/or its affiliates. 49

InnoDB Redo Log - Trending (2) Let's take a look at this second graph: Copyright @ 2023 Oracle and/or its affiliates. 50

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. InnoDB Redo Log - Trending Copyright @ 2023 Oracle and/or its affiliates. 51

We can see that for the same period there was no new lsn. InnoDB Redo Log - Trending Copyright @ 2023 Oracle and/or its affiliates. 52

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 ! InnoDB Redo Log - Trending Copyright @ 2023 Oracle and/or its affiliates. 53

InnoDB Redo Log Archiving Copyright @ 2023 Oracle and/or its affiliates. 54

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) Copyright @ 2023 Oracle and/or its affiliates. 55

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. Copyright @ 2023 Oracle and/or its affiliates. 56

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/ Copyright @ 2023 Oracle and/or its affiliates. 57

Redo Log Archiver (4) - Example 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/ / | | + +------------------------------+-------------------------------------+ ------------------------------+-------------------------------------+ Copyright @ 2023 Oracle and/or its affiliates. 58

Redo Log Archiver (5) - Example 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 innodb_redo_log_archive_start( ('label1' 'label1', ,'backup1' 'backup1') ) | | + +---------------------------------------------------+ ---------------------------------------------------+ | | 0 0 | | + +---------------------------------------------------+ ---------------------------------------------------+ Copyright @ 2023 Oracle and/or its affiliates. 59

Redo Log Archiver (5) - Example 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 innodb_redo_log_archive_start( ('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 Copyright @ 2023 Oracle and/or its affiliates. 59

Redo Log Archiver (6) We have the possibility to check if redo log archiving is running: select select * * from from performance_schema 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 from performance_schema 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 | | + +-----------+-----------------------------------------------------+------------+ -----------+-----------------------------------------------------+------------+ Copyright @ 2023 Oracle and/or its affiliates. 60

InnoDB Redo Log Functions Copyright @ 2023 Oracle and/or its affiliates. 61

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 from performance_schema 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_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) ) Copyright @ 2023 Oracle and/or its affiliates. 62

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. Copyright @ 2023 Oracle and/or its affiliates. 63

Share your ❤ to MySQL #mysql Join our slack channel! bit.ly/mysql-slack Copyright @ 2023 Oracle and/or its affiliates. 64

MySQL 8.0 DBA Certi cation Copyright @ 2023 Oracle and/or its affiliates. 65

MySQL 8.0 Developer Certi cation Copyright @ 2023 Oracle and/or its affiliates. 66

Questions ? Copyright @ 2023 Oracle and/or its affiliates. 67