Decrease database size?

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)
Blacktiger63
Registered User
Posts: 55
Joined: Thu Oct 17, 2013 7:43 pm

Decrease database size?

Post by Blacktiger63 »

Hello.

I'm helping a friend with his phpbb 3.0.11 forum. We want to upgrade to 3.0.12 and from there to 3.1.x or maybe directly from 3.0.11 to 3.1.x.

However, I'm wondering about his database size. Its 466 MiB big and we would like to try and clean up as much as possible, before upgrading.

This I found under maintenance -> search index:
Total number of indexed words: 287365 Total number of word to post relations indexed: 4908190
I've managed several forums but 466 MiB seems a little bit very big for the kind of forum my friend is running.

The cause is not the attachments, because they are already outside of the database in a directory, so they don't count for the size of the database.

Is there a way we can see what is causing this big size? Or a way to decrease the size?

PhpBB version 3.0.11
Php version 5.4.36
Mysql version 5.5.41
Last edited by Mick on Fri Jan 30, 2015 10:45 am, edited 1 time in total.
Reason: Solved
User avatar
Lumpy Burgertushie
Registered User
Posts: 68195
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: Decrease database size?

Post by Lumpy Burgertushie »

every single word in all the posts are in those database tables and many of them more than once of course.

the search tables can often be over 2/3 of the database size.

you can always delete the index and rebuild it but that probably won't make that much difference.

however, if you are going to upgrade to 3.1 then do not worry about it as those tables will not be moved over to the new board.

the thing to worry about is to clean up the rest of the database before you do the upgrade.

download the Support Toolkit and run the included database cleaner to remove any and all tables/data that is not standard phpbb. this will remove all MODs that were installed.

when you upgrade, you are not going to use any of the 3.0 files so whatever may be in them does not matter.

once you do that, you can follow these instructions to upgrade to 3.1.2:
Installing phpbb 3.1.x

Tools needed to set up and customise phpBB

  1. Backup all board files and the database.
  2. Download the full package.
  3. Unzip to your desktop and open the phpBB3 directory.
  4. Remove (delete) the config.php file, and the /images and /files directories from the package.
  5. Via FTP or SSH delete everything from your board installation on the server EXCEPT for the config.php file, and the /files, /images, and /store directories.
  6. Via FTP or SSH upload the remaining files and directories (from step 3 above) to the root of your board installation on the server overwriting the existing files.
  7. In your browser go to http://www.example.com/yourforum/instal ... update.php
  8. Let that run to completion.
  9. Via FTP or SSH delete the folder named /install/ from the root of your board installation.
  10. Hold one foot in the air and spin around in a circle three times saying "I am a phpbb guru".
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.3 Styles by PlanetStyles.net

If nobody is in the forest, does a tree really fall?
Blacktiger63
Registered User
Posts: 55
Joined: Thu Oct 17, 2013 7:43 pm

Re: Decrease database size?

Post by Blacktiger63 »

Hello Lumpy.

Thank you for the quick reply. I understand that all words are in the database and the search index is too.
I will download the support toolkit as you suggested and run the database cleaner. He has no mods installed so that should not give any problems. Thanks!

I'm just wondering where the 466 MiB is coming from, but I checked again, there are rather a lot of posts indeed.
Total posts 469326 • Total topics 298771

However.... Another friend has 160.444 posts but a lot less topics (28000), his database is 92 MiB. So I see the difference here. The phpbb friend has about 4x more posts and 10x more topics.
I presume this could explain the size of the database?
User avatar
Lumpy Burgertushie
Registered User
Posts: 68195
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: Decrease database size?

Post by Lumpy Burgertushie »

comparing the number of post s is not always a good indicator of post table size. it will depend on how much text is in the posts. you could have 10 zillion posts of one word each and have a very small database.

you could have one post with 10 zillion words in it and have a monstrous database.


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

Premium phpBB 3.3 Styles by PlanetStyles.net

If nobody is in the forest, does a tree really fall?
Blacktiger63
Registered User
Posts: 55
Joined: Thu Oct 17, 2013 7:43 pm

Re: Decrease database size?

Post by Blacktiger63 »

In that case I don't understand where the size is coming from.
Because he has a very lot of posts, but it's a satellite feed forum, so every post has only little content.

But who knows, maybe it will decrease after I ran the tool you suggested.;)
Blacktiger63
Registered User
Posts: 55
Joined: Thu Oct 17, 2013 7:43 pm

Re: Decrease database size?

Post by Blacktiger63 »

Sorry to bump this thread again.

