mysql (maria) memory calculation & usage creep
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have used the mysql memory calculator (http://www.mysqlcalculator.com) to estimate the memory usage of my dedicated mysql server with 8GB ram and 4CPUs.
These are the actual figures and the calculation of my usage:
binlog_cache_size 32.00 KB
innodb_buffer_pool_size 5120.00 MB
innodb_log_buffer_size 8.00 MB
join_buffer_size 1.00 MB
key_buffer_size 128.00 MB
max_connections 150
query_cache_size 0
read_buffer_size 128.00 KB
read_rnd_buffer_size 256.00 KB
sort_buffer_size 2.00 MB
thread_stack 292.00 KB
tmp_table_size 32.00 MB
=================================== ==========
Total Max Memory 5841.73 MB
=================================== ==========
Over a period of a week, the memory just keeps increasing way beyond the estimated calculations until it consumes pretty much all of the 8GB and never drops back until the engine is restarted.
So my guess is that there is some variable which is omitted from the calculation that is resulting in this over-usage.
Any suggestions on what I can do to troubleshoot this ?
mysql mariadb
asked Mar 6 at 13:35
crankshaftcrankshaft
11114
add a comment |
I have used the mysql memory calculator (http://www.mysqlcalculator.com) to estimate the memory usage of my dedicated mysql server with 8GB ram and 4CPUs.
These are the actual figures and the calculation of my usage:
binlog_cache_size 32.00 KB
innodb_buffer_pool_size 5120.00 MB
innodb_log_buffer_size 8.00 MB
join_buffer_size 1.00 MB
key_buffer_size 128.00 MB
max_connections 150
query_cache_size 0
read_buffer_size 128.00 KB
read_rnd_buffer_size 256.00 KB
sort_buffer_size 2.00 MB
thread_stack 292.00 KB
tmp_table_size 32.00 MB
=================================== ==========
Total Max Memory 5841.73 MB
=================================== ==========
Over a period of a week, the memory just keeps increasing way beyond the estimated calculations until it consumes pretty much all of the 8GB and never drops back until the engine is restarted.
So my guess is that there is some variable which is omitted from the calculation that is resulting in this over-usage.
Any suggestions on what I can do to troubleshoot this ?
mysql mariadb
asked Mar 6 at 13:35
crankshaftcrankshaft
11114
add a comment |
I have used the mysql memory calculator (http://www.mysqlcalculator.com) to estimate the memory usage of my dedicated mysql server with 8GB ram and 4CPUs.
These are the actual figures and the calculation of my usage:
binlog_cache_size 32.00 KB
innodb_buffer_pool_size 5120.00 MB
innodb_log_buffer_size 8.00 MB
join_buffer_size 1.00 MB
key_buffer_size 128.00 MB
max_connections 150
query_cache_size 0
read_buffer_size 128.00 KB
read_rnd_buffer_size 256.00 KB
sort_buffer_size 2.00 MB
thread_stack 292.00 KB
tmp_table_size 32.00 MB
=================================== ==========
Total Max Memory 5841.73 MB
=================================== ==========
Over a period of a week, the memory just keeps increasing way beyond the estimated calculations until it consumes pretty much all of the 8GB and never drops back until the engine is restarted.
So my guess is that there is some variable which is omitted from the calculation that is resulting in this over-usage.
Any suggestions on what I can do to troubleshoot this ?
mysql mariadb
asked Mar 6 at 13:35
crankshaftcrankshaft
11114
I have used the mysql memory calculator (http://www.mysqlcalculator.com) to estimate the memory usage of my dedicated mysql server with 8GB ram and 4CPUs.
These are the actual figures and the calculation of my usage:
binlog_cache_size 32.00 KB
innodb_buffer_pool_size 5120.00 MB
innodb_log_buffer_size 8.00 MB
join_buffer_size 1.00 MB
key_buffer_size 128.00 MB
max_connections 150
query_cache_size 0
read_buffer_size 128.00 KB
read_rnd_buffer_size 256.00 KB
sort_buffer_size 2.00 MB
thread_stack 292.00 KB
tmp_table_size 32.00 MB
=================================== ==========
Total Max Memory 5841.73 MB
=================================== ==========
Over a period of a week, the memory just keeps increasing way beyond the estimated calculations until it consumes pretty much all of the 8GB and never drops back until the engine is restarted.
So my guess is that there is some variable which is omitted from the calculation that is resulting in this over-usage.
Any suggestions on what I can do to troubleshoot this ?
mysql mariadb
mysql mariadb
asked Mar 6 at 13:35
crankshaftcrankshaft
11114
asked Mar 6 at 13:35
crankshaftcrankshaft
11114
asked Mar 6 at 13:35
crankshaftcrankshaft
11114
asked Mar 6 at 13:35
crankshaftcrankshaft
11114
asked Mar 6 at 13:35
crankshaftcrankshaft
11114
11114
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
OK, there appears to be an error with that calculator (http://www.mysqlcalculator.com/) .
tmp_table_size
Should be included in the per-connection calculations and not in the base-memory calculations.
The base-memory variables are those that are listed above the max_connections, and the per-connection variables are listed below.
This made a huge difference to the calculation and the reason why the actual usage did not match the calculations.
This is courtesy of: @Christopher Schultz
SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_additional_mem_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;
answered Mar 10 at 4:01
crankshaftcrankshaft
11114
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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
});
}
});
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%2fsuperuser.com%2fquestions%2f1411800%2fmysql-maria-memory-calculation-usage-creep%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
OK, there appears to be an error with that calculator (http://www.mysqlcalculator.com/) .
tmp_table_size
Should be included in the per-connection calculations and not in the base-memory calculations.
The base-memory variables are those that are listed above the max_connections, and the per-connection variables are listed below.
This made a huge difference to the calculation and the reason why the actual usage did not match the calculations.
This is courtesy of: @Christopher Schultz
SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_additional_mem_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;
answered Mar 10 at 4:01
crankshaftcrankshaft
11114
add a comment |
OK, there appears to be an error with that calculator (http://www.mysqlcalculator.com/) .
tmp_table_size
Should be included in the per-connection calculations and not in the base-memory calculations.
The base-memory variables are those that are listed above the max_connections, and the per-connection variables are listed below.
This made a huge difference to the calculation and the reason why the actual usage did not match the calculations.
This is courtesy of: @Christopher Schultz
SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_additional_mem_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;
answered Mar 10 at 4:01
crankshaftcrankshaft
11114
add a comment |
OK, there appears to be an error with that calculator (http://www.mysqlcalculator.com/) .
tmp_table_size
Should be included in the per-connection calculations and not in the base-memory calculations.
The base-memory variables are those that are listed above the max_connections, and the per-connection variables are listed below.
This made a huge difference to the calculation and the reason why the actual usage did not match the calculations.
This is courtesy of: @Christopher Schultz
SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_additional_mem_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;
answered Mar 10 at 4:01
crankshaftcrankshaft
11114
OK, there appears to be an error with that calculator (http://www.mysqlcalculator.com/) .
tmp_table_size
Should be included in the per-connection calculations and not in the base-memory calculations.
The base-memory variables are those that are listed above the max_connections, and the per-connection variables are listed below.
This made a huge difference to the calculation and the reason why the actual usage did not match the calculations.
This is courtesy of: @Christopher Schultz
SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_additional_mem_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;
answered Mar 10 at 4:01
crankshaftcrankshaft
11114
answered Mar 10 at 4:01
crankshaftcrankshaft
11114
answered Mar 10 at 4:01
crankshaftcrankshaft
11114
answered Mar 10 at 4:01
crankshaftcrankshaft
11114
11114
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1411800%2fmysql-maria-memory-calculation-usage-creep%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
