Re: Query regarding implementation of parallel-replication
It's good to know. Keep up with good work, cheers!! -- *Wagner Bianchi, MySQL Database Specialist* Mobile: +55.31.8654.9510 E-mail: m...@wagnerbianchi.com Twitter: @wagnerbianchijr 2014-09-06 3:01 GMT-03:00 Ajay Garg ajaygargn...@gmail.com: Hi Wagner. That is what I did as the last resort, and that is only what solved the issue. Thanks. On Fri, Sep 5, 2014 at 1:52 AM, wagnerbianchi.com m...@wagnerbianchi.com wrote: You can try these steps: 1-) Stop slave and write down the replication coordinates getting that in MySQL's error log (*very important step*); 2-) Issue the `reset slave` command on MySQL Slave; 3-) Issue the CHANGE MASTER TO considering the replication coordinates you've just written down on step 1; 4-) Give replication a start; 5-) Check if the issue has gone away. If you're not comfortable to do that, just share the SHOW SLAVE STATUS output with us. Let us know how's it going, cheers!! -- Wagner Bianchi, MySQL Database Specialist Mobile: +55.31.8654.9510 E-mail: m...@wagnerbianchi.com Twitter: @wagnerbianchijr 2014-09-04 7:24 GMT-03:00 Ajay Garg ajaygargn...@gmail.com: Hi all. Unfortunately, I have run into the logs, as described at http://bugs.mysql.com/bug.php?id=71495 Unfortunately, the issue does not go away, even after reverting back to slave-parallel-workers=0 in my.cnf, and restarting the mysql instance. Any quick idea, as to how we may get the mysql+replication up and running (even with the plain old non-multi-threaded mode)? On Tue, Sep 2, 2014 at 12:57 PM, Ajay Garg ajaygargn...@gmail.com wrote: Thanks Akshay for the reply. On Tue, Sep 2, 2014 at 12:25 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Hello Ajay, I tried testing the slave-parallel-workers few months ago, what I can surely tell you its still under development, and at that time needed some critical bug fixing. It is helpful in situations where each schema has even workload. The case you mentioned above doesnt have so. DB2 is getting different type of load than the others, in that case the other slave workers should be able to proceed with their workload as opposed to db2 which is still executing the long running statement. Now just imagine what happens if we try to take a backup, what binlog position should be captured ? the show slave status will print what ? this is where it needs development, I tried testing backups on it, but there is no concrete documentation on what position it would fetch. db2-statement-1 (very, very long-running) db2-statement-2 (short-running) about the above scenario, the next db2-statement-2 it will wait for the long running statement-1 to complete. Surely.. !! :) However, my concern is how this tracking is done. That is, how is the db-wise segregation of statements done (from a single-binlog-file originally coming onto the slave) ? If this segregation is not done, then I cannot think of a way on how things would scale up, like for example, when the slave-relay-log-file contains a random mix of statements from tens of different databases. Any pointers on the actual current implementation of this db-wise statements-segregation will be a great confidence-booster !! :) Thanks and Regards, Ajay However db2-statement-2 can be picked up by any other sql worker thread. This is a good feature added in mysql, however still needs to go through lot of testing. Please share your observation and findings in case it differs from the above. Cheers!!! Akshay On Mon, Sep 1, 2014 at 8:27 AM, Ajay Garg ajaygargn...@gmail.com wrote: Hi all. We have replication set-up, where we cater to HUUGEE 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
Re: Query regarding implementation of parallel-replication
Hi Wagner. That is what I did as the last resort, and that is only what solved the issue. Thanks. On Fri, Sep 5, 2014 at 1:52 AM, wagnerbianchi.com m...@wagnerbianchi.com wrote: You can try these steps: 1-) Stop slave and write down the replication coordinates getting that in MySQL's error log (*very important step*); 2-) Issue the `reset slave` command on MySQL Slave; 3-) Issue the CHANGE MASTER TO considering the replication coordinates you've just written down on step 1; 4-) Give replication a start; 5-) Check if the issue has gone away. If you're not comfortable to do that, just share the SHOW SLAVE STATUS output with us. Let us know how's it going, cheers!! -- Wagner Bianchi, MySQL Database Specialist Mobile: +55.31.8654.9510 E-mail: m...@wagnerbianchi.com Twitter: @wagnerbianchijr 2014-09-04 7:24 GMT-03:00 Ajay Garg ajaygargn...@gmail.com: Hi all. Unfortunately, I have run into the logs, as described at http://bugs.mysql.com/bug.php?id=71495 Unfortunately, the issue does not go away, even after reverting back to slave-parallel-workers=0 in my.cnf, and restarting the mysql instance. Any quick idea, as to how we may get the mysql+replication up and running (even with the plain old non-multi-threaded mode)? On Tue, Sep 2, 2014 at 12:57 PM, Ajay Garg ajaygargn...@gmail.com wrote: Thanks Akshay for the reply. On Tue, Sep 2, 2014 at 12:25 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Hello Ajay, I tried testing the slave-parallel-workers few months ago, what I can surely tell you its still under development, and at that time needed some critical bug fixing. It is helpful in situations where each schema has even workload. The case you mentioned above doesnt have so. DB2 is getting different type of load than the others, in that case the other slave workers should be able to proceed with their workload as opposed to db2 which is still executing the long running statement. Now just imagine what happens if we try to take a backup, what binlog position should be captured ? the show slave status will print what ? this is where it needs development, I tried testing backups on it, but there is no concrete documentation on what position it would fetch. db2-statement-1 (very, very long-running) db2-statement-2 (short-running) about the above scenario, the next db2-statement-2 it will wait for the long running statement-1 to complete. Surely.. !! :) However, my concern is how this tracking is done. That is, how is the db-wise segregation of statements done (from a single-binlog-file originally coming onto the slave) ? If this segregation is not done, then I cannot think of a way on how things would scale up, like for example, when the slave-relay-log-file contains a random mix of statements from tens of different databases. Any pointers on the actual current implementation of this db-wise statements-segregation will be a great confidence-booster !! :) Thanks and Regards, Ajay However db2-statement-2 can be picked up by any other sql worker thread. This is a good feature added in mysql, however still needs to go through lot of testing. Please share your observation and findings in case it differs from the above. Cheers!!! Akshay On Mon, Sep 1, 2014 at 8:27 AM, Ajay Garg ajaygargn...@gmail.com wrote: Hi all. We have replication set-up, where we cater to HUUGEE 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 clarifiy, as to how the above statements will be
Re: Query regarding implementation of parallel-replication
Hi all. Unfortunately, I have run into the logs, as described at http://bugs.mysql.com/bug.php?id=71495 Unfortunately, the issue does not go away, even after reverting back to slave-parallel-workers=0 in my.cnf, and restarting the mysql instance. Any quick idea, as to how we may get the mysql+replication up and running (even with the plain old non-multi-threaded mode)? On Tue, Sep 2, 2014 at 12:57 PM, Ajay Garg ajaygargn...@gmail.com wrote: Thanks Akshay for the reply. On Tue, Sep 2, 2014 at 12:25 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Hello Ajay, I tried testing the slave-parallel-workers few months ago, what I can surely tell you its still under development, and at that time needed some critical bug fixing. It is helpful in situations where each schema has even workload. The case you mentioned above doesnt have so. DB2 is getting different type of load than the others, in that case the other slave workers should be able to proceed with their workload as opposed to db2 which is still executing the long running statement. Now just imagine what happens if we try to take a backup, what binlog position should be captured ? the show slave status will print what ? this is where it needs development, I tried testing backups on it, but there is no concrete documentation on what position it would fetch. db2-statement-1 (very, very long-running) db2-statement-2 (short-running) about the above scenario, the next db2-statement-2 it will wait for the long running statement-1 to complete. Surely.. !! :) However, my concern is how this tracking is done. That is, how is the db-wise segregation of statements done (from a single-binlog-file originally coming onto the slave) ? If this segregation is not done, then I cannot think of a way on how things would scale up, like for example, when the slave-relay-log-file contains a random mix of statements from tens of different databases. Any pointers on the actual current implementation of this db-wise statements-segregation will be a great confidence-booster !! :) Thanks and Regards, Ajay However db2-statement-2 can be picked up by any other sql worker thread. This is a good feature added in mysql, however still needs to go through lot of testing. Please share your observation and findings in case it differs from the above. Cheers!!! Akshay On Mon, Sep 1, 2014 at 8:27 AM, Ajay Garg ajaygargn...@gmail.com wrote: Hi all. We have replication set-up, where we cater to HUUGEE 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 clarifiy, as to how the above statements will be managed amongst 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? Thanks and Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Regards, Ajay -- Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query regarding implementation of parallel-replication
You can try these steps: 1-) Stop slave and write down the replication coordinates getting that in MySQL's error log (*very important step*); 2-) Issue the `reset slave` command on MySQL Slave; 3-) Issue the CHANGE MASTER TO considering the replication coordinates you've just written down on step 1; 4-) Give replication a start; 5-) Check if the issue has gone away. If you're not comfortable to do that, just share the SHOW SLAVE STATUS output with us. Let us know how's it going, cheers!! -- *Wagner Bianchi, MySQL Database Specialist* Mobile: +55.31.8654.9510 E-mail: m...@wagnerbianchi.com Twitter: @wagnerbianchijr 2014-09-04 7:24 GMT-03:00 Ajay Garg ajaygargn...@gmail.com: Hi all. Unfortunately, I have run into the logs, as described at http://bugs.mysql.com/bug.php?id=71495 Unfortunately, the issue does not go away, even after reverting back to slave-parallel-workers=0 in my.cnf, and restarting the mysql instance. Any quick idea, as to how we may get the mysql+replication up and running (even with the plain old non-multi-threaded mode)? On Tue, Sep 2, 2014 at 12:57 PM, Ajay Garg ajaygargn...@gmail.com wrote: Thanks Akshay for the reply. On Tue, Sep 2, 2014 at 12:25 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Hello Ajay, I tried testing the slave-parallel-workers few months ago, what I can surely tell you its still under development, and at that time needed some critical bug fixing. It is helpful in situations where each schema has even workload. The case you mentioned above doesnt have so. DB2 is getting different type of load than the others, in that case the other slave workers should be able to proceed with their workload as opposed to db2 which is still executing the long running statement. Now just imagine what happens if we try to take a backup, what binlog position should be captured ? the show slave status will print what ? this is where it needs development, I tried testing backups on it, but there is no concrete documentation on what position it would fetch. db2-statement-1 (very, very long-running) db2-statement-2 (short-running) about the above scenario, the next db2-statement-2 it will wait for the long running statement-1 to complete. Surely.. !! :) However, my concern is how this tracking is done. That is, how is the db-wise segregation of statements done (from a single-binlog-file originally coming onto the slave) ? If this segregation is not done, then I cannot think of a way on how things would scale up, like for example, when the slave-relay-log-file contains a random mix of statements from tens of different databases. Any pointers on the actual current implementation of this db-wise statements-segregation will be a great confidence-booster !! :) Thanks and Regards, Ajay However db2-statement-2 can be picked up by any other sql worker thread. This is a good feature added in mysql, however still needs to go through lot of testing. Please share your observation and findings in case it differs from the above. Cheers!!! Akshay On Mon, Sep 1, 2014 at 8:27 AM, Ajay Garg ajaygargn...@gmail.com wrote: Hi all. We have replication set-up, where we cater to HUUGEE 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 clarifiy, as to how the above statements will be managed amongst 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
Re: Query regarding implementation of parallel-replication
Ping !! :) On Mon, Sep 1, 2014 at 8:27 AM, Ajay Garg ajaygargn...@gmail.com wrote: Hi all. We have replication set-up, where we cater to HUUGEE 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 clarifiy, as to how the above statements will be managed amongst 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? Thanks and Regards, Ajay -- Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query regarding implementation of parallel-replication
Thanks Akshay for the reply. On Tue, Sep 2, 2014 at 12:25 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Hello Ajay, I tried testing the slave-parallel-workers few months ago, what I can surely tell you its still under development, and at that time needed some critical bug fixing. It is helpful in situations where each schema has even workload. The case you mentioned above doesnt have so. DB2 is getting different type of load than the others, in that case the other slave workers should be able to proceed with their workload as opposed to db2 which is still executing the long running statement. Now just imagine what happens if we try to take a backup, what binlog position should be captured ? the show slave status will print what ? this is where it needs development, I tried testing backups on it, but there is no concrete documentation on what position it would fetch. db2-statement-1 (very, very long-running) db2-statement-2 (short-running) about the above scenario, the next db2-statement-2 it will wait for the long running statement-1 to complete. Surely.. !! :) However, my concern is how this tracking is done. That is, how is the db-wise segregation of statements done (from a single-binlog-file originally coming onto the slave) ? If this segregation is not done, then I cannot think of a way on how things would scale up, like for example, when the slave-relay-log-file contains a random mix of statements from tens of different databases. Any pointers on the actual current implementation of this db-wise statements-segregation will be a great confidence-booster !! :) Thanks and Regards, Ajay However db2-statement-2 can be picked up by any other sql worker thread. This is a good feature added in mysql, however still needs to go through lot of testing. Please share your observation and findings in case it differs from the above. Cheers!!! Akshay On Mon, Sep 1, 2014 at 8:27 AM, Ajay Garg ajaygargn...@gmail.com wrote: Hi all. We have replication set-up, where we cater to HUUGEE 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 clarifiy, as to how the above statements will be managed amongst 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? Thanks and Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Query regarding implementation of parallel-replication
Hi all. We have replication set-up, where we cater to HUUGEE 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 clarifiy, as to how the above statements will be managed amongst 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? Thanks and Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql