Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

Need help splitting my huge database into multiple tables (too big for shared hosting environment)

adrial79adrial79
edited April 2015 in Vanilla 2.0 - 2.8

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!

Comments

  • hgtonighthgtonight ∞ · New Moderator

    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?

  • R_JR_J Ex-Fanboy Munich Admin

    Maybe a OPTIMIZE TABLE GDN_ActivityComment/GDN_Activity is needed?
    https://dev.mysql.com/doc/refman/5.0/en/optimize-table.html

  • BleistivtBleistivt Moderator

    @adrial79 said:
    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?

    The plugin probably timed out because phps script execution limit.

    Try running the queries directly on your databse or running it multiple times.

  • 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())>2

    many host allow you to set up cron jobs.

    grep is your friend.

  • BleistivtBleistivt Moderator
    edited April 2015

    edit: answer above

  • Awesome. So copy and paste that directly into the mysql command line without changing anything?

  • @adrial79 said:
    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. :)

  • adrial79adrial79
    edited May 2015

    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?

  • whu606whu606 I'm not a SuperHero; I just like wearing tights... MVP
    edited May 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.

  • whu606whu606 I'm not a SuperHero; I just like wearing tights... MVP

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

Sign In or Register to comment.