Xenforo database suddenly grows very quickly

[Silmarillion]

Hello, everyone!

I have a very strange problem and I address it to you. In the hope of a help / solution. Yesterday, completely unexpectedly, the database of our Xenforo was blocked. This happens when the capacity (5.5 GB) is exceeded. Half a year ago we moved from vB3 to a new managed server. At that time the size of the database (17 year old forum!) Was ~ 4.4 GB. The customer support of our host said that the 5.5 GB should be enough for a longer period of time. In all the years before, the database had only grown very slowly.
So yesterday I was surprised, not to say shocked, that the database was suddenly occupied with 9.08 GB. phpmyadmin currently has 4.6 GB. However, our hoster informs you that InnoDBs may display incorrect displays and that only the value in the database overview is decisive here. As an exception, our hoster doubled the capacity of the DB so that it is now 10.5 GB. However, from yesterday to today, 400 (!) MB of additional memory should have been added. 9.45 GB are now shown (9.08 yesterday!). How can that be? What is suddenly going wrong? I haven't changed anything on the forum since April. Everything went smoothly.

I am a server noob and currently absolutely speechless and haphazard. At the moment I have to assume that we will use a few hundred MB more tomorrow and then the DB will be locked again and probably no longer unlocked.

My hoster sent me the following message today:

Hello Christian,

Thank you very much for your email.

The discrepancy has something to do with INNODB's data free concept.

InnoDB: what does data free mean?


This is basically not an error, but rather a database routine.

I recommend that you optimize the database. This can also be done automatically using cron jobs.

Instructions and further information can be found at:

Exceeding the MySQL database capacity - IONOS help

Thank you for your cooperation.

regards

Alexander

IONOS customer service

What do you think? Mind you, it is a managed server. As a loyal customer for many years, I feel a bit ripped off.

I am grateful for any help and for any problem-solving.

It is, as before, a phenomenon and a mystery where this inexplicable memory requirement is suddenly supposed to come from !?

I haven't changed anything in the forum and the user behavior hasn't changed significantly either.

Best regards,
Chris

 

[Chris D]

They should be able to tell you specifically which database related files are growing and what the size of each table space is.

It's just a simple command that needs to be run in the MySQL/MariaDB data directory. Defaults to /var/lib/mysql/<database_name>/:

# ls -lS
total 5189636
-rw-rw---- 1 mysql mysql 1254096896 Sep 23 14:18 xf_post.ibd
-rw-rw---- 1 mysql mysql  482344960 Sep 23 13:48 xf_conversation_message.ibd
-rw-rw---- 1 mysql mysql  301989888 Sep 23 14:25 xf_ip.ibd
-rw-rw---- 1 mysql mysql  222298112 Sep 23 14:11 xf_reaction_content.ibd
-rw-rw---- 1 mysql mysql  167772160 Sep 23 14:26 xf_es_thread_similar.ibd
-rw-rw---- 1 mysql mysql  167772160 Sep 23 14:25 xf_user.ibd

Our largest table is xf_post at just over a GB.

 

[Silmarillion]

Hi Chris,

first of all, thank you for your quick response.

We are also discussing the topic in the German forum. (Xendach)
After I posted the add-ons I used, @Kirby expressed the assumption that it could be Xon's add-on "User Activity". Unfortunately, I only have very limited rights on my managed server and cannot use Redis, for example. Kirby said it was a deadly combination in connection with "User Activity".

What do you suggest? Deactivate the addon? Could Kirby be right? I think very highly of him!

I have only two large tables: xf_post (2,1 GB) und xf_search_index (1,9 GB).

 

[Chris D]

If they are your two largest tables, I’m not sure where the other 6GB you mentioned is being used up.

 

[Silmarillion]

If they are your two largest tables, I’m not sure where the other 6GB you mentioned is being used up.

That is exactly the big question that arises for me. And to which I have not yet found an answer.

I downloaded and unzipped a current backup. The SQL file is exactly 3.37 GB in size.

 

[HappyWorld]

Have you tried to optimize all tables in your server? (such as suggested by your hosting)
You can use tools such as phpmyadmin.

 

[Chris D]

I can't help but feel your host isn't being very transparent here.

If they are going to restrict your disk space in such a way they ought to be able to give you a clear itemisation of what is taking up the space. You mentioned specifically in your first post that the database is over 10GB. I'm not sure how that can be the case if a restored backup of the SQL data is 3.37GB.

That being said, on a running MySQL server there are occasions where the InnoDB files on the filesystem don't recover space when the data within them shrinks. This is particularly true if innodb_file_per_table is disabled. Plus there will be various InnoDB log files etc.

Is the host able to confirm if innodb_file_per_table is enabled and in either case list exactly what files are contributing to the space they claim is being used?

 

[Kirby]

innodb_file_per_table is enabled and data_free is insignificant (~ 165 MB).

Without being able to access MySQL data directory it isn't really possible to tell what the problem would be - only the host could.

IONOS help on optimizing tables is pretty much ... not helpful at all

