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
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
