_Vault_ Posted February 18, 2022 Posted February 18, 2022 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 |
_Vault_ Posted February 18, 2022 Author Posted February 18, 2022 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?!
IveLeft... Posted February 18, 2022 Posted February 18, 2022 Cloudflare has bot protection even on the free plan (even better on the paid plan)
_Vault_ Posted February 19, 2022 Author Posted February 19, 2022 23 hours ago, Muddy Boots said: Cloudflare has bot protection even on the free plan (even better on the paid plan) 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...
IveLeft... Posted February 20, 2022 Posted February 20, 2022 You can add the bot (if its not in cloud flares rules0 as an extra rule in their firewall.
Marc Posted February 21, 2022 Posted February 21, 2022 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
Recommended Posts