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.

Mysql process > 1000% in CPU Load

Discussion in 'Workarounds and Optimization' started by Dolphin882, Nov 8, 2011.

  1. Dolphin882

    Dolphin882 Member

    Joined:
    Nov 8, 2011
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi all
    I am new here.
    I am having problem with the mysqld process; when I run TOP command from SSH; it shows that process is sometimes using > 1000 % of the CPU Load!

    Our server with 16 Core ; 128GB ram
    Here is my.cnf settings looks like

    Code:
    [client]
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    
    [mysqld_safe]
    socket          = /var/lib/mysql/mysql.sock
    nice            = 0
    
    [mysqld]
    user            = mysql
    pid-file        = /var/lib/mysql/mysqld.pid
    datadir        = /var/lib/mysql
    socket        = /var/lib/mysql/mysql.sock
    basedir        = /usr
    port        = 3306
    tmpdir        = /db/tmpfs01
    language        = /usr/share/mysql/english
    skip-external-locking
    skip-name-resolve
    skip-networking
    skip-locking
    #UTF-8
    default-character-set=utf8
    character-set-server=utf8
    collation-server=utf8_general_ci
    
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address            = 127.0.0.1
    
    
    key_buffer              = 256M
    key_buffer_size     = 512M
    max_allowed_packet      = 128M
    thread_cache        = 8
    thread_stack            = 128K
    thread_cache_size       = 8
    
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover         = BACKUP
    
    max_connections        = 500
    #max_user_connections     = 500
    max_connect_errors    = 100
    connect_timeout        =60
    interactive_timeout    =60
    wait_timeout        =60
    
    delayed_insert_timeout    =120
    join_buffer_size    =32M
    sort_buffer_size    =512M
    myisam_sort_buffer_size    =64M
    read_buffer_size    =64M
    read_rnd_buffer_size    =64M
    table_cache        =80
    query_cache_size    =512M
    query_cache_limit    =256M
    query_cache_type    =2
    #query_cache_min_res_unit = 1024
    tmp_table_size        = 512M
    #max_tmp_tables        = 1024
    max_heap_table_size     = 256M
    long_query_time        = 2
    low_priority_updates = 1
    thread_concurrency = 8
    concurrent_insert = 2
    default-character-set=utf8
    #
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    # server-id              = 1
    # log_bin                        = /var/log/mysql/mysql-bin.log
    expire_logs_days        = 10
    max_binlog_size         = 100M
    server-id               = 1
    binlog-ignore-db        = mysql
    replicate-ignore-db     = mysql
    
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1
    
    # Disabling symbolic-links is recommended to prevent assorted security risks;
    # to do so, uncomment this line:
    # symbolic-links=0
    
    #[mysqld_safe]
    #log-error=/var/log/mysqld.log
    #pid-file=/var/run/mysqld/mysqld.pid
    
    #server-id = 2
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet      = 128M
    
    [mysql]
    #no-auto-rehash # faster start of mysql but no tab completition
    no-auto-rehash
    
    [isamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 32M
    write_buffer = 32M
    
    [myisamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 32M
    write_buffer = 32M
    
    [mysqlhotcopy]
    interactive-timeout
    #
    # * IMPORTANT: Additional settings that can override those from this file!
    #   The files must end with '.cnf', otherwise they'll be ignored.
    #
    #!includedir /etc/mysql/conf.d/
    


    PLEASE HELP, ALL the website hosted on this linux webserver loading extremely low when the CPU load for mysqld gones high !
     
  2. storminternet

    storminternet Well-Known Member

    Joined:
    Nov 2, 2011
    Messages:
    462
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Have you checked mysql process list by command mysqladmin pr when mysql is eating cpu resources.
    From the process list you will come to know the exact database which is consuming high cpu resources.
     
    #2 storminternet, Nov 9, 2011
    Last edited: Nov 9, 2011
  3. Dolphin882

    Dolphin882 Member

    Joined:
    Nov 8, 2011
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi Storm

    Thanks for the reply

    I run this command but got many lines like this

    Code:
     82795  | leechprotect     | localhost | leechprotect     | Sleep   | 1311 |                      |                                                                                                      |
    | 116228 | admin_phuketnews | localhost | admin_phuketnews | Query   | 0    | Locked               | UPDATE `wp_posts` SET `guid` = 'http://phuketnews.easybranches.com/2011/11/11/standoff-looms-at-anti |
    | 119866 | easyurls_phuket  | localhost | easyurls_phuket  | Sleep   | 29   |                      |                                                                                                      |
    | 361748 | admin_chinanews  | localhost | admin_chinanews  | Query   | 6    | Sending data         | SELECT COUNT(*) FROM wp_term_relationships, wp_posts WHERE wp_posts.ID = wp_term_relationships.objec |
    | 414758 | admin_easynews   | localhost | admin_main       | Query   | 134  | Waiting for table    | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (600574,600571,600570,600572, |
    | 416935 | admin_easynews   | localhost | admin_main       | Query   | 116  | Waiting for table    | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (600574,600571,600570,600572, |
    | 419679 | admin_easynews   | localhost | admin_main       | Query   | 123  | Waiting for table    | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (600574,600571,600570,600572, |
    | 421483 | admin_easynews   | localhost | admin_main       | Query   | 70   | Waiting for table    | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (600574,600571,600570,600572, |
    | 427533 | admin_easynews   | localhost | admin_main       | Query   | 93   | Waiting for table    | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (600574,600571,600570,600572, |
    | 432442 | admin_easynews   | localhost | admin_main       | Query   | 55   | Waiting for table    | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (600574,600571,600570,600572, |
    | 438197 | admin_easynews   | localhost | admin_main       | Query   | 10   | Waiting for table    | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (600574,600571,600570,600572, |
    | 441522 | admin_easynews   | localhost | admin_main       | Query   | 24   | Waiting for table    | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (600574,600571,600570,600572, |
    | 451555 | admin_easynews   | localhost | admin_main       | Query   | 347  | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts. |
    | 458559 | admin_easynews   | localhost | admin_main       | Query   | 322  | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts. |
    | 460689 | admin_thainews   | localhost | admin_thainews   | Query   | 6    | Sending data         | SELECT COUNT(*) FROM wp_term_relationships, wp_posts WHERE wp_posts.ID = wp_term_relationships.objec |
    | 461605 | admin_easynews   | localhost | admin_main       | Query   | 304  | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts. |
    | 461942 | admin_easynews   | localhost | admin_main       | Query   | 301  | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts. |
    | 462232 | admin_easynews   | localhost | admin_main       | Query   | 301  | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts. |
    | 468077 | admin_easynews   | localhost | admin_main       | Query   | 276  | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts. |
    | 471851 | admin_easynews   | localhost | admin_main       | Query   | 259  | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts. |
    | 481137 | admin_easynews   | localhost | admin_main       | Query   | 232  | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts. |
    | 488876 | admin_easynews   | localhost | admin_main       | Query   | 206  | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts. |



    Can u explain me why this happen?

    Any configuration was wrong in my my.cnf?
     
  4. storminternet

    storminternet Well-Known Member

    Joined:
    Nov 2, 2011
    Messages:
    462
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Database admin_main seems to be consuming high cpu resources. You need to optimize it with the help of database administrator.
     
  5. Dolphin882

    Dolphin882 Member

    Joined:
    Nov 8, 2011
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator

    Storm
    Any idea how to optimize the database?
    We do not have the budget to hire for a database admin yet :(
     
  6. sumesh

    sumesh Registered

    Joined:
    Nov 14, 2011
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello,

    you can try optimizing a database from command line using this command : mysqlcheck -r -o db_name

    r option meand repair and o option for optimize.

    Just give this a try, if still having high resource usages, you may defenitly need to contact db admin to get this corrected.

    Also donot forget to restart mysql after changes.

    you can also try setting log slow quries in your my.cnf to check for slow queries.

    Thank you.
     
  7. storminternet

    storminternet Well-Known Member

    Joined:
    Nov 2, 2011
    Messages:
    462
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Database maintenance can be carried out while your site is shutdown. Try MySQL :: MySQL 5.1 Reference Manual :: 4.6.3 myisamchk utility to repair , optimize the database.
     
  8. Dolphin882

    Dolphin882 Member

    Joined:
    Nov 8, 2011
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi

    I've tried run mysqlcheck -r -o db_name ; after completed; I restarted the mysql. the CPU load is normal for few hours then it up to > 1000 again .
    How to fix if we have Slow query in our table?
    IF switch from MyISAM to Innodb the best options?
     
  9. ruzbehraja

    ruzbehraja Well-Known Member

    Joined:
    May 19, 2011
    Messages:
    383
    Likes Received:
    7
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    1. Firstly, you need to cleanup your my.cnf file and remove the deprecated / unnecessary variables and all commented lines starting with a #. This will help in dealing with lesser information. You do not need to put the default port values, username etc.

    See this great link which will help you clean it up: http://forums.cpanel.net/f354/i-need-mysql-optimizations-169706.html#post703978

    2. Enable slow query logging by adding the following line to your my.cnf:
    log-slow-queries = /var/lib/mysql/slow.log

    See more: http://forums.cpanel.net/f354/i-need-mysql-optimizations-169706.html#post704178

    3. Run the MySQL Tuner script (during non-peak) hours on your server, by entering the following commands:
    wget mysqltuner.pl
    chmod 755 mysqltuner.pl
    ./mysqltuner.pl

    Copy and Paste the output here.
     
  10. ruzbehraja

    ruzbehraja Well-Known Member

    Joined:
    May 19, 2011
    Messages:
    383
    Likes Received:
    7
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    I dont think you need the max_connections to be 500.

    You can start by setting:

    max_connections = 200
     
  11. ruzbehraja

    ruzbehraja Well-Known Member

    Joined:
    May 19, 2011
    Messages:
    383
    Likes Received:
    7
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    thread_concurrency is not supported in Linux and FreeBSD. You can remove that line too if applicable.
     
  12. keaza

    keaza Member

    Joined:
    Nov 13, 2011
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    London, United Kingdom, United Kingdom
    cPanel Access Level:
    Root Administrator
    that amount of ram is a over kill unless you have a site like google on it .... i would suggest toning your server down a bit and buy a few smaller servers and get a load balancer in to spread the load out evenly would make things run alot more efficaint
     
  13. ruzbehraja

    ruzbehraja Well-Known Member

    Joined:
    May 19, 2011
    Messages:
    383
    Likes Received:
    7
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Is your machine 32-bit? What are the other specs....?
     
  14. Dolphin882

    Dolphin882 Member

    Joined:
    Nov 8, 2011
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi
    My machine is 128GB RAM and 64 bit Linux
    What are the other options besides buying for a few smaller servers and load balancing?
    Here is the output from mysqltunner.pl

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce or eliminate persistent connections to reduce connection usage
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    max_connections (> 250)
    wait_timeout (< 60)
    interactive_timeout (< 60)
    join_buffer_size (> 2.0M, or always use indexes with joins)
    table_cache (> 512)
    innodb_buffer_pool_size (>= 17M)
     
  15. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,482
    Likes Received:
    203
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    I'd be looking at the wordpress site(s) and what its got going on with it. Tweaking the server to handle an out of date wordpress with out of date modules is a waste of time, IMHO. I'm not saying this is the case here, I'm just making the comment that if you're seeing this kind of behavior:
    ...the website should be the first to be tweaked.

    Tweaking the server is good, fixing the website causing the issues, better.
     
  16. chrismfz

    chrismfz Well-Known Member

    Joined:
    Jul 4, 2007
    Messages:
    109
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Greece
    cPanel Access Level:
    DataCenter Provider
    join_buffer_size =32M
    sort_buffer_size =512M
    myisam_sort_buffer_size =64M
    read_buffer_size =64M
    read_rnd_buffer_size =64M

    those are variables per connection. In most circumstances those shouldn't be higher than 1M.
    Now your Mysql tries to allocate connections * all those variables.
    Should be almost 16 GB + 256 GB + 32 GB + 32 GB + 32 GB = 368 GB of resources (plus key_buffer and tmp tables and so on...). (If we assume connections = 500).

    with a # free -m do you see the machine swapping ? (using the swap space).

    You should decrease all those values:

    join_buffer_size =32M
    sort_buffer_size =512M
    myisam_sort_buffer_size =64M
    read_buffer_size =64M
    read_rnd_buffer_size =64M

    and comment out those or decrease them too using exactly the same levels (1M for a start)
    [isamchk]
    sort_buffer_size = 256M
    read_buffer = 32M
    write_buffer = 32M

    [myisamchk]
    sort_buffer_size = 256M
    read_buffer = 32M
    write_buffer = 32M
     
Loading...

Share This Page