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.

Extreme MySQL CPU Usage

Discussion in 'Workarounds and Optimization' started by Dakado, Jun 19, 2015.

  1. Dakado

    Dakado Member

    Joined:
    Jun 19, 2015
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Czech Republic
    cPanel Access Level:
    Root Administrator
    Hello,

    We got MySQL DB under around 200 queries /sec in avarage. In rush hours the MySQL is consuming up to 500% of Intel Xeon E3-1246 v3 is that normal ? Also the entire DB takes 800 MB on the disk but the MySQL is consuming almost 6 GB of RAM.

    Some friends told me that this usage is extremly high but we are running MySQL server under that load about 6 months, it never crashed but it is consuming the power of entire machine.


    My config looks like that:
    innodb_file_per_table = 1
    join_buffer_size = 1M
    open_files_limit = 10000
    myisam_use_mmap = 1
    query_cache_type = 1
    query_cache_limit = 3072M
    max_heap_table_size = 3072M
    tmp_table_size = 3072M
    thread_cache_size = 50
    table_open_cache = 2000
    concurrent_insert = 2
    max_connections = 3000
    sort_buffer_size = 2M
    max_heap_table_size = 64M
    key_buffer_size = 860M
    read_rnd_buffer_size = 32M
    thread_concurrency = 8
    query_cache_size = 1024M
    key_buffer = 32M
    read_buffer_size = 32M
    innodb_buffer_pool_size = 5000M
    thread_stack = 1M
    innodb_log_buffer_size = 2M



    Thanks for any suggestion :)
     
  2. 24x7ss

    24x7ss Well-Known Member

    Joined:
    Sep 30, 2014
    Messages:
    271
    Likes Received:
    16
    Trophy Points:
    18
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Twitter:
    I will suggest you try optimizing mysql using mysql tunner script. It will help you to optimize mysql settings.
    I can see that you have set max connection to 3000. If there are not much mysql connection then reduce it and also enable slow query settings on mysql this will help you in optimizing database queries also.
     
  3. Dakado

    Dakado Member

    Joined:
    Jun 19, 2015
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Czech Republic
    cPanel Access Level:
    Root Administrator
    There are about 1700-2000 active connections but I cannot really reduce it. 1 connection = 1 connected software and we got 1700 separated softwares running at the time.

    But I believe that there must be something terribly wrong with my DB because I dont think so some optimalization woud reduce the CPU usage from 500% to 50 for example.

    2 weeks ago I have written my own server that works like "cache" and is handling a lot of data and there are about 20 queries per second and the server uses about 0.3% - 1% CPU
     
  4. 24x7ss

    24x7ss Well-Known Member

    Joined:
    Sep 30, 2014
    Messages:
    271
    Likes Received:
    16
    Trophy Points:
    18
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Twitter:
    Have you tried enabling slow queries. It will help you to track which database queries are eating up server resources and also try running "watch mysqladmin proc" command on server. This will help you to track the queries/db's which is eating up server resources. Try to optimize database queries.

    If still you are facing same issue then I will recommend you to go with MariaDB. It is lighter, faster and stable then Mysql. You will see drastic change in performance of site and server.
     
  5. Dakado

    Dakado Member

    Joined:
    Jun 19, 2015
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Czech Republic
    cPanel Access Level:
    Root Administrator
    WHere should I run that command "watch mysqladmin proc" ? I got Debian and it does not seems to be valid command.
     
  6. ModServ

    ModServ Well-Known Member

    Joined:
    Oct 17, 2006
    Messages:
    332
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Egypt
    cPanel Access Level:
    Root Administrator
    What is the output of this script execution?

    Code:
    wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh
    sh tuning-primer.sh
    
     
  7. Dakado

    Dakado Member

    Joined:
    Jun 19, 2015
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Czech Republic
    cPanel Access Level:
    Root Administrator
    Is this script safe ? I have a really really important data in the DB so I dont want to make anything bad to my DB.
     
  8. ModServ

    ModServ Well-Known Member

    Joined:
    Oct 17, 2006
    Messages:
    332
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Egypt
    cPanel Access Level:
    Root Administrator
  9. Dakado

    Dakado Member

    Joined:
    Jun 19, 2015
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Czech Republic
    cPanel Access Level:
    Root Administrator
    Ok the output is here:
    hastebin.com/ojupixujan.vhdl
     
    #9 Dakado, Jun 25, 2015
    Last edited by a moderator: Jun 25, 2015
  10. Dakado

    Dakado Member

    Joined:
    Jun 19, 2015
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Czech Republic
    cPanel Access Level:
    Root Administrator
    I have solved the problem by adding keys to my tables to rows like "name" or "user". So it means rows WHERE I am selecting from table.
     
  11. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

Share This Page