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.
Scam Warning
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
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 » Sun Dec 09, 2012 1:08 am

Since you are using less than 1% of the available database space there doesn't seem to be any need to optimise the database and with small databases you are unlikely to see any benefit from doing so.

You should make sure you backup the database before making any changes, including an optimisation, because as AmigoJack said there is a risk.
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests

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

Re: DB Table 'overhead' and deleted topics.

Post by Lumpy Burgertushie » Sun Dec 09, 2012 3:06 am

wmtipton wrote:
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.
depending on your board, 10MB could be used up in only a few posts.

that is an extremely small number to try to limit database size to.


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 » Sun Dec 09, 2012 5:51 am

Lumpy Burgertushie wrote: depending on your board, 10MB could be used up in only a few posts.

that is an extremely small number to try to limit database size to.


robert
Most of these boards are for temporary material that gets deleted/pruned after so long.
The ones that arent like that are locked and pretty much read only. I use phpBB3 forum software on those ones because of the search feature and the ease of adding new material.

On the couple that are 'discussion' forums they get pruned pretty quickly...nothing older than about 30 days or so.
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 » Sun Dec 09, 2012 5:55 am

Oyabun1 wrote:Since you are using less than 1% of the available database space there doesn't seem to be any need to optimise the database and with small databases you are unlikely to see any benefit from doing so.

You should make sure you backup the database before making any changes, including an optimisation, because as AmigoJack said there is a risk.
It did dawn on me after reading some of the points made that maybe I really dont need to worry about it. I didnt realize that the overhead didnt just keep growing perpetually. I thought it would just keep getting larger and larger until what might be a 2MB database would end up being 10MB or so, which would be entirely unacceptable.

If a DB is 2MB total...what would you say the maximum overhead would ever be?
Reason being is the backups. I dont see any point in backing up all that extra stuff that isnt part of the threads/posts.
mysql database backup software - mysql Workbench

Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: DB Table 'overhead' and deleted topics.

Post by Oleg » Mon Dec 10, 2012 12:15 am

Database overhead does not affect the size of dumps as it only exists in internal database structures.

If you are backing up data for its content as opposed to performing a system-wide backup of everything, you should always dump databases instead of backing up their raw files.
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com

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 » Mon Dec 10, 2012 4:52 am

Oleg wrote:Database overhead does not affect the size of dumps as it only exists in internal database structures.

If you are backing up data for its content as opposed to performing a system-wide backup of everything, you should always dump databases instead of backing up their raw files.
Not sure of the difference.
I always use the backup and restore within phpBB3 itself. Ive tried other methods and dont trust them, not to mention they end up being a pain in the butt :)
mysql database backup software - mysql Workbench

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 » Mon Dec 10, 2012 7:08 am

The phpBB backup and restore drops the existing tables and recreates them, so any overhead in the tables is eliminated.
                      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 » Thu Dec 13, 2012 2:57 pm

Oyabun1 wrote:The phpBB backup and restore drops the existing tables and recreates them, so any overhead in the tables is eliminated.
Thats interesting. I may have just assumed otherwise. I'll have to definitely check it out because if thats the case then theres no real need for me to bother with optimizing unless its for a specific reason.
:)

edit...very interesting.
In two of two cases the exported database backup was actually slightly larger AFTER I optimized the table (only about 1-3KB...235KB/236KB and 519KB/521KB)) but the difference was negligible.
Guess theres really no need to optimize just for export size after all :)

These were forums that dont get a lot of use, of course, better to mess with them :D
Thanks a ton for informing me of the facts about the backups. :)
mysql database backup software - mysql Workbench

Locked

Return to “[3.0.x] Support Forum”