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.

Slow server (MySQL / IOPs)

Discussion in 'Workarounds and Optimization' started by PatrickVeenstra, Jul 1, 2014.

  1. PatrickVeenstra

    PatrickVeenstra Well-Known Member

    Joined:
    Feb 12, 2012
    Messages:
    72
    Likes Received:
    2
    Trophy Points:
    8
    Location:
    Barcelona
    cPanel Access Level:
    Root Administrator
    I have a VPS with 6x3,2GHz and 16GB RAM.

    It was responding slow, so I decided to do a reboot. The reboot took a lot of time, but finally it worked.

    Right now it's responding extremely slow. Server load is 25; the primary consumer is probably MySQL, top:
    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    1877 mysql 20 0 8755m 1.1g 5732 S 2.7 6.8 2:23.33 mysqld

    although I guess that heavy IOPs is the main cause:

    Trans./Sec 35.15
    Blocks Read/sec 1289.24
    Blocks Written/Sec 393.83
    Total Blocks Read 2005588
    Total Blocks Written 612648

    executing tuning-primer.sh freezes (or seems to freeze, it's been there for quite some minutes) at INNODB status.

    I haven't changed the my.cnf file in months, but here it is:
    Code:
    [myisamchk]
    read_buffer=32M
    write_buffer=32M
    key_buffer=128M
    sort_buffer=128M
    
    [mysqld]
    table_definition_cache=1600
    query_cache_type=1
    read_buffer_size=16M
    join_buffer_size=3M
    max_user_connections=300
    low_priority_updates=1
    thread_cache_size=512
    tmp_table_size=256M
    query_cache_size=64M
    sort_buffer_size=8M
    max_heap_table_size=256M
    innodb_log_buffer_size=4M
    innodb_file_per_table=1
    open_files_limit=12010
    key_buffer=2048M
    table_cache=3072
    query_cache_limit=3M
    concurrent_insert=2
    myisam_sort_buffer_size=512M
    max_allowed_packet=268435456
    max_connections=300
    innodb_buffer_pool_size=3G
    
    [isamchk]
    read_buffer=32M
    write_buffer=32M
    key_buffer=128M
    sort_buffer=128M
    Anyone can help please?
     
  2. storminternet

    storminternet Well-Known Member

    Joined:
    Nov 2, 2011
    Messages:
    462
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    I would suggest you to run tuning-primer.sh again as it will give you clear idea about the actual usage by mysql.
    Secondly if you need to check with host if there is any other vps, consuming resources badly and overloading hardware node on which your vps is hosted.
     
  3. PatrickVeenstra

    PatrickVeenstra Well-Known Member

    Joined:
    Feb 12, 2012
    Messages:
    72
    Likes Received:
    2
    Trophy Points:
    8
    Location:
    Barcelona
    cPanel Access Level:
    Root Administrator
    Thank you for your reply. I already tried to run tuning-primer.sh again, but it hangs again on INNODB status. I'm going to contact my provider now.
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    The following command may also help you identify any particular databases responsible for the usage:

    Code:
    mysqladmin processlist
    Thank you.
     
  5. PatrickVeenstra

    PatrickVeenstra Well-Known Member

    Joined:
    Feb 12, 2012
    Messages:
    72
    Likes Received:
    2
    Trophy Points:
    8
    Location:
    Barcelona
    cPanel Access Level:
    Root Administrator
    Right now my server load is 16, being the top user mysql (command: mysqld). mysqladmin processlist says:
    +---------+--------------+-----------+--------------+----------------+------+--------------------+------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +---------+--------------+-----------+--------------+----------------+------+--------------------+------------------+
    | 121 | DELAYED | localhost | eximstats | Delayed insert | 1 | Waiting for INSERT | |
    | 419267 | eximstats | localhost | eximstats | Sleep | 1 | | |
    | 1144633 | leechprotect | localhost | leechprotect | Sleep | 4694 | | |
    | 1205799 | DELAYED | localhost | eximstats | Delayed insert | 215 | Waiting for INSERT | |
    | 1209751 | root | localhost | | Query | 0 | | show processlist |
    +---------+--------------+-----------+--------------+----------------+------+--------------------+------------------+

    - - - Updated - - -

    Right now, Load Averages: 10.83 12.12 14.69

    top:
    1877 mysql 20 0 8990m 2.4g 6360 S 10.6 15.3 156:55.97 mysqld
    .. and a three PHP processes that use much less CPU
    .. and a lot of stuff that uses 0.0
     
  6. PatrickVeenstra

    PatrickVeenstra Well-Known Member

    Joined:
    Feb 12, 2012
    Messages:
    72
    Likes Received:
    2
    Trophy Points:
    8
    Location:
    Barcelona
    cPanel Access Level:
    Root Administrator
    The issue has been solved by moving the VPS to another server. Load at peak is < 0.5 so everything is great again. The server is not identical (6x3,4GHz instead of 6x3,2GHz), but I suppose that it was caused by hard disk overloading.
     
  7. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  8. codelabkenya

    codelabkenya Registered

    Joined:
    Jul 19, 2014
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    hello

    i have same issue my server load has been running at an average of 0.5 but for the last 5 days it has been running at an average of 2.0 there is these specific mysql processes that is consuming 25% of cpu usage and 18% of RAM

    /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/codelab.codelabkenya.net.err --open-files-limit=50000 --pid-file=/var/lib/mysql/codelab.codelabkenya.net.pid

    i will appreciate your assistance
    Robert Mwaura
     
  9. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    I'm not sure it is the same issue as you suggest, but you are welcome to run the command I suggested earlier in this thread to get an idea of which databases are utilizing the most resources.

    Thank you.
     
Loading...

Share This Page