(How to) repair/optimize large Post table (1Gb), hang using admincp/phpmyadmin

[basketmen]

i can repaired and optimized all tables from admincp -> Maintanance with no problem, except the Post table, because its already have large size (1Gb)

so i try repair that single table alone :

try it from admincp -> Maintanance, Its stopped loading by its self in the middle only get Please wait..., no Done message at all, its just hanged

try it from phpmyadmin, get very high load in the middle, cannot opening the server or the whm/cpanel even already more than 30 minutes, so need to restart the server from leap (manual reboot by the host company)

so is there any way to repair and optimize it guys? or it is no need to repair/optimize for years?

Nb. I use a dedicated server, here is my server specs
Intel Single Xeon E3110 3.0ghz L2 cache 6MB
Memory 4 GB DDR2 RAM
Harddisk 500 GB Sata 7200rpm
Cent OS 5.4
WHM/Cpanel

vb 3.6

[Marco van Herwaarden]

Unless there is a reason to do so, it should not be needed.

Large tables can best be repaired/optimized from the command line.

[basketmen]

argh, after try repair from phpmyadmin the post table still cannot be used until now

and i got this error each time opening any thread MySQL Error : Table './username_dbname/post' is marked as crashed and should be repaired

the post table in phpmyadmin is saying in use









please help guys what should i do now so the forum back again

[snakes1100]

1. ssh into server
2. cd to mysql's data dir (or if you know your path)
3a. myisamchk -r /var/log/mysql/dbname/post.* (its possible at this point you may need to use the "-o" option)
3b. myisamchk -o /var/log/mysql/dbname/post.*
4. You may need to do a -r option after the -o run.

[Marco van Herwaarden]

Please note that even though the webinterface (AdminCP/phpMyAdmin) might time-out, the process repairing the tables might still be running in the background.

Also it will sometimes work to run the repair twice if you get an in-use message the first time.

[basketmen]

thanks snake1100 and Marco van Herwaarden

i already made it optimize/repair it using myisamchk command line from ssh, i recommend vbulletin or some one create a instruction for optimize/repair large database table (1Gb or more), since i dont found working instruction in vb.com or vb.org


so here is what i do exactly based from snake1100 guide and other resources, it is working for me, but please let me know if you have more advice from this steps, i believe this will be really helping many of us :



1. ssh into server



2. Stop the MySQL server (it is needed to stop the mysql server to do this right?)
/etc/rc.d/init.d/mysql stop




3. cd to mysql's data dir (or if you know your path) :
cd /var/lib/mysql/database_name/




4a.
myisamchk -o /var/lib/mysql/database_name/post.* --> i try this, but its say not working, get message like this "Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag"

so i add -f and its working to repair the post table in 5 hours :
myisamchk -o -f /var/lib/mysql/database_name/post.*




4b.
myisamchk -r /var/lib/mysql/database_name/post.* (its possible at this point you may need to use the "-o" option) --> I am still didnt doing this, since step 4a is already working, do i still need do this step guys?






5. Restart MySQL again:
/etc/rc.d/init.d/mysql start

You may need to do a -r option after the -o run.

--> Btw i am still not sure what snake1100 mean, is it mean after run the 4a step above, i need to run step 4b?


like you see guys, i own a big boards using vb now, but still dont get the right/official instruction, i search it outside vb




other sources
http://www3.wiredgorilla.com/content/view/347/58/
http://dev.mysql.com/doc/refman/5.1/...am-repair.html

http://wpguru.co.uk/2010/01/how-to-r...ommand-line-2/ --> i try this first but its not working, the server down in the middle of proccess and the ssh canceled by its self, the myisamchk command above is working, but maybe need more advice to be more perfect

--------------- Added [DATE]1286242782[/DATE] at [TIME]1286242782[/TIME] ---------------

Originally Posted by Marco van Herwaarden

Please note that even though the webinterface (AdminCP/phpMyAdmin) might time-out, the process repairing the tables might still be running in the background.

Also it will sometimes work to run the repair twice if you get an in-use message the first time.

i dont think its still running repairing using AdminCP/phpMyAdmin, the load number in whm is very high until 200-400 and cannot opening anything again including whm (normally the server load are only 1-5 in whm), when running myisamchk above the load only about 20 so the whm still can accessed

i am still didnt try repair/optimize again from AdminCP/phpMyAdmin when the table have an in use message, but i think it will same

[Marco van Herwaarden]

The manual ( Common MySQL Error Messages) will link you to a search on mysql.com listing the various repair options for the different MySQL releases and tabletypes.

[basketmen]

Originally Posted by Marco van Herwaarden

The manual ( Common MySQL Error Messages) will link you to a search on mysql.com listing the various repair options for the different MySQL releases and tabletypes.

nice link, its not bad, its only still hard enough to search in mysql.com which one tutorial that the most suitable for large database, and recommended by vb official or members to use


btw if you can please check my post no.6 above, if you have more advice please let me know

[Lynne]

Basketmen, have you posted over on vb.com in the Server Configuration forum? George (eva2000) may have some good suggestions on what you should do.

[basketmen]

Originally Posted by Lynne

Basketmen, have you posted over on vb.com in the Server Configuration forum? George (eva2000) may have some good suggestions on what you should do.

ok dokey