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.

High Server Load

Discussion in 'Workarounds and Optimization' started by iweb2u, Dec 15, 2011.

  1. iweb2u

    iweb2u Registered

    Joined:
    Dec 15, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,

    I need help to optimize my virtual server.
    I have some Joomla websites and our server is high server load.
    I try to optimize mysql but I continue having problems.

    >> MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> 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-log
    [OK] Operating on 64-bit architecture


    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 37M (Tables: 1438)
    [!!] Total fragmented tables: 11


    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 22h 41m 14s (966K q [5.752 qps], 26K conn, TX: 1B, RX: 148M)
    [--] Reads / Writes: 57% / 43%
    [--] Total buffers: 86.0M global + 22.9M per thread (200 max threads)
    [!!] Maximum possible memory usage: 4.6G (118% of installed RAM)
    [OK] Slow queries: 0% (25/966K)
    [OK] Highest usage of available connections: 64% (129/200)
    [OK] Key buffer size / total MyISAM indexes: 24.0M/21.9M
    [OK] Key buffer hit rate: 99.3% (11M cached / 82K reads)
    [OK] Query cache efficiency: 88.9% (689K cached / 775K selects)
    [!!] Query cache prunes per day: 3338
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 16K sorts)
    [!!] Joins performed without indexes: 6696
    [!!] Temporary tables created on disk: 26% (14K on disk / 55K total)
    [OK] Thread cache hit rate: 99% (129 created / 26K connections)
    [!!] Table cache hit rate: 0% (192 open / 61K opened)
    [OK] Open file limit used: 32% (337/1K)
    [OK] Table locks acquired immediately: 99% (286K immediate / 286K locks)


    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 20M)
    join_buffer_size (> 20.0M, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 42M)
    table_cache (> 192)


    This is the my.cnf

    [mysqld]
    skip-innodb
    max_connections=200
    query_cache_type=1
    query_cache_size=20M
    query_cache_limit=1M
    sort_buffer_size=2M
    read_rnd_buffer_size=512K
    tmp_table_size=32M
    max_heap_table_size=32M
    thread_cache_size=24
    key_buffer_size=24M
    table_cache=192
    join_buffer_size=20M
    log-slow-queries=/var/lib/mysql/slow.log
    tmp_table_size=32M
    max_heap_table_size=42M

    vmstat 5 5

    procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
    r b swpd free buff cache si so bi bo in cs us sy id wa st
    0 1 121264 3175296 31992 416488 0 0 21 25 1 0 1 0 95 4 0
    0 2 121264 3174176 32120 416872 0 0 34 217 1082 307 2 1 50 48 0
    0 1 121264 3158952 32224 417000 0 0 9 275 1090 226 1 1 47 51 0
    0 2 121264 3151796 32348 420720 0 0 718 296 1107 300 4 1 46 48 0
    1 1 121264 3148984 32512 424356 0 0 494 1611 1085 251 12 2 30 55 0


    TOP -s -c


    top - 12:05:30 up 219 days, 4:47, 2 users, load average: 1.85, 1.95, 1.65
    Tasks: 118 total, 1 running, 115 sleeping, 1 stopped, 1 zombie
    Cpu(s): 0.3%us, 0.5%sy, 0.0%ni, 83.5%id, 15.3%wa, 0.2%hi, 0.2%si, 0.0%st
    Mem: 4044532k total, 865032k used, 3179500k free, 31644k buffers
    Swap: 2096472k total, 121264k used, 1975208k free, 413340k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    1069 named 25 0 226m 8244 2044 S 0.3 0.2 1:01.14 /usr/sbin/named -u named
    6791 root 18 0 122m 16m 1412 S 0.3 0.4 0:52.69 lfd - sleeping
    1 root 15 0 10352 496 464 S 0.0 0.0 0:08.70 init [3]
    2 root RT -5 0 0 0 S 0.0 0.0 2:18.90 [migration/0]
    3 root 34 19 0 0 0 S 0.0 0.0 0:01.79 [ksoftirqd/0]
    4 root RT -5 0 0 0 S 0.0 0.0 1:22.72 [migration/1]
    5 root 34 19 0 0 0 S 0.0 0.0 0:00.95 [ksoftirqd/1]
    6 root 10 -5 0 0 0 S 0.0 0.0 0:27.10 [events/0]
    7 root 10 -5 0 0 0 S 0.0 0.0 0:05.41 [events/1]
    8 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 [khelper]
    49 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 [kthread]
    54 root 10 -5 0 0 0 S 0.0 0.0 0:12.12 [kblockd/0]
    55 root 10 -5 0 0 0 S 0.0 0.0 0:00.49 [kblockd/1]
    56 root 14 -5 0 0 0 S 0.0 0.0 0:00.00 [kacpid]
    114 root 14 -5 0 0 0 S 0.0 0.0 0:00.00 [cqueue/0]
    115 root 14 -5 0 0 0 S 0.0 0.0 0:00.00 [cqueue/1]
    118 root 14 -5 0 0 0 S 0.0 0.0 0:00.00 [khubd]
    120 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 [kseriod]
    196 root 15 0 0 0 0 S 0.0 0.0 0:00.03 [khungtaskd]
    199 root 10 -5 0 0 0 S 0.0 0.0 0:16.78 [kswapd0]
    200 root 14 -5 0 0 0 S 0.0 0.0 0:00.00 [aio/0]
    201 root 14 -5 0 0 0 S 0.0 0.0 0:00.00 [aio/1]
    342 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 [kpsmoused]
    383 root 10 -5 0 0 0 S 0.0 0.0 0:00.31 [mpt_poll_0]
    384 root 18 -5 0 0 0 S 0.0 0.0 0:00.00 [mpt/0]
    385 root 18 -5 0 0 0 S 0.0 0.0 0:00.00 [scsi_eh_0]
    389 root 18 -5 0 0 0 S 0.0 0.0 0:00.00 [ata/0]


    Maybe I need to do some tunning in Apache configurations.

    Thanks
     
    #1 iweb2u, Dec 15, 2011
    Last edited: Dec 15, 2011
  2. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    This is probably most of your problem:
    The join buffer is not only per-session, it's per-join. That means if you have a query with 3 joins in one query, MySQL allocates 60MB of memory, runs the query, then dumps it again. Very expensive and wasteful operation. It's also the reason mysqltuner is warning that mysql can use 118% of your installed RAM.

    Please make the following changes (or add the variable if it isn't already in your my.cnf):

    Code:
    join_buffer_size = 1M  #   This could probably be smaller yet, but this is a reasonable value
    sort_buffer_size = 1M
    concurrent_insert = 2     # Default 1
    tmp_table_size = 64M
    max_heap_table_size = 64M
    table_cache = 3072
    thread_cache_size = 64
    query_cache_size = 32M
    Once you've done that, reboot MySQL then wait 24 hours and repost the information from your first post, but please place it inside CODE tags so it is easy to read. I noticed you have a high cpu wait %, but it was impossible to read the vmstat to see if this was from swapping or other I/O. If it is swapping, just reducing the join_buffer_size as I indicated above could help tremendously.

    Let me know how that turns out.
     
Loading...

Share This Page