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 - mysqld

Discussion in 'Workarounds and Optimization' started by jpbazinet, Dec 18, 2011.

  1. jpbazinet

    jpbazinet Registered

    Joined:
    Dec 18, 2011
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I've had a massive server load for a few days now that I've been trying to troubleshoot.
    There are a few sites on the server, but the major one seems to be findus which has a database of over 4 GB.

    I've checked and rechecked it for corruption, and optimized.

    Any help tuning this, would be appreciated.
    (I've also run mysqltuner, but since i've just restarted mysql, it's output is not really relevant right now).

    Server:
    Code:
    Processor #1VendorGenuineIntel [TABLE="class: datatable brick, width: 100%, align: center"]
    [TR]
    [TD="class: cell, align: left"]NameIntel(R) Core(TM)2 Quad CPU    Q9400  @ 2.66GHz Speed2666.904 MHz Cache3072 KB
    [/TD]
    [/TR]
    [/TABLE]
    
    My process manager:

    -removed by infopro-


    Top:

    Code:
    Tasks: 321 total,   1 running, 312 sleeping,   0 stopped,   8 zombie
    Cpu(s): 47.2%us, 21.1%sy,  0.0%ni, 31.4%id,  0.1%wa,  0.0%hi,  0.2%si,  0.0%st
    Mem:   6096036k total,  5201700k used,   894336k free,   241548k buffers
    Swap:  2096472k total,      152k used,  2096320k free,  3144856k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    12686 mysql     15   0 1193m 542m 4188 S 252.3  9.1  48:05.92 mysqld
    19834 findus    16   0     0    0    0 Z  1.0  0.0   0:00.05 php <defunct>
    13291 root      15   0 13000 1428  820 R  0.7  0.0   0:03.11 top
    14242 nobody    15   0 84828 9864 1880 S  0.7  0.2   0:00.19 httpd
    19860 findus    18   0     0    0    0 Z  0.7  0.0   0:00.04 php <defunct>
    20198 findus    17   0 99388  10m 5596 S  0.7  0.2   0:00.02 php
    20199 findus    16   0     0    0    0 Z  0.7  0.0   0:00.02 php <defunct>
    20204 findus    17   0 99388  10m 5596 S  0.7  0.2   0:00.02 php
    14317 nobody    15   0 80496 6348 2664 S  0.3  0.1   0:00.28 httpd
    19528 nobody    15   0 79804 4664 1756 S  0.3  0.1   0:00.01 httpd
    19688 nobody    15   0 79800 4712 1852 S  0.3  0.1   0:00.02 httpd
        1 root      15   0 10356  688  576 S  0.0  0.0   0:01.62 init
        2 root      RT  -5     0    0    0 S  0.0  0.0   0:01.93 migration/0
        3 root      34  19     0    0    0 S  0.0  0.0   0:06.47 ksoftirqd/0
        4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0
        5 root      RT  -5     0    0    0 S  0.0  0.0   0:02.13 migration/1
        6 root      34  19     0    0    0 S  0.0  0.0   0:02.63 ksoftirqd/1
        7 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/1
        8 root      RT  -5     0    0    0 S  0.0  0.0   0:00.93 migration/2
        9 root      34  19     0    0    0 S  0.0  0.0   0:01.79 ksoftirqd/2
       10 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/2
       11 root      RT  -5     0    0    0 S  0.0  0.0   0:03.72 migration/3
       12 root      34  19     0    0    0 S  0.0  0.0   0:01.00 ksoftirqd/3
       13 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/3
    
    
    my.cnf:

    Code:
    [mysqld]
    wait_timeout = 100
    # connection_timeout = 10
    interactive_timeout = 100
    max_connections = 3000
    max_user_connection = 600
    tmp_table_size = 64M
    max_heap_table_size = 64M
    key_buffer_size = 384M
    innodb_buffer_pool_size = 76M
    join_buffer_size = 6M
    thread_cache_size = 128
    query_cache_size = 200M
    table_cache = 1024
    ft_min_word_len = 3
    # The following can be used as easy to replay backup logs or for replication.
    server-id               = 3
    log_bin                 = /var/log/mysql/mysql-bin.log
    # WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
    expire_logs_days        = 10
    max_binlog_size         = 100M
    binlog-ignore-db = mysql,information_schema,business_main,mycontac_main,sharebar_test,findrest_main,findnumb_dandb,findnumb_news
    
    
     
    #1 jpbazinet, Dec 18, 2011
    Last edited by a moderator: Dec 19, 2011
  2. storminternet

    storminternet Well-Known Member

    Joined:
    Nov 2, 2011
    Messages:
    462
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    If you again notice high server load then run command
    and optimize the database responsible for high server load.
     
  3. jpbazinet

    jpbazinet Registered

    Joined:
    Dec 18, 2011
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    The load never goes down... it ranges between 14 and 150.

    I've already optimized.

    Code:
    +-------+--------------+-----------+-------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
    | Id    | User         | Host      | db          | Command | Time | State          | Info                                                                                                 |
    +-------+--------------+-----------+-------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
    | 15265 | findus_admin | localhost | findus_main | Sleep   | 580  |                |                                                                                                      |
    | 16371 | findus_admin | localhost | findus_main | Sleep   | 240  |                |                                                                                                      |
    | 16590 | findus_admin | localhost | findus_main | Sleep   | 79   |                |                                                                                                      |
    | 16593 | findus_admin | localhost | findus_main | Sleep   | 71   |                |                                                                                                      |
    | 16611 | findus_admin | localhost | findus_main | Sleep   | 75   |                |                                                                                                      |
    | 16678 | findus_admin | localhost | findus_main | Sleep   | 65   |                |                                                                                                      |
    | 16685 | findus_admin | localhost | findus_main | Sleep   | 156  |                |                                                                                                      |
    | 16799 | findus_admin | localhost | findus_main | Sleep   | 60   |                |                                                                                                      |
    | 16822 | findus_admin | localhost | findus_main | Sleep   | 42   |                |                                                                                                      |
    | 16868 | findus_admin | localhost | findus_main | Sleep   | 33   |                |                                                                                                      |
    | 16884 | findus_admin | localhost | findus_main | Sleep   | 24   |                |                                                                                                      |
    | 17008 | findus_admin | localhost | findus_main | Sleep   | 21   |                |                                                                                                      |
    | 17060 | findus_admin | localhost | findus_main | Sleep   | 59   |                |                                                                                                      |
    | 17065 | findus_admin | localhost | findus_main | Sleep   | 58   |                |                                                                                                      |
    | 17073 | findus_admin | localhost | findus_main | Sleep   | 4    |                |                                                                                                      |
    | 17077 | findus_admin | localhost | findus_main | Sleep   | 5    |                |                                                                                                      |
    | 17090 | findus_admin | localhost | findus_main | Sleep   | 70   |                |                                                                                                      |
    | 17097 | findus_admin | localhost | findus_main | Sleep   | 5    |                |                                                                                                      |
    | 17105 | findus_admin | localhost | findus_main | Sleep   | 4    |                |                                                                                                      |
    | 17127 | findus_admin | localhost | findus_main | Sleep   | 0    |                |                                                                                                      |
    | 17129 | findus_admin | localhost | findus_main | Query   | 78   | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17146 | findus_admin | localhost | findus_main | Sleep   | 4    |                |                                                                                                      |
    | 17149 | findus_admin | localhost | findus_main | Query   | 69   | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17178 | findus_admin | localhost | findus_main | Query   | 52   | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17190 | findus_admin | localhost | findus_main | Query   | 52   | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17214 | findus_admin | localhost | findus_main | Query   | 48   | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17218 | findus_admin | localhost | findus_main | Query   | 47   | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17233 | findus_admin | localhost | findus_main | Query   | 45   | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17251 | findus_admin | localhost | findus_main | Query   | 36   | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17254 | findus_admin | localhost | findus_main | Query   | 35   | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17278 | findus_admin | localhost | findus_main | Sleep   | 5    |                |                                                                                                      |
    | 17280 | findus_admin | localhost | findus_main | Query   | 20   | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17281 | findus_admin | localhost | findus_main | Query   | 18   | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17286 | findus_admin | localhost | findus_main | Sleep   | 6    |                |                                                                                                      |
    | 17295 | findus_admin | localhost | findus_main | Query   | 12   | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17304 | findus_admin | localhost | findus_main | Query   | 8    | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17306 | findus_admin | localhost | findus_main | Query   | 7    | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17307 | findus_admin | localhost | findus_main | Query   | 7    | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17309 | findus_admin | localhost | findus_main | Query   | 5    | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17312 | findus_admin | localhost | findus_main | Query   | 4    | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17315 | findus_admin | localhost | findus_main | Query   | 3    | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17316 | findus_admin | localhost | findus_main | Query   | 3    | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17317 | findus_admin | localhost | findus_main | Sleep   | 0    |                |                                                                                                      |
    | 17318 | findus_admin | localhost | findus_main | Query   | 2    | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17319 | findus_admin | localhost | findus_main | Query   | 0    | Sorting result | SELECT
                                                                     bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
    | 17320 | root         | localhost |             | Query   | 0    |                | show processlist                                                                                     |
    +-------+--------------+-----------+-------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
     
  4. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    We'll need to see your mysqltuner report before we can give any decent recommendations... but a couple things that stand out right now:

    • The findus_main database seems to be spending a lot of time sorting running the same (or nearly the same) query. Proper indexing or rewriting the query could solve your issue.
    • You said the findus db is over 4GB.. but I noticed you innodb_buffer_pool and key_buffer_pool are miniscule in comparison. Mysqltuner will let me know for sure, but I have a feeling one of those two values needs to be increased.
    • It seems like max_connections and max_user_connection are unusually high... but we'll need to see mysqltuner to confirm.
    • Your join_buffer_size is likely too high and your system is wasting cycles allocating/deallocating more memory than it needs. MySQL is a little "funny" in how it allocates the join buffer -- it determines how much memory it needs for the join, and then allocates the larger of that number or your join_buffer_size. So if it determines it only needs 128K to do the join, it'll allocate 6M anyway because of that setting. Worse yet, it'll allocate 6M per-join, per-thread. In most cases, you're better off setting this to something like 256K and letting MySQL decide when it actually needs more.

    Anyway, please post the results of mysqltuner.pl as soon as it's been 24 hours, and we'll see how we can help.
     
  5. hoststop

    hoststop Well-Known Member

    Joined:
    Dec 6, 2011
    Messages:
    84
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Bolton, UK
    cPanel Access Level:
    Root Administrator
    @jpbazinet There is a need to optimize my.cnf on your server. Please check with the web host admin & should be able to do it for you. If you require any assistance in optimizing my.cnf, let me know.
     
  6. jpbazinet

    jpbazinet Registered

    Joined:
    Dec 18, 2011
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I did a lot more research and basically started over with a blank my.cnf file.
    Things have been much better (no more loads of 150) but still need work (varies from 1.00 to 6.00).

    Here is my current my.cnf

    Code:
    [mysqld]
    max_connections = 500
    thread_cache_size = 4
    table_cache = 1024
    query_cache_limit = 1M
    query_cache_size = 17M
    query_cache_type = 1
    key_buffer_size = 2G
    read_buffer_size = 128K
    join_buffer_size = 128K
    innodb_buffer_pool_size = 60M
    # The following can be used as easy to replay backup logs or for replication.
    server-id               = 3
    log_bin                 = /var/log/mysql/mysql-bin.log
    # WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
    expire_logs_days        = 10
    max_binlog_size         = 100M
    binlog-ignore-db = mysql,information_schema,business_main,mycontac_main,sharebar_test,findrest_main,findnumb_dandb,findnumb_news
    log-slow-queries=/var/lib/mysql/slow.log
    
    And here is my mysqltuner for the last 19 hours.

    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
    
    -------- 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: 2G (Tables: 458)
    [--] Data in InnoDB tables: 53M (Tables: 203)
    [!!] Total fragmented tables: 14
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 16h 16m 44s (4M q [75.351 qps], 154K conn, TX: 12B, RX: 557M)
    [--] Reads / Writes: 88% / 12%
    [--] Total buffers: 2.1G global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 3.4G (59% of installed RAM)
    [OK] Slow queries: 0% (1K/4M)
    [OK] Highest usage of available connections: 6% (30/500)
    [OK] Key buffer size / total MyISAM indexes: 2.0G/1.8G
    [OK] Key buffer hit rate: 100.0% (1B cached / 170K reads)
    [OK] Query cache efficiency: 46.3% (1M cached / 3M selects)
    [!!] Query cache prunes per day: 2417908
    [OK] Sorts requiring temporary tables: 0% (4K temp sorts / 832K sorts)
    [!!] Temporary tables created on disk: 27% (78K on disk / 287K total)
    [OK] Thread cache hit rate: 94% (7K created / 154K connections)
    [OK] Table cache hit rate: 72% (1K open / 1K opened)
    [OK] Open file limit used: 28% (1K/4K)
    [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
    [OK] InnoDB data size / buffer pool: 53.2M/60.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_size (> 17M)
        tmp_table_size (> 32M)
        max_heap_table_size (> 16M)
    
     
  7. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    That looks MUCH better :)

    Couple of suggestions:

    • I'd bump thread_cache_size up to 32.
    • max_heap_table_size needs to be increased to at least 32M. The reason is tmp_table_size is actually limited by max_heap_table_size, so even though tmp_table_size is currently 32M, it can only use the 16M defined by max_heap_table_size. This will lower your "Temporary tables created on disk".
    • query_cache_size could stand to be larger... Try 32M. The cache generally doesn't hurt performance until it is well over 100M, so for now it is safe to increase the cache to keep your query cahce prunes at a minimum.
    • Keep an eye on "Table cache hit rate". If that starts dropping, you need to increase table_cache. 1536 or 2048 might be good numbers.
    • Keep an eye on "Highest usage of available connections". If you're never using more than a small fraction of your available connections, it is safe to decrease max_connections accordingly. Currently your max connections were 6% of the available connections -- if that holds, you might consider dropping max_connections to around 100.

    One last thing -- you do want to wait until mysqltuner says that mysql has been up for at least 24hr (48 is even better) before posting results. In the example you posted, it had only been up for 16hr. Once MySQL is happy, you'll want to take a look at optimizing Apache as well.
     
Loading...

Share This Page