How to simulate slow synchronisation with a galera cluster
0
We have some bugs concerning synchronization on our galera cluster. Our web application does a redirect after writing data to prevent resending data on reload. On the development environment we have no problems. On production with some server load, the second node is sometimes not synchronized if we read the data written a few milliseconds before.
To solve this, we use node pinning, to read from the same node as written before and we are experimenting with SET SESSION wsrep-sync-wait=6; for INSERT/UPDATES/DELETE like described here to avoid reduce that behavior.
Although we have no problem to solve issues with replication, we have problems finding bugs caused by lacking replication.
We would like to simulate a very slow synchronization to test our application. Is there some config option to let a galera cluster behave like under heavy load?
mysql mariadb galera
asked Nov 26 '18 at 8:39
TrendfischerTrendfischer
3,05732136
add a comment |
0
We have some bugs concerning synchronization on our galera cluster. Our web application does a redirect after writing data to prevent resending data on reload. On the development environment we have no problems. On production with some server load, the second node is sometimes not synchronized if we read the data written a few milliseconds before.
To solve this, we use node pinning, to read from the same node as written before and we are experimenting with SET SESSION wsrep-sync-wait=6; for INSERT/UPDATES/DELETE like described here to avoid reduce that behavior.
Although we have no problem to solve issues with replication, we have problems finding bugs caused by lacking replication.
We would like to simulate a very slow synchronization to test our application. Is there some config option to let a galera cluster behave like under heavy load?
mysql mariadb galera
asked Nov 26 '18 at 8:39
TrendfischerTrendfischer
3,05732136
add a comment |
0
0
0
We have some bugs concerning synchronization on our galera cluster. Our web application does a redirect after writing data to prevent resending data on reload. On the development environment we have no problems. On production with some server load, the second node is sometimes not synchronized if we read the data written a few milliseconds before.
To solve this, we use node pinning, to read from the same node as written before and we are experimenting with SET SESSION wsrep-sync-wait=6; for INSERT/UPDATES/DELETE like described here to avoid reduce that behavior.
Although we have no problem to solve issues with replication, we have problems finding bugs caused by lacking replication.
We would like to simulate a very slow synchronization to test our application. Is there some config option to let a galera cluster behave like under heavy load?
mysql mariadb galera
asked Nov 26 '18 at 8:39
TrendfischerTrendfischer
3,05732136
We have some bugs concerning synchronization on our galera cluster. Our web application does a redirect after writing data to prevent resending data on reload. On the development environment we have no problems. On production with some server load, the second node is sometimes not synchronized if we read the data written a few milliseconds before.
To solve this, we use node pinning, to read from the same node as written before and we are experimenting with SET SESSION wsrep-sync-wait=6; for INSERT/UPDATES/DELETE like described here to avoid reduce that behavior.
Although we have no problem to solve issues with replication, we have problems finding bugs caused by lacking replication.
We would like to simulate a very slow synchronization to test our application. Is there some config option to let a galera cluster behave like under heavy load?
mysql mariadb galera
mysql mariadb galera
asked Nov 26 '18 at 8:39
TrendfischerTrendfischer
3,05732136
asked Nov 26 '18 at 8:39
TrendfischerTrendfischer
3,05732136
edited Nov 30 '18 at 8:40
Trendfischer
asked Nov 26 '18 at 8:39
TrendfischerTrendfischer
3,05732136
asked Nov 26 '18 at 8:39
TrendfischerTrendfischer
3,05732136
asked Nov 26 '18 at 8:39
TrendfischerTrendfischer
3,05732136
3,05732136
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
You need to include 1 because you are reading.
SET SESSION wsrep_sync_wait=1; -- or any odd number
SELECT ... ; -- this will wait if necessary
SET SESSION wsrep_sync_wait=0; -- (optional); to avoid slowing down subsequent SQL
answered Nov 26 '18 at 21:10
Rick JamesRick James
70.3k563104
You are right, we cannot avoid this with a 6. But we cannot do this for all selects. We already tried this with the unexpected effect bringing down the whole cluster. We seem to have too many write requests. So we still have to identify critical selects and need some way to simulate a slow cluster for testing. Using a value of 6 just slows down writing requests on heavier load.
– Trendfischer
Nov 28 '18 at 8:58
@Trendfischer - For aSELECT, I think only 1 is needed. Abandon the6.
– Rick James
Nov 28 '18 at 15:56
Sorry, I did not explained it in detail: The 6 is for INSERTS/UPDATES, by waiting for synchronization on writing, we reduce the risk of a lacking synchronization in a multi node write environment. Sadly, this is node based, not cluster based. On ideal circumstances, this would delay the COMMIT until all nodes would have written the data. But galera just delays until all nodes state, they could write the data. Our intended purpose is, to slow down the writes, not the reads with SELECT, what a "1" would do and did not work for us.
– Trendfischer
Nov 30 '18 at 8:37
Maybe you should use 6 (or maybe 7) in front of writes and 1 in front of reads?
– Rick James
Nov 30 '18 at 22:03
Sorry, but we cannot use 1 in front of all reads. The "1" works similar to a LOCK TABLES READ on every write request. We have around 5000 reading queries per second, imagine all of them waiting for a single slow writing query. This is too slow and that's why I can't pin this as the accepted answer. This question is about how to simulate a slow synchronization to find critical reads (which should wait). Our problem is finding bugs, not fixing them.
– Trendfischer
Dec 3 '18 at 9:31
|
show 4 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53477322%2fhow-to-simulate-slow-synchronisation-with-a-galera-cluster%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You need to include 1 because you are reading.
SET SESSION wsrep_sync_wait=1; -- or any odd number
SELECT ... ; -- this will wait if necessary
SET SESSION wsrep_sync_wait=0; -- (optional); to avoid slowing down subsequent SQL
answered Nov 26 '18 at 21:10
Rick JamesRick James
70.3k563104
You are right, we cannot avoid this with a 6. But we cannot do this for all selects. We already tried this with the unexpected effect bringing down the whole cluster. We seem to have too many write requests. So we still have to identify critical selects and need some way to simulate a slow cluster for testing. Using a value of 6 just slows down writing requests on heavier load.
– Trendfischer
Nov 28 '18 at 8:58
@Trendfischer - For aSELECT, I think only 1 is needed. Abandon the6.
– Rick James
Nov 28 '18 at 15:56
Sorry, I did not explained it in detail: The 6 is for INSERTS/UPDATES, by waiting for synchronization on writing, we reduce the risk of a lacking synchronization in a multi node write environment. Sadly, this is node based, not cluster based. On ideal circumstances, this would delay the COMMIT until all nodes would have written the data. But galera just delays until all nodes state, they could write the data. Our intended purpose is, to slow down the writes, not the reads with SELECT, what a "1" would do and did not work for us.
– Trendfischer
Nov 30 '18 at 8:37
Maybe you should use 6 (or maybe 7) in front of writes and 1 in front of reads?
– Rick James
Nov 30 '18 at 22:03
Sorry, but we cannot use 1 in front of all reads. The "1" works similar to a LOCK TABLES READ on every write request. We have around 5000 reading queries per second, imagine all of them waiting for a single slow writing query. This is too slow and that's why I can't pin this as the accepted answer. This question is about how to simulate a slow synchronization to find critical reads (which should wait). Our problem is finding bugs, not fixing them.
– Trendfischer
Dec 3 '18 at 9:31
|
show 4 more comments
1
You need to include 1 because you are reading.
SET SESSION wsrep_sync_wait=1; -- or any odd number
SELECT ... ; -- this will wait if necessary
SET SESSION wsrep_sync_wait=0; -- (optional); to avoid slowing down subsequent SQL
answered Nov 26 '18 at 21:10
Rick JamesRick James
70.3k563104
You are right, we cannot avoid this with a 6. But we cannot do this for all selects. We already tried this with the unexpected effect bringing down the whole cluster. We seem to have too many write requests. So we still have to identify critical selects and need some way to simulate a slow cluster for testing. Using a value of 6 just slows down writing requests on heavier load.
– Trendfischer
Nov 28 '18 at 8:58
@Trendfischer - For aSELECT, I think only 1 is needed. Abandon the6.
– Rick James
Nov 28 '18 at 15:56
Sorry, I did not explained it in detail: The 6 is for INSERTS/UPDATES, by waiting for synchronization on writing, we reduce the risk of a lacking synchronization in a multi node write environment. Sadly, this is node based, not cluster based. On ideal circumstances, this would delay the COMMIT until all nodes would have written the data. But galera just delays until all nodes state, they could write the data. Our intended purpose is, to slow down the writes, not the reads with SELECT, what a "1" would do and did not work for us.
– Trendfischer
Nov 30 '18 at 8:37
Maybe you should use 6 (or maybe 7) in front of writes and 1 in front of reads?
– Rick James
Nov 30 '18 at 22:03
Sorry, but we cannot use 1 in front of all reads. The "1" works similar to a LOCK TABLES READ on every write request. We have around 5000 reading queries per second, imagine all of them waiting for a single slow writing query. This is too slow and that's why I can't pin this as the accepted answer. This question is about how to simulate a slow synchronization to find critical reads (which should wait). Our problem is finding bugs, not fixing them.
– Trendfischer
Dec 3 '18 at 9:31
|
show 4 more comments
1
1
1
You need to include 1 because you are reading.
SET SESSION wsrep_sync_wait=1; -- or any odd number
SELECT ... ; -- this will wait if necessary
SET SESSION wsrep_sync_wait=0; -- (optional); to avoid slowing down subsequent SQL
answered Nov 26 '18 at 21:10
Rick JamesRick James
70.3k563104
You need to include 1 because you are reading.
SET SESSION wsrep_sync_wait=1; -- or any odd number
SELECT ... ; -- this will wait if necessary
SET SESSION wsrep_sync_wait=0; -- (optional); to avoid slowing down subsequent SQL
answered Nov 26 '18 at 21:10
Rick JamesRick James
70.3k563104
answered Nov 26 '18 at 21:10
Rick JamesRick James
70.3k563104
answered Nov 26 '18 at 21:10
Rick JamesRick James
70.3k563104
answered Nov 26 '18 at 21:10
Rick JamesRick James
70.3k563104
70.3k563104
You are right, we cannot avoid this with a 6. But we cannot do this for all selects. We already tried this with the unexpected effect bringing down the whole cluster. We seem to have too many write requests. So we still have to identify critical selects and need some way to simulate a slow cluster for testing. Using a value of 6 just slows down writing requests on heavier load.
– Trendfischer
Nov 28 '18 at 8:58
@Trendfischer - For aSELECT, I think only 1 is needed. Abandon the6.
– Rick James
Nov 28 '18 at 15:56
Sorry, I did not explained it in detail: The 6 is for INSERTS/UPDATES, by waiting for synchronization on writing, we reduce the risk of a lacking synchronization in a multi node write environment. Sadly, this is node based, not cluster based. On ideal circumstances, this would delay the COMMIT until all nodes would have written the data. But galera just delays until all nodes state, they could write the data. Our intended purpose is, to slow down the writes, not the reads with SELECT, what a "1" would do and did not work for us.
– Trendfischer
Nov 30 '18 at 8:37
Maybe you should use 6 (or maybe 7) in front of writes and 1 in front of reads?
– Rick James
Nov 30 '18 at 22:03
Sorry, but we cannot use 1 in front of all reads. The "1" works similar to a LOCK TABLES READ on every write request. We have around 5000 reading queries per second, imagine all of them waiting for a single slow writing query. This is too slow and that's why I can't pin this as the accepted answer. This question is about how to simulate a slow synchronization to find critical reads (which should wait). Our problem is finding bugs, not fixing them.
– Trendfischer
Dec 3 '18 at 9:31
|
show 4 more comments
You are right, we cannot avoid this with a 6. But we cannot do this for all selects. We already tried this with the unexpected effect bringing down the whole cluster. We seem to have too many write requests. So we still have to identify critical selects and need some way to simulate a slow cluster for testing. Using a value of 6 just slows down writing requests on heavier load.
– Trendfischer
Nov 28 '18 at 8:58
@Trendfischer - For aSELECT, I think only 1 is needed. Abandon the6.
– Rick James
Nov 28 '18 at 15:56
Sorry, I did not explained it in detail: The 6 is for INSERTS/UPDATES, by waiting for synchronization on writing, we reduce the risk of a lacking synchronization in a multi node write environment. Sadly, this is node based, not cluster based. On ideal circumstances, this would delay the COMMIT until all nodes would have written the data. But galera just delays until all nodes state, they could write the data. Our intended purpose is, to slow down the writes, not the reads with SELECT, what a "1" would do and did not work for us.
– Trendfischer
Nov 30 '18 at 8:37
Maybe you should use 6 (or maybe 7) in front of writes and 1 in front of reads?
– Rick James
Nov 30 '18 at 22:03
Sorry, but we cannot use 1 in front of all reads. The "1" works similar to a LOCK TABLES READ on every write request. We have around 5000 reading queries per second, imagine all of them waiting for a single slow writing query. This is too slow and that's why I can't pin this as the accepted answer. This question is about how to simulate a slow synchronization to find critical reads (which should wait). Our problem is finding bugs, not fixing them.
– Trendfischer
Dec 3 '18 at 9:31
You are right, we cannot avoid this with a 6. But we cannot do this for all selects. We already tried this with the unexpected effect bringing down the whole cluster. We seem to have too many write requests. So we still have to identify critical selects and need some way to simulate a slow cluster for testing. Using a value of 6 just slows down writing requests on heavier load.
– Trendfischer
Nov 28 '18 at 8:58
You are right, we cannot avoid this with a 6. But we cannot do this for all selects. We already tried this with the unexpected effect bringing down the whole cluster. We seem to have too many write requests. So we still have to identify critical selects and need some way to simulate a slow cluster for testing. Using a value of 6 just slows down writing requests on heavier load.
– Trendfischer
Nov 28 '18 at 8:58
@Trendfischer - For a
SELECT, I think only 1 is needed. Abandon the 6.– Rick James
Nov 28 '18 at 15:56
@Trendfischer - For a
SELECT, I think only 1 is needed. Abandon the 6.– Rick James
Nov 28 '18 at 15:56
Sorry, I did not explained it in detail: The 6 is for INSERTS/UPDATES, by waiting for synchronization on writing, we reduce the risk of a lacking synchronization in a multi node write environment. Sadly, this is node based, not cluster based. On ideal circumstances, this would delay the COMMIT until all nodes would have written the data. But galera just delays until all nodes state, they could write the data. Our intended purpose is, to slow down the writes, not the reads with SELECT, what a "1" would do and did not work for us.
– Trendfischer
Nov 30 '18 at 8:37
Sorry, I did not explained it in detail: The 6 is for INSERTS/UPDATES, by waiting for synchronization on writing, we reduce the risk of a lacking synchronization in a multi node write environment. Sadly, this is node based, not cluster based. On ideal circumstances, this would delay the COMMIT until all nodes would have written the data. But galera just delays until all nodes state, they could write the data. Our intended purpose is, to slow down the writes, not the reads with SELECT, what a "1" would do and did not work for us.
– Trendfischer
Nov 30 '18 at 8:37
Maybe you should use 6 (or maybe 7) in front of writes and 1 in front of reads?
– Rick James
Nov 30 '18 at 22:03
Maybe you should use 6 (or maybe 7) in front of writes and 1 in front of reads?
– Rick James
Nov 30 '18 at 22:03
Sorry, but we cannot use 1 in front of all reads. The "1" works similar to a LOCK TABLES READ on every write request. We have around 5000 reading queries per second, imagine all of them waiting for a single slow writing query. This is too slow and that's why I can't pin this as the accepted answer. This question is about how to simulate a slow synchronization to find critical reads (which should wait). Our problem is finding bugs, not fixing them.
– Trendfischer
Dec 3 '18 at 9:31
Sorry, but we cannot use 1 in front of all reads. The "1" works similar to a LOCK TABLES READ on every write request. We have around 5000 reading queries per second, imagine all of them waiting for a single slow writing query. This is too slow and that's why I can't pin this as the accepted answer. This question is about how to simulate a slow synchronization to find critical reads (which should wait). Our problem is finding bugs, not fixing them.
– Trendfischer
Dec 3 '18 at 9:31
|
show 4 more comments
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53477322%2fhow-to-simulate-slow-synchronisation-with-a-galera-cluster%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
