| Bug #116753 | COMPRESSION_PERCENTAGE in binary_log_transaction_compression_stats is incorrect | ||
|---|---|---|---|
| Submitted: | 22 Nov 2024 5:03 | Modified: | 27 Nov 2024 8:27 |
| Reporter: | Ke Yu (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 8.0.40, 8.4.3, 9.1.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[22 Nov 2024 5:03]
Ke Yu
[27 Nov 2024 3:58]
Ke Yu
Hello, any progress on this bug?
[27 Nov 2024 7:21]
MySQL Verification Team
Hello Ke Yu,
Thank you for the report and feedback.
I tried to reproduce by setting simple S->R setup, created tables with dummy data but not seeing the issue. Is there anything I'm missing here? Thank you.
-- Started S/R with
SOURCE='source'
rm -rf $SOURCE/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/$SOURCE --log-error-verbosity=3
bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/$SOURCE --core-file --socket=/tmp/mysql_source.sock --port=3333 --log-error=$PWD/$SOURCE/log.err --log-bin=$SOURCE-bin --server_id=1 --mysqlx=0 --log-error-verbosity=3 --secure-file-priv=/tmp/ --performance-schema=ON --binlog-rows-query-log-events=ON --binlog-transaction-compression=ON --binlog-transaction-compression-level-zstd=10 --compression-algorithms='zstd' 2>&1 &
REPLICA='replica'
rm -rf $REPLICA/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/$REPLICA --log-error-verbosity=3
bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/$REPLICA --core-file --socket=/tmp/mysql_replica.sock --port=6666 --log-error=$PWD/$REPLICA/log.err --log-bin=$REPLICA-bin --server_id=2 --mysqlx=0 --log-error-verbosity=3 --secure-file-priv=/tmp/ --performance-schema=ON --binlog-transaction-compression=ON --binlog-transaction-compression-level-zstd=10 --compression-algorithms='zstd' 2>&1 &
-- enabled instruments n consumers etc
mysql> UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES';
mysql> UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES';
-- Querying table performance_schema.binary_log_transaction_compression_stats doesn't show any discrepencies
regards,
Umesh
[27 Nov 2024 8:08]
Ke Yu
I made a test case:
$ cat t/binlog_compress_100.test
CREATE TABLE `t1` (
`uid` bigint unsigned NOT NULL AUTO_INCREMENT,
`data` mediumblob NOT NULL,
PRIMARY KEY (`uid`)
);
set binlog_transaction_compression=on;
delimiter $$;
CREATE PROCEDURE insert_into_table(IN num INTEGER)
BEGIN
declare x INT;
set x = 1;
while x < num do
start transaction;
insert into t1 (`data`) VALUES (repeat(md5(rand()), 3200));
commit;
set x = x + 1;
end while;
end$$
delimiter ;$$
call insert_into_table(10);
select COMPRESSION_TYPE,COMPRESSED_BYTES_COUNTER,UNCOMPRESSED_BYTES_COUNTER,COMPRESSION_PERCENTAGE from performance_schema.binary_log_transaction_compression_stats where COMPRESSION_TYPE = 'ZSTD';
run test case:
./mtr binlog_compress_100.test
You can get the following result like:
select COMPRESSION_TYPE,COMPRESSED_BYTES_COUNTER,UNCOMPRESSED_BYTES_COUNTER,COMPRESSION_PERCENTAGE from performance_schema.binary_log_transaction_compression_stats where COMPRESSION_TYPE = 'ZSTD';
COMPRESSION_TYPE COMPRESSED_BYTES_COUNTER UNCOMPRESSED_BYTES_COUNTER COMPRESSION_PERCENTAGE
ZSTD 1592 923310 100
You can find that COMPRESSION_PERCENTAGE is 100. But UNCOMPRESSED_BYTES_COUNTER/COMPRESSED_BYTES_COUNTER = 923310/1592 = 579.
COMPRESSION_PERCENTAGE does not reflect the true compression rate.
[27 Nov 2024 8:27]
MySQL Verification Team
Thank you for the details. Verified as described. regards, Umesh
[27 Nov 2024 8:29]
MySQL Verification Team
-- 8.0.40
./mtr binlog_compress_100.test --nocheck-testcases
Logging: ./mtr binlog_compress_100.test --nocheck-testcases
MySQL Version 8.0.40
Checking supported features
- Binaries are debug compiled
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/src/Bug116793/mysql-test/var'
Installing system database
Using parallel: 1
==============================================================================
TEST NAME RESULT TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE `t1` (
`uid` bigint unsigned NOT NULL AUTO_INCREMENT,
`data` mediumblob NOT NULL,
PRIMARY KEY (`uid`)
);
set binlog_transaction_compression=on;
CREATE PROCEDURE insert_into_table(IN num INTEGER)
BEGIN
declare x INT;
set x = 1;
while x < num do
start transaction;
insert into t1 (`data`) VALUES (repeat(md5(rand()), 3200));
commit;
set x = x + 1;
end while;
end$$
call insert_into_table(10);
select COMPRESSION_TYPE,COMPRESSED_BYTES_COUNTER,UNCOMPRESSED_BYTES_COUNTER,COMPRESSION_PERCENTAGE from performance_schema.binary_log_transaction_compression_stats where COMPRESSION_TYPE = 'ZSTD';
COMPRESSION_TYPE COMPRESSED_BYTES_COUNTER UNCOMPRESSED_BYTES_COUNTER COMPRESSION_PERCENTAGE
ZSTD 1588 923310 100
[ 50%] main.binlog_compress_100 [ pass ] 279
[100%] shutdown_report [ pass ]
------------------------------------------------------------------------------
The servers were restarted 0 times
The servers were reinitialized 0 times
Spent 0.279 of 25 seconds executing testcases
