3GB DB mysqldump import failure, MySQL -> MariaDB

[Mouth]

I am attempting to migrate to a new hosting provider, thus mysqldump on the source for DB backup and mysql import on the target.

On disk, my MySQL 5.7 source DB is 5.0GB and the mysqldump file is 3GB, thus importing the 3GB .sql into the target server with MariaDB 10.6

mysqldump with --hex-blob --opt --default-character-set=utf8mb4 --single-transaction [database] > ~/[database].sql whilst the XF site is closed.

On the target server, import with mysql --default-character-set=utf8mb4 --quick --unbuffered [database] < ~/[database].sql

Each time I attempt import, the INSERT statement appears to hang on a large table, eg. xf_post. 'show processlist;' appearing to show that sql service is active, but that the INSERT is just sitting there timing-out. Server activity at this points lower (minimal CPU and IO activity) instead of being high whilst importing/inserting other tables.

The target DB server is 4GB RAM, with 3GB allocated to MariaDB. Config 'tunings' from default are;

key_buffer_size = 20M
max_allowed_packet = 1G

innodb_buffer_pool_size = 3G
innodb_log_file_size = 1G
innodb_io_capacity = 20000
innodb_read_io_threads = 5000
innodb_write_io_threads = 5000
innodb_flush_log_at_trx_commit = 0

Nothing appears in the MariaDB error.log whilst the import is running or hangs.

Instead of running mysql import with above command line, I've also tried if from within mysql using some 'optimal' import settings commonly found through a google search ...

set global net_buffer_length = 1000000;
set global max_allowed_packet = 1000000000;
SET character_set_server = 'utf8mb4';
SET foreign_key_checks = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
DROP DATABASE [database];
CREATE DATABASE [database];
USE [database];
source ~/[database].sql;

... but get the same result, INSERT statement appearing to hang for a large table.

To ensure there's no doubt, I've let both import scenarios sit in their 'hung' state for several hours before concluding its hung and stopping and restarting the SQL service.

Perhaps a 3G buffer_pool is not large enough for a 3GB .sql import?

Anyone can offer advice and/or examples of mysqldump and mysql import commands/process that have worked fine for them in similar scenarios and RAM/DB sizes?

 

[Kirby]

Hmm, I've never worked with such a small MySQL server, but I don't think the buffer pool is too small.
Is the disk NVME? Otherwise innodb_io_capacity seems to be too high to me.

Also innodb_read_io_threads and innodb_write_io_threads are way too high (the max value ist 64 anyway?).

Did you see anything in syslog?

 

[Mouth]

Otherwise innodb_io_capacity seems to be too high to me.

Also innodb_read_io_threads and innodb_write_io_threads are way too high (the max value ist 64 anyway?).

Thanks, corrected those;

innodb_io_capacity = 1500
innodb_write_io_threads = 4
innodb_read_io_threads = 4

With these figures above, still the same issue. With ought any of the non-default tunings, still the same issue.

Did you see anything in syslog?

No, just MariaDB start and stop notices.

 

[Mouth]

MySQL 5.7 source DB is 5.0GB and the mysqldump file is 3GB, thus importing the 3GB .sql into the target server with MariaDB 10.6

I removed/uninstalled MariaDB 10.6 and instead installed MySQL 8.0, which has resolved and enabled me to perform the import.

 

[Masetrix]

If you switch from MySQL 5.6/7 to MariaDB, you can uninstall MySQL 5.6/7 and install MariaDB. (10.5)
You should then have all my.cnf / mysql.cnf or my.ini used in MySQL, replaced by the originals from MariaDB.
Then MariaDB should continue to run just like MySQL 5.6/7 before.
Only once you have used MySQL Version 8.0, you have to go through the whole procedure with export (MySQL) and import of all databases (MariaDB).

Attention:
Old entries in the my.cnf, (~sql mode = ...) of MySQL 5.6/7 can produce strange errors with MariaDB, but no error messages.

In any case, you should back up the folder and contents of /etc/mysql and /var/lib/mysql before changing.

Hth