MySQL query_cache_size = 0 after WHM Upgrade

yoachan

Well-Known Member
Jan 10, 2010
65
0
56
Dear all,

this early morning I've finished upgrading my WHM from WHM 11.28.xx to WHM 11.30.1. :D :D :D
Just now, few hours after update, I noticed some changes in my Munin for mysql.
"Cache hits" (the purple lines) vanished, and only green lines left there.
And when I check from MySQL,

Code:
mysql> SHOW VARIABLES LIKE 'query_cache_%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 8388608 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
So that means that query_cache_size was reset to zero, and I need to reset it back to it's previous value.
But when I do set query set, I got nothing but 1 warning:

Code:
mysql> SET GLOBAL query_cache_size = 268435456;
Query OK, 0 rows affected, 1 warning (0.00 sec)
And my query_cache_size still stuck at zero.
If you ask me, "what's the warning?"
Then I'll have to ask, "Where to look?"

Does anyone know how to fix it??? :confused:

As separate topic (don't know if it's related),
phpMyAdmin is acting weird too.... At my first SHOW VARIABLES, request it returns an empty page.
The time I did SET command, it returns to phpMyAdmin's front panel (as if you click phpMyAdmin logo in the left frame). Finally I did everything via command line using "mysql -u root -p" command.

Thanks in advanced.

Regards,


Yoachan.
 
Last edited:

yoachan

Well-Known Member
Jan 10, 2010
65
0
56
Ok, I finally managed to see the warning. it's
Code:
Query cache failed to set size 268435456; new query cache size is 0
That's weird...... 268435456 is my previous value.... which is exactly 256MB.... :confused: :confused: :confused:
 

yoachan

Well-Known Member
Jan 10, 2010
65
0
56
@Tristan: Thanks for your reply :)

Check /etc/my.cnf file to see if it's listed in there for some reason as 0 size.
this is my /etc/my.cnf
Code:
# cat /etc/my.cnf
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
set-variable = max_connections=500
safe-show-database
local-infile=0
skip-locking
skip-innodb
query_cache_limit=8M
query_cache_size=256M
query_cache_type=1
local-infile=0
max_connections=500
max_user_connections=100
interactive_timeout=60
wait_timeout=60
connect_timeout=30
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=2048
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=100
thread_concurrency=4
myisam_sort_buffer_size=64M
open_files_limit=8192
server-id=1
ft_min_word_len=3

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
and it's my /root/.my.cnf
Code:
[mysqld]
tmp_table_size=64M
max_heap_table_size=64M
those two files' last update were more than five months ago....
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
Can you check if you have any other my.cnf files on the system?

Also, variables shouldn't be set in /root/.my.cnf file as that's just for the root password. Please take those values out of /root/.my.cnf file.
 

yoachan

Well-Known Member
Jan 10, 2010
65
0
56
@Tristan:
Again, thank you for your reply

Can you check if you have any other my.cnf files on the system?
Yes. it's /usr/local/cpanel/whostmgr/my.cnf which contains
Code:
[mysqld]
set-variable = max_connections=500
log-slow-queries
safe-show-database
other than this file, there are lots of my.cnf in each /var/home/virtfs/[username]/etc/my.cnf
the content of this files are exactly the same as /etc/my.cnf
there are some copies of /etc/my.cnf in my backup directories if that count....

Also, variables shouldn't be set in /root/.my.cnf file as that's just for the root password. Please take those values out of /root/.my.cnf file.
Ok. thanks for telling me. will fix it.
 
Last edited:

yoachan

Well-Known Member
Jan 10, 2010
65
0
56
As another note, I've just able to change query_cache_size to 16MB using

Code:
SET GLOBAL query_cache_size = 16777216;
but when I tried 32MB (and any number beyond 32MB) it failed...
I don't know the exact max number....
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator

yoachan

Well-Known Member
Jan 10, 2010
65
0
56
What is your MySQL version on the system?

Code:
mysql_config --version
Code:
#mysql_config --version
5.0.92
Next, what is your architecture type, 32-bit or 64-bit?
it's 64-bit.
CENTOS 5.6 x86_64 using WHM 11.30.1 (build 4)
(just upgrade WHM yesterday)
The MySQL 5.0 manual shows the maximum values for each architecture type:

MySQL :: MySQL 5.0 Reference Manual :: 5.1.3 Server System Variables
fortunately 256MB is so much smaller than 18446744073709547520 :confused:
 
Last edited:

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
This is a definite mystery then. Please open up a ticket in WHM > Support Center > Contact cPanel or using the link in my signature. We will likely have to strace restarting MySQL and seeing it reset the value to see what file is triggering it. I'm very curious what could be happening personally.
 

yoachan

Well-Known Member
Jan 10, 2010
65
0
56
This is a definite mystery then. Please open up a ticket in WHM > Support Center > Contact cPanel or using the link in my signature. We will likely have to strace restarting MySQL and seeing it reset the value to see what file is triggering it. I'm very curious what could be happening personally.
Ok, Tristan. :)
thanks for your help.
Will post a ticket :)
 

yoachan

Well-Known Member
Jan 10, 2010
65
0
56
Dear Tristan,

I think my case is solved.
Few minutes ago, I restarted mysql and at first it kind of "hung" for around 15 minutes.
At second try, mysql is restarted properly and when I checked "query_cache_size", it properly set to 256 MB.

Other than that I got this mail from cpanel saying

Executable:

/usr/local/cpanel/cpsrvd-ssl (deleted)

The file system shows this process is running an executable file that has been deleted. This typically happens when the original file has been replaced by a new file when the application is updated. To prevent this being reported again, restart the process that runs this excecutable file. See csf.conf and the PT_DELETED text for more information about the security implications of processes running deleted executable files.
I think when cpanel upgraded mysql, it still leave the old instance running, instead of turn it off first before replacing program... It got confused and confused me too... :eek:

regards,

Yoachan
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
That email wouldn't have been from cPanel as it mentions csf.conf, so it would have been an email from ConfigServer Security & Firewall (CSF) instead. Since that email is about an entirely different topic, I'd suggest opening a new thread over at the CSF forum at http://forum.configserver.com/ location. If this were a cPanel issue, I'd have suggested opening a new thread for the new issue here, but we wouldn't handle CSF-based alerts on the cPanel forum.

As for restarting MySQL, if you change variables, MySQL should be restarted when you do those changes. I didn't realize you weren't restarting MySQL when revising files (such as /root/.my.cnf) or trying to modify variables.
 

yoachan

Well-Known Member
Jan 10, 2010
65
0
56
As for restarting MySQL, if you change variables, MySQL should be restarted when you do those changes. I didn't realize you weren't restarting MySQL when revising files (such as /root/.my.cnf) or trying to modify variables.
nope. I changed nothing in my mysql config. In matter of fact as I said earlier it suddently went to zero after the upgrade. That's why I never invoke a restart request. I did this restart due to changes that I made to "heap", but as a bonus my "query_cache" value was corrected too....

Ok it's just an info. Thanks for everything :)