UBB.threads PHP Forum Software Community Forums Resources and Customizing How Do I? How do I decrease my data base? ASAP!

I am needing to reduce the size of my enormous data base from 3.8 gig down to under 1 gig. ASAP!
We have thousands of ancient threads that we need to delete. Is there any quick and easy way to do this? For example, I was thinking that I would delete everything prior to 2010. How would I do this?

Any Ideas?

Louise
815-337-8000

Well if you just want to delete posts.
Depending on what version you are using:
Control Panel » Prune Topics
Elect the options you want and delete away.
There is a date option.
It may take a while!

But this method does not give you the option to undelete.

Why delete content; if you have old content that's an search engine goldmine for incoming traffic... I was absolutely hate to get rid of all of that especially if you run an online service like you do.

You could delete it, or you could mirror the db then prune the old posts off of the main forum and have a separate archive forum.

Just semi curious of the 1gb limit...

As Ruben stated, the prune tool would be the proper method of pruning old content, even if you hired someone to do it for you it'd be the same method.

Thanks so much for your replies. I appreciate the help.

Where do you host a website with a data base of almost 4 gig?

SIMPLE SOLUTION 1:
You could purge only from your "off-topic" style forums to 1yr or 6mo or less, keeping the meat of your general topics in tact.

SIMPLE SOLUTION 2:
Your database tables may also have an excess amount of "overhead" (read HERE and HERE) that can be reduced, and could significantly lower the overall disk space your database uses.

1) Close your forums.
2) Make a backup of all your forum tables - or your entire forum database.
3) "Optimize tables" or "Repair tables" from either command line SSH or from phpMyAdmin (if available)
From phpMyAdmin:
a. from the server web interface
b. open the table for your forum
c. at the bottom of the table list, click "Check All"
d. next to that, from the drop-down list, select "Optimize table"
e. wait. wait some more. keep waiting until the process is done.
when complete, you'll hopefully see a green section at the top of the page that says, "Your SQL query has been executed successfully."

Now review the disk space that your database tables are utilizing, to confirm the space has been freed; Click on your database name from the top of the page to view the list of tables again. The total size of all your tables will be shown at the end/bottom of the list, on the right.

4) Reopen your forums.


These simple solutions should provide some breathing room for your disks.

OK. Thanks. Simple Solution 2 sounds like it may be quite helpful - at lease a good start. Any downside to this approach? Anything I need to be aware of before trying it this weekend?

just make sure you have enough disk space for the rebuild of your database to complete correctly. always make a backup before touching your database in this way. close your forums before you do it. dont forget to reopen your forums afterwards.

overall, running the optimization is quite simple. you can find a lot of information on what the procedure does by searching google for "mysql overhead" and "phpmyadmin optimize tables" -- most of those Google results should be very human-readable, and not require a large technical vocabulary to understand.

I agree if you want to optimize your db in lieu of deleting posts.
And making a backup prior is also important.
Either way you go.
Just bear in mind that the easy way backups normally will fail with a large database.
such as phpmyadmin or the built in backup with UBB.
Especially the posts and topics tables

phpMyAdmin export development has come a long way with PHP5.3/5.5's php.ini having the default memory limit set to 128M, rather than the old days of PHP4's 8M limit defaults and handling there-of.

But I'm fully with you on that, Ruben. In 2002/2005, I was burned by it so many times. Then I learned how to do these things through SSH

EDIT: My several ~ 500MB/1GB DBs, I havent had problems in the past 5 years. I still always do backups before-hand when performing this action. It's just good procedure to do so.

if none of the above quick-solutions will work for your situation, i have good news and bad news for you:

Your data has out-grown any "minimal" shared-hosting plan

Just looking at their forums I'd recommend they be on a VPS and not a Shared Host; a VPS would give them dedicated resources to do as they see fit and is always the "next step" for popular forums that outgrow a Shared Host. I can provide several options that I've used in the past if interested.

Oh, BTW; there are several HUGE UBB Forums, such as BellaOnline; so there are options out there regardless of how large your site gets.

For anyone else reading this topic looking for a similar solution, I believe that it is important to emphasize and include within this discussion, a quote from the topic I previously linked to:

SOURCE: http://forums.phpfreaks.com/topic/282841-can-somebody-tell-me-what-overhead-is-in-my-sql-database/


If disk space is not an issue, and your host is only limiting the max-size you may have per database, this may be the simple solution (short-term fix) you're looking for.