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 MySQL and server load. Optimization Request

Discussion in 'Workarounds and Optimization' started by seemans, Oct 14, 2015.

  1. seemans

    seemans Member

    Joined:
    Sep 15, 2010
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    My server shows extremely high load with high CPU usage of mysqld, although there is only one site on the server, the server has Good CPU, great disk(SSD)and enough memory, I think.

    To reduce the server load and cpu usage of mysqld, what is the best way to do that?

    1) Optimize my.cnf values ? and how? Please advise.

    2) Change the current CPU(single processer, 4 cores, 8 HT) to Dual processer with more cores like Intel Xeon E5-2620 2.0GHz (dual processors, 12 cores, 24 HT)
    Do you think this will work?

    Code:
    ---------------
    
    # Only 1 site on the server.
    # Database: MariaDB 10.0
    # CPU: E3-1276v3 3.60 GHz  (4 cored, 8HT)
    # Memory: 16GB
    # Disk: 240GB Intel S3500 Enterprise SSD
    
    # The output of TOP command.  The CPU usage of mysqld is over 700%.
    
    load average: 44.39, 39.49, 32.58
    
    Tasks: 451 total,  3 running, 247 sleeping,  0 stopped,  1 zombie
    
    Cpu0  :  90.6%us,  1.0%sy,  0.0%ni, 89.4%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu1  :  90.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu2  : 100.0%us,  0.3%sy,  0.0%ni, 99.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu3  :  99.3%us,  0.0%sy,  0.0%ni, 99.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu4  :  84.3%us,  1.3%sy,  0.0%ni, 84.4%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu5  : 100.0%us,  0.0%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu6  :  97.0%us,  0.7%sy,  0.0%ni, 92.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu7  : 100.0%us,  0.3%sy,  0.3%ni, 99.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    
    Mem:  16290984k total, 14994580k used,  1296404k free,  1480720k buffers
    Swap:  2097148k total,  123264k used,  1973884k free,  8578072k cached
    
    #USER:mysql  #%CPU: 731.4  (very high!!!)
    
    
    
    # My current my.cnf  :  /etc/my.cnf  (default cpanel server)
    
    [mysqld]
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    max_allowed_packet=268435456
    open_files_limit=10000
    
    # mysqltuner result.
    
    ./mysqltuner.pl
    >>  MySQLTuner 1.6.1 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at MySQLTuner-perl by major
    >>  Run with '--help' for additional options and output filtering
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 10.0.21-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
    [--] Data in MyISAM tables: 179M (Tables: 131)
    [--] Data in InnoDB tables: 480K (Tables: 14)
    [!!] Total fragmented tables: 16
    
    -------- Security Recommendations  -------------------------------------------
    [OK] There is no anonymous account in all database users
    [OK] All database users have passwords assigned
    [--] There is 605 basic passwords in the list.
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 33d 9h 57m 47s (259M q [89.882 qps], 2M conn, TX: 1045B, RX: 47B)
    [--] Reads / Writes: 96% / 4%
    [--] Binary logging is disabled
    [--] Total buffers: 416.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum reached memory usage: 838.8M (5.27% of installed RAM)
    [OK] Maximum possible memory usage: 836.0M (5.25% of installed RAM)
    [OK] Slow queries: 0% (90K/259M)
    [!!] Highest connection usage: 100%  (152/151)
    [OK] Aborted connections: 0.26%  (5323/2030757)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 2% (248K temp sorts / 8M sorts)
    [!!] Joins performed without indexes: 2545695
    [OK] Temporary tables created on disk: 25% (1M on disk / 4M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (400 open / 150K opened)
    [OK] Open file limit used: 4% (493/10K)
    [OK] Table locks acquired immediately: 99% (358M immediate / 359M locks)
    
    -------- MyISAM Metrics -----------------------------------------------------
    [!!] Key buffer used: 24.1% (32M used / 134M cache)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/31.5M
    [OK] Read Key buffer hit rate: 100.0% (339B cached / 757K reads)
    [!!] Write Key buffer hit rate: 54.3% (7M cached / 3M writes)
    
    -------- InnoDB Metrics -----------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 128.0M/480.0K
    [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
    [!!] InnoDB Used buffer: 3.86% (316 used/ 8191 total)
    [OK] InnoDB Read buffer efficiency: 99.26% (42164 hits/ 42480 total)
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 11 writes)
    
    -------- AriaDB Metrics -----------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server..
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
      Run OPTIMIZE TABLE to defragment tables for better performance
      Reduce or eliminate persistent connections to reduce connection usage
      Adjust your join queries to always utilize indexes
      Set thread_cache_size to 4 as a starting value
      Increase table_open_cache gradually to avoid file descriptor limits
      Read this before increasing table_open_cache over 64: table_cache negative scalability - MySQL Performance Blog
      Beware that open_files_limit (10000) variable
      should be greater than table_open_cache ( 400)
    Variables to adjust:
      max_connections (> 151)
      wait_timeout (< 28800)
      interactive_timeout (< 28800)
      query_cache_size (>= 8M)
      join_buffer_size (> 128.0K, or always use indexes with joins)
      thread_cache_size (start at 4)
      table_open_cache (> 400)
      innodb_buffer_pool_instances (=1)
    
    
     
    #1 seemans, Oct 14, 2015
    Last edited by a moderator: Oct 14, 2015
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,682
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    You may also want to run a command such as "mysqladmin processlist" to see which databases are using up the most CPU when the server load is high.

    Thank you.
     
  3. seemans

    seemans Member

    Joined:
    Sep 15, 2010
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    Hi, There is one site on the server , and only one database there.
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,682
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    The following thread may help if you want to ensure it's MySQL resulting in the high load:

    Troubleshooting high server loads on Linux servers

    Otherwise, you may want to consult with a qualified system administrator if you don't receive additional user-feedback to this thread.

    Thank you.
     
  5. seemans

    seemans Member

    Joined:
    Sep 15, 2010
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    I found lots of `Waiting for table level lock` by checking `SHOW FULL PROCESSLIST;

    and this causes stop responding new mysql query, and the number of processes of the server goes spike.

    Changing any values on my.cnf in any way didn`t solve this. It still comes up and keep there for a while suddenly again and again and kill my server.

    Please advise what to do.. how to prevent this lock status.. I don`t know what causes this.


    Code:
    MariaDB [(none)]> SHOW FULL PROCESSLIST;
    
    Waiting for table level lock | update products set products_ordered = products_ordered + 1 where products_id = '11174'
    
    Waiting for table level lock | select distinct p.products_id, pd.products_name, p.products_image from products p, products_description pd left join products_details pdt on pd.products_id = pdt.products_id where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '4' order by p.products_ordered desc, pd.products_name limit 100
    
    Waiting for table level lock | select p.products_id, pd.products_name, p.products_model, p.products_price, p.products_weight, p.products_tax_class_id from products p, products_description pd where p.products_id='17406' and pd.products_id = p.products_id and pd.language_id = '4'
    
    Waiting for table level lock | select p.products_id, pd.products_name, p.products_model, p.products_price, p.products_weight, p.products_tax_class_id from products p, products_description pd where p.products_id='20115' and pd.products_id = p.products_id and pd.language_id = '4'
    
    Waiting for table level lock | select distinct p.products_id, pd.products_name, p.products_image from products p, products_description pd left join products_details pdt on pd.products_id = pdt.products_id where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '4' order by p.products_ordered desc, pd.products_name limit 100
    
    
    # current value of :  /etc/my.cnf  (some lines added to default cpanel setting)
    
    [mysqld]
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    max_allowed_packet=268435456
    open_files_limit=10000
    max_connections=300
    thread_cache_size=256
    tmp_table_size=256M
    max_heap_table_size=256M
    table_open_cache=2500
    
    
     
    #5 seemans, Oct 16, 2015
    Last edited by a moderator: Oct 16, 2015
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,682
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page