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.

Higher MySQL RAM usage, need to optimize

Discussion in 'Workarounds and Optimization' started by cbu, Feb 12, 2014.

  1. cbu

    cbu Active Member

    Joined:
    Aug 17, 2013
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hello all!

    I have VPS with running 2.5GB RAM, Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz (cache 15360 KB). It front end Nginx and backend Apache. This VPS run two wordpress sites. First site page preview 3,000-3,500 (daily) second site 500-600 (daily). First site mysql database size is 56.1MB and 2nd site MySQL database size is 10.3MB.

    Code:
    root@host [~]# mysql --version
    mysql  Ver 14.14 Distrib 5.5.35, for Linux (i686) using readline 5.1

    When I'm running ps -aux command, I can see higher RAM usage


    Code:
    root@host [~]# ps -aux
    Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ
    
    USER   PID    %CPU %MEM    VSZ   RSS      TTY   STAT START   TIME COMMAND
    mysql  11980  0.4       3.5   755956 93016   ?        Sl   07:50     0:05  /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql -

    When I'm running free -m I receive following output.

    Code:
    root@host [~]# free -m
                 total       used       free     shared    buffers     cached
    Mem:          2560       1927        632          0          0       1516
    -/+ buffers/cache:        410       2149
    Swap:            0          0          0

    When I'm running MySQL tuner, I get following output.

    Code:
    root@host [~]# ./mysqltuner.pl
    
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.35-cll
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 66M (Tables: 239)
    [--] Data in InnoDB tables: 4M (Tables: 28)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 7
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 21m 18s (22K q [17.603 qps], 380 conn, TX: 97M, RX: 3M)
    [--] Reads / Writes: 78% / 22%
    [--] Total buffers: 608.0M global + 3.6M per thread (50 max threads)
    [OK] Maximum possible memory usage: 786.1M (30% of installed RAM)
    [OK] Slow queries: 0% (0/22K)
    [OK] Highest usage of available connections: 10% (5/50)
    [OK] Key buffer size / total MyISAM indexes: 16.0M/12.9M
    [!!] Key buffer hit rate: 93.0% (320K cached / 22K reads)
    [OK] Query cache efficiency: 59.8% (11K cached / 18K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
    [!!] Joins performed without indexes: 74
    [OK] Temporary tables created on disk: 19% (192 on disk / 1K total)
    [OK] Thread cache hit rate: 98% (5 created / 380 connections)
    [!!] Table cache hit rate: 2% (96 open / 4K opened)
    [OK] Open file limit used: 9% (177/1K)
    [OK] Table locks acquired immediately: 100% (10K immediate / 10K locks)
    [OK] InnoDB data size / buffer pool: 4.9M/384.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        join_buffer_size (> 1.0M, or always use indexes with joins)
        table_cache (> 96)
    
    root@host [~]#
    This is my MySQL configuration file (my.cnf):- /http://pastebin.com/x5FwuKiK

    How do I reduce MySQL memory usage?

    Thanks!
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    replace my.cnf with this

    [mysqld]
    default-storage-engine = MyISAM
    local-infile = 0

    max_connections = 50
    wait_timeout = 30
    connect_timeout = 10

    thread_cache_size = 16
    table_open_cache = 700

    key_buffer_size = 30M

    join_buffer_size = 512K
    sort_buffer_size=256K

    query_cache_type = 1
    query_cache_size = 30M
    query_cache_limit = 1M

    tmp_table_size = 30M
    max_heap_table_size = 30M

    open_files_limit=5000

    innodb_buffer_pool_size = 30M
    innodb_file_per_table = 1
    innodb_stats_on_metadata=0

    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1


    memory savings will be huge, without loosing anything from the performance
     
  3. cbu

    cbu Active Member

    Joined:
    Aug 17, 2013
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Thank you thinkbot your support.

    Now it giving me following recommendation. Could you please tell me how to correct that issue? Also is it OK use "Maximum possible memory usage: 201.6M (7% of installed RAM)"

    Could you please tell me how do I adjust Maximum possible memory usage?

    This is my MySQLTuner log.


     
    #3 cbu, Feb 13, 2014
    Last edited: Feb 13, 2014
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    There is nothing to adjust now, it just tells you that your maximum memory usage for MySQL can be 201.6M, meaning 7% of installed RAM

    previously it was 786.1MB
    [OK] Maximum possible memory usage: 786.1M (30% of installed RAM)

    of course it's not very accurate, its just a pointer, calculating mysql memory is bit more complicated
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  6. cbu

    cbu Active Member

    Joined:
    Aug 17, 2013
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Now I'm using thinkbot updated configuration.

    Looks like table_cache getting lower in every minutes. 20 minutes ago it's ratio is 98%, but now it's reduced to 10%. PHPMyAdmin advice to "Flushing the query cache". Here's it advice,


    This is the MySQL Tuner result.


    How do I fix this issue?

    - - - Updated - - -

    Thank you Michael. I use MySQLTuner. So if I use both of these scripts same time, it will cause any issue?


    - - - Updated - - -

    Thank you Michael. I use MySQLTuner. So if I use both of these scripts same time, it will cause any issue?


     
    #6 cbu, Feb 13, 2014
    Last edited: Feb 13, 2014
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hey, this is not an issue, you got 239 MyiSAM tables, they all fit in table_open_cache 700 buffer, there are also some temporary tables created along the way
    It's very good
     
  8. cbu

    cbu Active Member

    Joined:
    Aug 17, 2013
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Thank thinkbot.
     
  9. cbu

    cbu Active Member

    Joined:
    Aug 17, 2013
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Randomly I can see several MySQL server has gone away for query MySQL error. How do I fix it?

    eg:-
     
  10. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    this particular query should be immediate, probably query before that one took longer than wait_timeout allowed
    checkout slow query log
     
  11. cbu

    cbu Active Member

    Joined:
    Aug 17, 2013
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    After the 24 hours, I get following result.

    I changed
    max_connections = 80
    query_cache_size = 40M

    But no idea about following things, what could be the proper value for them?

    wait_timeout (< 30)
    interactive_timeout (< 28800)


    When I run MySQL tuning primer it gives following output.


     
  12. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    you dont have to increase all the values recommended by this script, those are only pointers, you need to understand the impact of the change before making the actuall change

    Please review your slow queries log, since you probably have some queries that stuck your server, and this is why your connections limit gets to max

    you dont have to increase or change those values
    wait_timeout (< 30)
    interactive_timeout (< 28800)
     
    #12 thinkbot, Feb 15, 2014
    Last edited: Feb 15, 2014
  13. cbu

    cbu Active Member

    Joined:
    Aug 17, 2013
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Thank you thinkbot your support. I checked the slow query log, it has lots of things.


    Most of the log query shows wp options table. Yes there are some wordpress plugin too. Specially security plugin (eg:-Wordfence Security)

    - - - Updated - - -

    Thanks again thinkbot. I just increased table_open_cache. Looks like it improved the page loading time.

    Are there any way to periodically delete unwanted table cache? I think only way to reduce temporary tables is restart the SQL server. Am I correct? I can't restart SQL service regularly.
     
  14. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    "Are there any way to periodically delete unwanted table cache? I think only way to reduce temporary tables is restart the SQL server. Am I correct? I can't restart SQL service regularly."

    You don't understand, there is nothing to deltee there
    to reduce temporary tables on disk, you have to optimize/rewrite slow queries

    First check what queries takes the most time, to do that review slow query log
    then you can check why they take so much time, and optimize them

    and install munin plugin in WHM, so you can have better view on server resources usage
     
  15. cbu

    cbu Active Member

    Joined:
    Aug 17, 2013
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Thanks again. Yes, I have munin server monitor plugin. Again thank you so much for your help. :D
     
  16. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    if you have, you can post here please images for CPU, LOAD, RAM ? it would show us more the difference and current load
     
  17. cbu

    cbu Active Member

    Joined:
    Aug 17, 2013
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Thank you again think bot your support. These are the MUNIN screenshots. Looks like slow mysql queries started after tweak the my.cnf file. Is it its configuration issue or just a normal?

    MySQL
    2.png
    1.png

    RAM.
    11.png

    Load.
    10.png

    CPU
    9.png


     
    #17 cbu, Feb 16, 2014
    Last edited: Feb 16, 2014
  18. cbu

    cbu Active Member

    Joined:
    Aug 17, 2013
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Is it ok if I adjust long_query_time=0.1 value. Because I can see lots of WordPress database error MySQL server has gone away for query errors in WordPress debug.
     
    #18 cbu, Feb 16, 2014
    Last edited: Feb 16, 2014
  19. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    long_query_time=0.1
    means only to mark queries as slow when they take more than 0,1s

    so it doesn't make any difference, it's just for logging queries, so we know which ones are slower

    the best thing you can do now, is make a reeview of slow queries
    cd /root
    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest
    ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

    and then copy contents of slow.txt here in [code brackets, or on some file upload site
     
  20. cbu

    cbu Active Member

    Joined:
    Aug 17, 2013
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    I just installed that package. How much time should I wait for take that report?
     
Loading...

Share This Page