A recently added virtual server has dramatically increased MySQL use and so some configuration adjustments are needed. I ran mysqltuner.pl to get some clues as to what to adjust; some of the resulting recommendations are:
[!!] Query cache is disabled
[!!] Thread cache is disabled
[!!] InnoDB data size / buffer pool: 30.3M/8.0M
Add skip-bdb to MySQL configuration to disable BDB
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Variables to adjust:
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
thread_cache_size (start at 4)
innodb_buffer_pool_size (>= 30M)
I have tried (repeatedly) to make some edits using Webmin>Servers>MySQL Database Server>MySQL System Variables but changes are not preserved across MySQL server restarts and the file /etc/my.cnf remains unchanged. I have also tried editing /etc/my.cnf directly, and here things get stranger: some parameter changes take effect (and become visible in the gui, others are ignored.
Any help with getting this figured out is greatly appreciated!
Status:
Closed (fixed)
Comments
Submitted by JamieCameron on Fri, 03/25/2011 - 12:25 Comment #2
Yes, MySQL has multiple levels of variables - global, session and local.
I recommend editing /etc/my.cnf directly as suggested, and then restarting MySQL.
Perhaps I wasn't clear in my original problem description, so let me state explicitly:
I have tried editing /etc/my.cnf directly , then restarting MySQL. Most of my edits are ignored. What do I do now?
And - a separate issue, but fwiw, why is there a webmin screen for editing global MySQL parameters if it doesn't actually change /etc/my.cnf ?
I will enable support access, in case you have the opportunity to take a direct look.
Thanks for your help.
Submitted by JamieCameron on Fri, 03/25/2011 - 12:44 Comment #4
Are you using the "MySQL Server Configuration" page? That is the one that edits my.cnf .
The "MySQL System Variables" page only changes live settings, which will be lost on a MySQL restart.
While I appreciate the clarification regarding the "MySQL System Variables" (which maybe should be stated directly on that page?), that still does not address the explicitly stated problem:
When I directly edit /etc/my.cnf, then restart MySQL, my changes are ignored.
This is turning from an annoyance into a problem; I really need to get the MySQL server properly configured for the task load it now has to deal with.
Submitted by JamieCameron on Fri, 03/25/2011 - 14:58 Comment #6
When you say your changes are ignored, do you mean the my.cnf file is being reverted? Or are you seeing the old values on the "MySQL System Variables" page?
In my.cnf , are you making the changes in the [mysqld] section?