SELECT concat('optimize table `', table_schema, '`.`', TABLE_NAME, '`;')
FROM information_schema.tables
WHERE table_schema = database()
  AND data_free / (data_length + index_length + data_free) > 0.6
ORDER BY data_free DESC;

returns an empty result set.

 

[Silmarillion]

I just wanted to answer @Chris D and tell him that I will pass it on to my hoster in exactly the same way. But @Kirby was faster. So that doesn't lead to anything either? Or should I try it anyway?

There is one big problem: my host claims the database is 9.4 GB. A forum backup only has 3.37 GB! They simply blame this discrepancy on errors in InnoDB. They don't say more about it. And I emphasize it again: we are talking about a managed server! It is precisely then that such problems are in the hoster's area of responsibility. Especially since I haven't changed anything in half a year.

 

[Silmarillion]

News from my hoster ...

Dear Mr. K.,

I would like to introduce myself as your new personal contact.

Regarding your problem with the database allocation, I have the following technical information for you.

A significant part of the memory used can be traced back to indexes. Indexes are used for faster searches in the database. In your case it is especially the "Full Text Search" indices that occupy a lot of memory (starting with FTS):

141M FTS_00000000006b9591_000000000086fcd6_INDEX_6.ibd
156M import_log_vbulletin_1.ibd
217M FTS_00000000006b9591_000000000086fccf_INDEX_1.ibd
525M FTS_00000000006b9591_000000000086fccf_INDEX_4.ibd
557M FTS_00000000006b9591_000000000086fccf_INDEX_3.ibd
573M FTS_00000000006b9591_000000000086fccf_INDEX_6.ibd
637M FTS_00000000006b9591_000000000086fccf_INDEX_5.ibd
801M FTS_00000000006b9591_000000000086fccf_INDEX_2.ibd
2.2G xf_search_index.ibd
2.4G xf_post.ibd

Here you could agree with your technician whether he can start at this point in order to reduce the memory requirement.

Maybe @Chris D or someone else can do something with it !?

By the way, it was confirmed to me that the technicians of my hoster found and confirmed 4.9 GB of used memory. Which is a big difference to the supposedly occupied 9.4 GB. I have still not been told where this difference is supposed to come from.

I'm slowly suspecting that the ad is simply wrong and that the fault lies with my hoster. Especially since I was told that several customers are affected.

 

[Chris D]

I have to admit some amount of naivety when it comes to MyISAM tables generally and whether they'd have similar storage requirements for full text indexes but the most notable thing here is that it appears your xf_search_index table has been converted from the MyISAM engine to InnoDB.

This is not supported by us and not the default. It means the table storage for the xf_search_index table has been changed from what we set it to which is MyISAM, to be InnoDB.

Is this something you intended to happen? If not, I'd recommend reverting it back to its default.

The process for that would be:

1. Disable the search functionality on your forum or close your forum
2. Drop the xf_search_index table.
3. Recreate the xf_search index table as follows:

CREATE TABLE `xf_search_index` (
  `content_type` varchar(25) COLLATE utf8mb4_general_ci NOT NULL,
  `content_id` int unsigned NOT NULL,
  `title` varchar(250) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `message` mediumtext COLLATE utf8mb4_general_ci NOT NULL,
  `metadata` mediumtext COLLATE utf8mb4_general_ci NOT NULL,
  `user_id` int unsigned NOT NULL DEFAULT '0',
  `item_date` int unsigned NOT NULL,
  `discussion_id` int unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`content_type`,`content_id`),
  KEY `user_id_item_date` (`user_id`,`item_date`),
  FULLTEXT KEY `title_message_metadata` (`title`,`message`,`metadata`),
  FULLTEXT KEY `title_metadata` (`title`,`metadata`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

4. Rebuild your search index from Admin > Tools > Rebuild search index

The hope is that after step 2, these files will no longer be present:

141M FTS_00000000006b9591_000000000086fcd6_INDEX_6.ibd
217M FTS_00000000006b9591_000000000086fccf_INDEX_1.ibd
525M FTS_00000000006b9591_000000000086fccf_INDEX_4.ibd
557M FTS_00000000006b9591_000000000086fccf_INDEX_3.ibd
573M FTS_00000000006b9591_000000000086fccf_INDEX_6.ibd
637M FTS_00000000006b9591_000000000086fccf_INDEX_5.ibd
801M FTS_00000000006b9591_000000000086fccf_INDEX_2.ibd
2.2G xf_search_index.ibd

This would free up about 5.5GB evidently.

However, after step 4, at least some of that space will be re-used. I just don't know how much because I'm not totally clear on how MyISAM stores this data. Certainly worth a try though because then the software will be functioning as default.

But, if I may be frank, this may well just be a sign that you have outgrown your host. Of course a fledgling forum starting out then a limit of 5-10GB isn't totally unreasonable and would take quite some time to fill up. But with a xf_post table that is exceeding a few GB your forum has grown and may well grow (hopefully) even further so you may well need a bit more breathing room.

Something to think about at least.