Mysql many databases vs single one + Replication

[slaw]

Hello everyone,

I have MySQL 5.6.27 and some problems with replication (huge delay between master and slave). I have a Master instance with 10 cores and Slave instance with the same hardware. My Master uses all resources heavily (all cores, 80% from each one) but Slave only 1 core (80% from one core and maybe 5% or less from the other ones) and my replication is very delayed.

Is better to split all tables between many databases on the same instance or is better to keep all tables in the same DB and instance or it doesn’t matter from replication point of view? For example is better to have 100 tables in 10 databases (the same MySQL instance) or 100 tables in 1 database (the same instance)?

Many thanks,
all the best
Sla

[jrivera]

In 5.6 replication, it is better to have several databases than a single database with multiple tables. However, in v5.7 replication with slave_parallel_type=LOGICAL_CLOCK can be used to overcome this. To quote from this blog post: https://www.percona.com/blog/2016/02/10/estimating-potential-for-mysql-5-7-parallel-replication/

Unlike MySQL 5.6, where parallel replication can only be used when replicas have several schemas, MySQL 5.7 replicas can read binlog group commit information coming from the master to replicate transactions in parallel even when a single schema is used.