Deleting posts not reducing database size enough...?

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
3BB
Registered User
Posts: 22
Joined: Tue Jan 30, 2007 6:18 pm

Deleting posts not reducing database size enough...?

Post by 3BB » Mon Oct 13, 2008 1:49 pm

Template(s) used: Acid by Hedonism
Any and all MODs: PHPBB Admin ToolKit 2.1
Do you use a port of phpBB: What? I downloaded from the phpbb site and uploaded files to mine.
Version of phpBB: 2.0.22
Version of PHP: Server version: 5.0.51-log
Which database server and version: MySQL client version: 4.1.15
Host: FluidHosting.com
Did someone install this for you/who: No
Is this an upgrade/from what to what: No
Is this a conversion/from what to what: No
Have you searched for your problem: Yes
If so, what terms did you try: various combinations of optimize, pruning, deleting, size, database
Do you have a test account for us: Sorry, cannot.
State the nature of your problem:

Forum got spammed with over 150,000 posts and 5,000 not activated users. All posts were deleted with the prune feature and all not-activated users were deleted with the ToolKit.

Forum now has fewer than 200 posts and under 100 users, but the size is 1730MB. No posts have attachments. All the posts and users currently did not use anywhere near 1GB before the spam...not sure exactly, but way, way less.

After the spam hit, db size went to 3500MB. Why has it reduced to only 1730MB after cleaning up?

I have resynched the forums...in fact, I've deleted almost everything but a small set of threads that I need to backup.

What is going on? How can I force phpBB to clean up the database? The PHPBB Toolkit shows one user "Anonymous" with over 150,000 posts, but I cannot delete him (says "Anonymous" is required).
Last edited by 3BB on Mon Oct 13, 2008 2:16 pm, edited 1 time in total.

User avatar
karlsemple
Former Team Member
Posts: 39802
Joined: Mon Nov 01, 2004 8:54 am
Location: Hereford, UK
Contact:

Re: Deleting posts not reducing database size enough...?

Post by karlsemple » Mon Oct 13, 2008 1:52 pm

If you have phpmyadmin go into your board database and check all the tables and then in the operations drop down menu at the bottoms click 'optimize' to get phpmyadmin to optimize the database, and then test to see if this gets a more reasonable size.
Image

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: Deleting posts not reducing database size enough...?

Post by espicom » Mon Oct 13, 2008 2:00 pm

When you delete something in a database, it does not reduce the size of the tables. It simply marks the "deleted" record as such, and adds the space it occupied to the "free space list". Your database should not GROW in size for a long time. If you view the database in phpmyadmin, you will see "overhead" on the various tables... This is that free space, and the database engine will try to use it, prior to asking the operating system for more space.

The optimize or repair option Karl mentioned (not something you can do through phpBB) will actually remove the empty records, by copying the "live" records to a new table, and renaming it. It is a side-effect of the repair operation done by the program in the first link in my signature, which you can use if you don't have phpmyadmin.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

3BB
Registered User
Posts: 22
Joined: Tue Jan 30, 2007 6:18 pm

Re: Deleting posts not reducing database size enough...?

Post by 3BB » Mon Oct 13, 2008 2:09 pm

Hi,

Thanks for such a quick reply.

I went into phpmyadmin, checked all the tables, and optimized them. Took quite awhile...all the time from your post until this one.

The size of the database remained unchanged: 1730MB.

The culprits, as listed in phpmyadmin, seem to be:

phpbb_search_wordmatch : 74 million records, 1500MB size
phpbb_search_wordlist : 3.5 million records, 167MB size

If I "empty" or "drop" those two tables, will that corrupt everything or will they be repopulated correctly from the remaining posts?

Thanks!
Last edited by 3BB on Mon Oct 13, 2008 2:17 pm, edited 1 time in total.

3BB
Registered User
Posts: 22
Joined: Tue Jan 30, 2007 6:18 pm

Re: Deleting posts not reducing database size enough...?

Post by 3BB » Mon Oct 13, 2008 2:13 pm

espicom wrote:When you delete something in a database, it does not reduce the size of the tables. It simply marks the "deleted" record as such, and adds the space it occupied to the "free space list". Your database should not GROW in size for a long time. If you view the database in phpmyadmin, you will see "overhead" on the various tables... This is that free space, and the database engine will try to use it, prior to asking the operating system for more space.
Strange.

Why did it reduce from 3.5GB down to 1.7GB then?

And how can I reduce it further? My hosting plan does not allow for a 1.7GB database...I need to make it small like it used to be.

