Need help splitting my huge database into multiple tables (too big for shared hosting environment)
adrial79
✭
Hey all!
Godaddy says my GDN_Activity table is too huge (over 1GB) and I need to split it up into multiple tables to not violate their terms of service.
Is this something I could do myself, not being a database guy at all? (I'm a graphic designer.) 
And if it's too complicated, which of you awesome veterans can I hire to do it for me? 
Thanks!
0
Comments
I would suggest purging the database of old items. @x00 has written many good things about this in the past. Also, there is a plugin (http://vanillaforums.org/addon/activitypurge-plugin).
But really, you need to get off of GoDaddy.
Links from @x00:
http://vanillaforums.org/discussion/comment/195196/#Comment_195196
http://vanillaforums.org/discussion/comment/196086/#Comment_196086
Search first
Check out the Documentation! We are always looking for new content and pull requests.
Click on insightful, awesome, and funny reactions to thank community volunteers for their valuable posts.
Hey! Thanks for the suggestion!
However, I ran the activity purge plugin and it only took down the size of the database from 1.7GB to 1.6 ... What would be the best thing to do at this juncture?
Maybe a OPTIMIZE TABLE GDN_ActivityComment/GDN_Activity is needed?
https://dev.mysql.com/doc/refman/5.0/en/optimize-table.html
The plugin probably timed out because phps script execution limit.
Try running the queries directly on your databse or running it multiple times.
My themes: pure | minusbaseline - My plugins: CSSedit | HTMLedit | InfiniteScroll | BirthdayModule | [all] - PM me about customizations
VanillaSkins.com - Plugins, Themes and Graphics for Vanillaforums OS
Ah, okay. Thanks @Bleistivt!
Since I haven't done this before, I just want to make doubly sure I'm doing this correctly ...
So I'll go into phpmyadmin and run a query on the forum database that will be this?
00 3 * * * mysql -u username -p pass forumdb -e "DELETE FROM GDN_Activity WHERE ActivityTypeID NOT IN ( SELECT ActivityTypeID FROM GDN_ActivityType WHERE Name = 'WallComment' ) AND TIMESTAMPDIFF(MONTH,DateInserted,NOW())>2"And I will switch these out with my information, right?
username
pass
forumdb
Is that all? Thank you!
That is a cron job, and the command is using msql command directly not phpmyadmin
The query part is
DELETE FROM GDN_Activity WHERE ActivityTypeID NOT IN ( SELECT ActivityTypeID FROM GDN_ActivityType WHERE Name = 'WallComment' ) AND TIMESTAMPDIFF(MONTH,DateInserted,NOW())>2many host allow you to set up cron jobs.
grep is your friend.
edit: answer above
My themes: pure | minusbaseline - My plugins: CSSedit | HTMLedit | InfiniteScroll | BirthdayModule | [all] - PM me about customizations
VanillaSkins.com - Plugins, Themes and Graphics for Vanillaforums OS
Awesome. So copy and paste that directly into the mysql command line without changing anything?
That is just a single query it is not a cron job, it doesn’t prevent from growing again.
Read up on contab
http://en.wikipedia.org/wiki/Cron
Ask you host to help set one up.
grep is your friend.
Awesome! Thanks for your awesome help everyone!
So I ran the query directly in phpMyAdmin and it looks like it deleted 1,021,510 rows! Whew.
http://screencast.com/t/2Lj2T7w9
Now strange part is I go back to look at my database size in cPanel and it still hasn't gone down at all in size (1696.76 MB). Does it just take a moment to update those numbers?
Probably yes.
Like i said you want to run the query periodically, by setting up a crontab.
grep is your friend.
Yeah, got it. I'll set it up as a crontab, too. Thanks for that. I just needed to do this manually initially to try to bring my database size down so GoDaddy doesn't slap me around.
Hm, looks like the database is still 1.5gb even when I look at it through phpMyAdmin:
http://screencast.com/t/1B7qDQJUqv
Any other ideas on how to get this down further? Is the only other option to split it up into different databases? Like one database for 2012, another for 2013, 2014, 2015?
Have you tried optimizing and/or repairing the table?
You can do this via phpMyAdmin.
I did check the table and the results said it was a-okay, but I haven't tried optimizing it. I'll try that now! Thanks!
Oh wow, that was like magic. Leave it to a simple suggestion to completely WOW a noob.
1.5 gb down to 3.4 mb in a few seconds ... Yay! Thank you SO MUCH! You made my day, sir.
You're welcome, and glad it helped.
The DB would have been reduced to that size by your deleting the rows, it was just the figure being reported that needed updating (I think...)