MySQLD is using 175% of the cpu

Question

On one of the servers, I have mysqld running as slave DB.

The CPU usage is 175% at the moment used by mysqld.

Here are the results of SHOW PROCESSLIST;

+----------+-------------+-----------+---------+---------+---------+-----------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id       | User        | Host      | db      | Command | Time    | State                                                                       | Info                                                                                                 |
+----------+-------------+-----------+---------+---------+---------+-----------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
|   109029 | system user |           | NULL    | Connect | 2346123 | Waiting for master to send event                                            | NULL                                                                                                 |
|   109030 | system user |           | NULL    | Connect |       0 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL                                                                                                 |
| 12944426 | root        | localhost | NULL    | Query   |       0 | NULL                                                                        | SHOW PROCESSLIST                                                                                     |
| 12944443 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944445 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944463 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944466 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944467 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944469 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944470 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944471 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944476 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944479 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944482 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944484 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944485 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944488 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944490 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944491 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944492 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944493 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944495 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944496 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944498 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944499 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944501 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944502 | mdpipub     | localhost | mdpipub | Query   |       0 | Sending data                                                                | SELECT DISTINCT(article_id) FROM authors WHERE CONCAT(
                firstname,
          |
| 12944503 | mdpipub     | localhost | mdpipub | Sleep   |       0 |                                                                             | NULL                                                                                                 |
| 12944504 | mdpipub     | localhost | mdpipub | Query   |       0 | Sending data                                                                | SELECT DISTINCT(article_id) FROM authors WHERE CONCAT(
                firstname,
          |
+----------+-------------+-----------+---------+---------+---------+-----------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+

that might be a show process list now but when the cpu was at 175% the list would be different. Show the recent entries in slow query log — Mike, Sep 25 '14 at 11:49

score 1 · Answer 1 · answered Sep 25 '14 at 12:29

1

This is because 2 potential heavy SELECT queries are running. The load of all threads is then summed up for the main process and may show more than 100%.

answered Sep 25 '14 at 12:29

ThePsyjo

23
4

score 0 · Accepted Answer · answered Sep 26 '14 at 10:14

0

I fund the problem. I got an attack on the server by someone executing constantly the search function on the website. I just banned this IP and it is fine at the moment.

answered Sep 26 '14 at 10:14

Milos Cuculovic

401
2
8
21

2

this indicates that you have bigger problems, if someone can DoS your server with a lot of search requests – thanasisk Sep 26 '14 at 10:19
That's trouth @thanasisk. Should I add something in the firewall to avoid a certain limit of queries per minute? – Milos Cuculovic Sep 29 '14 at 08:42
that is a good first step. Second step is to make the search more robust. – thanasisk Sep 29 '14 at 09:15
More robust? I mean, the search is robust. The attacker created a search simulation by inserting some search words. – Milos Cuculovic Sep 29 '14 at 10:04
If he made the same query over and over again, where is caching to save the day? – thanasisk Sep 29 '14 at 11:30
The query was not the same but with some random words. – Milos Cuculovic Sep 29 '14 at 11:43

MySQLD is using 175% of the cpu

2 Answers2