XF 2.3 The database has grown by 27 GB in just a few days

[USCSS_Nostromo]

I asked AI for help. It analyzed the login logs and database dump backups from a week ago, a month ago, and today.

Here's what it found:

=================================================================
The dump size increase is almost entirely caused by the XenForo search cache table xf_search, not by posts.

What's inside​

Table	On disk	Rows	Description
xf_post	~29 GB	27 million	Actual posts — growing slowly
xf_search	~27 GB	242K	Temporary cache of search results

The xf_search table contains only about 24–36 hours of data (since October 12 at 04:11), yet the search_results field alone already occupies 26.7 GB. This is a BLOB containing a list of matching IDs.

Hourly stats: 10,000–13,000 searches per hour, almost all from guests (user_id = 0), with an average of ~3,500 results per query. Each row occupies approximately 100–350 KB, resulting in ~1.3 GB of raw data per hour.

Why the​

mysqldump writes these BLOBs into SQL (often as hex, which increases their size even further), and then gzip compresses the output. As the cache grows from almost nothing to 27 GB, the compressed archive can easily increase by ~3 GB per day — exactly what we're seeing: 13 → 16 → 19 → 20 GB.

Posts and private conversations aren't growing nearly as fast. The October 4 weekly backup was 14 GB, while the October 11 backup was already 16 GB. This increase coincides with the search cache growth.

What's causing it?​

Almost all entries have an empty search_query, the post type, and belong to guests:

search_query = '' → 242,526 entries
=================================================================

What should I do about this? Has anyone else experienced something like this?

 

[smallwheels]

What should I do about this?

Simple: Remove the permission to search for guests. It is a permission setting that takes 2 seconds to do.

 

[rdn]

I have a 30 million-post forum, and I have this issue too.

What I did is I pruned that specific table twice a day:

2 2,14 * * * /usr/bin/mariadb -e "TRUNCATE TABLE xf_db.xf_search;" >/dev/null 2>&1

 

[USCSS_Nostromo]

I have a 30 million-post forum, and I have this issue too.

What I did is I pruned that specific table twice a day:

2 2,14 * * * /usr/bin/mariadb -e "TRUNCATE TABLE xf_db.xf_search;" >/dev/null 2>&1

I've disabled search for guests. I don't see the point of generating this kind of traffic and putting this much load on the server (especially the disks).
I've even disallowed /search/ in robots.txt, so these queries don't even get indexed by Google.
I don't understand what these bots are, what they're trying to find, or what the point of all this is.
If they're AI bots, we could provide them with search access through an API and add information about it to llms.txt.