Extreme MySQL CPU Usage

Dakado

• June 19, 2015 17:36

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 :)

• 

  24x7ss

  • June 20, 2015 03:25

  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.

  0
• 

  Dakado

  • June 20, 2015 09:54

  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

  0
• 

  24x7ss

  • June 20, 2015 15:11

  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.

  0
• 

  Dakado

  • June 21, 2015 16:59

  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.

  
  WHere should I run that command "watch mysqladmin proc" ? I got Debian and it does not seems to be valid command.

  0
• 

  ModServ

  • June 25, 2015 08:29

  What is the output of this script execution?
  wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh sh tuning-primer.sh
  

  0
• 

  Dakado

  • June 25, 2015 09:24

  What is the output of this script execution?
  wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh sh tuning-primer.sh
  

  
  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.

  0
• 

  ModServ

  • June 25, 2015 09:28

  Sure, it analyses the statistics of MySQL and output the results with tips, read more info here:

  0
• 

  Dakado

  • June 25, 2015 09:38

  Sure, it analyses the statistics of MySQL and output the results with tips, read more info here:

  0
• 

  Dakado

  • June 26, 2015 09:36

  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.

  0
• 

  cPanelMichael

  • August 05, 2015 19:10

  Hello :) I'm happy to see you were able to address the issue. Thank you for updating us with the outcome.

  0

Please sign in to leave a comment.