Making the Database as Small as Possible

[Digital Jedi]

Hey gang. I'm trying to make the database backup of our forum smaller. I know we need to prune some old users and threads. But I was wondering what other ways there are that I might be overlooking.

I know I read somewhere about a way to purge the signature data, but I cannot find it. I was also wondering if avatar info was part of that and, if so, if there's a way to clear that out too.

Any links, suggestions, ideas would be appreciated.

[Lynne]

Have you already made sure that your attachments, avatars and user profile pictures are all in the file system, not the database?

[Digital Jedi]

Yup, they are all in the file system. I think there are maybe some custom avatars that aren't, but those are very few.

To be honest, keeping signature, attachment and avatar information is not a high priority. We had to change the file structure anyway, so most everyone was going to have to choose a new avatar. We don't use attachments. And signatures have gotten a little out of hand. If there's a way to start from scratch, that would be beneficial for me on a couple of fronts.

[Lynne]

When you say start from scratch, you mean delete all avatars and signatures? Or do you mean start your whole forum over from scratch?

[Digital Jedi]

I want to keep posts and threads and such. Hence the reason for the backup. Extraneous things like signatures and avatar settings and things like that are not important. Anything to make the backup smaller, and by extension make transferring it to the new server not take as long.

I did do a mass pruning of some older obsolete threads and inactive posters. But is there anything else I didn't think of? I seem to remember someone saying you could purge signature data, but I can't seem to find it. Will purging some account settings make a difference/even possible?

[snakes1100]

How big is the DB after dumping?

Xfer'ing a DB over shouldn't be that bad thru ftp, are you using --opt as well when dumping it and then compressing it for xfer to the new server?

[Digital Jedi]

Server related maintenance isn't really my forte. The fellow who donates his server space is the one handling transferring the backup. He simply requested that I do what I can to make the backup smaller then it was when we did our test run. I think the backup was somewhere in the neighborhood of 56 MB, but that's after it's been compressed. He said it took over 10 hours, and since I know his time is limited, I wanted to make it easier on him.

[momo2]

How do i make my made my attachments, avatars and user profile pictures are all in the file system, not the database?

[snakes1100]

Server related maintenance isn't really my forte. The fellow who donates his server space is the one handling transferring the backup. He simply requested that I do what I can to make the backup smaller then it was when we did our test run. I think the backup was somewhere in the neighborhood of 56 MB, but that's after it's been compressed. He said it took over 10 hours, and since I know his time is limited, I wanted to make it easier on him.

10 hrs for a 56mb ds xfer to another server, i think there is something else going on with the FTp connections on one of the servers, that should be maybe a 5 minute xfer from server to server.

I wouldn't waste your time compressing it any further, the DB isnt the issue.

How do i make my made my attachments, avatars and user profile pictures are all in the file system, not the database?

In the admincp under each option in the left menu, attachments --> attachment storage type, do the same for the others you want to move.

[momo2]

please explain more:
/path/to/your/attachment/directory
now as an example give me a path ? outside the forum folder or inside ? and for avatars also

[Dismounted]

You need to enter the FULL PATH to the newly created directory.
eg. /home/dismount/vbattach

[Digital Jedi]

10 hrs for a 56mb ds xfer to another server, i think there is something else going on with the FTp connections on one of the servers, that should be maybe a 5 minute xfer from server to server.

I wouldn't waste your time compressing it any further, the DB isnt the issue.

Well, 56m is the file size compressed (sql.gz). I don't know too much about how Impex works, but I was under the impression that we were dealing with SSH access, not FTP access. This is an area that he works in for a living, so maybe I'm just conveying what he said to me improperly.

Suffice to say, I was asked to trim it down some, so I'd like to explore all my options. I also would like to figure out how to purge everyone's signatures, because I'm going to institute new signature rules and want everyone to start from scratch. Does anyone know how to do that?

[snakes1100]

Apparently this person doesnt know what they are doing in either case.

I would get someone else to do what you need done, impex is an easy click and go interface, xfering a 56mb db from one server to another is just a simple cmd as well in ssh.

[Marco van Herwaarden]

You don't need ImpEx to transfer servers.

For instructions see the manual Moving Servers (http://www.vbulletin.com/docs/html/moving_servers)

[Digital Jedi]

I appreciate the info and I realize that everyone is just trying to help. Thank you for that. But I may have given too much information when I mentioned the reason why we were doing it. There are other reasons, which I've mentioned, that I want to make the database smaller. I'm not the one moving the servers, nor am I the one with any practical knowledge of how to do that or even the proper access. I intend on learning, but in the meantime, my only concern and question right now is how to make the database smaller.

Snake, the person doing the transfer is the person who owns the new server, so there is no one else to get to do that. But I assure you, he knows what he's doing, since this is part and parcel of his profession. I'm the one who doesn't know what he's doing, so I'm likely not conveying the information I have accuarately. In fact, I guarantee it. That's probably why we shouldn't focus on what I mentioned about moving servers (since it's likely inaccurate), and just focus on the part about making the database smaller. That's what I primarily would like to know. (Like purging signatures, I'm really interested in finding out if that's possible) The server transfer stuff, while greatly appreciated, is part of another lesson for me, for another time.

[Marco van Herwaarden]

Moving attachments etc out to the filesystem will make the database smaller.

Pruning signatures etc. will most likely hardly make a difference.

[Paul M]

Removing signatures is a simple sql command

Update usertextfield Set signature = " ";

It will make little difference to the size of your database, which is frankly quite small already.

As for 56M taking 10 hours - that's a joke - it's about the speed of an ancient 11K modem.

For a server to server transfer you should be talking a couple of minutes at most.

[Digital Jedi]

Thank you, Paul, and Marco. Like I said, I had other reasons for purging signatures, other then making the backup smaller. Sigs have been getting a little out of hand lately, and this will help start everyone from square one with some new sig rules in place.

I probably shouldn't have mentioned the transfer thing, because I'm pretty sure I don't know what I'm talking about. All that information is stuff I "put together" from conversations I've had via email, rather then actual firsthand information. I think the backup was about 56M, but I'm not entirely certain. Our forum has been around for nearly 5 years, so there has to be some size issues going on.

Thank you again, and other then moving/deleting attachments (which I'm pretty sure are not in the database, but I'll check), am I correct in assuming that's the extent of trimming things down?

[Dismounted]

If your forum has been up for 5 years, I'd expect the database to be more than 56MB.

[pheroman]

also you can remove all pm's this will keep your database more small by insert this sql
DELETE FROM pm;
DELETE FROM pmreceipt;
DELETE FROM pmtext;
and after this insert this
UPDATE `user` SET `pmtotal` = 0, `pmunread` = 0;
soory my english is poor :)