I finally had time to try the STK to cleanup the database.
As a result, it seems that admin rights were reset, even the main admin can't see the Moderator forums now anymore.
I presume that should not be the case, should it?
Is there a way to fix this or do I need to restore the backup I made before using the Database cleanup?
User avatar
Lumpy Burgertushie
Registered User
Posts: 68195
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: Decrease database size?

Post by Lumpy Burgertushie »

depends on what you did with the database cleanup.

you are only supposed to remove things added to the default when you install MODs.

either you removed something that you shouldn't or somehow some permissions were added due to MODs etc.

yes, I would suggest you restore the backup and try again.
If this is a live board then you really should be doing this on a copy of the board installed on your computer.

you can use wamp or xamp etc. to create a server setup on your home computer for this.

then you can experiment until it all works as you like and then do it on the live board.


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

Premium phpBB 3.3 Styles by PlanetStyles.net

If nobody is in the forest, does a tree really fall?
Blacktiger63
Registered User
Posts: 55
Joined: Thu Oct 17, 2013 7:43 pm

Re: Decrease database size?

Post by Blacktiger63 »

Oh I thought I had to click everything, so I clicked next every time until the database cleanup said it was ready.

However I can't remember having seen things passing where it would say to reset admin permissions to something.
It did not make the database smaller either.
There are also no mods installed which have something to do with permissions.

Anyway, no worries, I'll restore the backup and make the upgrade from there this weekend.

Just one question. We're running 3.0.11 at this point. I've seen there is no STK for 3.0.13 at this moment.
What can I do best? Upgrade first to 3.0.13 and then upgrade to 3.1.2?
Or can I upgrade from 3.0.11 to 3.1.2 without issues?
User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: Decrease database size?

Post by Oyabun1 »

Blacktiger63 wrote:I've seen there is no STK for 3.0.13 at this moment.
The STK works with 3.0.13. However, if you run it on 3.0.11 you won't need to run it again on 3.0.13 because, presumably, you won't be adding anything non-standard to the database, so there would be nothing to be removed.
Blacktiger63 wrote:Or can I upgrade from 3.0.11 to 3.1.2 without issues?
Yes.
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests
Blacktiger63
Registered User
Posts: 55
Joined: Thu Oct 17, 2013 7:43 pm

Re: Decrease database size?

Post by Blacktiger63 »

Even now the database is restored and this way in fact, the database cleaner has not run?
Or do I still need to run the database cleaner again before upgrading?

There was however no change in size of the database after running it the first time.
User avatar
Lumpy Burgertushie
Registered User
Posts: 68195
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: Decrease database size?

Post by Lumpy Burgertushie »

the database cleanup has little to no effect on the size of the database. the point of it is to clean out anything that MODs have added. this is so that when you upgrade to 3.1 you won't have any residual database entries that would mess up the upgrade.

like I said in my post above, the search tables are going to be the main part of the database size.
however, if you are going to upgrade then don't worry about it..


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

Premium phpBB 3.3 Styles by PlanetStyles.net

If nobody is in the forest, does a tree really fall?
Blacktiger63
Registered User
Posts: 55
Joined: Thu Oct 17, 2013 7:43 pm

Re: Decrease database size?

Post by Blacktiger63 »

Great, thank you very much!
Blacktiger63
Registered User
Posts: 55
Joined: Thu Oct 17, 2013 7:43 pm

Re: Decrease database size?

Post by Blacktiger63 »

Just wanted to inform that the upgrade went flawless. :D

The only thing I'm kicking this topic with this reply is to notify you of something I discovered.
It's not really important I think, but I'm not sure.

When doing the database upgrade, on top it stated that it was making use of mysql 4 database.
However, we are running mysql 5.5.41.

As said, I just wanted to inform you of this.
Thank you for all the help!
User avatar
AmigoJack
Registered User
Posts: 5782
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Decrease database size?

Post by AmigoJack »

Blacktiger63 wrote:mysql 4 database
No, that's the abstraction layer you were and still are using, which is not bound to a DBMS version. Use mysqli instead:
[url=https://www.phpbb.com/community/viewtopic.php?p=13512826#p13512826]Re: How to update from MySQLI to MySQLI?[/url] wrote:Try changing $dbms = 'mysql'; to $dbms = 'mysqli'; in your config.php file
  • The worst thing about censorship is ███████████
  • "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." Affin, 2018-11-20
  • "But this shit is not here for you. You can follow with your. Maybe the question, instead, was for you, who know, so you shoved us how you are." axe70, 2020-10-10
Blacktiger63
Registered User
Posts: 55
Joined: Thu Oct 17, 2013 7:43 pm

Re: Decrease database size?

Post by Blacktiger63 »

Oke I will change that, thank you!
Locked

Return to “[3.0.x] Support Forum”