Mysql – Slow MariaDB Load of MySQL Dump

innodbmariadbMySQLmysqldump

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.

Best Answer

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.

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.

Mysql – ERROR 1166 (42000) at line 65203: Incorrect column name ”

The only database object I can think of with the option CREATE ALGORITHM is a VIEW

To see all the views you have, run this

SELECT table_schema,table_name FROM information_schema.views;

SELECT table_schema,table_name FROM information_schema.tables WHERE engine IS NULL;

From the given error message, one of the views in your database has a column that no longer exists. This can easily happen if you drop a table or a column from a table that is referenced in the view.

Please look over all your views and make sure you can SELECT from all of them.

CORRECTIVE ACTION

You may have to recreate the mysqldump after you fix the VIEW in the source database.

If you cannot recreate the mysqldump due to the size, you may have edit the mysqldump to ignore the VIEW definition altogether.

Best Answer

Related Solutions

MySQL database drop insanely slow

UPDATE 2013-04-15 18:04 EDT

Recommendation : Remove all Data and Index Pages from ibdata1

UPDATE 2013-04-22 08:10 EDT

Mysql – ERROR 1166 (42000) at line 65203: Incorrect column name ”

CORRECTIVE ACTION

Related Question