Jump to content

High CPU caused by SQL query


_Vault_

Recommended Posts

Hey,

since the last update I'm running into issues that the mariadb server is running on 100% CPU for a long time. Everytime this happens I see the following multiple queries in the SQL Processlist. What is this doing, and do you have any idea where it comes from?

| 7791 | ipb         | localhost | ipb | Execute |   67 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685257 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7798 | ipb         | localhost | ipb | Execute |   67 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685257 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7805 | ipb         | localhost | ipb | Execute |   64 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685261 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7808 | ipb         | localhost | ipb | Execute |   61 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685263 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7811 | ipb         | localhost | ipb | Execute |   60 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685264 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7816 | ipb         | localhost | ipb | Execute |   55 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685269 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7817 | ipb         | localhost | ipb | Execute |   54 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685270 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7826 | root        | localhost | NULL    | Query   |    0 | Init                     | SHOW FULL PROCESSLIST                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |    0.000 |
| 7833 | ipb         | localhost | ipb | Execute |   46 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685278 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7839 | ipb         | localhost | ipb | Execute |   43 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685281 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7845 | ipb         | localhost | ipb | Execute |   42 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685283 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7846 | ipb         | localhost | ipb | Execute |   39 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685285 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7848 | ipb         | localhost | ipb | Execute |   39 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685285 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7849 | ipb         | localhost | ipb | Execute |   40 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685285 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7850 | ipb         | localhost | ipb | Execute |   37 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685287 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7851 | ipb         | localhost | ipb | Execute |   37 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685287 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7854 | ipb         | localhost | ipb | Execute |   36 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685288 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7856 | ipb         | localhost | ipb | Execute |   36 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685289 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7860 | ipb         | localhost | ipb | Execute |   34 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685289 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7869 | ipb         | localhost | ipb | Execute |   31 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685293 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7871 | ipb         | localhost | ipb | Execute |   31 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685293 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7874 | ipb         | localhost | ipb | Execute |   29 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685295 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7875 | ipb         | localhost | ipb | Execute |   29 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685295 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7888 | ipb         | localhost | ipb | Execute |   24 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685300 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |    0.000 |
| 7889 | ipb         | localhost | ipb | Execute |   24 | Sending data             | /*ipb::ipb::IPS\Content\Search\Mysql\_Query::search:360*/ SELECT main.* FROM `core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\links\\Link','IPS\\links\\Link\\Comment','IPS\\links\\Link\\Review') ) ) AND index_date_created>1613685301 AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY index_date_created DESC LIMIT 0,25 |   

 

Link to comment
Share on other sites

I think this was a crawler / bot hammering the /discover page on my forum. I blocked that IP in Cloudflare and since then it has become quiet. I wonder if I can prevent guests from accessing this page, as it doesnt seem to be delivered from Redis cache as configured?!

Link to comment
Share on other sites

23 hours ago, Muddy Boots said:

Cloudflare has bot protection even on the free plan (even better on the paid plan)

40854723_Screenshot2022-02-18at23_35_07.thumb.png.9dc4e3171430e46ca9af3667b0b348cc.png

 

 

Thanks, this was already activated but this bot still made it through. I still wonder why these requests were not simply served by Redis though but generated 100% cpu on mariadb...

Link to comment
Share on other sites

Yeah it seems like something is trying to hammer search there. I would suggest also ensuring you have flood control set on searching, so that users can only search every x seconds. This may prevent items such as this. You can see this on the first tab of every group, under the permissions section

Link to comment
Share on other sites

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...