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.

optimizing server help

Discussion in 'Workarounds and Optimization' started by thcpanel, Oct 23, 2010.

  1. thcpanel

    thcpanel Registered

    Joined:
    Oct 23, 2010
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Hello everybody my current server for the past two days, helped increase the load by 200%.

    I come here asking for help to locate what might be the cause.
    Currently I use the following settings.

    i7 920 + 12GB DDR3 + HD 300GB + 1gbit
    Apache+Nginx+Cpanel

    http://i56.tinypic.com/28at7v7.png

    I thank you to everyone who can help.
     
  2. 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
    Given the MySQL usage, you might try grabbing mysqltuner.pl to see if you might optimize MySQL some. The other stuff showing is httpd usage, which we could tackle after MySQL.

    Code:
    cd /root
    wget mysqltuner.pl
    chmod +x mysqltuner.pl
    ./mysqltuner.pl
    Let it run until you get the output, then paste it here and paste the contents of your /etc/my.cnf as well. Please ensure to provide your MySQL version when you do so.

    Finally, which arch type do you have? 32-bit or 64-bit?

    Since we haven't put the pdf online yet on the cPanel site for this presentation, I've uploaded it to RapidShare for people to view. If you'd like some tips on MySQL Optimization, please review my presentation from this year's cPanel conference at this location:

    http://rapidshare.com/files/426824911/cPanel-MySQLOptimization.pdf
     
  3. thcpanel

    thcpanel Registered

    Joined:
    Oct 23, 2010
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    mysql
    my.cnf
    mysqltunner
    http://i54.tinypic.com/16llmk5.png
     
  4. 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
    To optimize all tables, run this command:

    Code:
    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
    Next, to enable the slow query log under MySQL 5.0, you would put this into /etc/my.cnf file:

    Code:
    log-slow-queries=/var/lib/mysql/slow.log
    Then create the file using these steps:

    Code:
    cd /var/lib/mysql
    touch slow.log
    chmod 660 slow.log
    chown mysql:mysql slow.log
    Your tmp_table_size and max_heap_table_size are not equal and should be equal. I'd suggest reducing it to 400M for both and 400M might even be too large.

    This means you'd end up with this in your /etc/my.cnf following the other recommendations of mysqltuner.pl:

    Code:
    [mysqld]
    max_connections=500
    
    # RAM/512 * 16M + 16M
    tmp_table_size = 400M
    
    # RAM/512 * 32M + 32M
    max_heap_table_size = 400M 
    
    query_cache_type=1
    query_cache_size=8M
    query_cache_limit=1M
    thread_cache_size=4
    table_cache=80
    I've removed safe-show-databases as it's deprecated since MySQL 4.1. I removed the set-variable = in front of the max_connections as again that's deprecated to use set-variable. I reduced table_cache to a more sane figure so we can slowly increase it. I added the query cache options and thread cache.

    After you make the changes, you'd want to restart MySQL. Give it 24 hours after these changes, then re-check it at that point with mysqltuner.pl again.
     
  5. thcpanel

    thcpanel Registered

    Joined:
    Oct 23, 2010
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
  6. 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
    You need to give it 24 hours to see the results for MySQL anyway. You need that amount of time to see any databases going to the slow query logs. The load might well not just be MySQL. There's a lot of Apache activity there and it might well be you need to optimize Apache or block IPs if some are purposely hitting the machine.

    You could see how many hits are Apache ones:

    Code:
    netstat -an|grep :80|wc -l
    netstat -an|grep :80|grep SYN
     
  7. thcpanel

    thcpanel Registered

    Joined:
    Oct 23, 2010
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Ok, I'll wait for 24 hours, how do I see the logs slow?

     
  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
    Please provide the return for the other netstat:

    Code:
    netstat -an|grep :80|grep SYN|wc -l
    I want to see if you are getting DoS attacked. 994 is a lot of processes.
     
Loading...

Share This Page