Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQL 8.0 - InnoDB Dynamic Redo Logs (v2)

lefred
May 25, 2023
130

MySQL 8.0 - InnoDB Dynamic Redo Logs (v2)

Since MySQL 8.0.30, the InnoDB Redo Log capacity can be modified online.

Learn how to change the capacity of your redo logs but also how to monitor the checkpointing operations and subscribe to the new redo log consumer service.

lefred

May 25, 2023
Tweet

Transcript

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

    View full-size slide

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

    View full-size slide

  3. @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

    View full-size slide

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

    View full-size slide

  5. 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

    View full-size slide

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

    View full-size slide

  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.
    7

    View full-size slide

  8. 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

    View full-size slide

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

    View full-size slide

  10. 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

    View full-size slide

  11. 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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  14. 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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  17. 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

    View full-size slide

  18. 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

    View full-size slide

  19. 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

    View full-size slide

  20. 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

    View full-size slide

  21. 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

    View full-size slide

  22. 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

    View full-size slide

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

    View full-size slide

  24. 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

    View full-size slide

  25. 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

    View full-size slide

  26. 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

    View full-size slide

  27. 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

    View full-size slide

  28. 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

    View full-size slide

  29. 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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  36. 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

    View full-size slide

  37. 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

    View full-size slide

  38. 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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  41. 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

    View full-size slide

  42. 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

    View full-size slide

  43. 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

    View full-size slide

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

    View full-size slide

  45. 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

    View full-size slide

  46. 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

    View full-size slide

  47. 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

    View full-size slide

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

    View full-size slide

  49. 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

    View full-size slide

  50. 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

    View full-size slide

  51. 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

    View full-size slide

  52. 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

    View full-size slide

  53. 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

    View full-size slide

  54. 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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  57. 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

    View full-size slide

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

    View full-size slide

  59. 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

    View full-size slide

  60. 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

    View full-size slide

  61. 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

    View full-size slide

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

    View full-size slide

  63. 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

    View full-size slide

  64. 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

    View full-size slide

  65. 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

    View full-size slide

  66. 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

    View full-size slide

  67. 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

    View full-size slide

  68. 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

    View full-size slide

  69. 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

    View full-size slide

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

    View full-size slide

  71. 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

    View full-size slide

  72. 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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide