very large database file

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
zaki
Registered User
Posts: 32
Joined: Thu Mar 06, 2003 10:52 am

very large database file

Post by zaki » Thu Apr 24, 2003 6:45 pm

hello,

I installed phpBB 2 months ago, when it reached around 1000 post the size of MySql database file was 1Mbyte, now I have 2300 post but the size of Database is 13 MByte !!!

This is very scarry, because I have a limited space of 120 Mbyte.

Is there a way to solve this without activating Prunning ?

I tried the Gzip, but it seems that my server's safe mode is not compatible with it.

Thank you for your help.

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: very large database file

Post by drathbun » Thu Apr 24, 2003 7:45 pm

zaki wrote: I installed phpBB 2 months ago, when it reached around 1000 post the size of MySql database file was 1Mbyte, now I have 2300 post but the size of Database is 13 MByte !!!

I have 50K+ posts and my database is 100MB.
I tried the Gzip, but it seems that my server's safe mode is not compatible with it.

GZIP doesn't do anything for disk usage. It compresses the web page before it is sent to the user, and then their browser uncompresses it. So it helps with bandwidth usage, but not disk space.

Dave
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

cmi
Registered User
Posts: 7
Joined: Sat Oct 12, 2002 8:01 pm
Location: germany

Post by cmi » Thu Apr 24, 2003 7:51 pm

maybe there are just some really huge postings on your post? any mods, tons of inactive users?

Callisto
Registered User
Posts: 1578
Joined: Sun Jan 12, 2003 8:20 pm
Location: UK
Contact:

Post by Callisto » Thu Apr 24, 2003 8:16 pm

Do an optmise on the tables (using something like phpmyadmin) as that will get the size down a bit.

zaki
Registered User
Posts: 32
Joined: Thu Mar 06, 2003 10:52 am

Re: very large database file

Post by zaki » Thu Apr 24, 2003 8:21 pm

drathbun wrote:
zaki wrote:I installed phpBB 2 months ago, when it reached around 1000 post the size of MySql database file was 1Mbyte, now I have 2300 post but the size of Database is 13 MByte !!!

I have 50K+ posts and my database is 100MB.


!!!!!!!! :!: :!: :!: :!: :!:

What exactly is happening in my forum then :!:

I have 101 users, 2100 posts, my database only bacup is 13.7MB.
I tried the Gzip, but it seems that my server's safe mode is not compatible with it.

GZIP doesn't do anything for disk usage. It compresses the web page before it is sent to the user, and then their browser uncompresses it. So it helps with bandwidth usage, but not disk space.

Dave


So what exactly is happening here ?

how do I see which field in the DB that is increasing so fast ? using phpmyadmin I guess, but how ? any ideas ?

User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Post by A_Jelly_Doughnut » Thu Apr 24, 2003 9:07 pm

Check the tables sizes in the far right column when you select your DB in phpMyAdmin
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish

zaki
Registered User
Posts: 32
Joined: Thu Mar 06, 2003 10:52 am

Post by zaki » Thu Apr 24, 2003 9:15 pm

A_Jelly_Doughnut wrote: Check the tables sizes in the far right column when you select your DB in phpMyAdmin


here's what I have, Ko is KB, can u see anything strange ?

I have the dictionary (English) installed, and some other mods.

