Monday, March 20, 2023

MySQL - determine transaction size

Running MySQL InnoDB Cluster / Group Replication, there is a transaction size limit (https://dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_transaction_size_limit)


The default value of "group_replication_transaction_size_limit"  is about 143 MB (150000000 bytes)

How can we determine the size of a batch job (single transaction) in MySQL?


Setting a small value with variable group_replication_transaction_size_limit and running the transaction produces the error message in error log. 


For example :

Assuming there is primary node on port 3310

mysql -uroot -p -h127.0.0.1 -P3310  -e "  set global group_replication_transaction_size_limit=1024;"


mysql -uroot -p -h127.0.0.1 -P3310 << EOL

create database if not exists demox;

create table demox.mytable (f1 int not null auto_increment primary key, f2 varchar(1024));

insert into demox.mytable (f2) values (repeat('a',1024));

EOL


Checking the error log file, we may see the message like 

2023-03-21T06:40:40.997931Z 1133317 [ERROR] [MY-011608] [Repl] Plugin group_replication reported: 'Error on session 1133317. Transaction of size 1833 exceeds specified limit 1024. To increase the limit please adjust group_replication_transaction_size_limit option.'

2023-03-21T06:40:40.997991Z 1133317 [ERROR] [MY-010207] [Repl] Run function 'before_commit' in plugin 'group_replication' failed


By adjusting the transaction size limit, the size is logged in the error log file.


 Note: This is not a good practice for production usage.  Adjusting the global variable  'group_replication_transaction_size_limit' affects all transactions.