Getting a lot of wsrep: initiating TOI for write set (-1)

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

I have a two node cluster up and running using show processlist I have got alot of

wsrep: initiating TOI for write set (-1)    | drop table if exists data_search

Drop table if exists if a temp table, but this seems to be jamming up the server. What exactly is write set(-1)
The error log has Page cleaner took 4784ms to flush 2232 pages. Is it ok to kill write set(-1)

2 Likes
2

Hi,
Error like
Page cleaner took 4784ms to flush 2232 pages
could be a result of improper my.cnf configuration. Can you please clarify what DB/Cluster version you are running and what configs did you changed from defaults?

2 Likes
3

My version is:
Ubuntu 20.04.3 LTS
8.0.26-16.1 Percona XtraDB Cluster (GPL), Release rel16, Revision b141904, WSREP version 26.4.3

My config is below:

[client]
socket=/var/run/mysqld/mysqld.sock
ssl-ca=/etc/mysql/percona-cert/ca.pem
ssl-cert=/etc/mysql/percona-cert/server-cert.pem
ssl-key=/etc/mysql/percona-cert/server-key.pem

[mysqld]
server-id=404
user=mysql
tmpdir=/db2/tmp
datadir=/db1
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
local_infile=ON
log-error-verbosity=3
log-error=/var/log/mysql/error.log

default_storage_engine=InnoDB
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

innodb_buffer_pool_size = 20G
innodb_buffer_pool_instances = 8
innodb_lock_wait_timeout = 100 #default is 50 seconds
wait_timeout = 300 #was 86400 defaut is 28800 seconds
interactive_timeout = 600 #was 86400 default is 28800 seconds
max_heap_table_size = 96M
sort_buffer_size = 16M
join_buffer_size = 16M
tmp_table_size = 96M
max_sort_length = 8K
max_allowed_packet = 16M
group_concat_max_len = 16M
max_connections = 200
max_connect_errors = 10

max_binlog_size = 128M

log-bin=binlog
log_replica_updates
binlog_expire_logs_seconds=604800
wsrep_provider=/usr/lib/galera4/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.2.61
binlog_format=ROW
wsrep_applier_threads=8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.2.64
wsrep_cluster_name=WebDB-cluster
wsrep_node_name=DB404
pxc_strict_mode=PERMISSIVE
ssl-ca=/etc/mysql/percona-cert/ca.pem
ssl-cert=/etc/mysql/percona-cert/server-cert.pem
ssl-key=/etc/mysql/percona-cert/server-key.pem
tls_version=TLSv1.2,TLSv1.3
[sst]
tmpdir=/db2/tmp
xbstream_opts="--verbose" #turned this on because I was getting errors.

sst-idle-timeout=0
encrypt=4
ssl-ca=/etc/mysql/percona-cert/ca.pem
ssl-cert=/etc/mysql/percona-cert/server-cert.pem
ssl-key=/etc/mysql/percona-cert/server-key.pem

[mysqld_safe]
service_startup_timeout=73200
2 Likes
4

It does not matter if you drop a permanent or a temporary table. DROP TABLE is a DDL operation. In TOI mode it will block all nodes in the cluster. Please read how to perform schema upgrades in PXC at Redirecting

1 Like