active 0 MyISAM 1,0 Ko
attachments 0 MyISAM 1,0 Ko
avatar 1 MyISAM 2,0 Ko
cache 1 MyISAM 1,1 Ko
configuration 1 MyISAM 8,5 Ko
designs 1 MyISAM 1,0 Ko
favorites 0 MyISAM 1,0 Ko
forum_display 2 MyISAM 5,1 Ko
forum_permissions 0 MyISAM 1,0 Ko
ipban 0 MyISAM 1,0 Ko
moderators 0 MyISAM 1,0 Ko
navigations 6 MyISAM 1,4 Ko
phpbb_auth_access 20 MyISAM 3,4 Ko
phpbb_banlist 2 MyISAM 3,1 Ko
phpbb_categories 3 MyISAM 3,1 Ko
phpbb_chatbox 0 MyISAM 4,5 Ko
phpbb_chatbox_session 0 MyISAM 2,1 Ko
phpbb_config 80 MyISAM 5,3 Ko
phpbb_disallow 1 MyISAM 2,0 Ko
phpbb_forum_prune 0 MyISAM 1,0 Ko
phpbb_forums 15 MyISAM 7,6 Ko
phpbb_groups 104 MyISAM 5,5 Ko
phpbb_lottery 2 MyISAM 3,2 Ko
phpbb_posts 2 090 MyISAM 159,0 Ko
phpbb_posts_text 2 090 MyISAM 3,3 Mo
phpbb_privmsgs 265 MyISAM 24,2 Ko
phpbb_privmsgs_text 265 MyISAM 91,2 Ko
phpbb_ranks 10 MyISAM 2,4 Ko
phpbb_search_results 1 MyISAM 8,4 Ko
phpbb_search_wordlist 14 916 MyISAM 634,5 Ko
phpbb_search_wordmatch 84 844 MyISAM 2,0 Mo
phpbb_sessions 7 MyISAM 32,2 Ko
phpbb_smilies 73 MyISAM 4,5 Ko
phpbb_spelling_words 192 931 MyISAM 9,2 Mo
phpbb_themes 1 MyISAM 2,3 Ko
phpbb_themes_name 1 MyISAM 3,5 Ko
phpbb_topics 393 MyISAM 56,9 Ko
phpbb_topics_watch 406 MyISAM 16,2 Ko
phpbb_user_group 104 MyISAM 3,8 Ko
phpbb_users 102 MyISAM 26,2 Ko
phpbb_vote_desc 8 MyISAM 3,5 Ko
phpbb_vote_results 21 MyISAM 3,8 Ko
phpbb_vote_voters 38 MyISAM 4,6 Ko
phpbb_words 3 MyISAM 2,6 Ko
pmsg 0 MyISAM 1,0 Ko
polls 0 MyISAM 1,0 Ko
posts 0 MyISAM 1,0 Ko
profiles 2 MyISAM 6,2 Ko
smileysets 1 MyISAM 1,0 Ko
smilies 8 MyISAM 3,2 Ko
templates 185 MyISAM 491,2 Ko
topicicons 1 MyISAM 1,0 Ko
topics 0 MyISAM 1,0 Ko
usergroup 4 MyISAM 2,3 Ko
usertitles 3 MyISAM 1,1 Ko
vars 29 MyISAM 1,7 Ko

total 56 table(s) 299 041 -- 16,1 Mo

User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Post by A_Jelly_Doughnut » Thu Apr 24, 2003 9:45 pm

The phpbb_spelling_words table is over 9 Megs itself. Or more than half of the DB size.
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish

zaki
Registered User
Posts: 32
Joined: Thu Mar 06, 2003 10:52 am

Post by zaki » Thu Apr 24, 2003 10:32 pm

A_Jelly_Doughnut wrote: The phpbb_spelling_words table is over 9 Megs itself. Or more than half of the DB size.



I installed the spelling mod the 12/3, and all backups I made afterward were less than 1 megabyte, suddenly it jumpes to 13 Megabytes

I am sure of this, my last backup (full) was the 4/4, and was 540 kb!!

User avatar
chotgor1
Registered User
Posts: 12
Joined: Fri Nov 08, 2002 9:26 am
Contact:

Post by chotgor1 » Thu Apr 24, 2003 11:35 pm

hi, i have very large database too. I have over 100K posts, if i didnt delete anything it would have been 180K posts by now, and the database filesize is 115MB.

It seems to be that 40% of the total phpbb database filesize is for 2-3 phpbb_search tables. Those tables hold bunch of junkie word combinations that used for search.php. When you empty those tables the users wont be able to make any search on your forum, the search results will be "nothing found". But the forums, topics, and posts are still there, you can navigate and read them. Plus after you emptied those search tables phpbb will start building it again with new posts, so your users will be able to search in the new posts again pretty soon.

So if you think its applicable to you, this is a good way to reduce your database size by almost 30-40%.

In my case emptying these search tables is mandatory every once in a while, becoz with my huge database, the search.php just totally knoks out my server resources. I just realized that, this phpbb.com/phpBB is actually restricting me to make multiple searches, 80% of the time when i try to make search, it gave me 404 error; maybe its a good way to keep the server resources safe.

Locked

Return to “2.0.x Support Forum”