MySQL 5.7 UPDATE slow due to not using index
We have an issue similar to this. Only, the solution there won't work for us as:
- We don't use multi-table
JOINS, butUPDATE … WHERE `userid` IN (<list of 10k ids>);
- We currently "only" have the issue on one replication slave, so we can't change the statement there.
Forcing the update to use an index using … USE INDEX (PRIMARY) SET … would help, according to EXPLAIN.
Is there any way using variables to get similar behaviour? Or is our best approach to change the application to use explicit index hints & rebuild the slave?
replication index update mysql-5.7
asked Jul 25 '18 at 14:47
DaPedaDaPeda
1
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
We have an issue similar to this. Only, the solution there won't work for us as:
- We don't use multi-table
JOINS, butUPDATE … WHERE `userid` IN (<list of 10k ids>);
- We currently "only" have the issue on one replication slave, so we can't change the statement there.
Forcing the update to use an index using … USE INDEX (PRIMARY) SET … would help, according to EXPLAIN.
Is there any way using variables to get similar behaviour? Or is our best approach to change the application to use explicit index hints & rebuild the slave?
replication index update mysql-5.7
asked Jul 25 '18 at 14:47
DaPedaDaPeda
1
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Have you tried replacing the IN list with a table holding the 10k IDs?
– Andriy M
Jul 25 '18 at 14:56
> We currently "only" have the issue on one replication slave, so we can't change the statement there. Would if we could…
– DaPeda
Jul 25 '18 at 14:57
Sorry if this is a stupid question but – does that circumstance prevent you from even trying a rewritten version of the statement to see if the index would be used?
– Andriy M
Jul 25 '18 at 15:04
Only the fact that it's a read-only slave & I can't create new tables for a sub-select. Also, it won't solve the immediate problem of our replication being 2 days behind by now.
– DaPeda
Jul 25 '18 at 15:12
add a comment |
We have an issue similar to this. Only, the solution there won't work for us as:
- We don't use multi-table
JOINS, butUPDATE … WHERE `userid` IN (<list of 10k ids>);
- We currently "only" have the issue on one replication slave, so we can't change the statement there.
Forcing the update to use an index using … USE INDEX (PRIMARY) SET … would help, according to EXPLAIN.
Is there any way using variables to get similar behaviour? Or is our best approach to change the application to use explicit index hints & rebuild the slave?
replication index update mysql-5.7
asked Jul 25 '18 at 14:47
DaPedaDaPeda
1
We have an issue similar to this. Only, the solution there won't work for us as:
- We don't use multi-table
JOINS, butUPDATE … WHERE `userid` IN (<list of 10k ids>);
- We currently "only" have the issue on one replication slave, so we can't change the statement there.
Forcing the update to use an index using … USE INDEX (PRIMARY) SET … would help, according to EXPLAIN.
Is there any way using variables to get similar behaviour? Or is our best approach to change the application to use explicit index hints & rebuild the slave?
replication index update mysql-5.7
replication index update mysql-5.7
asked Jul 25 '18 at 14:47
DaPedaDaPeda
1
asked Jul 25 '18 at 14:47
DaPedaDaPeda
1
asked Jul 25 '18 at 14:47
DaPedaDaPeda
1
asked Jul 25 '18 at 14:47
DaPedaDaPeda
1
asked Jul 25 '18 at 14:47
DaPedaDaPeda
1
1
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Have you tried replacing the IN list with a table holding the 10k IDs?
– Andriy M
Jul 25 '18 at 14:56
> We currently "only" have the issue on one replication slave, so we can't change the statement there. Would if we could…
– DaPeda
Jul 25 '18 at 14:57
Sorry if this is a stupid question but – does that circumstance prevent you from even trying a rewritten version of the statement to see if the index would be used?
– Andriy M
Jul 25 '18 at 15:04
Only the fact that it's a read-only slave & I can't create new tables for a sub-select. Also, it won't solve the immediate problem of our replication being 2 days behind by now.
– DaPeda
Jul 25 '18 at 15:12
add a comment |
Have you tried replacing the IN list with a table holding the 10k IDs?
– Andriy M
Jul 25 '18 at 14:56
> We currently "only" have the issue on one replication slave, so we can't change the statement there. Would if we could…
– DaPeda
Jul 25 '18 at 14:57
Sorry if this is a stupid question but – does that circumstance prevent you from even trying a rewritten version of the statement to see if the index would be used?
– Andriy M
Jul 25 '18 at 15:04
Only the fact that it's a read-only slave & I can't create new tables for a sub-select. Also, it won't solve the immediate problem of our replication being 2 days behind by now.
– DaPeda
Jul 25 '18 at 15:12
Have you tried replacing the IN list with a table holding the 10k IDs?
– Andriy M
Jul 25 '18 at 14:56
Have you tried replacing the IN list with a table holding the 10k IDs?
– Andriy M
Jul 25 '18 at 14:56
> We currently "only" have the issue on one replication slave, so we can't change the statement there. Would if we could…
– DaPeda
Jul 25 '18 at 14:57
> We currently "only" have the issue on one replication slave, so we can't change the statement there. Would if we could…
– DaPeda
Jul 25 '18 at 14:57
Sorry if this is a stupid question but – does that circumstance prevent you from even trying a rewritten version of the statement to see if the index would be used?
– Andriy M
Jul 25 '18 at 15:04
Sorry if this is a stupid question but – does that circumstance prevent you from even trying a rewritten version of the statement to see if the index would be used?
– Andriy M
Jul 25 '18 at 15:04
Only the fact that it's a read-only slave & I can't create new tables for a sub-select. Also, it won't solve the immediate problem of our replication being 2 days behind by now.
– DaPeda
Jul 25 '18 at 15:12
Only the fact that it's a read-only slave & I can't create new tables for a sub-select. Also, it won't solve the immediate problem of our replication being 2 days behind by now.
– DaPeda
Jul 25 '18 at 15:12
add a comment |
1 Answer
1
active
oldest
votes
So, for others, we found a solution.
TL;DR:
SET GLOBAL range_optimizer_max_mem_size=0;
and stop/start the slave.
According to the documentation on limiting range optimization memory usage, the default maximum is 8 MiB. Also, there is this:
For a simple query such as the following, where there is one candidate key for the range access method, each predicate combined with OR uses approximately 230 bytes:
SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
[…]
For a query with IN() predicates:
SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);
Each literal value in an IN() list counts as a predicate combined with OR.
So that would mean our UPDATE with 10k simple IDs would need 10.000 * 230 Bytes = approx. 2,2 MiB, well within the default memory size. Even with the pre-5.7.11 requirement of 700 Bytes / predicate we'd be save.
Only, apparently the documentation is wrong, because as soon as we set the value of range_optimizer_max_mem_size to 0 (means No Limit), the replication delay went down from 3 hours to 0 in a span of less than 15 minutes.
answered Jul 30 '18 at 8:45
DaPedaDaPeda
1
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
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%2fdba.stackexchange.com%2fquestions%2f213203%2fmysql-5-7-update-slow-due-to-not-using-index%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
So, for others, we found a solution.
TL;DR:
SET GLOBAL range_optimizer_max_mem_size=0;
and stop/start the slave.
According to the documentation on limiting range optimization memory usage, the default maximum is 8 MiB. Also, there is this:
For a simple query such as the following, where there is one candidate key for the range access method, each predicate combined with OR uses approximately 230 bytes:
SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
[…]
For a query with IN() predicates:
SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);
Each literal value in an IN() list counts as a predicate combined with OR.
So that would mean our UPDATE with 10k simple IDs would need 10.000 * 230 Bytes = approx. 2,2 MiB, well within the default memory size. Even with the pre-5.7.11 requirement of 700 Bytes / predicate we'd be save.
Only, apparently the documentation is wrong, because as soon as we set the value of range_optimizer_max_mem_size to 0 (means No Limit), the replication delay went down from 3 hours to 0 in a span of less than 15 minutes.
answered Jul 30 '18 at 8:45
DaPedaDaPeda
1
add a comment |
So, for others, we found a solution.
TL;DR:
SET GLOBAL range_optimizer_max_mem_size=0;
and stop/start the slave.
According to the documentation on limiting range optimization memory usage, the default maximum is 8 MiB. Also, there is this:
For a simple query such as the following, where there is one candidate key for the range access method, each predicate combined with OR uses approximately 230 bytes:
SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
[…]
For a query with IN() predicates:
SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);
Each literal value in an IN() list counts as a predicate combined with OR.
So that would mean our UPDATE with 10k simple IDs would need 10.000 * 230 Bytes = approx. 2,2 MiB, well within the default memory size. Even with the pre-5.7.11 requirement of 700 Bytes / predicate we'd be save.
Only, apparently the documentation is wrong, because as soon as we set the value of range_optimizer_max_mem_size to 0 (means No Limit), the replication delay went down from 3 hours to 0 in a span of less than 15 minutes.
answered Jul 30 '18 at 8:45
DaPedaDaPeda
1
add a comment |
So, for others, we found a solution.
TL;DR:
SET GLOBAL range_optimizer_max_mem_size=0;
and stop/start the slave.
According to the documentation on limiting range optimization memory usage, the default maximum is 8 MiB. Also, there is this:
For a simple query such as the following, where there is one candidate key for the range access method, each predicate combined with OR uses approximately 230 bytes:
SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
[…]
For a query with IN() predicates:
SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);
Each literal value in an IN() list counts as a predicate combined with OR.
So that would mean our UPDATE with 10k simple IDs would need 10.000 * 230 Bytes = approx. 2,2 MiB, well within the default memory size. Even with the pre-5.7.11 requirement of 700 Bytes / predicate we'd be save.
Only, apparently the documentation is wrong, because as soon as we set the value of range_optimizer_max_mem_size to 0 (means No Limit), the replication delay went down from 3 hours to 0 in a span of less than 15 minutes.
answered Jul 30 '18 at 8:45
DaPedaDaPeda
1
So, for others, we found a solution.
TL;DR:
SET GLOBAL range_optimizer_max_mem_size=0;
and stop/start the slave.
According to the documentation on limiting range optimization memory usage, the default maximum is 8 MiB. Also, there is this:
For a simple query such as the following, where there is one candidate key for the range access method, each predicate combined with OR uses approximately 230 bytes:
SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
[…]
For a query with IN() predicates:
SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);
Each literal value in an IN() list counts as a predicate combined with OR.
So that would mean our UPDATE with 10k simple IDs would need 10.000 * 230 Bytes = approx. 2,2 MiB, well within the default memory size. Even with the pre-5.7.11 requirement of 700 Bytes / predicate we'd be save.
Only, apparently the documentation is wrong, because as soon as we set the value of range_optimizer_max_mem_size to 0 (means No Limit), the replication delay went down from 3 hours to 0 in a span of less than 15 minutes.
answered Jul 30 '18 at 8:45
DaPedaDaPeda
1
answered Jul 30 '18 at 8:45
DaPedaDaPeda
1
answered Jul 30 '18 at 8:45
DaPedaDaPeda
1
answered Jul 30 '18 at 8:45
DaPedaDaPeda
1
1
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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.
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%2fdba.stackexchange.com%2fquestions%2f213203%2fmysql-5-7-update-slow-due-to-not-using-index%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
Have you tried replacing the IN list with a table holding the 10k IDs?
– Andriy M
Jul 25 '18 at 14:56
> We currently "only" have the issue on one replication slave, so we can't change the statement there. Would if we could…
– DaPeda
Jul 25 '18 at 14:57
Sorry if this is a stupid question but – does that circumstance prevent you from even trying a rewritten version of the statement to see if the index would be used?
– Andriy M
Jul 25 '18 at 15:04
Only the fact that it's a read-only slave & I can't create new tables for a sub-select. Also, it won't solve the immediate problem of our replication being 2 days behind by now.
– DaPeda
Jul 25 '18 at 15:12