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.

Optimization Request

Discussion in 'Workarounds and Optimization' started by crazyaboutlinux, Jan 4, 2012.

  1. crazyaboutlinux

    crazyaboutlinux Well-Known Member

    Joined:
    Nov 3, 2007
    Messages:
    938
    Likes Received:
    0
    Trophy Points:
    16
    Hi alphawolf50,

    following is taking too much cpu load

    Code:
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    23387 mysql     15   0  376m 118m 4224 S 148.6  3.0   0:33.71 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/serverhotname 
    Code:
    >> Hardware specs (#cpu cores, RAM)
    Operating System: CentOS 64-Bit Latest
    Server Specifications: Single Intel XEON (3430) 2.4 8MB 1156
    
    Total processors: 4
    
    Processor #1
    
        Vendor
            GenuineIntel
    
        Name
            Intel(R) Xeon(R) CPU X3430 @ 2.40GHz
    
        Speed
            1200.000 MHz
    
        Cache
            8192 KB
    
    root@server [~]# free -m
                 total       used       free     shared    buffers     cached
    Mem:          3940       2964        976          0        115       2184
    -/+ buffers/cache:        664       3276
    Swap:         4094        113       3980
    
    >> Contents of /etc/my.cnf
    Code:
    root@server [~]# cat /etc/my.cnf
    [mysqld]
    set-variable = max_connections=150
    wait_timeout = 50
    log-slow-queries
    safe-show-database
    local-infile = 0
    
    table_cache=256
    max_delayed_threads=20
    max_tmp_tables=32
    query_cache_type=1
    query_cache_size=96M
    max_heap_table_size=96M
    tmp_table_size=96M
    thread_cache_size=4
    
    
    >> Output of mysqltuner.pl
    Code:
    root@server [~]# ./mysqltuner.pl
    
     >>  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.1.56-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 2G (Tables: 730)
    [--] Data in InnoDB tables: 256K (Tables: 9)
    [--] Data in ARCHIVE tables: 160K (Tables: 12)
    [--] Data in MEMORY tables: 0B (Tables: 3)
    [!!] Total fragmented tables: 24
    
    -------- Security Recommendations  -------------------------------------------
    [!!] User 'eximstats@localhost' has no password set.
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 18m 11s (1M q [1K qps], 3K conn, TX: 889M, RX: 70M)
    [--] Reads / Writes: 98% / 2%
    [--] Total buffers: 210.0M global + 2.7M per thread (150 max threads)
    [OK] Maximum possible memory usage: 622.5M (15% of installed RAM)
    [OK] Slow queries: 0% (37/1M)
    [OK] Highest usage of available connections: 15% (23/150)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/241.8M
    [OK] Key buffer hit rate: 99.9% (120M cached / 70K reads)
    [OK] Query cache efficiency: 98.0% (1M cached / 1M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)
    [!!] Joins performed without indexes: 297
    [!!] Temporary tables created on disk: 34% (1K on disk / 2K total)
    [OK] Thread cache hit rate: 92% (243 created / 3K connections)
    [OK] Table cache hit rate: 95% (147 open / 154 opened)
    [OK] Open file limit used: 22% (235/1K)
    [OK] Table locks acquired immediately: 99% (36K immediate / 36K locks)
    [OK] InnoDB data size / buffer pool: 256.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
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 96M)
        max_heap_table_size (> 96M)
    
    >> Output of top

    Code:
    top - 13:18:45 up 5 days, 22:40,  4 users,  load average: 6.57, 8.67, 16.75
    Tasks: 238 total,   4 running, 233 sleeping,   0 stopped,   1 zombie
    Cpu(s): 38.2%us, 21.6%sy,  0.0%ni, 35.9%id,  4.2%wa,  0.0%hi,  0.1%si,  0.0%st
    Mem:   4034988k total,  3419560k used,   615428k free,   134336k buffers
    Swap:  4192924k total,   116560k used,  4076364k free,  2580900k cached
    
    PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    32261 mysql     15   0  465m  67m 4360 S 181.6  1.7  
    Please guide me how to solve this ???
     
  2. crazyaboutlinux

    crazyaboutlinux Well-Known Member

    Joined:
    Nov 3, 2007
    Messages:
    938
    Likes Received:
    0
    Trophy Points:
    16
    Re: Best MySQL Optimizing Guide

    top
    top - 16:08:27 up 6 days, 1:30, 4 users, load average: 12.71, 15.45, 15.67
    Tasks: 251 total, 2 running, 243 sleeping, 0 stopped, 6 zombie
    Cpu(s): 30.2%us, 22.0%sy, 0.0%ni, 1.1%id, 46.3%wa, 0.0%hi, 0.4%si, 0.0%st
    Mem: 4034988k total, 3157776k used, 877212k free, 275336k buffers
    Swap: 4192924k total, 116560k used, 4076364k free, 1976288k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    32261 mysql 15 0 516m 131m 4684 S 149.7 3.3 166:45.20 mysqld
     
  3. crazyaboutlinux

    crazyaboutlinux Well-Known Member

    Joined:
    Nov 3, 2007
    Messages:
    938
    Likes Received:
    0
    Trophy Points:
    16
    Re: Best MySQL Optimizing Guide

    server load is getting increased

    top - 16:21:16 up 6 days, 1:43, 4 users, load average: 20.35, 26.64, 21.71
    Tasks: 585 total, 1 running, 584 sleeping, 0 stopped, 0 zombie
    Cpu(s): 4.5%us, 2.6%sy, 0.0%ni, 5.1%id, 87.5%wa, 0.2%hi, 0.2%si, 0.0%st
    Mem: 4034988k total, 3856904k used, 178084k free, 24060k buffers
    Swap: 4192924k total, 294448k used, 3898476k free, 1631276k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    32261 mysql 15 0 660m 137m 4684 S 4.6 3.5 171:49.88 mysqld





    cat /proc/loadavg
    44.52 30.81 23.67 1/842 3167
     
  4. crazyaboutlinux

    crazyaboutlinux Well-Known Member

    Joined:
    Nov 3, 2007
    Messages:
    938
    Likes Received:
    0
    Trophy Points:
    16
    Re: Best MySQL Optimizing Guide

    any update guys ??
     
  5. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,450
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Re: Best MySQL Optimizing Guide

    I've moved all your posts to a new thread. No need to hijack someone else's thread I don't think.
     
  6. crazyaboutlinux

    crazyaboutlinux Well-Known Member

    Joined:
    Nov 3, 2007
    Messages:
    938
    Likes Received:
    0
    Trophy Points:
    16
    Thank you inforpro
     
  7. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hi crazy :)

    I do this in my free time, which has been scarce lately, so I'll likely be a tad slow responding on the forums for a while. That being said, let's get started...

    First thing, your mysqltuner output was for only 18 mintues of operation -- that deprives me of a lot of information I need to give you good advice. Please make sure any mysqltuner results you send are for at least 24 hours of operation, preferably 48.

    Here's what sticks out based on what you've posted so far:

    1. You've not set key_buffer_size. Please add key_buffer_size=256M, which is just large enough to cover your MyISAM indexes.
    2. Your thread_cache_size is only 4, but your maximum used connections so far is 23. You can save your server a small amount of work in recreating threads by setting thread_cache_size=16. We may increase this later, based on your next run of mysqltuner.
    3. You could benefit from adding myisam_use_mmap=1.
    4. You don't need to have all this: "set-variable = max_connections=150" -- just "max_connections=150" is sufficient with MySQL 5.1.x.
    5. Erase your table_cache setting and replace it with table_open_cache=1024. This will probably be increased later, pending your new mysqltuner results.
    6. Add table_definition_cache=768
    7. I like to set sort_buffer_size=256K, and then increase it only if really necessary. Will likely be adjusted further based on mysqltuner...
    8. Set join_buffer_size=256K.
    9. Way too many temp tables are being created on disk, especially in the first 18 minutes. Ideally you'd track down which queries are creating all these temp tables and modify them to be more efficient -- if you can't, bump both max_heap_table_size and tmp_table_size to 128M
    10. Remove safe-show-database -- it doesn't do anything in MySQL 5.1.x

    That should get you started on MySQL. I do want to point out though -- people like to blame MySQL for their high load just because MySQL appears at the top when they run the top command, but that doesn't necessarily mean it is the cause of their high load. Your system has high I/O wait% and high system % -- tweaking MySQL might fix these, but it's also possible that you've got a hard drive going bad or maybe you've got excessive network traffic. There are many factors, and we'll explore each depending on how this goes.

    Please do ensure MySQL has been running at least 24 hours before posting your next msyqltuner result. Also, I'd like to see another output from "top", including at least the top 10 processes. If you could put everything inside CODE tags it will help tremendously.
     
  8. crazyaboutlinux

    crazyaboutlinux Well-Known Member

    Joined:
    Nov 3, 2007
    Messages:
    938
    Likes Received:
    0
    Trophy Points:
    16
    Hi alphawolf50,

    Mysql on the server was taking load, and I have tweaked /etc/my.cnf and the server load has come down since then. The lines I modified are:

    ---------------
    query_cache_size=140M
    tmp_table_size=512M
    sort_buffer_size=4M
    join_buffer_size=4M
    ---------------
    top process list
    Code:
    top - 16:48:25 up 12 days,  2:10,  2 b2bs,  load average: 2.05, 2.19, 2.45
    Tasks: 214 total,   1 running, 212 sleeping,   0 stopped,   1 zombie
    Cpu(s): 16.7%us, 17.8%sy,  0.0%ni, 55.2%id,  9.8%wa,  0.1%hi,  0.3%si,  0.0%st
    Mem:   4034988k total,  3694864k used,   340124k free,   285348k buffers
    Swap:  4192924k total,   152288k used,  4040636k free,  2341304k cached
    
      PID b2b      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    15341 mysql     15   0 3912m 1.1g 782m S 67.8 28.5 851:21.35 mysqld
    12031 root      16   0  196m 102m 3108 S  3.3  2.6   0:22.52 spamd
     7587 b2b  16   0  102m  11m 6220 S  2.3  0.3   0:00.07 php
     7324 nobody    16   0  190m  72m 1952 S  2.0  1.8   0:00.36 httpd
     6078 nobody    16   0  190m  72m 1948 S  1.7  1.8   0:00.11 httpd
     7399 nobody    16   0  190m  71m 1912 S  1.7  1.8   0:00.17 httpd
     7409 nobody    15   0  190m  71m 1932 S  1.7  1.8   0:00.11 httpd
     7425 nobody    16   0  190m  72m 1960 S  1.7  1.8   0:00.15 httpd
     7427 nobody    16   0  190m  72m 1940 S  1.7  1.8   0:00.18 httpd
     6378 nobody    15   0  190m  72m 1956 S  1.3  1.8   0:01.21 httpd
     6891 nobody    16   0  190m  72m 1968 S  1.3  1.8   0:00.96 httpd
     7402 nobody    15   0  190m  72m 1956 S  1.3  1.8   0:00.16 httpd
     7407 nobody    16   0  190m  72m 1956 S  1.3  1.8   0:00.15 httpd
     7590 b2b  16   0  102m  11m 6180 S  1.3  0.3   0:00.04 php
     6407 nobody    15   0  190m  72m 1948 S  1.0  1.8   0:01.21 httpd
     7224 nobody    16   0  190m  72m 1960 S  1.0  1.8   0:00.46 httpd
     7412 nobody    16   0  190m  72m 1952 S  1.0  1.8   0:00.16 httpd
     6697 nobody    15   0  190m  72m 1964 S  0.7  1.8   0:00.86 httpd
     6704 nobody    15   0  190m  72m 1980 S  0.7  1.8   0:00.81 httpd
     7313 nobody    16   0  190m  72m 1948 S  0.7  1.8   0:00.30 httpd
     7321 nobody    16   0  190m  72m 1936 S  0.7  1.8   0:00.19 httpd
     7322 nobody    15   0  190m  72m 1936 S  0.7  1.8   0:00.17 httpd
     7325 nobody    15   0  190m  72m 1952 S  0.7  1.8   0:00.27 httpd
     1725 root      10  -5     0    0    0 S  0.3  0.0   0:54.95 kjournald
     2949 named     21   0  325m  58m 1892 S  0.3  1.5  15:12.99 named
     5748 root      18   0  126m  25m 1552 S  0.3  0.7   0:06.58 lfd
     5785 root      15   0 49704  10m 2388 S  0.3  0.3   0:04.72 tailwatchd
     7048 nobody    16   0  190m  72m 1948 S  0.3  1.8   0:00.60 httpd
     7087 root      15   0 12892 1232  824 R  0.3  0.0   0:00.36 top
     7411 nobody    15   0  190m  71m 1944 S  0.3  1.8   0:00.09 httpd
     7416 nobody    16   0  190m  72m 1952 S  0.3  1.8   0:00.16 httpd
     7431 nobody    16   0  190m  71m 1940 S  0.3  1.8   0:00.11 httpd
     7433 nobody    16   0  190m  72m 1960 S  0.3  1.8   0:00.15 httpd
     7436 nobody    16   0  190m  72m 1948 S  0.3  1.8   0:00.04 httpd
     7581 nobody    15   0  190m  71m 1920 S  0.3  1.8   0:00.01 httpd
    29227 root      18   0  188m  71m 3232 S  0.3  1.8   6:52.77 httpd
        1 root      15   0 10368  588  552 S  0.0  0.0   0:02.84 init
    
     
  9. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hi Crazy,

    You went rogue on me! You can't just trust mysqltuner's recommendations blindly, because it uses some pretty simple formulas to address complex issues. I don't look at the recommendations so much as I look at the additional data it provides. How did you decide upon these values?

    1. tmp_table_size is WAY too high. tmp_table_size is limited by max_heap_table_size, so although you set tmp_table_size to 512M (way too large), it is still limited to 128M by max_heap_table_size. You have to increase these together, and incrementally (from 128M to 512M is a HUGE jump.). Try setting these both to 160M (an increase of 32M).
    2. sort_buffer_size -- again, that was a huge jump from 256K to 4M. Why did you jump this high? First, from the MySQL documentation (MySQL :: MySQL 5.1 Reference Manual :: 5.1.3 Server System Variables):
      This explains a little better why, for most people, 256K is a better value, despite what mysqltuner tells you:More on understanding sort_buffer_size « MySQL Expert | MySQL Performance | MySQL Consulting. If you MUST go above 256K, do it in small increments (~256K each) and don't go above 2M unless you've got proof that it actually improves performance in your specific environment.
    3. join_buffer_size -- This one is even worse than sort_buffer_size to have as a large value. It is allocated not only per-thread, but per-join per-thread, and it is the minimum amount allocated, not the maximum. Forget mysqltuner, set this to 256K. MySQL will allocate more memory for joins if it thinks it needs it. You are better off properly indexing your tables than tweaking this variable.

    You also have a boatload of httpd processes running -- you should consider using MPM_worker.

    The next time your server load is high, please run this and post the results in CODE tags:
    Code:
    vmstat 5 5
    .
     
  10. crazyaboutlinux

    crazyaboutlinux Well-Known Member

    Joined:
    Nov 3, 2007
    Messages:
    938
    Likes Received:
    0
    Trophy Points:
    16
    Code:
    top - 12:40:39 up 12 days, 22:02,  3 users,  load average: 36.44, 46.98, 39.51
    Tasks: 759 total,   2 running, 750 sleeping,   0 stopped,   7 zombie
    Cpu(s):  2.1%us,  1.2%sy,  0.1%ni, 15.2%id, 80.7%wa,  0.4%hi,  0.3%si,  0.0%st
    Mem:   4034988k total,  3430860k used,   604128k free,     2148k buffers
    Swap:  4192924k total,   745200k used,  3447724k free,   744888k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    10847 b2b  16   0 12684  988  796 D  3.2  0.0   0:00.10 /usr/lib/courier-imap/bin/imapd /home/b2b/mail/xyz.com/shrenik
      323 root      10  -5     0    0    0 D  1.9  0.0   5:27.90 [kswapd0]
     8276 root      26  10 13288 1624  764 R  1.3  0.0   0:08.37 top -c
    10801 b2b  18   0  102m  10m 5880 D  1.0  0.3   0:00.07 /usr/bin/php /home/b2b/public_html/traderoom/index.php
    10813 b2b  18   0  102m  10m 5680 D  0.6  0.3   0:00.05 /usr/bin/php /home/b2b/public_html/ceramic-tile/index.php
    10838 b2b  18   0 55616 6380 3176 D  0.6  0.2   0:00.03 /usr/local/cpanel/3rdparty/bin/php-cgi /usr/local/cpanel/base/3rdparty/squirrelmail/src/left_main.php
    10848 root      18   0 29144 4064 1464 D  0.6  0.1   0:00.04 /usr/bin/perl /usr/local/cpanel/scripts/restartsrv_named --check
    15341 mysql     15   0 4236m 980m 689m S  0.6 24.9   1573:01 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/xyzineti
       67 root      10  -5     0    0    0 S  0.3  0.0   0:16.34 [kblockd/1]
     6683 nobody    16   0  190m  31m 1956 S  0.3  0.8   0:00.79 /usr/local/apache/bin/httpd -k start -DSSL
     7862 nobody    16   0  190m  31m 1956 S  0.3  0.8   0:00.17 /usr/local/apache/bin/httpd -k start -DSSL
     8659 nobody    16   0  189m  31m 1852 S  0.3  0.8   0:00.02 /usr/local/apache/bin/httpd -k start -DSSL
    10162 b2b  16   0  152m  45m 2568 D  0.3  1.2   0:00.33 spamd child
    10836 root      18   0 34604 5500 1508 D  0.3  0.1   0:00.07 /usr/bin/perl /usr/local/cpanel/scripts/restartsrv_mysql --check
    10837 b2b  18   0 55360 6312 3176 D  0.3  0.2   0:00.03 /usr/local/cpanel/3rdparty/bin/php-cgi /usr/local/cpanel/base/3rdparty/squirrelmail/src/right_main.php
    10849 root      18   0 31740 4648 1472 D  0.3  0.1   0:00.04 /usr/bin/perl /usr/local/cpanel/scripts/mysqlconnectioncheck
    10850 root      18   0 65272 3916 2680 D  0.3  0.1   0:00.01 /usr/sbin/exim -Mc 1RksHx-0002hh-1o
    10851 b2b  18   0 90372 3548 2880 D  0.3  0.1   0:00.02 /usr/bin/php /home/b2b/public_html/chat/admin/login.php
        1 root      15   0 10368  584  548 S  0.0  0.0   0:03.02 init [3]
        2 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 [migration/0]
        3 root      34  19     0    0    0 S  0.0  0.0   0:00.82 [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:00.00 [migration/1]
        6 root      34  19     0    0    0 S  0.0  0.0   0:00.33 [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.00 [migration/2]
        9 root      34  19     0    0    0 S  0.0  0.0   0:00.26 [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:00.00 [migration/3]
       12 root      34  19     0    0    0 S  0.0  0.0   0:00.54 [ksoftirqd/3]
       13 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 [watchdog/3]
       14 root      10  -5     0    0    0 S  0.0  0.0   0:00.06 [events/0]
       15 root      10  -5     0    0    0 S  0.0  0.0   0:00.04 [events/1]
       16 root      10  -5     0    0    0 S  0.0  0.0   0:00.02 [events/2]
       17 root      10  -5     0    0    0 S  0.0  0.0   0:00.01 [events/3]
       18 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 [khelper]
       59 root      10  -5     0    0    0 S  0.0  0.0   0:00.01 [kthread
    
    output of vmstat 5 5
    Code:
    [~]# 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 21 745216 651312   2168 741276    1    1   198  1375    3    4 17  9 57 17  0
     0 20 745212 645868   1760 736620 1094    0  1960   385 1231 1012  1  2 11 86  0
     0 21 745212 634756   1892 741148 1274    0  3120   186 1261 1155  1  2 20 78  0
     0 26 745212 626768   1988 742352  958    0  2516   190 1254 1160  1  2 31 66  0
     0 24 745208 631672   2196 741032  744    0  1889   225 1238 1115  2  2 21 75  0
    
     
    #10 crazyaboutlinux, Jan 11, 2012
    Last edited: Jan 11, 2012
  11. NixTree

    NixTree Well-Known Member

    Joined:
    Aug 19, 2010
    Messages:
    386
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Gods Own Country
    cPanel Access Level:
    Root Administrator
    Hello,

    With your last reply, it looks like the IO wait on your server is pretty high!!!Have you checked, why it is showing high?

    Thank you,
    Nibin.
     
  12. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Your system is swapping, hence the high I/O wait. Please post your entire my.cnf as it currently exists so I can see where we are currently. Might as well post another mysqltuner while you're at it.
     
  13. crazyaboutlinux

    crazyaboutlinux Well-Known Member

    Joined:
    Nov 3, 2007
    Messages:
    938
    Likes Received:
    0
    Trophy Points:
    16
    Here we go

    Code:
    root@server[~]# cat /etc/my.cnf
    [mysqld]
    max_connections=150
    wait_timeout = 50
    log-slow-queries
    local-infile = 0
    
    table_open_cache=1024
    table_definition_cache=768
    max_delayed_threads=20
    max_tmp_tables=256
    query_cache_type=1
    query_cache_size=140M
    max_heap_table_size=128M
    tmp_table_size=512M
    thread_cache_size=16
    key_buffer_size=256M
    myisam_use_mmap=1
    sort_buffer_size=4M
    join_buffer_size=4M
    root@myserver [~]#
    
    Code:
    root@server [~]# ./mysqltuner.pl
    
     >>  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.1.56-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 3G (Tables: 730)
    [--] Data in InnoDB tables: 288K (Tables: 9)
    [--] Data in ARCHIVE tables: 160K (Tables: 12)
    [--] Data in MEMORY tables: 0B (Tables: 3)
    [!!] Total fragmented tables: 24
    
    -------- Security Recommendations  -------------------------------------------
    [!!] User 'eximstats@localhost' has no password set.
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 17h 14m 56s (20M q [333.562 qps], 85K conn, TX: 25B, RX: 1B)
    [--] Reads / Writes: 97% / 3%
    [--] Total buffers: 534.0M global + 8.6M per thread (150 max threads)
    [OK] Maximum possible memory usage: 1.8G (46% of installed RAM)
    [OK] Slow queries: 0% (570/20M)
    [OK] Highest usage of available connections: 19% (29/150)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/246.3M
    [OK] Key buffer hit rate: 100.0% (3B cached / 43K reads)
    [OK] Query cache efficiency: 96.4% (19M cached / 20M selects)
    [!!] Query cache prunes per day: 9565
    [OK] Sorts requiring temporary tables: 0% (34 temp sorts / 94K sorts)
    [!!] Joins performed without indexes: 7690
    [!!] Temporary tables created on disk: 28% (32K on disk / 113K total)
    [OK] Thread cache hit rate: 99% (43 created / 85K connections)
    [OK] Table cache hit rate: 99% (971 open / 980 opened)
    [OK] Open file limit used: 77% (1K/2K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] InnoDB data size / buffer pool: 288.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
        Increasing the query_cache size over 128M may reduce performance
        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
    Variables to adjust:
        query_cache_size (> 140M) [see warning above]
        join_buffer_size (> 4.0M, or always use indexes with joins)
        tmp_table_size (> 512M)
        max_heap_table_size (> 128M)
    root@server [~]#
    
     
  14. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    You didn't do any of the changes I listed above: http://forums.cpanel.net/f189/optimization-request-250782.html#post1044931

    Your system is swapping, which means low memory. You have low memory because your per-session buffers are too large. Make the changes below and restart mysql. My previous post (linked) explains why.

    Code:
    join_buffer_size=256K
    sort_buffer_size=2M
    temp_table_size=160M
    max_heap_table_size=160M
     
  15. joomlads07

    joomlads07 Registered

    Joined:
    Aug 10, 2010
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    3 Locust Street, Suite A
    I got the information i was looking for want more thanks alphawolf50..:)
     
  16. crazyaboutlinux

    crazyaboutlinux Well-Known Member

    Joined:
    Nov 3, 2007
    Messages:
    938
    Likes Received:
    0
    Trophy Points:
    16
    sorry for the late update

    the server load was normal till the last weekend but today it went up very high

    today's mysqltuner report
    Code:
    root@mficorp [~]# ./mysqltuner.pl
    
     >>  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.1.56-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 2G (Tables: 740)
    [--] Data in InnoDB tables: 256K (Tables: 9)
    [--] Data in ARCHIVE tables: 160K (Tables: 12)
    [--] Data in MEMORY tables: 0B (Tables: 3)
    [!!] Total fragmented tables: 23
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4d 20h 26m 5s (144M q [344.380 qps], 709K conn, TX: 193B, RX: 10B)
    [--] Reads / Writes: 96% / 4%
    [--] Total buffers: 566.0M global + 2.9M per thread (150 max threads)
    [OK] Maximum possible memory usage: 997.2M (25% of installed RAM)
    [OK] Slow queries: 0% (6K/144M)
    [!!] Highest connection usage: 100%  (151/150)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/224.4M
    [OK] Key buffer hit rate: 100.0% (29B cached / 333K reads)
    [OK] Query cache efficiency: 95.9% (136M cached / 142M selects)
    [!!] Query cache prunes per day: 62339
    [OK] Sorts requiring temporary tables: 0% (199 temp sorts / 717K sorts)
    [!!] Joins performed without indexes: 74526
    [!!] Temporary tables created on disk: 30% (255K on disk / 830K total)
    [OK] Thread cache hit rate: 99% (1K created / 709K connections)
    [OK] Table cache hit rate: 22% (1K open / 4K opened)
    [OK] Open file limit used: 61% (1K/2K)
    [OK] Table locks acquired immediately: 99% (10M immediate / 10M locks)
    [OK] InnoDB data size / buffer pool: 256.0K/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce or eliminate persistent connections to reduce connection usage
        Increasing the query_cache size over 128M may reduce performance
        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
    Variables to adjust:
        max_connections (> 150)
        wait_timeout (< 50)
        interactive_timeout (< 28800)
        query_cache_size (> 140M) [see warning above]
        join_buffer_size (> 256.0K, or always use indexes with joins)
        tmp_table_size (> 160M)
        max_heap_table_size (> 160M)
    
    present my.cnf file

    Code:
    [mysqld]
    max_connections=150
    wait_timeout = 50
    log-slow-queries
    local-infile = 0
    
    table_open_cache=1024
    table_definition_cache=768
    max_delayed_threads=20
    max_tmp_tables=256
    query_cache_type=1
    query_cache_size=140M
    max_heap_table_size=160M
    tmp_table_size=160M
    thread_cache_size=16
    key_buffer_size=256M
    myisam_use_mmap=1
    sort_buffer_size=2M
    join_buffer_size=256K
    
     
  17. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hi Crazy,

    It looks like at some point your maximum connections were reached (150), whereas previously you never reached more than 50 simultaneous connections. Are you aware of any large traffic spikes? Or any new sites on the server that are generating a lot of traffic?

    Please post a new "vmstat 5 5" and "top" so I can look for issues outside of mysql. Once you've got that info, please replace your my.cnf with the one below and reboot. It's important that you get the info from vmstat and top before mysql is rebooted.

    Code:
    max_connections=200
    wait_timeout = 50
    log-slow-queries
    local-infile = 0
    
    table_open_cache=1536
    table_definition_cache=768
    query_cache_size=140M
    max_heap_table_size=192M
    tmp_table_size=192M
    thread_cache_size=48
    key_buffer_size=256M
    myisam_use_mmap=1
    sort_buffer_size=512K
    join_buffer_size=256K
    
     
  18. crazyaboutlinux

    crazyaboutlinux Well-Known Member

    Joined:
    Nov 3, 2007
    Messages:
    938
    Likes Received:
    0
    Trophy Points:
    16
    Are you aware of any large traffic spikes? Or any new sites on the server that are generating a lot of traffic?
    >> i think 1 should be, there is only 1 old site hosted on the server which is generating a lot of traffic & that is the b2b portal.

    I have got the report of top & vmstat 5 5 before mysql rebooted
    Code:
    top - 13:51:29 up 24 days, 23:13,  2 users,  load average: 31.91, 25.16, 15.48
    Tasks: 399 total,   1 running, 338 sleeping,   0 stopped,  60 zombie
    Cpu(s):  2.3%us, 25.3%sy,  0.0%ni,  4.2%id, 68.1%wa,  0.0%hi,  0.2%si,  0.0%st
    Mem:   4034988k total,  2949964k used,  1085024k free,    37800k buffers
    Swap:  4192924k total,   494364k used,  3698560k free,  1442620k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    24835 mysql     15   0 3302m 1.1g 755m S 99.7 27.5   4269:19 mysqld
    28569 cpanelro  16   0  118m  16m 4576 S  2.7  0.4   0:00.20 php-cgi
    28273 btotbb2b  18   0 12896 1236  872 D  2.0  0.0   0:01.80 imapd
    27673 nobody    15   0  190m  72m 1960 S  0.7  1.8   0:00.49 httpd
    28362 nobody    16   0  191m  72m 1956 S  0.7  1.8   0:00.33 httpd
    27449 nobody    15   0  191m  72m 1964 S  0.3  1.8   0:00.53 httpd
    27701 nobody    15   0  191m  72m 1964 S  0.3  1.8   0:00.66 httpd
    28148 nobody    15   0     0    0    0 Z  0.3  0.0   0:00.42 httpd <defunct>
    28435 nobody    15   0  191m  72m 1940 S  0.3  1.8   0:00.14 httpd
    28445 nobody    15   0  190m  72m 1928 S  0.3  1.8   0:00.30 httpd
    28721 btotbb2b  17   0 12824 1172  836 D  0.3  0.0   0:00.06 imapd
    28739 btotbb2b  16   0 88252 9340 2396 S  0.3  0.2   0:00.01 cpsrvd-ssl
        1 root      15   0 10368  580  544 S  0.0  0.0   0:05.61 init
        2 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 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   0:00.00 watchdog/0
        5 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 migration/1
        6 root      34  19     0    0    0 S  0.0  0.0   0:00.70 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.00 migration/2
        9 root      34  19     0    0    0 S  0.0  0.0   0:00.52 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:00.00 migration/3
       12 root      34  19     0    0    0 S  0.0  0.0   0:01.23 ksoftirqd/3
       13 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/3
       14 root      10  -5     0    0    0 S  0.0  0.0   0:00.11 events/0
       15 root      10  -5     0    0    0 S  0.0  0.0   0:00.07 events/1
       16 root      10  -5     0    0    0 S  0.0  0.0   0:00.05 events/2
       17 root      10  -5     0    0    0 S  0.0  0.0   0:00.03 events/3
       18 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 khelper
       59 root      10  -5     0    0    0 S  0.0  0.0   0:00.01 kthread
       66 root      10  -5     0    0    0 S  0.0  0.0   0:01.95 kblockd/0
       67 root      10  -5     0    0    0 S  0.0  0.0   0:31.22 kblockd/1
       68 root      10  -5     0    0    0 S  0.0  0.0   0:01.86 kblockd/2
       69 root      10  -5     0    0    0 S  0.0  0.0   0:02.20 kblockd/3
       70 root      14  -5     0    0    0 S  0.0  0.0   0:00.00 kacpid
      220 root      14  -5     0    0    0 S  0.0  0.0   0:00.00 cqueue/0
    
    vmstat 5 5 report
    Code:
     # 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  5 494340 1267032  81532 1553252    2    1   193  1253    3    3 17 10 56 17  0
     0  7 494340 1315076  81580 1506232    0    0   570   115 1077  780  1  1 61 38  0
     1  1 494340 1313916  82200 1578392    0    0 18418   369 1457 7025 29 14 31 26  0
     2  1 494340 1189840  82808 1643552    0    0 10307   370 1475 5324 15  7 53 25  0
     1  1 494340 984892  83900 1850872    7    0 22332   425 1476 5792 18 17 39 26  0
    
    i have replaced my.cnf as you mentioned & rebooted mysql service

    status after rebooting mysql
    # service mysql status
    MySQL running (1750) [ OK ]
     
  19. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Your system was swapping some, in addition to using all of its mysql connections. The new my.cnf will reduce max memory usage of MySQL by about 150MB, even with the increased connection limit. It's time to look at switching Apache over to mpm_worker, which handles high concurrency with less resources. Please post the output of these commands:
    Code:
    # grep -A 17 'These can be set in WHM' /usr/local/apache/conf/httpd.conf
    # service httpd status
    
    We should also look at your storage/filesystem to see if there are any bottlenecks or issues there. Your server host should be able to answer the RAID controller questions if you don't know the answer.
    1. Are you using RAID? If so:
      • Which level? (0, 1, 5, etc)
      • Do you which RAID controller is being used?
      • Does your RAID controller have a BBU?
      • Is the write cache enabled on the RAID controller?
    2. Which filesystem (ext3, ext4, etc) are you using?
    3. Do you have separate partitions for /var and /home?
    4. Have you mounted any partitions with noatime and nodiratime?

    If you don't know the answers to 2-4, you can just post the results of this:
    Code:
    # cat /etc/fstab
     
  20. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hi Crazy,

    I forgot to include the "[mysqld]" bit at the top of the last my.cnf I posted. In case you copied & pasted, please make sure [mysqld] is at the top of the file.
     
Loading...

Share This Page