How does parallelism work when loading takes place

[addatwork]

I have a backup system where a few tables make up for the large majority of the content. Lately I've been playing with dividing those large tables using rows or file chunks. I have some questions.

• does that help both the dump and the load?
• i get worse performance when loading a split backup than without the splitting. I'm assuming that's because of the locks and in those cases the inserts can't be really made in parallel. Is better performance expected when using rows or file chunks?

[davidducos]

Hi @addatwork,
There are several things to take into account here.
1- Table size
2- amount of tables

And on other hand you need to understand that -r and -F have similar outcomes but they are not the same.
-r will split the tables in chunks
-F will split the chunks of the size that you set. Even if it is 1 chunk

When you load a table, there other things to take into account like --innodb-optimize-keys which will split the table in 3 pieces when possible. It will create the table with only the columns and primary key, then it will add the secondary index and finally it will add the constraints.

Why don't you share the restore logs? so I can understand why you are not able to get the performance you should.

[addatwork]

Thank you for your response. Here's the restore log using -r (i've tried my best to anonymize some of the names, sorry, i know it's going to be hard to follow)

https://pastebin.com/1vGi82KX

As you can se the total restore time is 127min. Without chunking and with 20 threads i get around 80 min.

[addatwork]

Ran a few more tests with the new version:

• with no chunking and --innodb-optimize-keys : 120m
• with no chunking and no --innodb-optimize-keys: 88m (which is similar to what the old version with default settings was doing)

[davidducos]

@addatwork I will need the logs to understand where is the performance degradation

[addatwork]

i know, but they're so hard to anonymise... i'll see what i can do.

[midenok]

From MDEV-32883 by @dr-m:

... attempt to use multiple threads to write into a single table ... make it unsuitable for any efficient data loading into InnoDB ...

MDEV-16281 had been filed for implementing efficient multi-threaded data loading and index creation for InnoDB.

If multiple transactions are trying to insert into the same table, deadlocks or lock wait timeouts are unavoidable, at least when any secondary indexes exist. Building index trees is much more efficient when done a page at a time (MDEV-24621) rather than row-by-row scattered into multiple index trees of a table.

As far as I can tell, what needs to be fixed is the data loading. It is not optimal to load data into the same table row by row from multiple concurrent transactions. If each table were being accessed from at most one transaction at a time, no InnoDB locking conflicts could possibly occur. (Latch contention, yes, on the buffer pool and the redo log, but that should not be visible to the user.)

it would be helpful to enable bulk insert (MDEV-515, MDEV-24621) by using the mariadb-dump trick that is unfortunately not enabled by default (MDEV-32250). When doing that, make sure that each table is inserted into only from one thread, because in that mode, table-level locking and undo logging will be used.

Also, note that MySQL 8.2.0 includes bulk ingest

[midenok]

@addatwork You may compare performance with single-threaded single-file mysqldump . My first impression without proper performance measurements it works faster.