I have some problems with replication on Mysql 5.6.27.
Is better to split all tables between multiple databases or better is to use the single database with a lot of tables inside (from replication perspective) or it doesn’t matter?
For example will it help if I split my tables (± 100 tables, some of them ± 50 mln records) between e.g. 5 databases or it doesn’t matter?
also my master has a few cores and each one is used heavily (± 80% resources is used on every core) but on Slave I have similar hardware but only 1 core is used (±80%), the other ones are used in only 10%.
MySQL 5.6 provides only limited parallel slave execution, based on database level isolation. So, if you spread your tables between many databases, the parallel slave may be able to take advantage of it.
But I’d rather consider checking MySQL 5.7 and far better parallel replication capabilities, not restricted to database level.
When it comes to one db vs many dbs, it depends also on the db logic, but I’d rather look for many dbs.
Echoing przemek I would also take into account the size.
So it make sense to split one single database into multiple databases as this can give you a few more options on long term (speed, backups / recovery, replication, clustering etc.).
If a DB gets big (15 - 20 TB) in time then defo you’ll notice some sort of slowness, doing a simple SELECT even with Indexes in place will take a while, it’s expected having more data will only add more processing time.
On the other hand by having multiple DB’s and also having the luxury to split it then you can master it easily in time by spreading those DB across multiple servers, disks etc.
So you have plenty of advantages if you can split them (but don’t exaggerate by having a few gazillions, this will have a negative impact as well… is not quite recommended).
It’s all about the logic and as przemek said previously, if you have the luxury to change the Code and make use of multiple DBs that’s fantastic.
