Huge MySQL queries are killing my database server

rafaucau

The issue

I've been experiencing frequent downtime on my forum lately. After investigating, my hosting provider's support identified the cause: the generation of numerous large MySQL queries with many nested subqueries. For example:

I've tried using Clockwork to debug this, but I haven't been able to identify what's generating such massive queries. Visiting discussions generates much smaller queries, so that's probably not the issue. I suspect the cause might be heavy bot traffic (like Google crawlers) triggering these massive queries when accessing certain parts of the forum.
This as a serious performance issue, as these large queries are putting excessive load on the MySQL server, eventually causing it to crash.

Flarum information

Flarum core: 1.8.9
PHP version: 8.3.7
MySQL version: 11.4.3-MariaDB
Loaded extensions: Core, date, libxml, openssl, pcre, sqlite3, zlib, bz2, calendar, ctype, curl, hash, filter, ftp, gettext, json, iconv, SPL, pcntl, random, readline, Reflection, session, standard, mbstring, shmop, SimpleXML, tokenizer, xml, apcu, bcmath, dba, dom, fileinfo, geoip, gd, gmp, igbinary, imagick, inotify, msgpack, mailparse, mcrypt, memcache, memcached, exif, imap, intl, ldap, mysqlnd, mysqli, PDO, odbc, pgsql, pdo_mysql, pdo_sqlite, PDO_ODBC, pdo_pgsql, Phar, posix, redis, soap, sockets, sodium, snmp, sysvmsg, xmlreader, xmlwriter, xsl, zip, yaml, ionCube Loader, Zend OPcache
+-----------------------------------+---------------------------------------+------------------------------------------+
| Flarum Extensions                 |                                       |                                          |
+-----------------------------------+---------------------------------------+------------------------------------------+
| ID                                | Version                               | Commit                                   |
+-----------------------------------+---------------------------------------+------------------------------------------+
| flarum-flags                      | v1.8.2                                |                                          |
| flarum-tags                       | v1.8.3                                |                                          |
| flarum-approval                   | v1.8.2                                |                                          |
| flarum-lock                       | v1.8.2                                |                                          |
| flarum-gdpr                       | 1.0.0-beta.3                          |                                          |
| flarum-mentions                   | v1.8.5                                |                                          |
| flarum-subscriptions              | v1.8.1                                |                                          |
| fof-pages                         | 1.0.8                                 |                                          |
| v17development-support            | v2.8.0                                |                                          |
| flarum-suspend                    | v1.8.4                                |                                          |
| fof-follow-tags                   | 1.2.7                                 |                                          |
| fof-sitemap                       | 2.2.1                                 |                                          |
| fof-byobu                         | 1.3.8                                 |                                          |
| acpl-mobile-tab                   | 1.4.4                                 |                                          |
| fof-user-directory                | 1.3.3                                 |                                          |
| aberit-communities                | dev-master                            |                                          |
| blomstra-turnstile                | 0.1.6                                 |                                          |
| flarum-markdown                   | v1.8.1                                |                                          |
| sycho-move-posts                  | v0.1.7                                |                                          |
| clarkwinkelmann-author-change     | 1.0.3                                 |                                          |
| fof-masquerade                    | 2.1.5                                 |                                          |
| fof-best-answer                   | 1.6.0                                 |                                          |
| fof-oauth                         | 1.6.14                                |                                          |
| fof-drafts                        | 1.2.12                                |                                          |
| aberit-gamification               | dev-master                            |                                          |
| fof-nightmode                     | 1.6.0                                 |                                          |
| flarumite-simple-discussion-views | 1.2.4                                 |                                          |
| v17development-seo                | v2.0.3                                |                                          |
| the-turk-diff                     | dev-fix-parse-and-render-missing-args | e2a10587855af0b9141d9efb30b4aa4de90e057e |
| ianm-twofactor                    | 1.1.3                                 |                                          |
| ianm-html-head                    | 1.2.4                                 |                                          |
| ianm-follow-users                 | 1.4.10                                |                                          |
| fof-username-request              | 1.2.4                                 |                                          |
| fof-user-bio                      | 1.4.2                                 |                                          |
| fof-upload                        | 1.6.1                                 |                                          |
| fof-terms                         | 1.4.1                                 |                                          |
| fof-socialprofile                 | 1.1.6                                 |                                          |
| fof-polls                         | 2.2.9                                 |                                          |
| fof-merge-discussions             | 1.4.1                                 |                                          |
| fof-ignore-users                  | 1.2.1                                 |                                          |
| fof-geoip                         | 1.5.0                                 |                                          |
| fof-formatting                    | 1.0.3                                 |                                          |
| fof-anti-spam                     | 1.1.2                                 |                                          |
| flarum-statistics                 | v1.8.1                                |                                          |
| flarum-lang-polish                | dev-master                            |                                          |
| flarum-emoji                      | v1.8.1                                |                                          |
| flarum-bbcode                     | v1.8.0                                |                                          |
| flarum-akismet                    | v1.8.1                                |                                          |
| blomstra-realtime                 | 0.3.2                                 |                                          |
| blomstra-cache-assets             | 0.5                                   |                                          |
| askvortsov-rich-text              | v2.1.7                                |                                          |
| askvortsov-pwa                    | v3.4.1                                |                                          |
| askvortsov-moderator-warnings     | v0.6.3                                |                                          |
| acpl-lscache                      | v3.2.1                                |                                          |
| aberit-moderation-tools           | dev-master                            |                                          |
| aberit-forum                      | dev-master                            |                                          |
| aberit-follow-communities         | dev-master                            |                                          |
| aberit-ads                        | dev-master                            |                                          |
+-----------------------------------+---------------------------------------+------------------------------------------+
Queue driver: redis
Session driver: redis (Code override. Configured to file)
Scheduler status: Active
Mail driver: smtp
Debug mode: off

