SQL query fills up RDS free storage space after upgrade to MySQL 5.7
After the upgrade to MySQL 5.7 the execution of a SQL query fills up the free storage space.
The upgrade was performed through the AWS console, selecting the automatic procedure to upgrade the RDS from MySQL 5.6.27 to MySQL 5.7.11. However, the same query that runs smoothly on mysql5.6 exhaust the available file system space on the mysql5.7 instance.
Researching on the problem, there was identified that the /rdsdbdata/db/innodb/ibtmp1 file was extended up to use all the free storage space. The behaviour is shown on the figures.
An additional comparison of the execution plan between mysql5.6 and mysql5.7 shows differences in the inclusion of 256 million records for 5.7, even after the optimizer_switch parameter was aligned between the two database versions.
Some evidence exhibits a relation to with utilisation of user defined variables, but it is not conclusive. The SELECT statement, for example, includes a @count := @count + 1 attribute.
Question: How to mitigate the fact that MySQL 5.7 changed the execution plan therefore filling up the available storage space of the RDS instance.
execution-plan disk-space storage mysql-5.7
asked Jul 11 '16 at 8:17
Luis BorbonLuis Borbon
211
add a comment |
After the upgrade to MySQL 5.7 the execution of a SQL query fills up the free storage space.
The upgrade was performed through the AWS console, selecting the automatic procedure to upgrade the RDS from MySQL 5.6.27 to MySQL 5.7.11. However, the same query that runs smoothly on mysql5.6 exhaust the available file system space on the mysql5.7 instance.
Researching on the problem, there was identified that the /rdsdbdata/db/innodb/ibtmp1 file was extended up to use all the free storage space. The behaviour is shown on the figures.
An additional comparison of the execution plan between mysql5.6 and mysql5.7 shows differences in the inclusion of 256 million records for 5.7, even after the optimizer_switch parameter was aligned between the two database versions.
Some evidence exhibits a relation to with utilisation of user defined variables, but it is not conclusive. The SELECT statement, for example, includes a @count := @count + 1 attribute.
Question: How to mitigate the fact that MySQL 5.7 changed the execution plan therefore filling up the available storage space of the RDS instance.
execution-plan disk-space storage mysql-5.7
asked Jul 11 '16 at 8:17
Luis BorbonLuis Borbon
211
You might want to refer this post dba.stackexchange.com/questions/136627/…
– The Coder
Jan 25 '17 at 6:11
"the same query" -- Let's see that query.
– Rick James
May 19 '18 at 1:05
add a comment |
After the upgrade to MySQL 5.7 the execution of a SQL query fills up the free storage space.
The upgrade was performed through the AWS console, selecting the automatic procedure to upgrade the RDS from MySQL 5.6.27 to MySQL 5.7.11. However, the same query that runs smoothly on mysql5.6 exhaust the available file system space on the mysql5.7 instance.
Researching on the problem, there was identified that the /rdsdbdata/db/innodb/ibtmp1 file was extended up to use all the free storage space. The behaviour is shown on the figures.
An additional comparison of the execution plan between mysql5.6 and mysql5.7 shows differences in the inclusion of 256 million records for 5.7, even after the optimizer_switch parameter was aligned between the two database versions.
Some evidence exhibits a relation to with utilisation of user defined variables, but it is not conclusive. The SELECT statement, for example, includes a @count := @count + 1 attribute.
Question: How to mitigate the fact that MySQL 5.7 changed the execution plan therefore filling up the available storage space of the RDS instance.
execution-plan disk-space storage mysql-5.7
asked Jul 11 '16 at 8:17
Luis BorbonLuis Borbon
211
After the upgrade to MySQL 5.7 the execution of a SQL query fills up the free storage space.
The upgrade was performed through the AWS console, selecting the automatic procedure to upgrade the RDS from MySQL 5.6.27 to MySQL 5.7.11. However, the same query that runs smoothly on mysql5.6 exhaust the available file system space on the mysql5.7 instance.
Researching on the problem, there was identified that the /rdsdbdata/db/innodb/ibtmp1 file was extended up to use all the free storage space. The behaviour is shown on the figures.
An additional comparison of the execution plan between mysql5.6 and mysql5.7 shows differences in the inclusion of 256 million records for 5.7, even after the optimizer_switch parameter was aligned between the two database versions.
Some evidence exhibits a relation to with utilisation of user defined variables, but it is not conclusive. The SELECT statement, for example, includes a @count := @count + 1 attribute.
Question: How to mitigate the fact that MySQL 5.7 changed the execution plan therefore filling up the available storage space of the RDS instance.
execution-plan disk-space storage mysql-5.7
execution-plan disk-space storage mysql-5.7
asked Jul 11 '16 at 8:17
Luis BorbonLuis Borbon
211
asked Jul 11 '16 at 8:17
Luis BorbonLuis Borbon
211
asked Jul 11 '16 at 8:17
Luis BorbonLuis Borbon
211
asked Jul 11 '16 at 8:17
Luis BorbonLuis Borbon
211
asked Jul 11 '16 at 8:17
Luis BorbonLuis Borbon
211
211
You might want to refer this post dba.stackexchange.com/questions/136627/…
– The Coder
Jan 25 '17 at 6:11
"the same query" -- Let's see that query.
– Rick James
May 19 '18 at 1:05
add a comment |
You might want to refer this post dba.stackexchange.com/questions/136627/…
– The Coder
Jan 25 '17 at 6:11
"the same query" -- Let's see that query.
– Rick James
May 19 '18 at 1:05
You might want to refer this post dba.stackexchange.com/questions/136627/…
– The Coder
Jan 25 '17 at 6:11
You might want to refer this post dba.stackexchange.com/questions/136627/…
– The Coder
Jan 25 '17 at 6:11
"the same query" -- Let's see that query.
– Rick James
May 19 '18 at 1:05
"the same query" -- Let's see that query.
– Rick James
May 19 '18 at 1:05
add a comment |
1 Answer
1
active
oldest
votes
obviously this is an old issue, but we just had a massive problem with this and I thought I'd share:
SELECT queries can write a lot of data to MySQL's temporary file if executed with a 'Using temporary; using filesort;' plan as shown by EXPLAIN EXTENDED.
MySQL can change its execution plan at the drop of a hat based on changing statistics: as a totally not-hypothetical example, it can go from a perfectly reasonable plan using an index to retrieve ~100,000 rows that takes ~15 seconds to a massive on-disk mergesort that takes 45 minutes total :facepalm:.
This temporary table space will be reclaimed automatically on server restart (at least, that's how it worked for us on an AWS RDS MySQL 5.7 instance).
The temp table is usually called ibtmp1, and you can get the size like so:
SELECT * FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%ibtmp%' G
I haven't yet found a way to configure RDS to limit the size of this file.
answered 2 mins ago
AlexAlex
101
New contributor
Alex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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%2f143465%2fsql-query-fills-up-rds-free-storage-space-after-upgrade-to-mysql-5-7%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
obviously this is an old issue, but we just had a massive problem with this and I thought I'd share:
SELECT queries can write a lot of data to MySQL's temporary file if executed with a 'Using temporary; using filesort;' plan as shown by EXPLAIN EXTENDED.
MySQL can change its execution plan at the drop of a hat based on changing statistics: as a totally not-hypothetical example, it can go from a perfectly reasonable plan using an index to retrieve ~100,000 rows that takes ~15 seconds to a massive on-disk mergesort that takes 45 minutes total :facepalm:.
This temporary table space will be reclaimed automatically on server restart (at least, that's how it worked for us on an AWS RDS MySQL 5.7 instance).
The temp table is usually called ibtmp1, and you can get the size like so:
SELECT * FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%ibtmp%' G
I haven't yet found a way to configure RDS to limit the size of this file.
answered 2 mins ago
AlexAlex
101
New contributor
Alex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
obviously this is an old issue, but we just had a massive problem with this and I thought I'd share:
SELECT queries can write a lot of data to MySQL's temporary file if executed with a 'Using temporary; using filesort;' plan as shown by EXPLAIN EXTENDED.
MySQL can change its execution plan at the drop of a hat based on changing statistics: as a totally not-hypothetical example, it can go from a perfectly reasonable plan using an index to retrieve ~100,000 rows that takes ~15 seconds to a massive on-disk mergesort that takes 45 minutes total :facepalm:.
This temporary table space will be reclaimed automatically on server restart (at least, that's how it worked for us on an AWS RDS MySQL 5.7 instance).
The temp table is usually called ibtmp1, and you can get the size like so:
SELECT * FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%ibtmp%' G
I haven't yet found a way to configure RDS to limit the size of this file.
answered 2 mins ago
AlexAlex
101
New contributor
Alex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
obviously this is an old issue, but we just had a massive problem with this and I thought I'd share:
SELECT queries can write a lot of data to MySQL's temporary file if executed with a 'Using temporary; using filesort;' plan as shown by EXPLAIN EXTENDED.
MySQL can change its execution plan at the drop of a hat based on changing statistics: as a totally not-hypothetical example, it can go from a perfectly reasonable plan using an index to retrieve ~100,000 rows that takes ~15 seconds to a massive on-disk mergesort that takes 45 minutes total :facepalm:.
This temporary table space will be reclaimed automatically on server restart (at least, that's how it worked for us on an AWS RDS MySQL 5.7 instance).
The temp table is usually called ibtmp1, and you can get the size like so:
SELECT * FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%ibtmp%' G
I haven't yet found a way to configure RDS to limit the size of this file.
answered 2 mins ago
AlexAlex
101
New contributor
Alex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
obviously this is an old issue, but we just had a massive problem with this and I thought I'd share:
SELECT queries can write a lot of data to MySQL's temporary file if executed with a 'Using temporary; using filesort;' plan as shown by EXPLAIN EXTENDED.
MySQL can change its execution plan at the drop of a hat based on changing statistics: as a totally not-hypothetical example, it can go from a perfectly reasonable plan using an index to retrieve ~100,000 rows that takes ~15 seconds to a massive on-disk mergesort that takes 45 minutes total :facepalm:.
This temporary table space will be reclaimed automatically on server restart (at least, that's how it worked for us on an AWS RDS MySQL 5.7 instance).
The temp table is usually called ibtmp1, and you can get the size like so:
SELECT * FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%ibtmp%' G
I haven't yet found a way to configure RDS to limit the size of this file.
answered 2 mins ago
AlexAlex
101
New contributor
Alex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 2 mins ago
AlexAlex
101
New contributor
Alex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 2 mins ago
AlexAlex
101
answered 2 mins ago
AlexAlex
101
101
New contributor
Alex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Alex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Alex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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%2f143465%2fsql-query-fills-up-rds-free-storage-space-after-upgrade-to-mysql-5-7%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
You might want to refer this post dba.stackexchange.com/questions/136627/…
– The Coder
Jan 25 '17 at 6:11
"the same query" -- Let's see that query.
– Rick James
May 19 '18 at 1:05