I'm trying to get replication working between 2 servers running MariaDB 10.1.7 64 bit. The database I'm trying to replicate is huge, 520G and the export/import on the slave with disabled autocommits took 5 days. When I initially started the replication, it seemed that the slave is going to catch up. It actually looked very promising. Sadly, after 30 minutes when I checked the slave again I realized that it is actually falling behind. 1 week after during night time when the master wasn't so busy, it really seemed that it's catching up again but unfortunately now Seconds_Behind_Master is almost 1 week. I set slave_parallel_mode to aggressive and on the slave I've set slave_parallel_threads to 30, while on the master this is set to 10. The innodb_flush_log_at_trx_commit variable is 1 on the slave and 0 on the master. Here is the config for the master:
[client] port = 3306 socket = /var/lib/mysql/mysqld.sock [mysqld] port = 3306 socket = /var/lib/mysql/mysqld.sock log-error=/var/log/mysql/logs/mysql.log datadir=/RAID50/innodb/mysql/ init-connect = 'SET NAMES UTF8' character-set-server = utf8 server_id=1 sync_binlog=1 back_log = 50 max_connections = 450 max_connect_errors = 9999999 table_cache = 4096 max_allowed_packet = 16M binlog_cache_size = 1M max_binlog_size = 100M max_heap_table_size = 64M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 16 query_cache_size = 128M query_cache_limit = 4M query_alloc_block_size = 16K expire_logs_days=14 # Set the default table type event_scheduler=ON default-storage-engine=InnoDB thread_stack = 192K transaction_isolation = READ-COMMITTED tmp_table_size = 64M log-bin=/binlog/mysql/master-bin # binary logging format - mixed recommended binlog_format=mixed slow_query_log_file=/var/log/mysql/logs/slow-queries.log slow_query_log=1 long_query_time=1 tmpdir = /tmp slave_load_tmpdir = /var/log/mysql/tmpfs innodb_additional_mem_pool_size = 32M # This config file assumes a main memory of at least 8G # innodb_buffer_pool_size = 6.5G # Default setting innodb_buffer_pool_size = 8G # Set this option if you would like the InnoDB tablespace files to be # stored in another location. By default this is the MySQL datadir. innodb_file_per_table = 1 innodb_data_home_dir = /RAID50/innodb/mysql/ innodb_file_format = Barracuda innodb_data_file_path = ibdata1:156M;ibdata2:156M:autoextend # innodb_data_file_path = /data1/innodb/mysql/ innodb_autoextend_increment=156M # Number of threads allowed inside the InnoDB kernel. The optimal value # depends highly on the application, hardware as well as the OS # scheduler properties. A too high value may lead to thread thrashing. # innodb_thread_concurrency = 16 innodb_thread_concurrency = 32 innodb_flush_log_at_trx_commit = 0 innodb_log_buffer_size = 8M # make sure the log files are large enough that you don't hold up # checkpoints when the logs rotate! innodb_log_file_size = 200M # Total number of files in the log group. A value of 2-3 is usually good # enough. innodb_log_files_in_group = 2 innodb_log_group_home_dir = /var/log/mysql/innodb_logs innodb_max_dirty_pages_pct = 80 # use directIO to bypass filesystem cache where possible innodb_flush_method=O_DIRECT innodb_lock_wait_timeout = 30 innodb_commit_concurrency=0 innodb_open_files=2000 [mysqldump] # Do not buffer the whole result set in memory before writing it to # file. Required for dumping very large tables quick max_allowed_packet = 16M [mysql] no-auto-rehash default-character-set = utf8 # Only allow UPDATEs and DELETEs that use keys. #safe-updates [isamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] # Increase the amount of open files allowed per process. Warning: Make # sure you have set the global system limit high enough! The high value # is required for a large number of opened tables open-files-limit = 102400 This is the configuration file for the slave:
[mysqld] datadir=/data1/mysql socket=/var/lib/mysql/mysql.sock # replication implementation server-id=101 log-bin=mysql-bin binlog_format=mixed read-only=1 relay-log=mysql-relay-bin log-slave-updates=1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd init-connect = 'SET NAMES UTF8' character-set-server = utf8 innodb_file_per_table = 1 innodb_file_format = Barracuda innodb_data_file_path = ibdata1:156M;ibdata2:156M:autoextend innodb_thread_concurrency = 32 innodb_flush_log_at_trx_commit = 0 innodb_log_buffer_size = 8M innodb_log_file_size = 200M innodb_log_files_in_group = 2 innodb_max_dirty_pages_pct = 80 innodb_flush_method=O_DIRECT innodb_lock_wait_timeout = 30 innodb_commit_concurrency=0 innodb_open_files=2000 slave_parallel_threads = 10 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid open-files-limit = 102400 # # include all files from the config directory # #!includedir /etc/my.cnf.d Here is the output for SHOW SLAVE STATUS \G:
MariaDB [(none)]> SHOW SLAVE STATUS \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.11.25 Master_User: replicant Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.005174 Read_Master_Log_Pos: 86423579 Relay_Log_File: mysql-relay-bin.000109 Relay_Log_Pos: 77202510 Relay_Master_Log_File: master-bin.005116 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1932 Last_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist in engine Skip_Counter: 0 Exec_Master_Log_Pos: 77202221 Relay_Log_Space: 6273918609 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 580994 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1932 Last_SQL_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist in engine Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: aggressive 1 row in set (0.00 sec) I do know about the Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist in engine and it should be fixed, but I am not sure if it is the cause for the replication lag. Does anyone have any ideas what I should be tweaking? The slave has a better CPU than the master.
