DB Table 'overhead' and deleted topics.

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Get Involved
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
User avatar
wmtipton
Registered User
Posts: 564
Joined: Thu Apr 26, 2007 8:16 pm
Contact:

DB Table 'overhead' and deleted topics.

Post by wmtipton » Mon Dec 27, 2010 6:38 pm

Using phpBB 3.08 with mysql DB.

Ive been working on 'fixing' one of my forums that seemed to have something wrong in a couple tables.
After fixing most of the problems and getting it from the bloated 13MB or so down to 1.8MB I then deleted some unneeded threads and posts to clear up more space.
But the DB size remained the same even though I deleted a large enough portion of threads that I should have seen some change.

So I used phpmyadmin to optimize the tables and I noticed that in a column called 'overhead' there were a few tables that had anywhere from a couple KB to over a hundred KB.
After optimizing this column was now empty and my DB lost another 2MB in size.

My question is this.
When I delete a thread does it really not actually GET DELETED but hangs around in the DB anyway ?
I mean, whats the point of deleting a bunch of old, unused threads from a forum if they are still attached to the DB anyway ?
My hosting company is very generous with DB size....2GB or something like that, so this wont be an issue, but I know people are are limited to 50MB and this could run out VERY quickly even if they are deleting older threads but the data isnt actually being 'deleted' from the database.

Other than using phpmyadmin is there some other way for a thread to BE deleted entirely....?
Secondly....is this a phpBB3 issue or is it a mysql issue ?
Is the data still being there and the tables needing to be optimized to get rid of it because of phpBB3 or simply an issue with mysql ?

If its mysql then I guess Im barking at the wrong people and need to go chew off some ears at the mysql boards instead :)
mysql database backup software - mysql Workbench

User avatar
wmtipton
Registered User
Posts: 564
Joined: Thu Apr 26, 2007 8:16 pm
Contact:

Re: DB Table 'overhead' and deleted topics.

Post by wmtipton » Tue Dec 28, 2010 10:54 pm

No thoughts ?
:)
mysql database backup software - mysql Workbench

jenshenrik
Registered User
Posts: 75
Joined: Tue Dec 28, 2010 3:09 pm
Location: Denmark (Scandinavia)

Re: DB Table 'overhead' and deleted topics.

Post by jenshenrik » Wed Nov 28, 2012 2:16 pm

I´ve had the same issue as you, but now i´m optimizing the MySQL database overhead once a week and it has speeded up my site significantly :)

I wonder, why it cannot be done from the ACP without a MOD :)

jenshenrik
Registered User
Posts: 75
Joined: Tue Dec 28, 2010 3:09 pm
Location: Denmark (Scandinavia)

Re: DB Table 'overhead' and deleted topics.

Post by jenshenrik » Wed Nov 28, 2012 2:17 pm

--- Double Posted --- Sorry...

User avatar
wmtipton
Registered User
Posts: 564
Joined: Thu Apr 26, 2007 8:16 pm
Contact:

Re: DB Table 'overhead' and deleted topics.

Post by wmtipton » Wed Nov 28, 2012 2:55 pm

jenshenrik wrote:
I wonder, why it cannot be done from the ACP without a MOD :)
Ive wondered that myself. It seems to work really quickly with the other software. It would be easier to have the feature in the phpBB software itself if it can be done.
Is there a mod that will do it?
mysql database backup software - mysql Workbench

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50501
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: DB Table 'overhead' and deleted topics.

Post by stevemaury » Wed Nov 28, 2012 3:33 pm

jenshenrik wrote:I´ve had the same issue as you, but now i´m optimizing the MySQL database overhead once a week and it has speeded up my site significantly :)

I wonder, why it cannot be done from the ACP without a MOD :)
phpBB runs on many databases, not just MySQL. If one starts adding phpMyAdmin tools to the ACP, where to start and where to stop?
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

User avatar
Lumpy Burgertushie
Registered User
Posts: 66328
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: DB Table 'overhead' and deleted topics.

Post by Lumpy Burgertushie » Wed Nov 28, 2012 4:40 pm

and yes, when you delete a post/forum etc. it is deleted from the database.

the overhead that shows in phpmyadmin is not the old deleted posts etc.


by the way, 13 MB is a very tiny, tiny database for a bulletin board.

also, anywhere from 1/2 to 2/3 of the database size will be in the search tables. they are usually bigger than the post tables by far because every single word of every single post is in there and sometimes in there twice.


robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.2 Styles by PlanetStyles.net

If a tree falls in the forest and nobody is there, does it make a sound?

User avatar
wmtipton
Registered User
Posts: 564
Joined: Thu Apr 26, 2007 8:16 pm
Contact:

