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.
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
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...