Slow MariaDB Load of MySQL Dump

Question

I have a MySQL dump file generated from a MySQL 5.5.53 database. I am loading it on into a MariaDB 10.0.29 database. It is significantly slower (around four times) than when I load it into MySQL. All of my tables are InnoDB. I've found suggestions like disabling unique checks and foreign key checks during the load, but the dump file already has those:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

How can I speed this up?

---

Additional Information

The mysqldump options: --routines --single-transaction --quick --skip-extended-insert
These can't change. I know that the skip-extended-insert option makes one insert per record, which slows down the restore, but that option is needed for other reasons. It's also the same file that takes a quarter of the time to load into my MySQL database.

INNODB_FLUSH_LOG_AT_TRX_COMMIT is set to 2 on the MySQL server and 1 on the MariaDB server. I can update the configuration to match that. Both of them have SYNC_BINLOG set to 0. I hadn't yet made any changes to the stock configuration of MariaDB.

---

Configuration Comparison

I dumped the configurations to files using mysql -uroot -p -A -e"SHOW GLOBAL VARIABLES;" > file-name.txt, and then used pt-config-diff to compare them. I removed lines that contained directories and server names.

Variable                  MySQL Server                     MariaDB Server
========================= ================================ ================================
back_log                  50                               150
character_set_client      latin1                           utf8mb4
character_set_connection  latin1                           utf8mb4
character_set_database    latin1                           utf8mb4
character_set_results     latin1                           utf8mb4
character_set_server      latin1                           utf8mb4
collation_connection      latin1_swedish_ci                utf8mb4_general_ci
collation_database        latin1_swedish_ci                utf8mb4_general_ci
collation_server          latin1_swedish_ci                utf8mb4_general_ci
innodb_additional_mem_... 20971520                         8388608
innodb_autoextend_incr... 8                                64
innodb_buffer_pool_ins... 1                                14
innodb_buffer_pool_size   15032385536                      21474836480
innodb_concurrency_tic... 500                              5000
innodb_data_file_path     ibdata1:10M:autoextend           ibdata1:12M:autoextend
innodb_log_file_size      262144000                        5368709120
innodb_max_dirty_pages... 75                               75.000000
innodb_old_blocks_time    0                                1000
innodb_open_files         300                              512
innodb_purge_batch_size   20                               300
innodb_purge_threads      0                                1
innodb_stats_on_metadata  ON                               OFF
innodb_use_native_aio     OFF                              ON
innodb_version            5.5.53                           5.6.34-79.1
join_buffer_size          262144                           131072
key_buffer_size           268435456                        8589934592
max_connect_errors        10                               100
max_heap_table_size       1610612736                       67108864
max_relay_log_size        0                                104857600
max_seeks_for_key         18446744073709551615             4294967295
max_write_lock_count      18446744073709551615             4294967295
myisam_sort_buffer_size   8388608                          134216704
open_files_limit          3750                             3755
query_cache_limit         8388608                          268435456
query_cache_min_res_unit  4096                             2048
query_cache_size          268435456                        1073741824
read_buffer_size          4194304                          2097152
table_open_cache          1000                             512
thread_cache_size         16                               128
tmp_table_size            1610612736                       67108864
tmpdir                    /var/lib/mysql/tmp               /tmp
version                   5.5.53-0ubuntu0.12.04.1          10.0.29-MariaDB-0ubuntu0.16.04.1
version_comment           (Ubuntu)                         Ubuntu 16.04

---

More Information

• There are no MyISAM tables.
• The MySQL server has 24G of RAM. The MariaDB server has 32G of RAM.
• key_buffer_size is set to 25% of RAM, which is a recommendation I read somewhere
• 16G is the previous server (MySQL) setting. The new server (MariaDB) is set to 64M, which is much lower than 1% of RAM.
• The servers are both 64-bit Ubuntu, 12.0.4 for MySQL and 16.0.5 for MariaDB, and the databases were installed using apt, so I'm pretty sure they're also 64-bit.

Answer 1

Lower query_cache_size to only about 50M. The sizes you have are excessive. Every write to a table purges all QC entries for that table. The bigger the QC is, the longer that takes.

(I added some other notes in the Comments; none of them are likely to cause the problem in question, just other problems.)

More (after OP's updates)

The key_buffer is used only for MyISAM indexes. Since you have no MyISAM tables (except for system tables), 8GB is excessive; lower key_buffer_size to 20M.

innodb_buffer_pool_size, at 20G is fine for 32GB of RAM.

--extended-insert runs perhaps 10 times as fast as not using it. Did you have a reason for turning it off?

Probably the reason for the sluggishness is a combination of innodb_flush_log_at_trx_commit=1 with --skip-extended-insert. To explain:

• =1 causes an extra fsync (disk hit) after every write statement involving an InnoDB table.
• --skip causes each row to be a separate INSERT.

Conclusion: lots more disk hits than if you changed both.