0

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,
          |
+----------+-------------+-----------+---------+---------+---------+-----------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
2
  • What is the question? Sep 25, 2014 at 11:39
  • 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, 2014 at 11:49

2 Answers 2

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%.

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.

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .