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.
SELECT @@INNODB_FLUSH_LOG_AT_TRX_COMMIT, @@SYNC_BINLOG;
please.INSERTs
one-per-statement? Or Batched?SHOW VARIABLES;
and look at the different lines; don't worry about the extra/missing ones.