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.

CPU load is continuously above 35

Discussion in 'Workarounds and Optimization' started by imran_khan, Jun 11, 2013.

  1. imran_khan

    imran_khan Well-Known Member

    Joined:
    Jun 10, 2013
    Messages:
    154
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hello,

    I am facing CPU high load issue with the server. Server hardware and software details are as follows.
    CPU load is continuously above 35. Daily site visitor is 18000. So I am planning to Apache and MySQL performance tuning. Please suggest me on the same.

    Hardware Details:-
    Hard Disk:- 2000.4 GB X 2 HDD with software RAID-1.
    RAM:- 16 GB.
    CPU model name AMD Opteron(tm) Processor 3280
    Manufacturer: FUJITSU
    Product Name: D3090-A1
    Version: S26361-D3090-A1
    physical id: 1
    cpu cores: 4
    processor: 8

    Software Details:-
    Cpanel, Mysql and Apache.

    Mysql configuration file details:-

    [mysqld]
    skip-networking
    innodb_file_per_table=1

    query_cache_limit = 1M
    query_cache_size = 32M

    key_buffer_size = 64M

    max_heap_table_size = 256M
    tmp_table_size = 256M

    thread_cache = 128
    open_files_limit=2358

    max_connections = 1500
    max_user_connections = 300

    Apache configuration file details:-

    Timeout 300
    StartServers 5
    MinSpareServers 5
    MaxSpareServers 10
    ServerLimit 256
    MaxClients 150
    MaxRequestsPerChild 10000
    KeepAlive Off
    KeepAliveTimeout 5
    MaxKeepAliveRequests 100

    Thanks,
    Imran Khan.
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    run at least mysqltuner.pl and give the results of CPU and load graphs of munin here
    and copy result of top
     
  4. imran_khan

    imran_khan Well-Known Member

    Joined:
    Jun 10, 2013
    Messages:
    154
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hello,

    Thanks for the reply. Please find the out put of the command.

    # sar -q -f /var/log/sa/sa11

    runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15
    Average: 19 654 20.93 20.80 20.82

    #top c

    top - 22:10:40 up 4 days, 1:10, 1 user, load average: 6.24, 9.58, 10.35
    Tasks: 232 total, 2 running, 229 sleeping, 0 stopped, 1 zombie
    Cpu(s): 56.5%us, 15.0%sy, 2.5%ni, 22.1%id, 3.6%wa, 0.1%hi, 0.3%si, 0.0%st
    Mem: 16234832k total, 10328188k used, 5906644k free, 405156k buffers
    Swap: 2097136k total, 185596k used, 1911540k free, 6743444k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    11391 mysql 20 0 4382m 249m 3892 S 271.9 1.6 4028:17 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/eagle606.sta
    3442 inditv 39 19 1846m 1.8g 848 R 60.7 11.6 0:23.00 /usr/local/cpanel/3rdparty/bin/analog +CIMAGEDIR /images/ +CDOMAINSFILE /usr/local/cpanel/3rdparty/share/an
    8721 inditv 20 0 0 0 0 Z 58.7 0.0 0:00.42
    PHP:
     <defunct>
     
    8733 inditv    20   0  307m  26m  16m S 23.5  0.2   0:00.12 /usr/bin/php /home/inditv/public_html/profile_info.php
       60 root      20   0     0    0    0 S  2.0  0.0   0
    :55.35 [kblockd/2]
     
    3865 nobody    20   0  244m  69m 2232 S  2.0  0.4   0:01.76 /usr/local/apache/bin/httpd -k start -DSSL
     8732 root      20   0 15156 1320  900 R  2.0  0.0   0
    :00.01 top c
        1 root      20   0 19228 1008  836 S  0.0  0.0   0
    :06.30 /sbin/init

    # sar -p

            
    CPU     %user     %nice   %system   %iowait    %steal     %idle
    Average
    :        all     56.02      3.04     14.99      5.17      0.00     20.78

    # free -m
                 
    total       used       free     shared    buffers     cached
    Mem
    :         15854      12236       3617          0        396       8039
    -/+ buffers/cache:       3801      12052
    Swap
    :         2047        181       1866


    # sar -r
            
    kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit

    Average
    :         5634106  10600726     65.30    207311   7972464  14144229     77.16

    # sar -S
            
    kbswpfree kbswpused  %swpused  kbswpcad   %swpcad

    Average
    :        1889949    207187      9.88     14956      7.22

    # iostat -x 1 5
    Linux 2.6.32-279.19.1.el6.x86_64 (eagle606.startdedicated.com)  06/11/2013      _x86_64_        (8 CPU)

    avg-cpu:  %user   %nice %system %iowait  %steal   %idle
              56.51    2.48   15.35    3.59    0.00   22.07

    Device
    :         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
    sda              25.98   215.90   72.92   56.29 11949.36  2167.18   109.25     9.23   71.42   3.94  50.97
    sdb              26.00   214.83   74.51   55.73 12186.27  2153.99   110.11     9.21   70.72   4.06  52.84
    md1               0.00     0.00   23.74  266.67  1820.06  2129.33    13.60     0.00    0.00   0.00   0.00
    md0               0.00     0.00    0.00    0.00     0.01     0.00     5.05     0.00    0.00   0.00   0.00

    avg
    -cpu:  %user   %nice %system %iowait  %steal   %idle
              28.14    0.00    8.79   11.81    0.00   51.26

    Device
    :         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
    sda               1.00     0.00   26.00    5.00   216.00    28.00     7.87     0.98   31.77  23.26  72.10
    sdb               0.00     0.00   21.00    5.00   168.00    28.00     7.54     0.49   19.15  16.23  42.20
    md1               0.00     0.00   48.00    1.00   384.00     8.00     8.00     0.00    0.00   0.00   0.00
    md0               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

    avg
    -cpu:  %user   %nice %system %iowait  %steal   %idle
              18.22    0.00    8.92   12.19    0.00   60.68

    Device
    :         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
    sda               0.00     0.00   51.00    0.00   408.00     0.00     8.00     0.59   11.43  11.45  58.40
    sdb               0.00     0.00   36.00    0.00   288.00     0.00     8.00     0.44   12.36  12.22  44.00
    md1               0.00     0.00   87.00    0.00   696.00     0.00     8.00     0.00    0.00   0.00   0.00
    md0               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

    avg
    -cpu:  %user   %nice %system %iowait  %steal   %idle
              55.25    0.12   16.38    5.25    0.00   23.00

    Device
    :         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
    sda               0.00    73.00   16.00   27.00   128.00   802.00    21.63     1.11   25.26  15.70  67.50
    sdb               0.00    73.00   24.00   28.00   192.00   802.00    19.12     0.80   15.40  13.42  69.80
    md1               0.00     0.00   40.00   94.00   320.00   752.00     8.00     0.00    0.00   0.00   0.00
    md0               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

    avg
    -cpu:  %user   %nice %system %iowait  %steal   %idle
              88.88    0.00   10.62    0.00    0.00    0.50

    Device
    :         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
    sda               0.00     2.00    0.00   19.00     0.00   148.00     7.79     0.59   25.79  30.42  57.80
    sdb               0.00     2.00   23.00   18.00   216.00   148.00     8.88     0.54    9.93  11.83  48.50
    md1               0.00     0.00   22.00   15.00   208.00   120.00     8.86     0.00    0.00   0.00   0.00
    md0               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

    # sar -d
            
    DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
    Average
    :       dev8-0     87.51   3965.83   1484.83     62.29      9.48    108.30      6.25     54.72
    Average
    :      dev8-16     91.58   4528.76   1471.60     65.52      9.33    101.82      6.62     60.63
    Average
    :       dev9-1    213.07   2444.37   1436.75     18.22      0.00      0.00      0.00      0.00
    Average
    :       dev9-0      0.00      0.00      0.00      2.86      0.00      0.00      0.00      0.00

    I have run the 
    /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl file on the server.


    # perl mysqltuner.pl
    Name "XML::Simple::PREFERRED_PARSER" used only oncepossible typo at mysqltuner.pl line 148.

     
    >>  MySQLTuner 1.2.0_1 Major Hayden <major@mhtx.net>
     >>  
    Bug reportsfeature requests, and downloads at http://mysqltuner.com/
     
    >>  Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] 
    Skipped version check for MySQLTuner script
    [OKCurrently running supported MySQL version 5.1.69-cll
    [OKOperating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] 
    Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables3G (Tables357)
    [--] 
    Data in InnoDB tables208K (Tables13)
    [!!] 
    Total fragmented tables8

    -------- Performance Metrics -------------------------------------------------
    [--] 
    Up for: 16h 15m 15s (15M q [257.063 qps], 198K connTX11BRX4B)
    [--] 
    Reads Writes92% / 8%
    [--] 
    Total buffers362.0M global + 2.7M per thread (1500 max threads)
    [
    OKMaximum possible memory usage4.4G (28of installed RAM)
    [
    OKSlow queries0% (4K/15M)
    [
    OKHighest usage of available connections20% (305/1500)
    [
    OKKey buffer size total MyISAM indexes64.0M/1.4G
    [OKKey buffer hit rate99.8% (11B cached 21M reads)
    [
    OKQuery cache efficiency64.3% (8M cached 13M selects)
    [!!] 
    Query cache prunes per day1316472
    [OKSorts requiring temporary tables0% (1K temp sorts 885K sorts)
    [!!] 
    Joins performed without indexes60921
    [OKTemporary tables created on disk8% (109K on disk 1M total)
    [
    OKThread cache hit rate99% (874 created 198K connections)
    [!!] 
    Table cache hit rate0% (64 open 5M opened)
    [
    OKOpen file limit used1% (95/7K)
    [!!] 
    Table locks acquired immediately87%
    [
    OKInnoDB data size buffer pool208.0K/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
        
    Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours 
    recommendations may be inaccurate
        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
        Optimize queries 
    and/or use InnoDB to reduce lock wait
    Variables to adjust
    :
        
    query_cache_size (> 32M)
        
    join_buffer_size (> 128.0K, or always use indexes with joins)
        
    table_cache (> 64)

    Please check the out put of all the command and suggset me.
     
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    this

    mysql:

    Code:
    [mysqld]
    
    
    wait_timeout = 30
    connect_timeout = 1
    local-infile=0
    
    open_files_limit=25000
    default-storage-engine=MyISAM
    
    max_connections = 500
    max_user_connections = 150
    
    key_buffer_size = 5G
    max_allowed_packet=20M
    
    query_cache_size=50M
    query_cache_limit=1M
    tmp_table_size=100M
    max_heap_table_size=100M
    thread_cache_size=50
    
    table_open_cache = 1500
    table_definition_cache = 1000
    
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes
    
    sort_buffer_size=256K
    join_buffer_size=2M
    read_rnd_buffer_size=4M
    
    max_write_lock_count = 10
    concurrent_insert=2
    
    apache:
    Timeout 30
    StartServers 10
    MinSpareServers 5
    MaxSpareServers 10
    ServerLimit 300
    MaxClients 300


    and run this also (before restarting mysql)
    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest
    ./pt-query-digest /var/lib/mysql/*-slow.log > slow.txt

    and post here slow.txt to see slow queries

    after that restart mysql
     
  6. imran_khan

    imran_khan Well-Known Member

    Joined:
    Jun 10, 2013
    Messages:
    154
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hello Thinkbot,

    Thanks. Daily site visitors are 18000. So I am planning to optimize Apache as per below. Please suggest me on the same.

    Timeout 150
    StartServers 50
    MinSpareServers 25
    MaxSpareServers 50
    ServerLimit 256
    MaxClients 256
    MaxRequestsPerChild 10000
    KeepAlive On
    KeepAliveTimeout 10
    MaxKeepAliveRequests 150

    Thanks,
    Imran Khan.
     
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Timeout 15
    StartServers 20
    MinSpareServers 10
    MaxSpareServers 20
    ServerLimit 500
    MaxClients 500
    MaxRequestsPerChild 10000
    KeepAlive On
    KeepAliveTimeout 1
    MaxKeepAliveRequests 150


    You dont need to start at once many servers, they will increase when needed (since MinSpareServers)
    No need to keep long timeouts

    And keepalive 10 seconds would keep a lot of connections open without a point

    Regards
     
  8. imran_khan

    imran_khan Well-Known Member

    Joined:
    Jun 10, 2013
    Messages:
    154
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hello Thinkbot,

    Thanks. KeepAlive On is fine or can I set it to Off? Because currently it is Off and Is MaxRequestsPerChild 10000 fine as per my hardware or I need to decrease it.

    Thanks,
    Imran Khan.
     
  9. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    KeepAlive speeds up a little bit static content loading, so you can leave it on 1s or set it Off
    MaxRequestsPerChild means after how many requests restart the process, you can leave it 10000 or make smaller like 1000

    Regards
     
  10. imran_khan

    imran_khan Well-Known Member

    Joined:
    Jun 10, 2013
    Messages:
    154
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hello Thinkbot,

    Thank you very much. will this Apache setting decrease CPU load/improve server performance or I need to optimize MySQL with Apache?

    Thanks,
    Imran Khan.
     
  11. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hey,

    Yes both mysql and apache configs will improve overall performance
    But the script/code is the one that can take the most CPU time
    So the things like script caching, slow queries are still to check if you want to get best performance

    Please post mysqltuner results

    Regards
     
  12. imran_khan

    imran_khan Well-Known Member

    Joined:
    Jun 10, 2013
    Messages:
    154
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hello Thnikbot,

    Thanks. Really appreciate.

    Daily site visitors are 18000. so can i set max_connections = 1500 and max_user_connections = 300 instead of max_connections = 500 and max_user_connections = 150?

    For MySQL:-
    [mysqld]

    skip-networking
    innodb_file_per_table=1
    wait_timeout = 30
    connect_timeout = 1
    local-infile=0

    open_files_limit=25000
    default-storage-engine=MyISAM

    max_connections = 1500
    max_user_connections = 300

    key_buffer_size = 5G
    max_allowed_packet=20M

    query_cache_size=50M
    query_cache_limit=1M
    tmp_table_size=100M
    max_heap_table_size=100M
    thread_cache_size=50
    thread_cache = 128
    table_open_cache = 1500
    table_definition_cache = 1000

    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes

    sort_buffer_size=256K
    join_buffer_size=2M
    read_rnd_buffer_size=4M

    max_write_lock_count = 10
    concurrent_insert=2

    Thanks,
    Imran Khan.
     
  13. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You probably doesnt understand how it works :)

    Let's assume that avarage page generation time takes 0.1s
    To reach 500 connections limit, you would need to have 500 concurrent (at once) connections in that 0.1s

    I dont think you got so much :)

    In practise, even 50 max connections is well than enough if script is properly written and executes fast

    I was working last few days on the server where there is 400 page req/s and highest usage of available connections was 42, most of the time its 3-4, since page generation time is very fast, lower than 0.01

    please post mysqltuner.pl result

    Regards
     
  14. imran_khan

    imran_khan Well-Known Member

    Joined:
    Jun 10, 2013
    Messages:
    154
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hello Thinkbot,

    Thank you very much for quick response. Please check the below action plan and let me know.
    Then I will make the changes because this server is my customer server’s and it is in production.

    For Apache:-
    • Make the below changes in Apache configuration.
    Timeout 15
    StartServers 20
    MinSpareServers 10
    MaxSpareServers 20
    ServerLimit 500
    MaxClients 500
    MaxRequestsPerChild 10000
    KeepAlive Off
    KeepAliveTimeout 1
    MaxKeepAliveRequests 150

    • Restart the apache service.

    For MySQL:-
    • Make the below changes in MySQL configuration.
    [mysqld]

    skip-networking
    innodb_file_per_table=1
    wait_timeout = 30
    connect_timeout = 1
    local-infile=0

    open_files_limit=25000
    default-storage-engine=MyISAM

    max_connections = 500
    max_user_connections = 150

    key_buffer_size = 5G
    max_allowed_packet=20M

    query_cache_size=50M
    query_cache_limit=1M
    tmp_table_size=100M
    max_heap_table_size=100M
    thread_cache_size=50
    thread_cache = 128
    table_open_cache = 1500
    table_definition_cache = 1000

    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes

    sort_buffer_size=256K
    join_buffer_size=2M
    read_rnd_buffer_size=4M

    max_write_lock_count = 10
    concurrent_insert=2

    • Restart the MySQL service.
    • Run the mysqltuner.pl script.
     
    #14 imran_khan, Jun 14, 2013
    Last edited: Jun 14, 2013
  15. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    yes exactly, but you can run mysqltuner.pl first, so we will know how it run for few/several days

    and after restarting mysql, let it run few hours and generate new mysqltuner.pl
     
  16. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    can you share mysqltuner results ?
    Im just curious
     
  17. imran_khan

    imran_khan Well-Known Member

    Joined:
    Jun 10, 2013
    Messages:
    154
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hello Thinkbot,

    Thanks. I am waiting for customer approval. I will update you once they will approve. Please find the current script output.

    #perl mysqltuner.pl
    Name "XML::Simple::PREFERRED_PARSER" used only once: possible typo at mysqltuner.pl line 148.

    >> MySQLTuner 1.2.0_1 - 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.1.69-cll
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 3G (Tables: 357)
    [--] Data in InnoDB tables: 208K (Tables: 13)
    [!!] Total fragmented tables: 8

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3d 1h 23m 38s (66M q [250.579 qps], 921K conn, TX: 68B, RX: 19B)
    [--] Reads / Writes: 91% / 9%
    [--] Total buffers: 362.0M global + 2.7M per thread (1500 max threads)
    [OK] Maximum possible memory usage: 4.4G (28% of installed RAM)
    [OK] Slow queries: 0% (6K/66M)
    [OK] Highest usage of available connections: 19% (294/1500)
    [OK] Key buffer size / total MyISAM indexes: 64.0M/1.5G
    [OK] Key buffer hit rate: 99.8% (46B cached / 94M reads)
    [OK] Query cache efficiency: 66.4% (40M cached / 60M selects)
    [!!] Query cache prunes per day: 1068171
    [OK] Sorts requiring temporary tables: 0% (4K temp sorts / 3M sorts)
    [!!] Joins performed without indexes: 272830
    [OK] Temporary tables created on disk: 10% (491K on disk / 4M total)
    [OK] Thread cache hit rate: 99% (1K created / 921K connections)
    [!!] Table cache hit rate: 0% (64 open / 6M opened)
    [OK] Open file limit used: 1% (104/7K)
    [!!] Table locks acquired immediately: 94%
    [OK] InnoDB data size / buffer pool: 208.0K/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
    Optimize queries and/or use InnoDB to reduce lock wait
    Variables to adjust:
    query_cache_size (> 32M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 64)

    Thanks,
    Imran Khan.
     
  18. imran_khan

    imran_khan Well-Known Member

    Joined:
    Jun 10, 2013
    Messages:
    154
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hello Thinkbot,

    I have made the changes in Apache and MySQL configuration file. Current my.cnf file is as below.

    my.cnf file setting:-
    [mysqld]
    skip-networking
    innodb_file_per_table=1
    wait_timeout = 30
    connect_timeout = 1
    local-infile=0

    open_files_limit=25000
    default-storage-engine=MyISAM

    max_connections = 500
    max_user_connections = 150

    key_buffer_size = 5G
    max_allowed_packet=20M

    query_cache_size = 50M
    query_cache_limit = 1M
    tmp_table_size = 100M
    max_heap_table_size = 100M
    thread_cache_size=50
    thread_cache = 128
    table_open_cache = 1500
    table_definition_cache = 1000

    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes

    sort_buffer_size=256K
    join_buffer_size=2M
    read_rnd_buffer_size=4M

    max_write_lock_count = 10
    concurrent_insert=2

    Run the mysqltuner.pl after changes. mysqltuner.pl script output is as below. Please suggest me on this.

    # perl mysqltuner.pl
    Name "XML::Simple::PREFERRED_PARSER" used only once: possible typo at mysqltuner.pl line 148.

    >> MySQLTuner 1.2.0_1 - 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.1.69-cll
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 3G (Tables: 357)
    [--] Data in InnoDB tables: 208K (Tables: 13)
    [!!] Total fragmented tables: 7

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 6m 18s (64K q [170.365 qps], 981 conn, TX: 46M, RX: 16M)
    [--] Reads / Writes: 90% / 10%
    [--] Total buffers: 5.2G global + 6.6M per thread (500 max threads)
    [OK] Maximum possible memory usage: 8.4G (54% of installed RAM)
    [OK] Slow queries: 4% (3K/64K)
    [OK] Highest usage of available connections: 9% (45/500)
    [OK] Key buffer size / total MyISAM indexes: 5.0G/1.5G
    [OK] Key buffer hit rate: 99.9% (62M cached / 90K reads)
    [OK] Query cache efficiency: 63.5% (36K cached / 58K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 5% (114 temp sorts / 1K sorts)
    [!!] Joins performed without indexes: 333
    [OK] Temporary tables created on disk: 12% (482 on disk / 3K total)
    [OK] Thread cache hit rate: 95% (45 created / 981 connections)
    [OK] Table cache hit rate: 97% (322 open / 329 opened)
    [OK] Open file limit used: 1% (416/25K)
    [!!] Table locks acquired immediately: 92%
    [OK] InnoDB data size / buffer pool: 208.0K/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Optimize queries and/or use InnoDB to reduce lock wait
    Variables to adjust:
    join_buffer_size (> 2.0M, or always use indexes with joins)
     
  19. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Nice

    btw.
    thread_cache_size=50
    thread_cache = 128

    those 2 are the same

    current version naming is with _size
    so you can remove thread_cache = 128

    current mysqltuner result was on mysql running for only 6 mins, let it run for at least few more hours and generate mysqltuner and mysqlreport again

    [OK] Temporary tables created on disk: 12% (482 on disk / 3K total)
    [!!] Table locks acquired immediately: 92%

    Table locking is bad one,

    please also run pt-query-digest that I've mentioned before, so we can see which queries are locking, and which table

    Regards
     
  20. imran_khan

    imran_khan Well-Known Member

    Joined:
    Jun 10, 2013
    Messages:
    154
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hello Thinkbot,

    My client complaining "full member database is not accessible" Is this setting will affect database?

    Thanks,
    Imran.
     
Loading...

Share This Page