maria-discuss team mailing list archive

[Michael Caplan <michael@xxxxxxxxxxx>]

Hi,

I'm setting up a replication slave based off of MySQL 5.6.  There are a 
large number of tables (400K).  The MySQL 5.6 master takes around 10 
minutes to start up, where the new MariaDB 10.2 slave takes close to 30 
minutes.  What is making the startup take soo long?

In the below mysql log, you can see in the startup sequence between 
"Highest supported file format is Barracuda." and "InnoDB: 128 out of 
128 rollback segments are active." the startup process takes the 
majority of its time:


2018-01-07  9:38:15 139980432365760 [Note] InnoDB: Mutexes and rw_locks 
use GCC atomic builtins
2018-01-07  9:38:15 139980432365760 [Note] InnoDB: Uses event mutexes
2018-01-07  9:38:15 139980432365760 [Note] InnoDB: Compressed tables use 
zlib 1.2.8
2018-01-07  9:38:15 139980432365760 [Note] InnoDB: Using Linux native AIO
2018-01-07  9:38:15 139980432365760 [Note] InnoDB: Number of pools: 1
2018-01-07  9:38:15 139980432365760 [Note] InnoDB: Using SSE2 crc32 
instructions
2018-01-07  9:38:15 139980432365760 [Note] InnoDB: Initializing buffer 
pool, total size = 54G, instances = 8, chunk size = 128M
2018-01-07  9:38:18 139980432365760 [Note] InnoDB: Completed 
initialization of buffer pool
2018-01-07  9:38:18 139918113195776 [Note] InnoDB: If the mysqld 
execution user is authorized, page cleaner thread priority can be 
changed. See the man page of setpriority().
2018-01-07  9:38:18 139980432365760 [Note] InnoDB: Highest supported 
file format is Barracuda.
2018-01-07 10:11:48 139980432365760 [Note] InnoDB: 128 out of 128 
rollback segments are active.
2018-01-07 10:11:48 139980432365760 [Note] InnoDB: Creating shared 
tablespace for temporary tables
2018-01-07 10:11:48 139980432365760 [Note] InnoDB: Setting file 
'./ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-01-07 10:11:48 139980432365760 [Note] InnoDB: File './ibtmp1' size 
is now 12 MB.
2018-01-07 10:11:48 139980432365760 [Note] InnoDB: Waiting for purge to 
start
2018-01-07 10:11:48 139980432365760 [Note] InnoDB: 5.7.20 started; log 
sequence number 1394303471760
2018-01-07 10:11:48 139919461500672 [Note] InnoDB: Loading buffer 
pool(s) from /var/lib/mysql/ib_buffer_pool
2018-01-07 10:11:48 139980432365760 [Note] Server socket created on IP: 
'::'.
2018-01-07 10:11:49 139980432365760 [Note] Reading of all Master_info 
entries succeded
2018-01-07 10:11:49 139980432365760 [Note] Added new Master_info '' to 
hash table
2018-01-07 10:11:49 139980432365760 [Note] /usr/sbin/mysqld: ready for 
connections.
Version: '10.2.11-MariaDB-10.2.11+maria~xenial-log'  socket: 
'/var/lib/mysql/mysql.sock'  port: 3306  mariadb.org binary distribution


Any ideas why?  I've included my my.conf file below.


Thanks,


Mike



# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name usaddxprdsqlpm3 generated formcaplan@xxxxxxxxxx  at 2017-12-22 13:49:58

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqladmin]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid
character-set-server           = utf8
collation_server               = utf8_general_ci
performance_schema             = 0

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 20M
max-connect-errors             = 1000000
skip-name-resolve
sysdate-is-now                 = 1
innodb                         = FORCE

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
binlog_format                  = MIXED
server-id                      = 3
expire-logs-days               = 7
sync-binlog                    = 1

# REPLICATION #
read-only                      = 1
skip-slave-start               = 1
log-slave-updates              = 1
relay-log                      = /var/lib/mysql/relay-bin
slave-net-timeout              = 60
sync-master-info               = 1
sync-relay-log                 = 1
sync-relay-log-info            = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 10240

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 54G

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 0
slow-query-log                 = 0
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

[mariadb]
plugin-load-add=file_key_management
file_key_management_filename=/var/lib/mysql/keys.enc
file_key_management_filekey=FILE:/var/lib/mysql/mysql.key
file_key_management_encryption_algorithm=AES_CTR
encrypt_binlog=0