How to calculate the database size taken by discussion threads or subforums?

Get help with installation and running phpBB 3.1.x here. Please do not post bug reports, feature requests, or extension related questions here.
Suggested Hosts
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: phpBB 3.1.x is at its End of Life stage and support will NOT be provided after July 1st, 2018.
Locked
oliverfr
Registered User
Posts: 58
Joined: Thu Jul 14, 2011 12:38 am

How to calculate the database size taken by discussion threads or subforums?

Post by oliverfr » Sun Mar 18, 2018 8:36 pm

Hello!

Long story short, I would like to ask you guys if there is a way to calculate (with phpmyadmin or shell command line tools: bash or sql) how much database size is taken in the database
- by each subforum
- by each discussion thread (if that is doable, or course, only caring about the larger ones)

Some context, perhaps:
With almost the same total number of threads apparently, the database size taken by a forum that I host has tripled in a few months. I imagine there must be a thread or a subforum receiving tons of updates of the spammy kind, and I wouldn't care if we weren't now with a database size in the gigabytes.
So, I want to track the culprit(s), either by subforum, and/or by thread (if that can be done, naturally).
What I can see, in my phpmyadmin, is that the phpbb3_posts is THE bloated table. It's not a matter of optimization (already done), simply, it's that its contents have grown massively in numbers.

