i have a big problem (1.7gb mysql file) how to optimize such a file?

[ep3blue]

my host siteground shut down my database saying it's slowing down their servers. Reading on some posts here I think this is B.S. on siteground's part. I declined their VPS hosting since I cannot afford it. They gave me a copy of my database so i could optimize it into a smaller file or something and they'll just turn it back on once they review the size. Honestly I'm pretty much daunted by the task ahead of me. I'm also doubtful why my database grew to such proportions. I only have 900 users with at least 80,000 posts. Could it be they injected stupid stuff in my database? How can I suddenly jump up to 1.7gb? Normally i have less than 50mb of backup.

My question is: How do i optimize my database into something more manageable? What tools do i need? I can't use phpmyadmin since they (siteground) won't allow me.

Any help would be appreciated!!!! Thank you sirs! 

[djshank]

900 users and 80,000 posts... why aren't you on at least VPS, again?

[Compuart]

Can you check the size of each table? Maybe some error caused log_errors to grow to a huge size for instance.

[ep3blue]

Can you check the size of each table? Maybe some error caused log_errors to grow to a huge size for instance.

thank you for replying, how do i open a big file such as this? all my text file readers hang when i open it. can i split a file such as this?

900 users and 80,000 posts... why aren't you on at least VPS, again?

my past forum had more people and more posts with no problems. 

[Ben_S]

The size of a MySQL dump (sql) file doesn't really represent the size a database will take up as an actual MySQL database, ask them to turn it back on (with access to the forum disabled?) so you can see what tables are large?

[Compuart]

A simple tool should be able to do the trick. Lemme write one. Attached.

(You need a web server though)

[ep3blue]

A simple tool should be able to do the trick. Lemme write one. Attached.

(You need a web server though)

ok cool!!! i'll upload it to my host.

edit: how do i use it? it says:
Warning: fopen(./x.sql): failed to open stream: No such file or directory in /home/drpforum/public_html/ep3/file_split.php on line 7
unable to open file

btw thanks ben s, i'll contact siteground support and ask them to turn it on.

[Compuart]

(oh and of course change the $source_file variable to whatever the sql file is called)

[ep3blue]

(oh and of course change the $source_file variable to whatever the sql file is called)

LOL, i tried it without changing the variable.

[CountryLady]

ep3blue, I speak from personal experience when I say that what Siteground told you is their regular old song and dance routine. They used to be a good hosting company, then about the first of this year, all that changed, and they became the host from hades.

I got away from them before I finished up my paid contract because of their $%^&*~!@. You'll be wise to do the same. It will ALWAYS be SOMETHING.

Good Luck~!

[ep3blue]

ep3blue, I speak from personal experience when I say that what Siteground told you is their regular old song and dance routine. They used to be a good hosting company, then about the first of this year, all that changed, and they became the host from hades.

I got away from them before I finished up my paid contract because of their $%^&*~!@. You'll be wise to do the same. It will ALWAYS be SOMETHING.

Good Luck~!

thanks country lady! i feel i will need to jump ship as soon as financially possible. thanks.

[ep3blue]

Can you check the size of each table? Maybe some error caused log_errors to grow to a huge size for instance.

i can't seem to find the errors. is there a specific value i should look for?

EDIT: i found the smf_log_errors table with 1.5gb worth of crap when i dumped the database. i emptied the contents from phpmyadmin but i still need to dump 49 more tables into the database (i have joomla and smf bridged).

[Ben_S]

Just emptying log_errors should be fine and bring the database down to a sensible size.

[ep3blue]

i tried emptying the log_errors but even with a split file it's still huge. how do i open these large files? i'm using "viewer" and "large file viewer" programs and they hang even when i just try to go to the line where the log errors are. what programs do you recommend? any help is greatly appreciated. thanks!

[青山 素子]

I find ConTEXT usually works for very large files, but it will be slow (just because the file is so large).

[ep3blue]

I find ConTEXT [nofollow] usually works for very large files, but it will be slow (just because the file is so large).

ok cool! i'll give it a try! thanks!

[ep3blue]

update: My site is up and running!!! You can check it out at http://forums.tqpi.net [nofollow]

I'm so happy! Thanks to all!!!!