Bug #107473 | Updating BLOB column slows down purge and causes high trx_rseg_history_len | ||
---|---|---|---|
Submitted: | 3 Jun 2022 3:34 | Modified: | 24 Aug 2022 6:54 |
Reporter: | Lei Zeng | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
Version: | 8.0.23, 8.0.29 | OS: | CentOS |
Assigned to: | CPU Architecture: | Any |
[3 Jun 2022 3:34]
Lei Zeng
[3 Jun 2022 16:32]
Lei Zeng
Want to make correct to Step 4. use 'repeat(md5(rand()),9600 )' for 300k BLOB size. 4. Start 100 threads to run the following customized sysbench script to UPDATE statement on the BLOB columns, size = 300k #!/usr/bin/env sysbench require("oltp_common") function prepare_statements() -- do nothing here end function event() local table_name = "test1" local i = sysbench.rand.uniform(1, 1000) con:query(string.format("update %s set bin_data = repeat(md5(rand()),9600 ), extra_bin_data='', data_version=506 where uid= %d", table_name, i)) end
[4 Jun 2022 6:27]
Lei Zeng
Tested on the most recent version 8.0.29, see the same issue: trx_rseg_history_len kept increasing and .ibd file grew 10 or 20 times large after running UPDATE statement against BLOB for 20 min ********* Test case #1, Run 100 concurrent UPDATE statements with default purge parameters: mysql> show variables like '%purge%'; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | binlog_expire_logs_auto_purge | ON | | gtid_purged | | | innodb_max_purge_lag | 0 | | innodb_max_purge_lag_delay | 0 | | innodb_purge_batch_size | 300 | | innodb_purge_rseg_truncate_frequency | 128 | | innodb_purge_threads | 4 | | relay_log_purge | ON | +--------------------------------------+-------+ -rw-r----- 1 mysql mysql 11916017664 Jun 4 04:12 test1.ibd -rw-r----- 1 mysql mysql 115980894208 Jun 4 04:33 test1.ibd mysql> select now(),name,COUNT from information_schema.innodb_metrics where name in ('trx_rseg_history_len'); +---------------------+----------------------+-------+ | now() | name | COUNT | +---------------------+----------------------+-------+ | 2022-06-04 04:12:23 | trx_rseg_history_len | 0 | +---------------------+----------------------+-------+ mysql> select now(),name,COUNT from information_schema.innodb_metrics where name in ('trx_rseg_history_len'); +---------------------+----------------------+--------+ | now() | name | COUNT | +---------------------+----------------------+--------+ | 2022-06-04 04:32:42 | trx_rseg_history_len | 367261 | +---------------------+----------------------+--------+ ********* Test case #2, Run 100 concurrent UPDATE statements with customized purge parameters: mysql> show variables like '%purge%'; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | binlog_expire_logs_auto_purge | ON | | gtid_purged | | | innodb_max_purge_lag | 0 | | innodb_max_purge_lag_delay | 0 | | innodb_purge_batch_size | 5000 | >>>>>> | innodb_purge_rseg_truncate_frequency | 128 | | innodb_purge_threads | 12 | >>>>>> | relay_log_purge | ON | +--------------------------------------+-------+ -rw-r----- 1 mysql mysql 11916017664 Jun 4 05:08 test1.ibd -rw-r----- 1 mysql mysql 224315572224 Jun 4 06:20 test1.ibd mysql> select now(),name,COUNT from information_schema.innodb_metrics where name in ('trx_rseg_history_len'); +---------------------+----------------------+-------+ | now() | name | COUNT | +---------------------+----------------------+-------+ | 2022-06-04 05:09:29 | trx_rseg_history_len | 2 | +---------------------+----------------------+-------+ mysql> select now(),name,COUNT from information_schema.innodb_metrics where name in ('trx_rseg_history_len'); +---------------------+----------------------+--------+ | now() | name | COUNT | +---------------------+----------------------+--------+ | 2022-06-04 06:12:42 | trx_rseg_history_len | 536976 | +---------------------+----------------------+--------+
[4 Jun 2022 6:30]
Lei Zeng
make correction to 'Test case #2', I used 200 concurrent UPDATE statements with customized purge parameters, instead of 100
[24 Aug 2022 6:54]
MySQL Verification Team
Hello Lei Zeng, Thank you for the report and test case. regards, Umesh