And of course, this is why I come to humbly ask for help, I regret to say that getting a working mysql query to refine results, calculate sizes, and output results, this is is WAY beyond my basical skill :(
Sure, I can tell subforum is determined by forum_id while discussion thread is determined by topic_id, but that leads us nowhere ^^

Stackoverflow gave me a way to calculate data occupation, [ round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` ]... but how could that be combined with filtering for a phpbb forum, I have no idea.

How to combine it with a sort of [ select * from database_name where forum_id like each_subforum_id_ran_one_query_after_another ] ? Beats me.
Also beating me: how to combine it with a sort of [select * from phpbb3_posts; order by topic_id ]

I apologize to ask someone else to do the work for me, really.
But, please, would someone know if there's a way to obtain results, that would allow to tell "this is THAT subforum that takes all the place", and "these are THIS topic, THIS topic and THIS other one that are the main culprits"?

Really, if someone can help, THANK YOU!!

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

Re: How to calculate the database size taken by discussion threads or subforums?

Post by stevemaury » Sun Mar 18, 2018 9:54 pm

I imagine there must be a thread or a subforum receiving tons of updates of the spammy kind
How could that happen without your knowledge?

How about a link to your board?
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: 66479
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: How to calculate the database size taken by discussion threads or subforums?

Post by Lumpy Burgertushie » Sun Mar 18, 2018 10:55 pm

using phpmyadmin or whatever database management tool your host provides, go to your database, look at the tables. there will be a total size for each table listed. there are also some stats on the main page of the admin panel that may help you narrow it down some.

or, there is always just looking at the main page of your board and see which forums/topics etc. have the most posts.
then you can prune as you like from the admin panel.
I do NOT suggest you try to solve this problem directly in the database. you already stated that your sql skills are limited and it would be very easy to make a mistake that causes you a lot bigger problem than you have now.


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
warmweer
Registered User
Posts: 2362
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: How to calculate the database size taken by discussion threads or subforums?

Post by warmweer » Sun Mar 18, 2018 10:57 pm

AFAIK, there's no way to calculate the exact actual size of a single, specific row in MySQL.
An acceptable approximation for the space taken in by a topic or a subforum would be to look at the table size (posts_table), divide that by the number of records, and multiply that "record size" by the number of rows (posts) in a specific topic/subforum.

BTW there are topics, no threads in phpBB ;)
My board's not broken, it just went peculiar

oliverfr
Registered User
Posts: 58
Joined: Thu Jul 14, 2011 12:38 am

Re: How to calculate the database size taken by discussion threads or subforums?

Post by oliverfr » Sun Mar 18, 2018 11:38 pm

Thank you for your replies :)

I quote-reply in the order the replies were made.
stevemaury wrote:
Sun Mar 18, 2018 9:54 pm
I imagine there must be a thread or a subforum receiving tons of updates of the spammy kind
How could that happen without your knowledge?
Basically, I'm hosting it for a community of friends, I'm not caring about it as if it were my creation, my life's occupation or anything. I dislike to observe the growing volume occupation from the backups. And now it's gotten my attention, it's true, I'd like to fix it finally. Call it a challenge, call it learning (or more accurately save it in my .txt with mysql commands that might be of good use in the future), call it trying to keep things healthy.

Lumpy Burgertushie wrote:
Sun Mar 18, 2018 10:55 pm
using phpmyadmin or whatever database management tool your host provides, go to your database, look at the tables. there will be a total size for each table listed. there are also some stats on the main page of the admin panel that may help you narrow it down some.

or, there is always just looking at the main page of your board and see which forums/topics etc. have the most posts.
then you can prune as you like from the admin panel.
I do NOT suggest you try to solve this problem directly in the database. you already stated that your sql skills are limited and it would be very easy to make a mistake that causes you a lot bigger problem than you have now.
robert
Well, Robert, I know already which table is the one that has gotten fat, phpmyadmin is already telling, indeed, but it's not precise enough ;)

I cast a quick look at the index of the forum and opened subforums, there has been no visible / noticeable surge in the number of total discussion threads, so my only guess is that it stems more from a number of replies inside discussions, or maybe even not that many replies, but perhaps packed to the brim with large quantities of text. I have no idea about the number of posts in total, though, most I remember a bit is the number of discussions. At this point, guessing won't help, it's got to be additions from the database, haha.

Also, backups are common practice, it's a pair of clicks in my panel (virtualmin) so I don't mind screwing over the forum into oblivion, at worst I'll restore the backup I made before I run sql commands. I've wished I knew how to calculate database occupation like that a few times in the past, it can always be a chance to learn ^^

warmweer wrote:
Sun Mar 18, 2018 10:57 pm
AFAIK, there's no way to calculate the exact actual size of a single, specific row in MySQL.
An acceptable approximation for the space taken in by a topic or a subforum would be to look at the table size (posts_table), divide that by the number of records, and multiply that "record size" by the number of rows (posts) in a specific topic/subforum.

BTW there are topics, no threads in phpBB ;)
Ouch. This is precisely what I am dreaming of.
A sort of selective [ calculate total characters in a select * from phpbb3_posts where forum_id like "manually chosen number from the current IDs" ] ,
or [ calculate, order by size and print total size of: quantity of text in each successive select * from phpbb3_posts where topic_id = number increased by 1 every time ].

I don't know where is "hidden" the additional space, probably in replies buried somewhere, so averages wouldn't help much here.

Also: BTW there are topics, no threads in phpBB
Oops! :shock:
I was going along with "subforum, containing threads, in which we reply with posts"
OK, I was wrong, sorry about that, duly noted, thank you for that too :)

*

Well, still, thank you for your replies! :)

An idea occured to me, since I have backups, I could also "duplicate" the forum in some private subfolder with minimal changes (I'll google if homeurl or the equivalent must be changed, as in wordpress) and play around with the pruning tool, nuking entire subforums and checking the results in total table size...

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

Re: How to calculate the database size taken by discussion threads or subforums?

Post by Lumpy Burgertushie » Mon Mar 19, 2018 12:00 am

how about a link to the board in question. we could probably tell you exactly what you want to know in just a few minutes if we could see the board.
( by doing the same thing you could do ;) )

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?

oliverfr
Registered User
Posts: 58
Joined: Thu Jul 14, 2011 12:38 am

Re: How to calculate the database size taken by discussion threads or subforums?

Post by oliverfr » Wed Mar 28, 2018 9:43 pm

About that, I really apologize for the complication, but I don't think I can share the forum's URL. Not my own (I have spare server space and resources to offer, but, still, not mine to share). I know, it doesn't help, but I gotta deal with it :D

Ah well, at worst, I'll let that forum like that, even if this is unhealthy. Thank you for your time you guys, and sorry I couldn't help better with better info!

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

Re: How to calculate the database size taken by discussion threads or subforums?

Post by Lumpy Burgertushie » Wed Mar 28, 2018 11:54 pm

strange. I thought the whole purpose of a web based bulletin board was so that people could discuss things. usually that includes making it available to the people so they can use it/find it etc.

I do understand that there are certainly private boards out there and I guess this must be one of them.


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?

oliverfr
Registered User
Posts: 58
Joined: Thu Jul 14, 2011 12:38 am

Re: How to calculate the database size taken by discussion threads or subforums?

Post by oliverfr » Thu Mar 29, 2018 1:38 pm

Yeah, so many types of links that can't be shared... You'll have community only boards, adult boards, fantasy-sharing boards, fanfic boards, piracy boards, embarrassing questions boards, and god knows what else.
As I'm hosting stuff for me and for a few people, when I ask for support on forums, I don't want to act as the "missing link" that would eventually allow to tell who is hosted on the same machine as who because their admins happen to be friends or family parents. Simply, it's not up to me to tell. That won't be the first time it complicates things for me ^^

Locked

Return to “[3.1.x] Support Forum”