Insert update slow sometimes

Server version: 5.7.33-36-57 Percona XtraDB Cluster (GPL), Release rel36, Revision a1ed9c3, WSREP version 31.49, wsrep_31.49

My cluster is 3 nodes. Sometimes inserts or updates are very slow, exactly 30 seconds.
I’m not sure why this happens sometimes,Their state usually stays in the state of “wsrep in pre-commit stage”.

Looking at the logs of other nodes, only found [Warning] WSREP: Failed to report last committed 206542947, -110 (Connection timed out).

Can anyone tell me what is causing this, and if any other information is needed please let me know.

1 Like

You’ve got connectivity issues between your nodes. Connections between your nodes are timing out. Make sure all necessary ports are open (disable firewalls, iptables, etc) between all nodes (4567, 4444, 4568, 3306).

1 Like

All ports are open normally and there are usually no problems connecting. This warning only happens occasionally, I don’t understand why? Sometimes I don’t have this warning yet, but inserting is also slow, why?

1 Like

Have you done any disk IO benchmarks? Can you also provide your my.cnf?

1 Like



# Path to Galera library

# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
# In order for Galera to work correctly binlog format should be ROW

# MyISAM storage engine has only experimental support

# Slave thread to use
wsrep_slave_threads= 8


# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera

# Node IP address
# Cluster name

#If wsrep_node_name is not specified,  then system hostname will be used

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER

# SST method

How to disk IO benchmarks?

Below is the IO data on PMM.

1 Like

That’s your entire my.cnf? I don’t see any config for innodb buffer pool nor for redo logs which means you are using the defaults. The defaults are horrible. Look for some MySQL basics tuning blogs for those and other parameters to modify.

1 Like

Yes, there is no other configuration information left.

What is the problem with using the default value, Could you tell me which parameters need tuning?

1 Like

I answered both your questions in my previous post. The defaults are bad, increase innodb buffer pool and redo log size.

1 Like

My concurrency is not high, and from the performance of the monitoring data, there is no abnormality. Is there any other reason?

1 Like

The bottom graph shows an issue. You see where the bar graph goes ABOVE the red line? That’s a problem. As I said above, it means your my.cnf parameters are not properly tuned.

1 Like

Besides buffer pool, redo log, are there any other parameters that need to be adjusted?

How much should it be adjusted to?

I’m overwhelmed with gratitude for your kindness.

1 Like

Try just those for now and see how things improve. Buffer pool should be around 80% of system RAM and redo log should be at least 1GB.

1 Like