luceos

rafaucau how many rows does your notifications table have?

rafaucau

luceos 974762

But these queries are not related to notifications.

SychO

The queries being large isn't necessarily the problem.

How many posts are in the discussion 29278
What's the output of explain $query in MySQL?
What's the output without the communities table access and what's related to it?

rafaucau

SychO How many posts are in the discussion 29278

34655

SychO What's the output of explain $query in MySQL?


+----+--------------------+----------------------+-----------------+-------------------------------------------------------+-------------------------------------------+---------+-----------------------------------------------+--------+-------------++
| id | select_type        | table                | type            | possible_keys                                         | key                                       | key_len | ref                                           | rows   | Extra       |  |
+----+--------------------+----------------------+-----------------+-------------------------------------------------------+-------------------------------------------+---------+-----------------------------------------------+--------+-------------++
| 1  | PRIMARY            | discussions          | const           | PRIMARY,discussions_user_id_index,discussions_comm... | PRIMARY                                   | 4       | const                                         | 1      |             |  |
| 1  | PRIMARY            | posts                | ref             | posts_discussion_id_number_unique,posts_discussion... | posts_discussion_id_number_unique         | 4       | const                                         | 71626  | Using where |  |
| 63 | MATERIALIZED       | discussions          | index           | PRIMARY                                               | discussions_is_locked_index               | 1       | NULL                                          | 324023 | Using where; Using index |  |
| 76 | MATERIALIZED       | communities          | index           | PRIMARY,communities_last_posted_user_id_foreign,co... | communities_last_posted_user_id_foreign   | 5       | NULL                                          | 13     | Using index |  |
| 76 | MATERIALIZED       | discussion_community | ref             | PRIMARY,discussion_community_community_id_foreign     | discussion_community_community_id_foreign | 4       | *****_forum.communities.id                    | 25884  | Using index |  |
| 73 | MATERIALIZED       | discussion_community | index           | PRIMARY                                               | discussion_community_community_id_foreign | 4       | NULL                                          | 310615 | Using where; Using index |  |
| 74 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 72 | MATERIALIZED       | tags                 | index           | PRIMARY,tags_parent_id_foreign,tags_last_posted_us... | tags_parent_id_foreign                    | 5       | NULL                                          | 2562   | Using index |  |
| 72 | MATERIALIZED       | discussion_tag       | ref             | PRIMARY,discussion_tag_tag_id_foreign                 | discussion_tag_tag_id_foreign             | 4       | *****_forum.tags.id                           | 386    | Using index |  |
| 71 | MATERIALIZED       | communities          | index           | PRIMARY,communities_last_posted_user_id_foreign,co... | communities_last_posted_user_id_foreign   | 5       | NULL                                          | 13     | Using index |  |
| 71 | MATERIALIZED       | discussion_community | ref             | PRIMARY,discussion_community_community_id_foreign     | discussion_community_community_id_foreign | 4       | *****_forum.communities.id                    | 25884  | Using index |  |
| 68 | MATERIALIZED       | discussion_community | index           | PRIMARY                                               | discussion_community_community_id_foreign | 4       | NULL                                          | 310615 | Using where; Using index |  |
| 69 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 64 | MATERIALIZED       | discussion_tag       | index           | PRIMARY                                               | discussion_tag_tag_id_foreign             | 4       | NULL                                          | 935179 | Using where; Using index |  |
| 65 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 67 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 49 | MATERIALIZED       | discussions          | index           | PRIMARY                                               | discussions_is_locked_index               | 1       | NULL                                          | 324023 | Using where; Using index |  |
| 62 | MATERIALIZED       | communities          | index           | PRIMARY,communities_last_posted_user_id_foreign,co... | communities_last_posted_user_id_foreign   | 5       | NULL                                          | 13     | Using index |  |
| 62 | MATERIALIZED       | discussion_community | ref             | PRIMARY,discussion_community_community_id_foreign     | discussion_community_community_id_foreign | 4       | *****_forum.communities.id                    | 25884  | Using index |  |
| 59 | MATERIALIZED       | discussion_community | index           | PRIMARY                                               | discussion_community_community_id_foreign | 4       | NULL                                          | 310615 | Using where; Using index |  |
| 60 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 58 | MATERIALIZED       | tags                 | index           | PRIMARY,tags_parent_id_foreign,tags_last_posted_us... | tags_parent_id_foreign                    | 5       | NULL                                          | 2562   | Using index |  |
| 58 | MATERIALIZED       | discussion_tag       | ref             | PRIMARY,discussion_tag_tag_id_foreign                 | discussion_tag_tag_id_foreign             | 4       | *****_forum.tags.id                           | 386    | Using index |  |
| 57 | MATERIALIZED       | communities          | index           | PRIMARY,communities_last_posted_user_id_foreign,co... | communities_last_posted_user_id_foreign   | 5       | NULL                                          | 13     | Using index |  |
| 57 | MATERIALIZED       | discussion_community | ref             | PRIMARY,discussion_community_community_id_foreign     | discussion_community_community_id_foreign | 4       | *****_forum.communities.id                    | 25884  | Using index |  |
| 54 | MATERIALIZED       | discussion_community | index           | PRIMARY                                               | discussion_community_community_id_foreign | 4       | NULL                                          | 310615 | Using where; Using index |  |
| 55 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 50 | MATERIALIZED       | discussion_tag       | index           | PRIMARY                                               | discussion_tag_tag_id_foreign             | 4       | NULL                                          | 935179 | Using where; Using index |  |
| 51 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 53 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 48 | SUBQUERY           | discussion_community | ref             | PRIMARY,discussion_community_community_id_foreign     | PRIMARY                                   | 4       | const                                         | 1      |             |  |
| 48 | SUBQUERY           | communities          | eq_ref          | PRIMARY                                               | PRIMARY                                   | 4       | *****_forum.discussion_community.community_id | 1      |             |  |
| 45 | DEPENDENT SUBQUERY | discussion_community | index_subquery  | PRIMARY                                               | PRIMARY                                   | 4       | func                                          | 1      | Using where |  |
| 46 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 44 | SUBQUERY           | discussion_tag       | ref             | PRIMARY,discussion_tag_tag_id_foreign                 | PRIMARY                                   | 4       | const                                         | 1      |             |  |
| 44 | SUBQUERY           | tags                 | eq_ref          | PRIMARY                                               | PRIMARY                                   | 4       | *****_forum.discussion_tag.tag_id             | 1      |             |  |
| 43 | SUBQUERY           | discussion_community | ref             | PRIMARY,discussion_community_community_id_foreign     | PRIMARY                                   | 4       | const                                         | 1      |             |  |
| 43 | SUBQUERY           | communities          | eq_ref          | PRIMARY                                               | PRIMARY                                   | 4       | *****_forum.discussion_community.community_id | 1      |             |  |
| 40 | DEPENDENT SUBQUERY | discussion_community | index_subquery  | PRIMARY                                               | PRIMARY                                   | 4       | func                                          | 1      | Using where |  |
| 41 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 36 | DEPENDENT SUBQUERY | discussion_tag       | index_subquery  | PRIMARY                                               | PRIMARY                                   | 4       | func                                          | 3      | Using where |  |
| 37 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 39 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 35 | SUBQUERY           | discussion_community | ref             | PRIMARY,discussion_community_community_id_foreign     | PRIMARY                                   | 4       | const                                         | 1      |             |  |
| 35 | SUBQUERY           | communities          | eq_ref          | PRIMARY                                               | PRIMARY                                   | 4       | *****_forum.discussion_community.community_id | 1      |             |  |
| 32 | DEPENDENT SUBQUERY | discussion_community | index_subquery  | PRIMARY                                               | PRIMARY                                   | 4       | func                                          | 1      | Using where |  |
| 33 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 31 | SUBQUERY           | discussion_tag       | ref             | PRIMARY,discussion_tag_tag_id_foreign                 | PRIMARY                                   | 4       | const                                         | 1      |             |  |
| 31 | SUBQUERY           | tags                 | eq_ref          | PRIMARY                                               | PRIMARY                                   | 4       | *****_forum.discussion_tag.tag_id             | 1      |             |  |
| 30 | SUBQUERY           | discussion_community | ref             | PRIMARY,discussion_community_community_id_foreign     | PRIMARY                                   | 4       | const                                         | 1      |             |  |
| 30 | SUBQUERY           | communities          | eq_ref          | PRIMARY                                               | PRIMARY                                   | 4       | *****_forum.discussion_community.community_id | 1      |             |  |
| 27 | DEPENDENT SUBQUERY | discussion_community | index_subquery  | PRIMARY                                               | PRIMARY                                   | 4       | func                                          | 1      | Using where |  |
| 28 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 23 | DEPENDENT SUBQUERY | discussion_tag       | index_subquery  | PRIMARY                                               | PRIMARY                                   | 4       | func                                          | 3      | Using where |  |
| 24 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 26 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 22 | SUBQUERY           | discussion_community | ref             | PRIMARY,discussion_community_community_id_foreign     | PRIMARY                                   | 4       | const                                         | 1      |             |  |
| 22 | SUBQUERY           | communities          | eq_ref          | PRIMARY                                               | PRIMARY                                   | 4       | *****_forum.discussion_community.community_id | 1      |             |  |
| 19 | DEPENDENT SUBQUERY | discussion_community | index_subquery  | PRIMARY                                               | PRIMARY                                   | 4       | func                                          | 1      | Using where |  |
| 20 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 18 | SUBQUERY           | discussion_tag       | ref             | PRIMARY,discussion_tag_tag_id_foreign                 | PRIMARY                                   | 4       | const                                         | 1      |             |  |
| 18 | SUBQUERY           | tags                 | eq_ref          | PRIMARY                                               | PRIMARY                                   | 4       | *****_forum.discussion_tag.tag_id             | 1      |             |  |
| 17 | SUBQUERY           | discussion_community | ref             | PRIMARY,discussion_community_community_id_foreign     | PRIMARY                                   | 4       | const                                         | 1      |             |  |
| 17 | SUBQUERY           | communities          | eq_ref          | PRIMARY                                               | PRIMARY                                   | 4       | *****_forum.discussion_community.community_id | 1      |             |  |
| 14 | DEPENDENT SUBQUERY | discussion_community | index_subquery  | PRIMARY                                               | PRIMARY                                   | 4       | func                                          | 1      | Using where |  |
| 15 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 10 | DEPENDENT SUBQUERY | discussion_tag       | index_subquery  | PRIMARY                                               | PRIMARY                                   | 4       | func                                          | 3      | Using where |  |
| 11 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 13 | MATERIALIZED       | NULL                 | NULL            | NULL                                                  | NULL                                      | NULL    | NULL                                          | NULL   | Impossible WHERE |  |
| 7  | DEPENDENT SUBQUERY | discussion_community | index_subquery  | PRIMARY                                               | PRIMARY                                   | 4       | func                                          | 1      | Using where |  |
| 8  | DEPENDENT SUBQUERY | communities          | eq_ref          | PRIMARY,communities_last_posted_user_id_foreign,co... | PRIMARY                                   | 4       | func                                          | 1      |             |  |
| 8  | DEPENDENT SUBQUERY | perm_communities     | eq_ref          | PRIMARY,communities_last_posted_user_id_foreign,co... | PRIMARY                                   | 4       | *****_forum.communities.id                    | 1      | Using where |  |
| 3  | DEPENDENT SUBQUERY | discussion_tag       | index_subquery  | PRIMARY                                               | PRIMARY                                   | 4       | func                                          | 3      | Using where |  |
| 4  | DEPENDENT SUBQUERY | tags                 | eq_ref          | PRIMARY,tags_parent_id_foreign,tags_last_posted_us... | PRIMARY                                   | 4       | func                                          | 1      | Using where |  |
| 4  | DEPENDENT SUBQUERY | perm_tags            | eq_ref          | PRIMARY,tags_parent_id_foreign,tags_last_posted_us... | PRIMARY                                   | 4       | *****_forum.tags.id                           | 1      | Using where |  |
| 6  | DEPENDENT SUBQUERY | perm_tags            | unique_subquery | PRIMARY                                               | PRIMARY                                   | 4       | func                                          | 1      | Using where |  |
+----+--------------------+----------------------+-----------------+-------------------------------------------------------+-------------------------------------------+---------+-----------------------------------------------+--------+-------------++