Re: DB Table 'overhead' and deleted topics.

Post by wmtipton » Fri Nov 30, 2012 1:44 pm

stevemaury wrote:
jenshenrik wrote:I´ve had the same issue as you, but now i´m optimizing the MySQL database overhead once a week and it has speeded up my site significantly :)

I wonder, why it cannot be done from the ACP without a MOD :)
phpBB runs on many databases, not just MySQL. If one starts adding phpMyAdmin tools to the ACP, where to start and where to stop?
I guess that makes sense. Didnt really think of it that way. Not that I understand why there is any overhead at all to begin with since I dont know a whole lot about databases other than how to set them up for a forum :D


edit..I just found this....
https://www.phpbb.com/customise/db/mod/ ... pair_tool/

is this a decent mod?
I am using a MySql database.
mysql database backup software - mysql Workbench

jenshenrik
Registered User
Posts: 75
Joined: Tue Dec 28, 2010 3:09 pm
Location: Denmark (Scandinavia)

Re: DB Table 'overhead' and deleted topics.

Post by jenshenrik » Tue Dec 04, 2012 11:39 am

I installed the MOD on a "mirror board" and it seems to be a well functioning tool :)

Anyway, I use phpMyAdmin... :)

User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: DB Table 'overhead' and deleted topics.

Post by Oyabun1 » Tue Dec 04, 2012 12:19 pm

The place to ask about a MOD is that MOD's Discussion/Support forum, not here. There will be a lot more people with experience of the MOD there.
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests

User avatar
wmtipton
Registered User
Posts: 564
Joined: Thu Apr 26, 2007 8:16 pm
Contact:

Re: DB Table 'overhead' and deleted topics.

Post by wmtipton » Wed Dec 05, 2012 5:49 pm

Oyabun1 wrote:The place to ask about a MOD is that MOD's Discussion/Support forum, not here. There will be a lot more people with experience of the MOD there.
Thanks, I'll give it a look there :)

I installed it on all my boards and it works perfect. Much easier than having to open the other software just to optimize the tables...
mysql database backup software - mysql Workbench

jenshenrik
Registered User
Posts: 75
Joined: Tue Dec 28, 2010 3:09 pm
Location: Denmark (Scandinavia)

Re: DB Table 'overhead' and deleted topics.

Post by jenshenrik » Thu Dec 06, 2012 9:48 am

wmtipton wrote:
Oyabun1 wrote:The place to ask about a MOD is that MOD's Discussion/Support forum, not here. There will be a lot more people with experience of the MOD there.
Thanks, I'll give it a look there :)

I installed it on all my boards and it works perfect. Much easier than having to open the other software just to optimize the tables...
BUT: Remember, that the tool optimizes all of your tables (if selected). Not only the phpBB tables :)

User avatar
AmigoJack
Registered User
Posts: 5588
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: DB Table 'overhead' and deleted topics.

Post by AmigoJack » Thu Dec 06, 2012 10:19 am

OPTIMIZE TABLE is hazardous - the documentation is quite clear about this: if you have a huge table it can also totally halt your whole MySQL DBMS when running out of disk space during this operation.

Obviously nobody knows or cares about this.
The worst thing about censorship is ███████████
Affin wrote:
Tue Nov 20, 2018 9:51 am
The problem is probably not my English but you do not want to understand correctly.
...
We will not come anybody anyway, nevertheless, it's best to shit this.

jenshenrik
Registered User
Posts: 75
Joined: Tue Dec 28, 2010 3:09 pm
Location: Denmark (Scandinavia)

Re: DB Table 'overhead' and deleted topics.

Post by jenshenrik » Thu Dec 06, 2012 6:56 pm

AmigoJack wrote:OPTIMIZE TABLE is hazardous - the documentation is quite clear about this: if you have a huge table it can also totally halt your whole MySQL DBMS when running out of disk space during this operation.

Obviously nobody knows or cares about this.
I hope, you mean, IF you run out of disk space ;)

User avatar
wmtipton
Registered User
Posts: 564
Joined: Thu Apr 26, 2007 8:16 pm
Contact:

Re: DB Table 'overhead' and deleted topics.

Post by wmtipton » Sat Dec 08, 2012 8:35 pm

AmigoJack wrote:OPTIMIZE TABLE is hazardous - the documentation is quite clear about this: if you have a huge table it can also totally halt your whole MySQL DBMS when running out of disk space during this operation.

Obviously nobody knows or cares about this.
meh.
Mine are small. I wouldnt let any database get above 10MB or so anyway...not real need to for the sites I own or webmaster.
Takes about 3 seconds tops.
mysql database backup software - mysql Workbench

Locked

Return to “[3.0.x] Support Forum”