2

Since yesterday my CPU has been eating more than 300% of my CPU whether the number of visitors increases of decreases the CPU usage remains the same.I am in NGinx Ubuntu 18.02 in digitalocean.I am totally new to NGinx and I could set up my server following the tutorial provided in the digitalocean site.

I have been running for approximately one month and everything was smooth,but yesterday suddenly MYSQL started eating all the CPU.I have Googled and even read some of the stackover solution but nothing seems to help me in understanding in how to fix it.They say we shoykd try running this or that but what how to actually use the result of obtain from running those command is never explained.For instance I followed solution provided on this MySQL high CPU usage but it hard for newbie to follow it I ran

SHOW PROCESSLIST;

and got many result like these

| Id      | User          | Host      | db        | Command | Time | State             | Info                                                                                                         |
+---------+---------------+-----------+-----------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| 5015945 | root          | localhost | NULL      | Query   |    0 | starting          | SHOW PROCESSLIST                                                                                     |
| 5026029 | wordpressuser | localhost | wordpress | Sleep   |    0 |                   | NULL                                                                                                 |
| 5026036 | wordpressuser | localhost | wordpress | Query   |    0 | Sending data      | SELECT SQL_CACHE *

026038 | wordpressuser | localhost | wordpress | Query   |    0 | Sending to client | SELECT SQL_CACHE *

| 5026039 | wordpressuser | localhost | wordpress | Sleep   |    0 |                   | NULL                                                                                                 |
| 5026041 | wordpressuser | localhost | wordpress | Query   |    0 | Sending data      | SELECT SQL_CACHE *

| 5026047 | wordpressuser | localhost | wordpress | Query   |    0 | Sending data      | SELECT SQL_CACHE *

| 5026048 | wordpressuser | localhost | wordpress | Query   |    0 | Sending data      | SELECT SQL_CACHE *

| 5026049 | wordpressuser | localhost | wordpress | Query   |    0 | Sending data      | SELECT SQL_CACHE *

| 5026050 | wordpressuser | localhost | wordpress | Sleep   |    0 |                   | NULL                                                                                                 |
| 5026051 | wordpressuser | localhost | wordpress | Query   |    0 | Sending data      | SELECT SQL_CACHE *

| 5026052 | wordpressuser | localhost | wordpress | Sleep   |    0 |                   | NULL                                                                                                 |
| 5026053 | wordpressuser | localhost | wordpress | Sleep   |    0 |                   | NULL                                                                                                 |
| 5026054 | wordpressuser | localhost | wordpress | Query   |    0 | init              | SELECT tr.object_id FROM wp_term_relationships AS tr INNER JOIN wp_term_taxonomy AS tt ON tr.term_ta |
| 5026055 | wordpressuser | localhost | wordpress | Sleep   |    0 |                   | NULL                                                                                                 |
| 5026056 | wordpressuser | localhost | wordpress | Sleep   |    0 |                   | NULL                                                                                                 |
| 5026057 | wordpressuser | localhost | wordpress | Sleep   |    0 |                   | NULL                                                                                                 |
| 5026058 | wordpressuser | localhost | wordpress | Sleep   |    0 |                   | NULL                                                                                                 |
| 5026059 | wordpressuser | localhost | wordpress | Query   |    0 | Sending data      | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (101,50,67,288,74,103,330,107 |
+---------+---------------+-----------+-----------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+"

but how do exactly verify which is causing the issue? There may be others causing the issue but how do I determined it?

Can some please guide me of provide me some tutorial links that me guide me through this process from the ground up?

thank you.

Mat
  • 202,337
  • 40
  • 393
  • 406
Machine Nero
  • 21
  • 3
  • 2
    you can install https://newrelic.com/ on your server and it can tell you exact problem with your database query – Rajinder Jul 24 '18 at 05:26
  • 2
    Seems like you're using MySQL for running WordPress. I'd suggest looking for solutions to `wordpress` in addition to `high cpu`, and `mysql` in your searching. Another thing you can try is to enable the MySQL 'slow query logging' which will show which queries are taking a long time. However, this won't find queries that run quickly but are called a very large number of times. If your wordpress site is getting a lot of traffic, figure out how to add caching to it. Secondly, if your database has had a lot of data added recently consider purging any old, unneeded rows from some tables. – karmakaze Jul 24 '18 at 05:49
  • Thanks for the suggestion but can you tell me any other way? – Machine Nero Jul 24 '18 at 05:51
  • I have tried enabling slow query log before but after I enabled it the CPU consumption increases enormously.And say I enable it now and get some result how do I fix the slow query should I delete them of kill them? – Machine Nero Jul 24 '18 at 06:05
  • Has your host changed anything about the server configuration around the time that it started happening? – SpacePhoenix Jul 24 '18 at 06:55

0 Answers0