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.

SQL Problem

Discussion in 'Workarounds and Optimization' started by selvamurali, Jan 21, 2012.

  1. selvamurali

    selvamurali Active Member

    Joined:
    Jan 15, 2009
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Root Administrator
    dear Teamm


    now my sql server eating more than 100% from my CPU

    when i run mysqltuner its given following recommendation

    max_connections (> 100)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (> 96M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 80)


    already my.cnf value is

    [mysqld]
    innodb_buffer_pool_size = 500M
    max_heap_table_size = 64M
    query_cache_limit = 1M
    query_cache_size = 96M
    query_cache_type = 1
    table_cache = 128
    thread_cache_size = 4
    tmp_table_size = 96M


    now what can i do in my.cnf
    kindly help me
    tks
     
    #1 selvamurali, Jan 21, 2012
    Last edited: Jan 21, 2012
  2. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Please post your entire mysqltuner report, as it contains a lot of useful information. Its recommendations alone aren't very useful.
     
  3. selvamurali

    selvamurali Active Member

    Joined:
    Jan 15, 2009
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hi thanks for your reply

    here i given full recommendation

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate persistent connections to reduce connection usage
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    max_connections (> 100)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (> 96M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 80)


    now please tell me what to do?
    where i am confuse is >80 <28800
    pls guide me
    tks
     
  4. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hi Selvamurali,

    That's not the full report -- we need the rest of it. The very top of the report reads something like:
    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    We need everything from that point down.
     
  5. selvamurali

    selvamurali Active Member

    Joined:
    Jan 15, 2009
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Root Administrator
    sorry ya
    this is my full log...

    Code:
    ]# sudo ./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.0.92-community
    [!!] 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: 1G (Tables: 9347)
    [--] Data in InnoDB tables: 477M (Tables: 1803)
    [--] Data in MEMORY tables: 0B (Tables: 4)
    [!!] Total fragmented tables: 98
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 8h 25m 8s (87M q [430.053 qps], 534K conn, TX: 2B, RX: 2B)
    [--] Reads / Writes: 89% / 11%
    [--] Total buffers: 670.0M global + 2.7M per thread (100 max threads)
    [OK] Maximum possible memory usage: 938.7M (30% of installed RAM)
    [OK] Slow queries: 0% (1K/87M)
    [!!] Highest connection usage: 100%  (101/100)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/610.0M
    [OK] Key buffer hit rate: 96.9% (10B cached / 331M reads)
    [OK] Query cache efficiency: 89.9% (73M cached / 81M selects)
    [!!] Query cache prunes per day: 148657
    [OK] Sorts requiring temporary tables: 0% (467 temp sorts / 560K sorts)
    [!!] Joins performed without indexes: 11968
    [OK] Temporary tables created on disk: 23% (334K on disk / 1M total)
    [OK] Thread cache hit rate: 86% (71K created / 534K connections)
    [!!] Table cache hit rate: 0% (80 open / 918K opened)
    [OK] Open file limit used: 11% (117/1K)
    [OK] Table locks acquired immediately: 96% (11M immediate / 11M locks)
    [OK] InnoDB data size / buffer pool: 477.2M/500.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Reduce or eliminate persistent connections to reduce connection usage
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        max_connections (> 100)
        wait_timeout (< 28800)
        interactive_timeout (< 28800)
        query_cache_size (> 96M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        table_cache (> 80)

    -----------------
    Our Server is 2 Cloud Processor
    3 GB RAM
    200 GB Storage
     
  6. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hi Selvamurali,

    Thank you for the additional information. Here are my recommendations:
    1. Disable persistent connections in any application using them.
    2. Replace your my.cnf with the following:
      Code:
      [mysqld]
      max_connections = 150
      wait_timeout = 60
      
      thread_cache_size = 64
      table_cache = 8192
      query_cache_size = 96M
      tmp_table_size = 96M
      max_heap_table_size = 96M
      
      key_buffer_size = 64M
      
      innodb_buffer_pool_size = 512M
      innodb_flush_log_at_trx_commit = 2
      innodb_log_buffer_size = 4M
      
      sort_buffer_size = 256K
      join_buffer_size = 256K
      
    3. Restart MySQL
    4. Wait 24 Hours, post another mysqltuner report.

    Let me know if you'd like to know why I changed what I changed.

    If performance really seems to be suffering at any point during the 24 hour wait, please also post the results of this command:
    Code:
    vmstat 5 5
    Please post that inside of CODE tags, because the output is impossible to read otherwise. Thanks!
     
  7. selvamurali

    selvamurali Active Member

    Joined:
    Jan 15, 2009
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Root Administrator
    hi
    thank you. after 24 hours
    this is the mysqltuner.pl log


    VM State is

    but still server load not yet resolved
    tks
     
  8. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hi,

    Can you post "top" and "vmstat 5 5" again? Please make sure you're using [ CODE ] tags (remove spaces) instead of [ QUOTE ] tags. If you use the "Go Advanced" button when replying, the CODE tag looks like the # symbol in the button bar.

    Also, please post the results of:
    Code:
    service httpd status
    After you've done that, please disable persistent connections to eliminate that as a possible issue:
    1. Go this location in WHM: Main >> Service Configuration >> PHP Configuration Editor
    2. Click "Advanced Mode"
    3. Search for the setting called "mysql.allow_persistent", and set it to "Off"
    4. Save the changes
    5. Test your sites to make sure they are all working correctly.

    After all that, restart MySQL and keep an eye on your load. Run mysqltuner.pl from time to time, watching specifically for "Highest connection usage: 100% (151/150)". If you see that, go ahead and post the entire mysqltuner.pl report again.
     
  9. selvamurali

    selvamurali Active Member

    Joined:
    Jan 15, 2009
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hi thanks for your help. this is my top results in my server
    Code:
    root@vmclouds [~]# top
    top - 05:37:49 up 4 days, 15:07,  1 user,  load average: 14.35, 20.69, 19.68
    Tasks: 227 total,   8 running, 198 sleeping,  15 stopped,   6 zombie
    Cpu(s): 46.6%us, 36.6%sy,  0.0%ni, 12.3%id,  4.3%wa,  0.0%hi,  0.1%si,  0.0%st
    Mem:   3145904k total,  2737556k used,   408348k free,   202132k buffers
    Swap:  2096440k total,   501712k used,  1594728k free,  1095984k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
     3771 mysql     15   0 1310m 884m 4328 S 123.2 28.8   3454:05 mysqld
    30065 mytitbit  16   0 25436  12m 5732 S 18.9  0.4   0:00.10 php
    30066 mytitbit  15   0 25460  12m 5868 S 15.2  0.4   0:00.08 php
    30067 mytitbit  18   0 23528  10m 5340 R  7.6  0.3   0:00.04 php
    30068 mytitbit  17   0 22724 8940 4600 R  7.6  0.3   0:00.04 php
    30064 mytitbit  15   0 25460  12m 5864 S  3.8  0.4   0:00.08 php
        1 root      15   0  2176  580  548 S  0.0  0.0   0:00.85 init
        2 root      RT  -5     0    0    0 S  0.0  0.0   0:07.84 migration/0
        3 root      34  19     0    0    0 R  0.0  0.0   0:02.88 ksoftirqd/0
        4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0
        5 root      10  -5     0    0    0 S  0.0  0.0   0:00.05 events/0
        6 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 khelper
        7 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 kthread
        9 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 xenwatch
       10 root      10  -5     0    0    0 S  0.0  0.0   0:00.18 xenbus
       18 root      RT  -5     0    0    0 S  0.0  0.0   0:08.26 migration/1
       19 root      34  19     0    0    0 S  0.0  0.0   0:03.36 ksoftirqd/1
       20 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/1
       21 root      10  -5     0    0    0 S  0.0  0.0   0:00.04 events/1
       24 root      10  -5     0    0    0 S  0.0  0.0   0:00.02 kblockd/0
       25 root      10  -5     0    0    0 S  0.0  0.0   0:00.06 kblockd/1
       26 root      20  -5     0    0    0 S  0.0  0.0   0:00.00 cqueue/0
       27 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 cqueue/1
       31 root      20  -5     0    0    0 S  0.0  0.0   0:00.00 khubd
       33 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 kseriod
       97 root      20   0     0    0    0 S  0.0  0.0   0:00.16 khungtaskd
      100 root      10  -5     0    0    0 S  0.0  0.0   0:40.31 kswapd0
      101 root      11  -5     0    0    0 S  0.0  0.0   0:00.00 aio/0
      102 root      11  -5     0    0    0 S  0.0  0.0   0:00.00 aio/1
      243 root      20  -5     0    0    0 S  0.0  0.0   0:00.00 kpsmoused
      271 root      20  -5     0    0    0 S  0.0  0.0   0:00.00 kstriped
      284 root      10  -5     0    0    0 S  0.0  0.0   1:11.21 kjournald
      306 root      11  -5     0    0    0 S  0.0  0.0   0:00.48 kauditd
      334 root      12  -4  2408  368  364 S  0.0  0.0   0:00.03 udevd
      712 root      16  -5     0    0    0 S  0.0  0.0   0:00.00 kmpathd/0
      713 root      17  -5     0    0    0 S  0.0  0.0   0:00.00 kmpathd/1
      714 root      16  -5     0    0    0 S  0.0  0.0   0:00.00 kmpath_handlerd
    
    
    This is the Service Status for apache

    Code:
     service httpd status
    
                          Apache Server Status for localhost
    
       Server Version: Apache/2.2.21 (Unix) mod_ssl/2.2.21
              OpenSSL/0.9.8e-fips-rhel5 mod_auth_passthrough/2.1 mod_bwlimited/1.4
              FrontPage/5.0.2.2635 mod_jk/1.2.30
    
       Server Built: Oct 21 2011 17:03:42
         _________________________________________________________________
    
       Current Time: Wednesday, 25-Jan-2012 05:39:39 IST
       Restart Time: Friday, 20-Jan-2012 14:32:32 IST
       Parent Server Generation: 1
       Server uptime: 4 days 15 hours 7 minutes 7 seconds
       Total accesses: 1746095 - Total Traffic: 43.4 GB
       CPU Usage: u4.68 s5.8 cu540.1 cs0 - .138% CPU load
       4.36 requests/sec - 113.8 kB/second - 26.1 kB/request
       51 requests currently being processed, 19 idle workers
    
    CCCWWCWC_WCWWWWWWCWWWCW_WW.CW_._WWWW_W__WC__WW_W_W____W.W.W....W
    ..C.._W._...W_WWW....._..WW.W................C..................
    ................................................................
    ................................................................
    
       Scoreboard Key:
       "_" Waiting for Connection, "S" Starting up, "R" Reading Request,
       "W" Sending Reply, "K" Keepalive (read), "D" DNS Lookup,
       "C" Closing connection, "L" Logging, "G" Gracefully finishing,
       "I" Idle cleanup of worker, "." Open slot with no current process
    
     
  10. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hi,

    You're already using mpm_worker, correct? Did you configure Apache using just the cPanel GUI ( WHM>> Service Configuration >> Apache Configuration >> Global Configuration)? Or did you set other directives in an includes file? Please post the output of the command below, and any directives you might have set elsewhere.

    Code:
    grep -A 17 'These can be set in WHM' /usr/local/apache/conf/httpd.conf
    Are you using suPHP or fcgid? If you're using suPHP, would you consider switching to fcgid if I gave instructions?

    Which version of PHP are you using? (5.2, 5.3, etc)

    Are you only hosting one site on this server?
     
    #10 alphawolf50, Jan 24, 2012
    Last edited: Jan 24, 2012
  11. selvamurali

    selvamurali Active Member

    Joined:
    Jan 15, 2009
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Root Administrator
    hi


    i am using that grep command. that given following output

    Code:
    root@vmclouds [~]# grep -A 17 'These can be set in WHM' /usr/local/apache/conf/httpd.conf
    # These can be set in WHM under 'Apache Global Configuration'
    Timeout 300
    
    ServerSignature On
    
    
    
    <IfModule prefork.c>
    
    
    </IfModule>
    
    

    2. i am using only GUI Mode

    3. yes, i am using suPHP. but this server have 215 websites running here.
     
  12. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hmm... that command should have listed the settings without you having to give them to me manually. Can you find the values for these settings in WHM >> Service Configuration >> Apache Configuration >> Global Configuration:
    • StartServers
    • MinSpareServers
    • MaxSpareServers
    • ServerLimit
    • MaxClients
    • MaxRequestsPerChild
    • KeepAlive
    • KeepAliveTimeout
    • MaxKeepAliveRequests

    I thought maybe you only had one site on the server because all of the PHP processes that displayed in "top" were for user mytitbit. No matter -- fcgid handles high-concurrency better than suPHP anyway. When you get a chance, could you rebuild Apache with support for "Mod FCGID". You'll find it in the exhaustive list. Leave "Mod SuPHP" checked as well, as you'll want to be able to go back easily if there are any issues along the way. After Apache is done compiling, it will ask you which PHP 5 handler you'd like to use -- leave this on suPHP until I give instructions on setting up fcgid.

    While you're posting those variables above, could you also post a new mysqltuner so I can see if turning off persistent connections has made any difference at all? My next post is going to contain a lot of things to adjust, so I want to make sure I've got the most recent data.

    Is there any chance you can upgrade to 4GB of RAM without needing to get a different VPS?
     
  13. selvamurali

    selvamurali Active Member

    Joined:
    Jan 15, 2009
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Root Administrator
    ok give me some time will check it and let you know
    can you PM Your Mail ID
     
  14. selvamurali

    selvamurali Active Member

    Joined:
    Jan 15, 2009
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Root Administrator
    hi Today i start compile to install mod-fcgid.
    and what can i do it now?
    kindly help me
     
  15. shzzzzyyyy

    shzzzzyyyy Registered

    Joined:
    Apr 6, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    thx mate
    it's working
     
Loading...

Share This Page