--paralllel 64 is not using that many threads but only few (max 4 to 5)

MySQL & MariaDB Percona XtraBackup
1

MySQL DB: Percona MySQL 5.7 or Oracle MySQL 5.7.27
xtrabackup: recognized server arguments: --datadir=/srv/mysql//data --innodb_buffer_pool_size=12G --innodb_file_per_table=1 --log_bin=/srv/mysql//binlog/mysql-binlog- --server-id=3 --tmpdir=/srv/mysql//temp --innodb_log_buffer_size=32M --innodb_log_file_size=400M --innodb_flush_log_at_trx_commit=0
innobackupex version 2.4.28 Linux (x86_64) (revision id: 44a8f7b)

Host: CentOS 7.9 CPU = 16, RAM 46GB DB size 8TB, all of them or 90% of MyISAM

I used --parallel = 64, but still the pstree-p doesnt show all these parallel threads, only I could 4 to 6 threads at any given time.

But if I use Percona MySQL 8 with Xtrabackupex 8, its honouring the paralllel 64 with atleast 30+ threads, What would be the reason? Due to the volume of the data, its taking lot of time, eventually, I am getting stale file error at the end of the process while xtrabackup is trying to write into xtrabackup_logfile (failing at the last step) with stale file error as we store the backup in nfs mount.

can you please help me with any suggestion or solution to speed it up, so I can avoid that stale file error. I am not sure why xtrabackupex, if it get stale file error, recreate the file or retry instead of existing file handler.

2

90% of 8TB is 7.2TB, MyISAM data. This means PXB is starting an InnoDB transaction, backing up the InnoDB tables, then holding that open with READ LOCKs on the MyISAM tables while 7.2TB is copied in a non-transactional manner, over network to NFS.

I would recommend that you try streaming and compressing the backup to NFS, that way you’re keeping the data flowing constantly. Example:

xtrabackup --backup --stream=xbstream --parallel 16 --compress --compress-threads 16 >/path/nfs/backup.xbs

I’m pretty sure specifying 64 threads on a 16 CPU machine will not result in any benefit. That’s 3x more threads than you have CPUs. Plus you need threads for IO and network.