Things to keep in mind while editing Mysql “my.cnf” file
Today I tried to boost mysql's(installed in my system Ubuntu 14.04) performance as I have many sites(PHP/Magento) installed in my system which relies on mysql as database server, but when I edited the lines like "query_cache_size", "query_cache_type" and other lines(only lines with numeric values which I increased), but instead of speeding it up, it decreased mysql's performance and also it timed-out when I tried to open multiple different pages in browser tabs.
So I need to know what to keep in mind when I edit the "my.cnf" the next time so it doesn't decrease performance but boost it.
What is the relation between those values like "query_cache_size", "max_connections" etc.? Are they inter-dependent ?
mysql performance performance-testing
asked Aug 13 '15 at 19:41
Vicky DevVicky Dev
1064
bumped to the homepage by Community♦ 17 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 |
Today I tried to boost mysql's(installed in my system Ubuntu 14.04) performance as I have many sites(PHP/Magento) installed in my system which relies on mysql as database server, but when I edited the lines like "query_cache_size", "query_cache_type" and other lines(only lines with numeric values which I increased), but instead of speeding it up, it decreased mysql's performance and also it timed-out when I tried to open multiple different pages in browser tabs.
So I need to know what to keep in mind when I edit the "my.cnf" the next time so it doesn't decrease performance but boost it.
What is the relation between those values like "query_cache_size", "max_connections" etc.? Are they inter-dependent ?
mysql performance performance-testing
asked Aug 13 '15 at 19:41
Vicky DevVicky Dev
1064
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Hmm... tricky without a lot more information. InnoDB or MyISAM. RAM, CPU and HDD config. OS? 32 or 64 bit? Activity GB/day read/write? To answer one of your questions, yes, they are independent. You say that you've changed something and the situation has disimproved - first thing to do is to bring back the status quo ante - i.e. restore your old my.cnf.
– Vérace
Aug 13 '15 at 19:48
RAM - 8GB, Engine - InnoDB, CPU - Core I3, 32bit system, Activity ? No idea exactly, but as far as I know there are 20(or more) sites installed in my system and I have only one mysql server which is used by all those sites and those sites are frequently accessed about 8 hours daily.
– Vicky Dev
Aug 13 '15 at 19:54
Also restored to the old default "my.cnf", but still need some suggestions(best with examples) as to how to increase the performance when I have that much sites installed relying on single mysql server.
– Vicky Dev
Aug 13 '15 at 19:59
I'm assuming you're using Linux? It has PAE (Physical Address Extension) - have you set that up? Why not use a 64 bit OS? Post your my.cnf here - some will have ideas. Anything special about your HDD config - RAID, SAN...?
– Vérace
Aug 13 '15 at 20:06
I assume you know that query_cache_size is a very special cache, it stores the results of queries based on the exact same SQL text This is not the general "page" cache for innodb (innodb_buffer_pool_size) Possibly by increasing many memory areas you used more RAM than you have ? (check "vmstat 1" swap in and out ) ?
– phil_w
Aug 14 '15 at 17:41
add a comment |
Today I tried to boost mysql's(installed in my system Ubuntu 14.04) performance as I have many sites(PHP/Magento) installed in my system which relies on mysql as database server, but when I edited the lines like "query_cache_size", "query_cache_type" and other lines(only lines with numeric values which I increased), but instead of speeding it up, it decreased mysql's performance and also it timed-out when I tried to open multiple different pages in browser tabs.
So I need to know what to keep in mind when I edit the "my.cnf" the next time so it doesn't decrease performance but boost it.
What is the relation between those values like "query_cache_size", "max_connections" etc.? Are they inter-dependent ?
mysql performance performance-testing
asked Aug 13 '15 at 19:41
Vicky DevVicky Dev
1064
Today I tried to boost mysql's(installed in my system Ubuntu 14.04) performance as I have many sites(PHP/Magento) installed in my system which relies on mysql as database server, but when I edited the lines like "query_cache_size", "query_cache_type" and other lines(only lines with numeric values which I increased), but instead of speeding it up, it decreased mysql's performance and also it timed-out when I tried to open multiple different pages in browser tabs.
So I need to know what to keep in mind when I edit the "my.cnf" the next time so it doesn't decrease performance but boost it.
What is the relation between those values like "query_cache_size", "max_connections" etc.? Are they inter-dependent ?
mysql performance performance-testing
mysql performance performance-testing
asked Aug 13 '15 at 19:41
Vicky DevVicky Dev
1064
asked Aug 13 '15 at 19:41
Vicky DevVicky Dev
1064
asked Aug 13 '15 at 19:41
Vicky DevVicky Dev
1064
asked Aug 13 '15 at 19:41
Vicky DevVicky Dev
1064
asked Aug 13 '15 at 19:41
Vicky DevVicky Dev
1064
1064
bumped to the homepage by Community♦ 17 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♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Hmm... tricky without a lot more information. InnoDB or MyISAM. RAM, CPU and HDD config. OS? 32 or 64 bit? Activity GB/day read/write? To answer one of your questions, yes, they are independent. You say that you've changed something and the situation has disimproved - first thing to do is to bring back the status quo ante - i.e. restore your old my.cnf.
– Vérace
Aug 13 '15 at 19:48
RAM - 8GB, Engine - InnoDB, CPU - Core I3, 32bit system, Activity ? No idea exactly, but as far as I know there are 20(or more) sites installed in my system and I have only one mysql server which is used by all those sites and those sites are frequently accessed about 8 hours daily.
– Vicky Dev
Aug 13 '15 at 19:54
Also restored to the old default "my.cnf", but still need some suggestions(best with examples) as to how to increase the performance when I have that much sites installed relying on single mysql server.
– Vicky Dev
Aug 13 '15 at 19:59
I'm assuming you're using Linux? It has PAE (Physical Address Extension) - have you set that up? Why not use a 64 bit OS? Post your my.cnf here - some will have ideas. Anything special about your HDD config - RAID, SAN...?
– Vérace
Aug 13 '15 at 20:06
I assume you know that query_cache_size is a very special cache, it stores the results of queries based on the exact same SQL text This is not the general "page" cache for innodb (innodb_buffer_pool_size) Possibly by increasing many memory areas you used more RAM than you have ? (check "vmstat 1" swap in and out ) ?
– phil_w
Aug 14 '15 at 17:41
add a comment |
Hmm... tricky without a lot more information. InnoDB or MyISAM. RAM, CPU and HDD config. OS? 32 or 64 bit? Activity GB/day read/write? To answer one of your questions, yes, they are independent. You say that you've changed something and the situation has disimproved - first thing to do is to bring back the status quo ante - i.e. restore your old my.cnf.
– Vérace
Aug 13 '15 at 19:48
RAM - 8GB, Engine - InnoDB, CPU - Core I3, 32bit system, Activity ? No idea exactly, but as far as I know there are 20(or more) sites installed in my system and I have only one mysql server which is used by all those sites and those sites are frequently accessed about 8 hours daily.
– Vicky Dev
Aug 13 '15 at 19:54
Also restored to the old default "my.cnf", but still need some suggestions(best with examples) as to how to increase the performance when I have that much sites installed relying on single mysql server.
– Vicky Dev
Aug 13 '15 at 19:59
I'm assuming you're using Linux? It has PAE (Physical Address Extension) - have you set that up? Why not use a 64 bit OS? Post your my.cnf here - some will have ideas. Anything special about your HDD config - RAID, SAN...?
– Vérace
Aug 13 '15 at 20:06
I assume you know that query_cache_size is a very special cache, it stores the results of queries based on the exact same SQL text This is not the general "page" cache for innodb (innodb_buffer_pool_size) Possibly by increasing many memory areas you used more RAM than you have ? (check "vmstat 1" swap in and out ) ?
– phil_w
Aug 14 '15 at 17:41
Hmm... tricky without a lot more information. InnoDB or MyISAM. RAM, CPU and HDD config. OS? 32 or 64 bit? Activity GB/day read/write? To answer one of your questions, yes, they are independent. You say that you've changed something and the situation has disimproved - first thing to do is to bring back the status quo ante - i.e. restore your old my.cnf.
– Vérace
Aug 13 '15 at 19:48
Hmm... tricky without a lot more information. InnoDB or MyISAM. RAM, CPU and HDD config. OS? 32 or 64 bit? Activity GB/day read/write? To answer one of your questions, yes, they are independent. You say that you've changed something and the situation has disimproved - first thing to do is to bring back the status quo ante - i.e. restore your old my.cnf.
– Vérace
Aug 13 '15 at 19:48
RAM - 8GB, Engine - InnoDB, CPU - Core I3, 32bit system, Activity ? No idea exactly, but as far as I know there are 20(or more) sites installed in my system and I have only one mysql server which is used by all those sites and those sites are frequently accessed about 8 hours daily.
– Vicky Dev
Aug 13 '15 at 19:54
RAM - 8GB, Engine - InnoDB, CPU - Core I3, 32bit system, Activity ? No idea exactly, but as far as I know there are 20(or more) sites installed in my system and I have only one mysql server which is used by all those sites and those sites are frequently accessed about 8 hours daily.
– Vicky Dev
Aug 13 '15 at 19:54
Also restored to the old default "my.cnf", but still need some suggestions(best with examples) as to how to increase the performance when I have that much sites installed relying on single mysql server.
– Vicky Dev
Aug 13 '15 at 19:59
Also restored to the old default "my.cnf", but still need some suggestions(best with examples) as to how to increase the performance when I have that much sites installed relying on single mysql server.
– Vicky Dev
Aug 13 '15 at 19:59
I'm assuming you're using Linux? It has PAE (Physical Address Extension) - have you set that up? Why not use a 64 bit OS? Post your my.cnf here - some will have ideas. Anything special about your HDD config - RAID, SAN...?
– Vérace
Aug 13 '15 at 20:06
I'm assuming you're using Linux? It has PAE (Physical Address Extension) - have you set that up? Why not use a 64 bit OS? Post your my.cnf here - some will have ideas. Anything special about your HDD config - RAID, SAN...?
– Vérace
Aug 13 '15 at 20:06
I assume you know that query_cache_size is a very special cache, it stores the results of queries based on the exact same SQL text This is not the general "page" cache for innodb (innodb_buffer_pool_size) Possibly by increasing many memory areas you used more RAM than you have ? (check "vmstat 1" swap in and out ) ?
– phil_w
Aug 14 '15 at 17:41
I assume you know that query_cache_size is a very special cache, it stores the results of queries based on the exact same SQL text This is not the general "page" cache for innodb (innodb_buffer_pool_size) Possibly by increasing many memory areas you used more RAM than you have ? (check "vmstat 1" swap in and out ) ?
– phil_w
Aug 14 '15 at 17:41
add a comment |
1 Answer
1
active
oldest
votes
There are a lot of inter-dependencies. Casual use of the Query cache can speed up some queries. Busy systems tend to be slowed down by the QC.
Overcommitting RAM is deadly for performance. Here is a discussion of that, and some of the settings.
32bit system with 8GB of RAM ! -- Is that a 32-bit OS? Or a 32-bit MySQL on a 64-bit OS? If the former, then 4GB is totally idle. In either case, MySQL is constrained as if it has only 4GB.
What kinds of things slowed down when you changed what settings? We can probably explain what happened if you give us some details.
answered Aug 17 '15 at 5:03
Rick JamesRick James
43.3k22259
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%2f110962%2fthings-to-keep-in-mind-while-editing-mysql-my-cnf-file%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
There are a lot of inter-dependencies. Casual use of the Query cache can speed up some queries. Busy systems tend to be slowed down by the QC.
Overcommitting RAM is deadly for performance. Here is a discussion of that, and some of the settings.
32bit system with 8GB of RAM ! -- Is that a 32-bit OS? Or a 32-bit MySQL on a 64-bit OS? If the former, then 4GB is totally idle. In either case, MySQL is constrained as if it has only 4GB.
What kinds of things slowed down when you changed what settings? We can probably explain what happened if you give us some details.
answered Aug 17 '15 at 5:03
Rick JamesRick James
43.3k22259
add a comment |
There are a lot of inter-dependencies. Casual use of the Query cache can speed up some queries. Busy systems tend to be slowed down by the QC.
Overcommitting RAM is deadly for performance. Here is a discussion of that, and some of the settings.
32bit system with 8GB of RAM ! -- Is that a 32-bit OS? Or a 32-bit MySQL on a 64-bit OS? If the former, then 4GB is totally idle. In either case, MySQL is constrained as if it has only 4GB.
What kinds of things slowed down when you changed what settings? We can probably explain what happened if you give us some details.
answered Aug 17 '15 at 5:03
Rick JamesRick James
43.3k22259
add a comment |
There are a lot of inter-dependencies. Casual use of the Query cache can speed up some queries. Busy systems tend to be slowed down by the QC.
Overcommitting RAM is deadly for performance. Here is a discussion of that, and some of the settings.
32bit system with 8GB of RAM ! -- Is that a 32-bit OS? Or a 32-bit MySQL on a 64-bit OS? If the former, then 4GB is totally idle. In either case, MySQL is constrained as if it has only 4GB.
What kinds of things slowed down when you changed what settings? We can probably explain what happened if you give us some details.
answered Aug 17 '15 at 5:03
Rick JamesRick James
43.3k22259
There are a lot of inter-dependencies. Casual use of the Query cache can speed up some queries. Busy systems tend to be slowed down by the QC.
Overcommitting RAM is deadly for performance. Here is a discussion of that, and some of the settings.
32bit system with 8GB of RAM ! -- Is that a 32-bit OS? Or a 32-bit MySQL on a 64-bit OS? If the former, then 4GB is totally idle. In either case, MySQL is constrained as if it has only 4GB.
What kinds of things slowed down when you changed what settings? We can probably explain what happened if you give us some details.
answered Aug 17 '15 at 5:03
Rick JamesRick James
43.3k22259
answered Aug 17 '15 at 5:03
Rick JamesRick James
43.3k22259
answered Aug 17 '15 at 5:03
Rick JamesRick James
43.3k22259
answered Aug 17 '15 at 5:03
Rick JamesRick James
43.3k22259
43.3k22259
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%2f110962%2fthings-to-keep-in-mind-while-editing-mysql-my-cnf-file%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
Hmm... tricky without a lot more information. InnoDB or MyISAM. RAM, CPU and HDD config. OS? 32 or 64 bit? Activity GB/day read/write? To answer one of your questions, yes, they are independent. You say that you've changed something and the situation has disimproved - first thing to do is to bring back the status quo ante - i.e. restore your old my.cnf.
– Vérace
Aug 13 '15 at 19:48
RAM - 8GB, Engine - InnoDB, CPU - Core I3, 32bit system, Activity ? No idea exactly, but as far as I know there are 20(or more) sites installed in my system and I have only one mysql server which is used by all those sites and those sites are frequently accessed about 8 hours daily.
– Vicky Dev
Aug 13 '15 at 19:54
Also restored to the old default "my.cnf", but still need some suggestions(best with examples) as to how to increase the performance when I have that much sites installed relying on single mysql server.
– Vicky Dev
Aug 13 '15 at 19:59
I'm assuming you're using Linux? It has PAE (Physical Address Extension) - have you set that up? Why not use a 64 bit OS? Post your my.cnf here - some will have ideas. Anything special about your HDD config - RAID, SAN...?
– Vérace
Aug 13 '15 at 20:06
I assume you know that query_cache_size is a very special cache, it stores the results of queries based on the exact same SQL text This is not the general "page" cache for innodb (innodb_buffer_pool_size) Possibly by increasing many memory areas you used more RAM than you have ? (check "vmstat 1" swap in and out ) ?
– phil_w
Aug 14 '15 at 17:41