Write process is Too Much SLOW - Restore process very slow - percona XtraDB Cluster 8.0

MySQL & MariaDB Percona XtraDB Cluster 8.x
,
1

Hello,
I have configured 3 node cluster - pxc 8.0 ,3 nodes are operational.

I am restoring a backup (physical backup) of 97.5 Gb @ node 1 ,taking much time to restore .
Could not be able to restore atleast 20 % from backup in 6 hrs of time .

Anything wrong…?

Is there anything need to optimize…?

hardware of 3 nodes
RAM - 8 Gb
vCPUs - 2
disk size - 150 Gb
OS - Ubuntu 18

Here is the config file of node 1

[client]
socket=/var/run/mysqld/mysqld.sock
max_allowed_packet=1G

[mysqld]
server-id=3
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysql/error.log
pid-file=/var/run/mysqld/mysqld.pid

log_timestamps = SYSTEM

# Binary log expiration period is 604800 seconds, which equals 7 days
binlog_expire_logs_seconds=604800
wsrep_provider          =       /usr/lib/galera4/libgalera_smm.so

innodb_buffer_pool_size =       6G
innodb_log_file_size       =       1G
max_allowed_packet      =       1G

# IP address
wsrep_cluster_address=gcomm://192.168.0.1,192.168.0.2,192.168.0.3
binlog_format=ROW
wsrep_slave_threads=48
wsrep_log_conflicts=OFF
innodb_autoinc_lock_mode=2


# Node IP address
wsrep_node_address=192.168.0.3
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc3
pxc_strict_mode=disabled


ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem
1 Like
2

What process of the restore is taking a long time? Copying over the network, or the actual --prepare phase? If copy over the network is slow, then you need to speak with your network administrator and see if there is any issue. Run some basic network tests to determine if the issue is network-related.

1 Like
3

Hi @matthewb
My network is excellent ,Iam connected to a remote machine(EC2 instances) where the backups are kept and restored .
the actual --prepare phase is taking long time.

1 Like
4

How big is your innodb_log_file_size? You can add --use-memory 4G to the --prepare phase to use a bigger buffer pool during the prepare steps. That should speed things up.

2 Likes
5

Hi @matthewb

My innodb_log_file_size = 1G

1 Like
7

changed the innodb_log_file_size=5G,still the restore process is running very slow.

Is there anything like performance optimization…?

1 Like
8

Did you add --use-memory 4G like I suggested? I never said anything about increasing log_file_size so why did you change that?

1 Like
9

I didn’t add --use-memory 4G.

1 Like
10

If you add that to the prepare, it’ll go much faster.

1 Like