SychO What's the output without the communities table access and what's related to it?

Communities are copy of tags with minor modifications, adding a third level of tags. If the issue lies with communities, it's likely also present with tags.
When I monitor discussion pages in Clockwork, the queries are much smaller, suggesting these heavy ones come from somewhere else. First, I need to identify which pages generate such queries so I can verify them in Clockwork - my hosting monitors large queries but doesn't track which pages generate them.

rafaucau

It seems that search engine bots were heavily crawling the API. After adding this to .htaccess:

<IfModule mod_headers.c>
    <If "%{REQUEST_URI} =~ m#^/api#">
        Header set X-Robots-Tag "noindex"
    </If>
</IfModule>

it appears the load has decreased, but I'll keep monitoring the situation.

CyberGene

rafaucau does adding the above stop search engine bots from indexing the forum? Or if only limits them querying the Flarum API? Why would the bots query the API at all? How do they know about it? Shouldn’t they index the discussion/post HTML pages instead?

rob006

rafaucau This doesn't prevent indexing API, since bot needs to request API first in order to discover this noindex header (for each URL individually). You should rather block it in robots.txt if you want to avoid these requests from bots, but this may have negative impact on SEO.

rafaucau

CyberGene No, it only prevents indexing of the API. Bots discover API endpoints because the Flarum frontend makes API requests that they can see when crawling pages. If you don't block API indexing, it will be indexed. You can verify this in Google Search Console indexing stats - check the JSON file type section. In my case, there are tens of thousands of indexing requests for API endpoints daily.

The recent v2 update of the Flarum SEO extension might have caused more intensive crawling - I've noticed a significant increase in refresh requests in indexing stats, including API endpoints (and other URLs) that Google had previously discovered.

rafaucau

rob006 You're partially right. Since Flarum is an SPA, it needs API requests to render pages (though some data is preloaded on the backend). While robots.txt provides crawling instructions, we can't be certain if bots will block API requests that are part of normal page functionality - different bots might handle this differently.

The X-Robots-Tag approach:

Signals to bots not to index API endpoints
Won't interfere with normal page rendering
Avoids potential SEO issues from robots.txt blocking

After discovering endpoints with X-Robots-Tag, bots may still crawl them occasionally to check for changes, but less frequently since they know not to index them. So while the initial requests still happen, the overall load is reduced. This makes X-Robots-Tag a good solution here.

rafaucau

I've added indexes and these huge queries now execute ~10x faster. Here are the indexes I added:

CREATE INDEX idx_disc_privacy ON discussions(is_private, is_approved, user_id);
CREATE INDEX idx_disc_visibility ON discussions(hidden_at, last_posted_at);
CREATE INDEX idx_disc_comments ON discussions(comment_count, user_id, last_posted_at);