The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL query_cache_size = 0 after WHM Upgrade

Discussion in 'General Discussion' started by yoachan, Aug 3, 2011.

  1. yoachan

    yoachan Well-Known Member

    Joined:
    Jan 10, 2010
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    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.
     
    #1 yoachan, Aug 3, 2011
    Last edited: Aug 3, 2011
  2. yoachan

    yoachan Well-Known Member

    Joined:
    Jan 10, 2010
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    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:
     
  3. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Check /etc/my.cnf file to see if it's listed in there for some reason as 0 size.
     
  4. yoachan

    yoachan Well-Known Member

    Joined:
    Jan 10, 2010
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    @Tristan: Thanks for your reply :)

    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....
     
  5. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    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.
     
  6. yoachan

    yoachan Well-Known Member

    Joined:
    Jan 10, 2010
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    @Tristan:
    Again, thank you for your reply

    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....

    Ok. thanks for telling me. will fix it.
     
    #6 yoachan, Aug 4, 2011
    Last edited: Aug 4, 2011
  7. yoachan

    yoachan Well-Known Member

    Joined:
    Jan 10, 2010
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    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....
     
  8. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
  9. yoachan

    yoachan Well-Known Member

    Joined:
    Jan 10, 2010
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    Code:
    #mysql_config --version
    5.0.92
    
    it's 64-bit.
    CENTOS 5.6 x86_64 using WHM 11.30.1 (build 4)
    (just upgrade WHM yesterday)
    fortunately 256MB is so much smaller than 18446744073709547520 :confused:
     
    #9 yoachan, Aug 4, 2011
    Last edited: Aug 4, 2011
  10. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    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.
     
  11. yoachan

    yoachan Well-Known Member

    Joined:
    Jan 10, 2010
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    Ok, Tristan. :)
    thanks for your help.
    Will post a ticket :)
     
  12. yoachan

    yoachan Well-Known Member

    Joined:
    Jan 10, 2010
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    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

    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
     
  13. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    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.
     
  14. yoachan

    yoachan Well-Known Member

    Joined:
    Jan 10, 2010
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    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 :)
     
Loading...

Share This Page