XF 2.3 large database size problem?

[Garfield™]

There are only 6,000 topics on my forum site, and there are no attachments, images, etc. in these topics.

However, when I look at the post table, I see that its size is 1.3 GB. This is not normal.

There were topics on my site before, I deleted them, I deleted them permanently.

How can I optimize the post table size?

 

[Garfield™]

When I say restructure vehicles like in an SS, it counts 50-60 thousand topics, whereas on my site there are 5-6 thousand topics.

 

[Garfield™]

Shouldn't so many topics and messages take up so much space?

 

[Paul B]

According to the screenshot from phpMyAdmin, there are around 73,000 rows in the post table.

Perhaps they were only soft deleted, not hard deleted.

 

[Garfield™]

According to the screenshot from phpMyAdmin, there are around 73,000 rows in the post table.

Perhaps they were only soft deleted, not hard deleted.

How can I delete it permanently and drastically?

 

[Garfield™]

Actually, I selected all the topics and said delete them permanently, I deleted them harshly but I don't understand why it happened like this.

 

[Garfield™]

@Paul B I think the topics were just deleted from the forum, they are on the DB side, how can I reconcile this situation? In the topic update section, the deleted topics appear as empty, the topics and the number of topics are normal in the forum, but the DB database is not like that

 

[ENF]

@Paul B I think the topics were just deleted from the forum, they are on the DB side, how can I reconcile this situation? In the topic update section, the deleted topics appear as empty, the topics and the number of topics are normal in the forum, but the DB database is not like that

Just FYI, after you actually clean up the data you want to remove, the DB space usage is not reclaimed as you'd might expect due to how the database handles the data.

MySQL doesn't reduce the size of ibdata1 (the system tablespace for the InnoDB infrastructure), so the space is still claimed even after clean up of the data and optimization of the table. I just dealt with this problem with a single table that had exceeded 23gb of usage. I cleaned up (deleted) all the rouge data and the only way to fix the space issue was to drop the database (after a backup) and restore the database which resolved the space issue.

 

[Garfield™]

Just FYI, after you actually clean up the data you want to remove, the DB space usage is not reclaimed as you'd might expect due to how the database handles the data.

MySQL doesn't reduce the size of ibdata1 (the system tablespace for the InnoDB infrastructure), so the space is still claimed even after clean up of the data and optimization of the table. I just dealt with this problem with a single table that had exceeded 23gb of usage. I cleaned up (deleted) all the rouge data and the only way to fix the space issue was to drop the database (after a backup) and restore the database which resolved the space issue.

As far as I understand, even if I delete the topics from the forum, the site, these topics will continue to remain in the database?

Isn't there a way to delete or optimize them from the database?

If I take a backup and restore this backup, nothing will change?

 

[ENF]

As far as I understand, even if I delete the topics from the forum, the site, these topics will continue to remain in the database?

Isn't there a way to delete or optimize them from the database?

If I take a backup and restore this backup, nothing will change?

Sorry, my reply was solely focused on the space issue after you clean the data out.
As Paul mentioned above, some of that data there could be content that was soft-deleted.

If you are using phpMyAdmin, go to your post table and look at the structure tab, you should see an info panel like this one:



That shows how much space is actually in use -- the overhead in this example is data I removed to fix the usage isue.
The 'data' is the actual storage used where as the the overhead (24.2GiB) is the empty space that ibdata is still holding on to.

I guess at first, you need to identify content that you want to remove. You'd have to hard delete any content from XF to remove it from the database. A soft delete won't remove it from your database physically.

 

[Garfield™]

Sorry, my reply was solely focused on the space issue after you clean the data out.
As Paul mentioned above, some of that data there could be content that was soft-deleted.

If you are using phpMyAdmin, go to your post table and look at the structure tab, you should see an info panel like this one:

View attachment 320699

That shows how much space is actually in use -- the overhead in this example is data I removed to fix the usage isue.
The 'data' is the actual storage used where as the the overhead (24.2GiB) is the empty space that ibdata is still holding on to.

I guess at first, you need to identify content that you want to remove. You'd have to hard delete any content from XF to remove it from the database. A soft delete won't remove it from your database physically.

I have already deleted the contents harshly,

 

[ENF]

I have already deleted the contents harshly,

Ah ok, well, then based on your screenshot of the information box, you still have actually 1.3 GiB of data in use in that table.
And that is odd, because even on a large site, the xf_post table doesn't consume that much data on an average site with usual post sizes. (not long stories or anything like that)

 

[Garfield™]

Ah ok, well, then based on your screenshot of the information box, you still have actually 1.3 GiB of data in use in that table.
And that is odd, because even on a large site, the xf_post table doesn't consume that much data on an average site with usual post sizes. (not long stories or anything like that)

yes I didn't understand either, I deleted the topics one by one harshly, it was deleted from the forum, it is not there anywhere but it is in the database, this situation is really strange, my site is a new site anyway

Is there no solution for this?

 

[ENF]

yes I didn't understand either, I deleted the topics one by one harshly, it was deleted from the forum, it is not there anywhere but it is in the database, this situation is really strange, my site is a new site anyway

Is there no solution for this?

Not a clear solution, no.
You'd need to investigate the data in the table to see why it's consuming so much space for what's actually in the database.

 

[Garfield™]

I solved the problem with some SQL questions I got from deepseek artificial intelligence now the DB size is 300 mb thanks