The "overhead" listed on all the various tables sums to 1.4MB only.

User avatar
karlsemple
Former Team Member
Posts: 39802
Joined: Mon Nov 01, 2004 8:54 am
Location: Hereford, UK
Contact:

Re: Deleting posts not reducing database size enough...?

Post by karlsemple » Mon Oct 13, 2008 2:14 pm

Hang on a moment you have gone from 1.7 MB to 1.7GB :shock: Which is it, as that makes a huge difference? The search tables contain the information used for the board search to work and are meant to be larger than the rest of the tables in size, they can grow quite large on bigger boards. You can empty them if you wish but then your board search will not return any search results :)

There is a mod which can be installed which will allow you to rebuild the search table after emptying them which will make sure that the search tables only contain relevant data. Note though that on a large board this will take a long time to do.
Image

3BB
Registered User
Posts: 22
Joined: Tue Jan 30, 2007 6:18 pm

Re: Deleting posts not reducing database size enough...?

Post by 3BB » Mon Oct 13, 2008 2:18 pm

karlsemple wrote:Hang on a moment you have gone from 1.7 MB to 1.7GB :shock: Which is it, as that makes a huge difference?
My mistake...corrected now. Sorry about that.

3BB
Registered User
Posts: 22
Joined: Tue Jan 30, 2007 6:18 pm

Re: Deleting posts not reducing database size enough...?

Post by 3BB » Mon Oct 13, 2008 2:21 pm

Okay, that makes sense now...

The search tables blew up in size with the 150,000 posts of spam. The posts were deleted, but deleting a post does not cause its additions to the search tables to be removed (guessing here?)

Best bet is to delete those tables in phpmyadmin and then use the rebuild mod if I care.

True?

User avatar
karlsemple
Former Team Member
Posts: 39802
Joined: Mon Nov 01, 2004 8:54 am
Location: Hereford, UK
Contact:

Re: Deleting posts not reducing database size enough...?

Post by karlsemple » Mon Oct 13, 2008 2:23 pm

The search tables blew up in size with the 150,000 posts of spam. The posts were deleted, but deleting a post does not cause its additions to the search tables to be removed (guessing here?)
Yes it would remove the entries, if deleted via phpBB, The Admin Toolkit however simply replaces the text with deleted and thus the search entries are probably untouched.
Image

3BB
Registered User
Posts: 22
Joined: Tue Jan 30, 2007 6:18 pm

Re: Deleting posts not reducing database size enough...?

Post by 3BB » Mon Oct 13, 2008 2:32 pm

Then that doesn't make sense. I deleted the posts via "pruning" in phpBB so the entries should have been deleted, right?

I used the Admin ToolKit to delete only the users, after I had already deleted the posts from within phpBB.

User avatar
karlsemple
Former Team Member
Posts: 39802
Joined: Mon Nov 01, 2004 8:54 am
Location: Hereford, UK
Contact:

Re: Deleting posts not reducing database size enough...?

Post by karlsemple » Mon Oct 13, 2008 2:34 pm

3BB wrote:Then that doesn't make sense. I deleted the posts via "pruning" in phpBB so the entries should have been deleted, right?

I used the Admin ToolKit to delete only the users, after I had already deleted the posts from within phpBB.

You would have thought so, but a quick glance at the code and I would say no it does not remove the entries from the search tables when you prune posts :)
Image

3BB
Registered User
Posts: 22
Joined: Tue Jan 30, 2007 6:18 pm

Re: Deleting posts not reducing database size enough...?

Post by 3BB » Mon Oct 13, 2008 2:55 pm

Not ideal, eh?

Is there any way to delete large numbers of posts and have their entries in the search tables removed?

I went ahead and just emptied the two search tables in question here...reduced the database size to 0.45 MB. :)

User avatar
karlsemple
Former Team Member
Posts: 39802
Joined: Mon Nov 01, 2004 8:54 am
Location: Hereford, UK
Contact:

Re: Deleting posts not reducing database size enough...?

Post by karlsemple » Mon Oct 13, 2008 3:03 pm

Post removed - my bad, really tired and mis-read the previous reply...that's me off for a bit, my apologies :)
Image

3BB
Registered User
Posts: 22
Joined: Tue Jan 30, 2007 6:18 pm

Re: Deleting posts not reducing database size enough...?

Post by 3BB » Mon Oct 13, 2008 3:07 pm

I guess I missed something. No worries. :)

Locked

Return to “2.0.x Support Forum”