1

I'm using a 3-node group replication cluster of Percona MySQL (8.0.25) behind ProxySQL nodes (2.3.2). The transaction-isolation param is set to READ-COMMITTED.

While I'm running mysqldump (through ProxySQL if it matters somehow)

mysqldump --defaults-extra-file=/root/.my.cnf --add-drop-trigger --all-databases --events --lock-for-backup --max-allowed-packet=1G --no-autocommit --routines --single-transaction --skip-lock-tables -ubackup -h127.0.0.1 -pxxxxxxxxxxx --port=6033

InnoDB's History Length (trx_rseg_history_len) is growing fast. Usually, I have values near 1k, but while mysqldump, I observe 6kk+.

enter image description here

Following the recipe from here, I see the next transactions

*************************** 1. row ***************************
     processlist_id: 629626
               user: backup
        trx_started: 2022-10-17 12:33:02
trx_isolation_level: REPEATABLE READ
           EVENT_ID: 1718894
         TIMER_WAIT: 37621000
         EVENT_NAME: statement/sql/show_create_db
              QUERY: SHOW CREATE DATABASE IF NOT EXISTS `auth`
  RETURNED_SQLSTATE: NULL
        MYSQL_ERRNO: 0
       MESSAGE_TEXT: NULL
             ERRORS: 0
           WARNINGS: 0
*************************** 2. row ***************************
     processlist_id: 629626
               user: backup
        trx_started: 2022-10-17 12:33:02
trx_isolation_level: REPEATABLE READ
           EVENT_ID: 1718895
         TIMER_WAIT: 27901000
         EVENT_NAME: statement/sql/savepoint
              QUERY: SAVEPOINT sp
  RETURNED_SQLSTATE: 00000
        MYSQL_ERRNO: 0
       MESSAGE_TEXT: NULL
             ERRORS: 0
           WARNINGS: 0
*************************** 3. row ***************************
     processlist_id: 629626
               user: backup
        trx_started: 2022-10-17 12:33:02
trx_isolation_level: REPEATABLE READ
           EVENT_ID: 1718896
         TIMER_WAIT: 554664000
         EVENT_NAME: statement/sql/show_tables
              QUERY: show tables
  RETURNED_SQLSTATE: NULL
        MYSQL_ERRNO: 0
       MESSAGE_TEXT: NULL
             ERRORS: 0
           WARNINGS: 0
*************************** 4. row ***************************
     processlist_id: 629626
               user: backup
        trx_started: 2022-10-17 12:33:02
trx_isolation_level: REPEATABLE READ
           EVENT_ID: 1718897
         TIMER_WAIT: 581915000
         EVENT_NAME: statement/sql/show_table_status
              QUERY: show table status like 'account'
  RETURNED_SQLSTATE: NULL
        MYSQL_ERRNO: 0
       MESSAGE_TEXT: NULL
             ERRORS: 0
           WARNINGS: 0
*************************** 5. row ***************************
     processlist_id: 629626
               user: backup
        trx_started: 2022-10-17 12:33:02
trx_isolation_level: REPEATABLE READ
           EVENT_ID: 1718898
         TIMER_WAIT: 34781000
         EVENT_NAME: statement/sql/set_option
              QUERY: SET SQL_QUOTE_SHOW_CREATE=1
  RETURNED_SQLSTATE: 00000
        MYSQL_ERRNO: 0
       MESSAGE_TEXT: NULL
             ERRORS: 0
           WARNINGS: 0
*************************** 6. row ***************************
     processlist_id: 629626
               user: backup
        trx_started: 2022-10-17 12:33:02
trx_isolation_level: REPEATABLE READ
           EVENT_ID: 1718899
         TIMER_WAIT: 33191000
         EVENT_NAME: statement/sql/set_option
              QUERY: SET SESSION character_set_results = 'binary'
  RETURNED_SQLSTATE: 00000
        MYSQL_ERRNO: 0
       MESSAGE_TEXT: NULL
             ERRORS: 0
           WARNINGS: 0
*************************** 7. row ***************************
     processlist_id: 629626
               user: backup
        trx_started: 2022-10-17 12:33:02
trx_isolation_level: REPEATABLE READ
           EVENT_ID: 1718900
         TIMER_WAIT: 158103000
         EVENT_NAME: statement/sql/show_create_table
              QUERY: show create table `account`
  RETURNED_SQLSTATE: NULL
        MYSQL_ERRNO: 0
       MESSAGE_TEXT: NULL
             ERRORS: 0
           WARNINGS: 0
*************************** 8. row ***************************
     processlist_id: 629626
               user: backup
        trx_started: 2022-10-17 12:33:02
trx_isolation_level: REPEATABLE READ
           EVENT_ID: 1718901
         TIMER_WAIT: 38441000
         EVENT_NAME: statement/sql/set_option
              QUERY: SET SESSION character_set_results = 'utf8mb4'
  RETURNED_SQLSTATE: 00000
        MYSQL_ERRNO: 0
       MESSAGE_TEXT: NULL
             ERRORS: 0
           WARNINGS: 0
*************************** 9. row ***************************
     processlist_id: 629626
               user: backup
        trx_started: 2022-10-17 12:33:02
trx_isolation_level: REPEATABLE READ
           EVENT_ID: 1718902
         TIMER_WAIT: 596614000
         EVENT_NAME: statement/sql/show_fields
              QUERY: show fields from `account`
  RETURNED_SQLSTATE: NULL
        MYSQL_ERRNO: 0
       MESSAGE_TEXT: NULL
             ERRORS: 0
           WARNINGS: 0
*************************** 10. row ***************************
     processlist_id: 629626
               user: backup
        trx_started: 2022-10-17 12:33:02
trx_isolation_level: REPEATABLE READ
           EVENT_ID: 1718903
         TIMER_WAIT: 544844000
         EVENT_NAME: statement/sql/show_fields
              QUERY: show fields from `account`
  RETURNED_SQLSTATE: NULL
        MYSQL_ERRNO: 0
       MESSAGE_TEXT: NULL
             ERRORS: 0
           WARNINGS: 0
10 rows in set (0.00 sec)

I know that, in general, the READ-COMMITTED transaction isolation level shouldn't be followed by history length growing. Here I see REPEATABLE-READ, which is reasonable because of the --single-transaction parameter of mysqldump. I have a relatively high load, near 40k QPS to a cluster, so the history length is growing fast while the backup runs ~1h.

Is there a way to deal with history length growing to prevent performance degradation?

1
  • 1
    Consider taking the node out of the cluster during the backup. Or check out gh-ost.
    – Rick James
    Commented Oct 18, 2022 at 19:00

1 Answer 1

1

This is a natural consequence of a long-running transaction on a database instance that has high traffic. That is, you are running lots of transactions, but the garbage-collection of the backlog of transactions and old row versions is hindered by the long-running transaction.

Since the HLL corrects itself rapidly after your mysqldump is over, I wouldn't worry about it.

If you are seeing measurable performance degradation of other queries during the mysqldump, I'd suggest creating a replica and run the mysqldump against the replica. This is a pretty popular solution.

By the way, the mysqldump transaction must be REPEATABLE-READ, to ensure a consistent backup.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.