We have replication set-up, where we cater to huge amounts of data. Since quite some time, we have been facing issues wherein the slave lags behind master quite a lot.
So, yesterday we were able to setup parallel replication, by incorporating the following changes ::
a) To begin with, partitioned some tables into dedicated databases.
b) Set up the "slave-parallel-workers" parameter.
The above seems to work functionally fine, but we have one doubt/query about the scalability of this solution.
First, I will jot down the flow as far as I understand (please correct if wrong) ::
"Even in parallel-replication scenario, the master writes all the binlog (combined for all databases) in just one file, which then gets passed onto the slave as single-file itself. Thereafter, all the replication commands (combined for all databases) are written sequentially onto one slave-relay file.
Thereafter, as per the documentation, the slave-SQL-Thread acts as the manager, handing over commands to worker-threads depending upon the databases on which the commands run."
So far, so good. However, what would happen if the slave-relay file contains the following ::
db1-statement-1 (short-running)
db2-statement-1 (very, very long-running)
db2-statement-2 (short-running)
db1-statement-2 (short-running)
db1-statement-3 (short-running)
We will be grateful if someone could please clarify, as to how the above statements will be managed among the Manager and the Worker-Threads (let's say there is just one worker-thread-per-db) ?
In particular, does the Manager thread creates internal slave-relay-log-files, one for per database-statements?
