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.

Optimize load average

Discussion in 'Workarounds and Optimization' started by diceman82, May 23, 2011.

  1. diceman82

    diceman82 Registered

    Joined:
    May 23, 2011
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Hi,
    on a dedicated server I have a problem with high load average.
    very often during the day I have a load average above 10

    the server has the following components:

    cpu : i7 950
    2 HD SATA 7200 rpm - 2TERA ( raid 1 )
    ram: 16GB ddr3

    talking with the support cpanel seems that the problem is due to an excessive use of disk:

    iostat -x 5

    Code:
    avg-cpu:  %user   %nice %system %iowait  %steal   %idle
               0,25    0,00    0,25    4,63    0,00   94,87
    
    Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
    sda               0,00   209,40  0,00 93,00     0,00  2417,60    26,00    17,05  180,87   4,07  [B]37,82[/B]
    sdb               0,00   218,60  0,00 82,00     0,00  2393,60    29,19    39,57  456,54   7,12  [B]58,42[/B]
    dm-0              0,00     0,00  0,00 303,20     0,00  2425,60     8,00   140,29  449,92   1,93  58,52
    dm-1              0,00     0,00  0,00  0,00     0,00     0,00     0,00     0,00    0,00   0,00   0,00
    dm-2              0,00     0,00  0,00 303,20     0,00  2425,60     8,00   140,29  449,92   1,93  58,52
    dm-3              0,00     0,00  0,00 303,20     0,00  2425,60     8,00   140,29  449,92   1,93  58,52
    dm-4              0,00     0,00  0,00  0,00     0,00     0,00     0,00     0,00    0,00   0,00   0,00
    
    
    I wanted to know if we can reduce optimizing mysql i / o


    my.cnf

    Code:
    # The following options will be passed to all MySQL clients
    [client]
    #password       = your_password
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    
    # The MySQL server
    [mysqld]
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    skip-locking
    key_buffer = 384M
    max_allowed_packet = 1M
    table_cache = 1024
    sort_buffer_size = 2M
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size = 128M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8
    
    
    #PERSONALIZZAZIONI
    max_connections = 800
    tmp_table_size = 64M
    max_heap_table_size = 64M
    open_files_limit = 8192
    #innodb_buffer_pool_size = 126M
    #tmpdir=/tmpdb
    join_buffer_size = 256K
    
    
    
    
    # Disable Federated by default
    skip-federated
    
    # required unique id between 1 and 2^32 - 1
    # defaults to 1 if master-host is not set
    # but will not function as a master if omitted
    server-id       = 1
    
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    
    [isamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    
    [myisamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    
    [mysqlhotcopy]
    interactive-timeout = 30
    
    
    

    lwp-request http://mysqltuner.pl | perl


    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
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 5274)
    [--] Data in InnoDB tables: 106M (Tables: 1725)
    [!!] Total fragmented tables: 180
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4d 15h 3m 34s (8M q [20.159 qps], 318K conn, TX: 34B, RX: 1B)
    [--] Reads / Writes: 66% / 34%
    [--] Total buffers: 586.0M global + 12.5M per thread (800 max threads)
    [OK] Maximum possible memory usage: 10.3G (65% of installed RAM)
    [OK] Slow queries: 0% (1K/8M)
    [OK] Highest usage of available connections: 4% (38/800)
    [OK] Key buffer size / total MyISAM indexes: 384.0M/664.3M
    [OK] Key buffer hit rate: 99.8% (119M cached / 262K reads)
    [OK] Query cache efficiency: 62.7% (3M cached / 5M selects)
    [!!] Query cache prunes per day: 78806
    [OK] Sorts requiring temporary tables: 0% (28 temp sorts / 325K sorts)
    [!!] Joins performed without indexes: 3578
    [OK] Temporary tables created on disk: 12% (41K on disk / 323K total)
    [OK] Thread cache hit rate: 99% (504 created / 318K connections)
    [!!] Table cache hit rate: 0% (1K open / 287K opened)
    [OK] Open file limit used: 22% (1K/8K)
    [OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
    [!!] InnoDB data size / buffer pool: 106.1M/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 128M)
        join_buffer_size (> 256.0K, or always use indexes with joins)
        table_cache (> 1024)
        innodb_buffer_pool_size (>= 106M)
    
    
    top
    Code:
    top - 14:56:25 up 88 days,  1:48,  1 user,  load average: 16.22, 9.67, 6.42
    Tasks: 311 total,   2 running, 305 sleeping,   0 stopped,   4 zombie
    Cpu(s):  2.9%us,  0.7%sy,  0.0%ni, 84.4%id, 11.9%wa,  0.1%hi,  0.0%si,  0.0%st
    Mem:  16434976k total, 16000184k used,   434792k free,  3806604k buffers
    Swap: 18481144k total,      140k used, 18481004k free,  8494920k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    29038 leggedia  16   0  161m  57m 6872 R  7.2  0.4   0:08.07 php
     7383 mysql     15   0  921m 390m 4976 S  1.6  2.4  27:51.86 mysqld
    15451 dovecot   15   0 39924 3812 1608 S  0.7  0.0   9:54.24 pop3-login
      437 root      15   0 12880 1292  820 R  0.2  0.0   0:00.06 top
      835 root      10  -5     0    0    0 S  0.2  0.0  48:26.45 kmirrord
      840 root      10  -5     0    0    0 S  0.2  0.0  42:11.66 kjournald
    19129 ltserver  16   0  112m 9792 5684 S  0.2  0.1   0:00.24 php
    31971 nobody    16   0     0    0    0 Z  0.2  0.0   0:00.08 httpd <defunct>
    32071 nobody    15   0 75152 8204 1712 S  0.2  0.0   0:00.01 httpd
    32717 root      15   0 87488  14m 2640 S  0.2  0.1   0:00.03 cpsrvd-ssl
    32718 root      15   0 87572  14m 2612 S  0.2  0.1   0:00.03 cpsrvd-ssl
    32720 root      15   0     0    0    0 Z  0.2  0.0   0:00.03 cpsrvd-ssl <defunct>
        1 root      15   0 10356  628  536 S  0.0  0.0   0:05.54 init
        2 root      RT  -5     0    0    0 S  0.0  0.0   0:02.11 migration/0
        3 root      34  19     0    0    0 S  0.0  0.0   0:00.42 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.01 migration/1
        6 root      34  19     0    0    0 S  0.0  0.0   0:00.75 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:01.94 migration/2
        9 root      34  19     0    0    0 S  0.0  0.0   0:00.40 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:02.55 migration/3
       12 root      34  19     0    0    0 S  0.0  0.0   0:00.44 ksoftirqd/3
       13 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/3
       14 root      RT  -5     0    0    0 S  0.0  0.0   0:02.07 migration/4
       15 root      34  19     0    0    0 S  0.0  0.0   0:00.39 ksoftirqd/4
       16 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/4
       17 root      RT  -5     0    0    0 S  0.0  0.0   0:01.93 migration/5
       18 root      34  19     0    0    0 S  0.0  0.0   0:00.55 ksoftirqd/5
       19 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/5
       20 root      RT  -5     0    0    0 S  0.0  0.0   0:02.07 migration/6
       21 root      34  19     0    0    0 S  0.0  0.0   0:00.36 ksoftirqd/6
       22 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/6
       23 root      RT  -5     0    0    0 S  0.0  0.0   0:02.55 migration/7
       24 root      34  19     0    0    0 S  0.0  0.0   0:00.34 ksoftirqd/7
       25 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/7
       26 root      10  -5     0    0    0 S  0.0  0.0   0:00.03 events/0
       27 root      10  -5     0    0    0 S  0.0  0.0   0:00.04 events/1
       28 root      10  -5     0    0    0 S  0.0  0.0   0:00.03 events/2
       29 root      10  -5     0    0    0 S  0.0  0.0   0:00.02 events/3
       30 root      10  -5     0    0    0 S  0.0  0.0   0:10.52 events/4
       31 root      10  -5     0    0    0 S  0.0  0.0   0:00.03 events/5
       32 root      10  -5     0    0    0 S  0.0  0.0   0:00.03 events/6
       33 root      10  -5     0    0    0 S  0.0  0.0   0:00.03 events/7
       34 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 khelper
      167 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 kthread
      178 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 kblockd/0
      179 root      10  -5     0    0    0 S  0.0  0.0   0:01.96 kblockd/1
      180 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 kblockd/2
      181 root      10  -5     0    0    0 S  0.0  0.0   0:00.01 kblockd/3
      182 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 kblockd/4
      183 root      10  -5     0    0    0 S  0.0  0.0   0:01.11 kblockd/5
      184 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 kblockd/6
      185 root      10  -5     0    0    0 S  0.0  0.0   0:00.03 kblockd/7
      186 root      13  -5     0    0    0 S  0.0  0.0   0:00.00 kacpid
      335 root      13  -5     0    0    0 S  0.0  0.0   0:00.00 cqueue/0
      336 root      14  -5     0    0    0 S  0.0  0.0   0:00.00 cqueue/1
    
    
    

    thanks
     
    #1 diceman82, May 23, 2011
    Last edited: May 23, 2011
  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
    It's unfortunate that online guides with default my.cnf configurations are being used for /etc/my.cnf settings, since these are old guides with invalid settings or old settings most times. As such, I had to pull out several options from the existing /etc/my.cnf file:

    To detail every single change:

    1. Removed the [client] portion as those ports and the socket location are already default. Removed the same in the [mysqld] section.

    2. Removed skip-locking as it is actually called skip-external-locking nowadays and the default.

    3. Changed every instance of key_buffer, read_buffer and write_buffer to the actual variable name of key_buffer_size, read_buffer_size and write_buffer_size instead.

    4. Removed thread_concurrency as it does not exist on Linux or FreeBSD systems (it only exists on Solaris).

    5. Removed anything already commented out of the file. Removed all of the comments about settings, since you could make a backup of the /etc/my.cnf file for those comments.

    6. Removed quick under mysqldump as quick is the default.

    7. Alphabetized all settings for easy reading ability.

    8. Changed query_cache_size to 150M

    To change to the above /etc/my.cnf please make a backup of the old one first:

    Code:
    cp /etc/my.cnf /etc/my.cnf.bak110523
    After doing that, then you would want to add under the [mysqld] section logging for the slow queries with this variable:

    Code:
    log-slow-queries=/var/lib/mysql/slow.log
    Please put this alphabetically into the file, so that it falls between key_buffer_size = 384M and max_allowed_packet = 1M entries. After adding that line into /etc/my.cnf location, then save the file and create the log file with these commands:

    Code:
    touch /var/lib/mysql/slow.log
    chown mysql:mysql /var/lib/mysql/slow.log && chmod 660 /var/lib/mysql/slow.log
    You would then want to restart MySQL.

    Of note, I did not change join_buffer_size as that variable can cause high memory usage or reduction in performance. I suggest keeping it as it already is for the setting. I did not increase table_cache as that is already high enough. I didn't do anything with innodb_buffer_pool_size as it was commented out of the file provided.

    At this point, I doubt basic MySQL optimization is going to highly improve performance on your machine, since you didn't receive many recommendations on what to change. This means you might have some database with slow queries that you'll find in the slow query log that needs to be removed or optimized, that you have one or more accounts that is/are too active, or that MySQL is not even the issue.
     
  3. diceman82

    diceman82 Registered

    Joined:
    May 23, 2011
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    hi,
    thanks for the quick response support.
    I proceeded to make the changes you mentioned and I'm monitoring the server.

    if mysql is not the problem, how come I have these second load average so high?

    unfortunately I can not understand and on other servers cpanel do not have this problem
     
  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
    Have you reviewed the WHM > Daily Process Logs for possible accounts that are abusing your server resources? It could be one or more accounts that have high usage scripts or too much traffic.
     
  5. diceman82

    diceman82 Registered

    Joined:
    May 23, 2011
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    I take a chance one day last week and I have this data:

    Code:
    leggedia		0.76	0.79	0.7
    ilmeglio		0.18	0.50	0.1
    benesser		0.17	0.29	0.2
    munin		0.12	0.00	0.0
    plusnetw		0.07	0.10	0.0
    studiomc		0.04	0.17	0.0
    mysql		0.04	2.11	0.0
    tuttoinv		0.03	0.00	0.2
    root		0.02	1.84	2.0
    gocciane		0.02	0.10	0.0
    antonioc		0.01	0.09	0.0
    
    
    top processes

    Code:
    lachiave		9.7	/usr/bin/php
    studiomc		9.4	/usr/bin/php
    reality5		9.0	/usr/bin/php
    ilmeglio		8.8	/usr/bin/php
    gocciane		8.0	/usr/bin/php
    antonioc		6.3	/usr/bin/php
    jobasian		5.4	/usr/bin/php
    apuntadi		5.3	/usr/bin/php
    tuttoinv		5.0	[php]
    apuntadi		4.6	/usr/bin/php
    jobasian		4.5	/usr/bin/php
    jobasian		4.1	/usr/bin/php
    tuttoinv		4.0	/usr/bin/php
    leggedia		35.5	/usr/bin/php
    benesser		32.0	/usr/bin/php
    siciliad		3.2	/usr/bin/php
    adopparm		3.0	/usr/bin/php
    tuttoinv		3.0	[php]
    
    From these data it seems to me not to see much of an exaggeration.

    thanks for support
     
    #5 diceman82, May 23, 2011
    Last edited: May 23, 2011
  6. adminlogs

    adminlogs Member

    Joined:
    May 30, 2011
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Hi ,

    From your first thread , its clearly saying the load is due to the following process

    " 29038 leggedia 16 0 161m 57m 6872 R 7.2 0.4 0:08.07 php "

    You need to check which file is causing this and from which location its running.

    To find the above you can use the following command

    lsof -p 29038 | more

    above command will clearly guide you to reach the file which causing this issue.

    Also let us know whether you are still facing this load issue.

    Sincerely,
    /http://www.adminlogs.info
     
Loading...

Share This Page