mydumper slooooow and "checksum mismatch" warnings

[astesin]

Good evening, dear colleagues, may I ask your opinion on the following?
I am now restoring the backup acquired from an old (5.7 maybe?) MySQL database to the fresh new MariaDB 10.11.5 server.
mydumper / myloader are of the version:

myloader0.14.3-1, built against MariaDB 10.2.9 with SSL support with GZIP (no space in front of version number but take it as is).

First question. When I asked mydumper to work in 16 threads, it just stuck. Okay, maybe a network overload problem, I restarted with just the basic 4 threads. The problem is - it was sloooooow... While the backup from the old MySQL took about an hour, the restore operation took about 4 hours and is still not complete. The MariaDB server shows no visible load at all, looks like it is just staying almost idle. Neither CPUs nor disks IOPS are showstoppers, but it takes, hmm, too much time. How do I find a bottleneck? Please, help!

The other issue is, that now when all threads are closed, mydumper is slowly slowly performing checksum validations. And what do I see? I see warnings about the checksums. I.e. for a table 'somedb'.'sometable' log says:

2023-09-26 21:40:41 [INFO] - Structure checksum confirmed for somedb.sometable
2023-09-26 21:40:41 [INFO] - Schema index checksum confirmed for somedb.sometable
2023-09-26 21:40:46 [WARNING] - Checksum mismatch found for somedb.sometable. Got '3413270774', expecting '3309409483'

How do I interpret this? Is this a sign of a fatal import error? Or maybe this is a result of charset conversion between different versions of UTF-8 encoding? Can I trust the restored results? I will check the tables for identity anyway, but can anyone tell me what to expect and how to cope with it?

Thanks in advance! Warmest regards, Andreas

[davidducos]

Hi @astesin,
If myloader is stuck, then there is a bug. You could try with a newer version.
About timings, take into account that dumps takes less time than imports.
To check data consistent we run checksum table, it is up to you to determine if it is a valid warning.

[astesin]

Thanks! I already upgraded to mydumper v0.15.1-3, built against MySQL 8.0.33-25 with SSL support

The next backup/restore cycle will be done with this new version tomorrow and I'll report my findings than. And of course, we'll make a comparison tomorrow so we'll know for sure are the tables actually corrupted or not.

Is there any chance that building mydumper/myloader locally with the latest MariaDB-provided libraries will somehow improve the picture?

Warmest regards, Andreas

[viswaprsnl]

Can you share your latest results here

[astesin]

The same story continues: while structure checksums and schema index checksums are OK, for some 2/3 of tables I'm getting the "Checksum mismatch" warnings. But visually the data is OK, I am waiting for programmers who are working with the restored DBMS - will they notice something unusual or erroneous with the restored data, or not? My only adequate guess is that while I am using the --tz-utc option, the source tables have their datetime values stored in the different assorted timezones and this causes (valid) conversions but checksums obviously become different than.

[tanji]

Datetime is indeed stored with the timezone, contrary to timestamp which is stored in UTC. You can get different checksums this way.

Also noted in the manual:

The checksum value depends on the table row format. If the row format changes, the checksum also changes. For example, the storage format for temporal types such as TIME, DATETIME, and TIMESTAMP changed in MySQL 5.6 prior to MySQL 5.6.5, so if a 5.5 table is upgraded to MySQL 5.6, the checksum value may change.

[astesin]

Thank you! I will stick with this hypothesis as a base, until I find a piece of evidence that points to some other possible cause.
And indeed, my source server is an ancient MySQL 5.5.smth - so your comment seems to pinpoint the root cause.