I have recently been thrown in the deep-end to manage our Database servers since our DBA left.

The current set-up is Mariadb (mariadb Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu), InnoDB (I turned on innodb_file_per_table as we had some 200+GB ibdata1 files.). We use parallel (conservative) replication.

a little bit of the config (just without ssl certs)

innodb_file_per_table = On
innodb_thread_concurrency = 0
innodb_buffer_pool_size = 40G
innodb_buffer_pool_instances = 20
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
table_open_cache = 8192
thread_cache_size = 256
table_cache = 70000
open_files_limit = 100000
collation-server = utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
## Logging
log-output = FILE
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/slow-log
long-query-time = 30
log_warnings = 2

I have noticed that sometimes our servers drift but this usually corrects itself as I am assuming a large statement(s), one of the reasons I turned on Logging. But some servers will just not correct themselves and one of these only has a 2.6G ibdata1 file, I have restarted slave a few times but its just very slow. I am good on the Linux and Hardware side and ensured there is no other problems with disks. The network link between the servers via web, each server has 1Gbps Connection though and we see no latency ~6ms ping and stable. Some servers drift when there is no load on the servers, like the slave is not requesting from the master and it certainly doesn't seem to use full bandwidth. Overnight one of our servers has gone from being synced to 3 hours behind and seems to be increasing. I cannot see any issue on other database or even any users logged in to our application. could something be locking it from replicating?

Any help or suggestions would be really appreciated as I am kind of thrown in the deep-end here, I am not a DBA but whilst we do not currently have one I am the "best fit" and we need to get these servers replicated correctly for production.

If anyone also has any suggestions on learning resources, I think maybe this setup needs to be started from scratch, we do not use GTID for example, I wonder if we even use the correct replication method.

Many Thanks.

1 Answer 1

innodb_file_per_table = On  -- only applies during CREATE TABLE or ALTER TABLE
table_open_cache = 8192
table_cache = 70000  -- old name for table_open_cache; 70K is too high
init-connect='SET NAMES utf8'  -- keep in mind that user=root skips init_connect
long-query-time = 30  -- so high as to be virtually useless

"one of these only has a 2.6G ibdata1 file" -- What do you mean?

"But some servers will just not correct" -- Do you mean 'some Slaves'? Or 'some Clients'?

"no latency ~6ms ping and stable" -- that implies several hundred miles or km.

"Some servers drift" -- clocks drift; servers don't. What do you mean?

"3 hours behind" -- Do SHOW SLAVE STATUS;

Replication existed for more than a decade before GTID was added. That won't be to blame for what you are seeing.

  • Sorry for the lack of clarification, I notice with a new server build where I have set innodb_file_per_table = On from the start it fetches data much more quickly (during a backup for example) the servers with the large ibdata1 are much slower - Master Master setup, so yes I mean Slaves. - Some Replication members can be between UK -> France. - The replication "Second Behind Master" drifting behind, this is from Show Slave Status; We notice that can go behind for hours and we can see this in the data also. - I am not blaming it on no GTID, But incorrect setup really.
    – hexce
    Commented Sep 8, 2019 at 21:24
  • @hexce - SHOW SLAVE STATUS should say why it stopped. UK->FR -- sounds like 6ms. The title says "parallel", but I see no further mention of such.
    – Rick James
    Commented Sep 8, 2019 at 21:56
  • It does not always stop with an error, I usually fix the error which is usually to do with tempTables, but I can skip over that (I do not think these should be repped anyway as they are only for the session), but the main problem is the rep drifting or lagging show slave status; show that both Slave_IO_Running: Yes Slave_SQL_Running: Yes
    – hexce
    Commented Sep 8, 2019 at 23:18
  • I say parallel because in show slave status we always have Parallel: (conservative)
    – hexce
    Commented Sep 9, 2019 at 5:48
  • @hexce - I suspect that the "parallel" application of the relay-log has nothing to do with your Question. But maybe it does.
    – Rick James
    Commented Sep 9, 2019 at 22:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.