[BUG] myloader performance regression for MariaDB 10.6

[LinuxJedi]

Describe the bug
MariaDB 10.6 tries to accelerate loading for InnoDB tables when using SET foreign_key_checks=0, unique_checks=0;. This is outlined in MDEV-515. Unfortunately whilst this increases single-threaded bulk insert performance this introduces table-level locks which reduce the performance of myloader.

To fix this we need to not do unique_checks=0 for myloader for MariaDB 10.6+ restorations.

[davidducos]

Hi @LinuxJedi,
I think that you will be able to achieve that using --defaults-file under session variables section https://github.com/mydumper/mydumper#defaults-file as the session variables are executed after SET unique_checks:

  mysql_query(td->thrconn, "/*!40014 SET UNIQUE_CHECKS=0 */");
  mysql_query(td->thrconn, "/*!40014 SET FOREIGN_KEY_CHECKS=0*/");

  execute_gstring(td->thrconn, set_session);

[davidducos]

I'm thinking that in the future we need to keep a /etc/mydumper.cnf file with this defaults settings there, to let the users determine the defaults values and change them at wish.

[LinuxJedi]

Right. But since this affects pretty much all MariaDB users (all should be on at least 10.6 this year) wouldn't be easier to just disable this if MariaDB is detected? There isn't a default mydumper.cnf right now and myloader is doing the wrong thing here.

[davidducos]

Adding the if clause will be the simplest solution, but after implemented #566, it should be smarter to start loading /etc/mydumper.cnf. As I don't want to do things for particular vendors when we can do it from a configuration file, for instance, maintain the different locking mechanism is not easy, an we are not even close to test all that.