@georgef - are there any specific tables that you keep finding corrupting?
I used to have a lot of issues with the options table until I made a quick change to WP and it (touch wood) hasn't happened again since.
is there a way that I can manually combine all the tables into a new db
It will take a few minutes, but basically:
1. back up all the databases to sql files
2. create a new db
3. run all of the backed up sql files on the new db.
4. set wp-config.php to point to the new db
5. remove the multi-db db.php file
What if I want to rescale to 256 db's?
Once you are in a single db - you could set up the new 256 db's and then use the move script to redistribute them back out to the new 256
The data bases thatmlmb_wpmuglob and thatmlmb_wpmuf was causing load on the server. Which made the server to respond slow. Please check these data base and tweak the settings accordingly with the help of a developer so that it uses less resources.
Here we copy the log from the server.
=========================================
mysqld 21149 mysql 96u REG 253,0 1840 3802268 /var/lib/mysql/thatmlmb_wpmuc/wp_1_myrp_categories.MYD
mysqld 21149 mysql 97u REG 253,0 11264 2430399 /var/lib/mysql/thatmlmb_wpmu5/wp_1954_terms.MYI
mysqld 21149 mysql 99u REG 253,0 1984 2430373 /var/lib/mysql/thatmlmb_wpmu5/wp_1954_terms.MYD
mysqld 21149 mysql 100u REG 253,0 11564704 3802322 /var/lib/mysql/thatmlmb_wpmuc/wp_1_posts.MYD
mysqld 21149 mysql 101u REG 253,0 3304 2654357 /var/lib/mysql/thatmlmb_wpmuglob/wp_mass_mailer.MYD
mysqld 21149 mysql 102u REG 253,0 273120 3802274 /var/lib/mysql/thatmlmb_wpmuc/wp_1_myrp_ratings.MYD
mysqld 21149 mysql 103u REG 253,0 140184 2654461 /var/lib/mysql/thatmlmb_wpmuglob/wp_sitemeta.MYD
mysqld 21149 mysql 104u REG 253,0 52948 3802388 /var/lib/mysql/thatmlmb_wpmuc/wp_1_term_taxonomy.MYD
mysqld 21149 mysql 105u REG 253,0 4914 2430367 /var/lib/mysql/thatmlmb_wpmu5/wp_1954_term_relationships.MYD
mysqld 21149 mysql 106u REG 253,0 11264 2430381 /var/lib/mysql/thatmlmb_wpmu5/wp_1954_options.MYI
mysqld 21149 mysql 107u unix 0xe81c4a80 7219110 /var/lib/mysql/mysql.sock
mysqld 21149 mysql 108u REG 253,0 40960 2654305 /var/lib/mysql/thatmlmb_wpmuglob/wp_bp_groups_groupmeta.MYI
mysqld 21149 mysql 109u REG 253,0 66800 2654279 /var/lib/mysql/thatmlmb_wpmuglob/wp_bp_groups_groupmeta.MYD
mysqld 21149 mysql 110u REG 253,0 66800 2654279 /var/lib/mysql/thatmlmb_wpmuglob/wp_bp_groups_groupmeta.MYD
mysqld 21149 mysql 111u REG 253,0 15632 2654288 /var/lib/mysql/thatmlmb_wpmuglob/wp_bp_groups.MYD
mysqld 21149 mysql 113u REG 253,0 14336 2430384 /var/lib/mysql/thatmlmb_wpmu5/wp_1954_postmeta.MYI
mysqld 21149 mysql 114u REG 253,0 8120 2430358 /var/lib/mysql/thatmlmb_wpmu5/wp_1954_postmeta.MYD
mysqld 21149 mysql 116u REG 253,0 176648 2430352 /var/lib/mysql/thatmlmb_wpmu5/wp_1954_options.MYD
mysqld 21149 mysql 117u REG 253,0 140184 2654461 /var/lib/mysql/thatmlmb_wpmuglob/wp_sitemeta.MYD
mysqld 21149 mysql 118u unix 0xf6035880 7218460 /var/lib/mysql/mysql.sock
mysqld 21149 mysql 119u REG 253,0 11564704 3802322 /var/lib/mysql/thatmlmb_wpmuc/wp_1_posts.MYD
mysqld 21149 mysql 121u REG 253,0 66104 3802391 /var/lib/mysql/thatmlmb_wpmuc/wp_1_terms.MYD
mysqld 21149 mysql 122u REG 253,0 11564704 3802322 /var/lib/mysql/thatmlmb_wpmuc/wp_1_posts.MYD
mysqld 21149 mysql 123u REG 253,0 1640140 3802313 /var/lib/mysql/thatmlmb_wpmuc/wp_1_options.MYD
mysqld 21149 mysql 124u REG 253,0 3072 3706063 /var/lib/mysql/thatmlmb_mlmads/oiopub_config.MYI
mysqld 21149 mysql 125u REG 253,0 10660 3704817 /var/lib/mysql/thatmlmb_mlmads/oiopub_config.MYD
mysqld 21149 mysql 126u REG 253,0 1024 2654338 /var/lib/mysql/thatmlmb_wpmuglob/wp_bp_messages_notices.MYI
mysqld 21149 mysql 127u REG 253,0 0 2654309 /var/lib/mysql/thatmlmb_wpmuglob/wp_bp_messages_notices.MYD
mysqld 21149 mysql 130u REG 253,0 66104 3802391 /var/lib/mysql/thatmlmb_wpmuc/wp_1_terms.MYD
mysqld 21149 mysql 138u REG 253,0 326864 2654491 /var/lib/mysql/thatmlmb_wpmuglob/wp_users.MYD
mysqld 21149 mysql 140u REG 253,0 140184 2654461 /var/lib/mysql/thatmlmb_wpmuglob/wp_sitemeta.MYD
root@server [/home/thatmlmb/public_html]# w
09:30:45 up 4 days, 3:30, 1 user, load average: 16.96, 22.39, 16.71
Doesn't look like I'm going to find anyone real soon that can assist with this and not sure if I trust (after a few bad incidents) anyone from odesk, elance, etc.
6593 pointsLike some sort of WPMU DEV GodExceptionally helpfulLifetime member
Keeper of the Dark Chocolate
—
12th November 2010
#
. In emergency when the site goes down they always reply that they found a corrupt table and its fixed.
In that case, it;s probably an index problem for that table. A repair from phpmyadmin should fix that issue is that's what the problem is.
I also had Mike (@venturemaker) remove the wp_postarchive table that was 3.3gb.
I;m lost. Where's wp_postarchive coming from? What plugin? 3.3gigs is huge though. The (ah heck, i forget the name of it) index of the hard drive and how it finds what sectors files are in (whatever it;s called. And I had coffee this morning even.) is probably failing.
Although my host always mentions that there is major overload on my db.
Doubt it;s causing the issue but running optimize once in a while may help. Especially if that 3.3gig you quote up there is full of wasted space.
To perform the optimization, log in to your phpMyAdmin and select the database whose tables you wish to optimize.
A list with all the database's tables will appear. Tick the tables you wish to optimize, or simply click [Check All] to select all tables.
From the [With selected:] drop-down menu choose Optimize table. This will execute the OPTIMIZE TABLE SQL query on the selected tables and they will be updated and optimized.
Just had them do a hardware test and came back with no errors.
That's good. Gotta admit though that I've deal with techs in the past who a hardware test is "Yup, that's hardware." I'm going to assume that you trust these folks.
Where's wp_postarchive coming from?
Google doesn;t pull anything up for it so I;m still at a loss. The file size would cause issues though. I know FAT32, it;s 4 gigs. But that's with a perfect system. Like the number of files you can have within a directory. The limit is 32k but after 12-15k, things start to get affected.
how would I go ahead with optimization?
That would actually be a host question as I;m not aware of what you;re running for software. You should have a program called phpmyadmin as you mention up there. You can do it within that program. Dealing with the corruption issue is a higher priority though.
Here's some pretty pics though as to how to do it. Please see if you understand it: (edit: No offense intended by the way how I worded that.)
Optimizing gets rid of all the left over space from deleted information and it;s supposed to double check the index for the database.
edit: I don;t know what level of access you have to this box but mysql does have an error logs, much like the webserver does. Should be something like /logs/mysql/error.log. Something under the /logs/ or /log/ root directory and with mysql in the name.
Sounds like it's the same table all the time, right?
Not sure if you;re running Innodb or MyISAM. I forget what CPanel defaults to. (We're a Direct Admin house.)
As to the amount of writing, do you have any sort of caching in place? If so, which one is it?
Multidb shouldn;t be an issue but I would mention it to him as well as the location of the alt config files. I;m sure (hopefully) that he's aware of how wordpress works but I doubt he or she knows how mutiltdb handles things.
As to the amount of writing, do you have any sort of caching in place? If so, which one is it?
I had WP Super Cache installed, but deactivated it and had someone try to configure W3 Total Cache, but that didnt work due to a clash with the multi-db plugin as discussed here in a thread somewhere.
I need to reconfigure WP Super Cache again, but at this time, I have no caching in place.
Responses (14)
Developer — 11th November 2010 #
@georgef - are there any specific tables that you keep finding corrupting?
I used to have a lot of issues with the options table until I made a quick change to WP and it (touch wood) hasn't happened again since.
It will take a few minutes, but basically:
1. back up all the databases to sql files
2. create a new db
3. run all of the backed up sql files on the new db.
4. set wp-config.php to point to the new db
5. remove the multi-db db.php file
Once you are in a single db - you could set up the new 256 db's and then use the move script to redistribute them back out to the new 256
Keeper of the Dark Chocolate — 11th November 2010 #
The hard drive may be failing. Have you contacted your host about it? I know you said "many people..." but I don;t see any mention of host.
I know my number one replacement cost is hard drives. One of the reasons why we do a complete image every other night as a backup.
Member — 11th November 2010 #
I can ask the host. In emergency when the site goes down they always reply that they found a corrupt table and its fixed. I'll check with them.
Ivan mentioned in this thread that wp_posts is crippled.
I also had Mike (@venturemaker) remove the wp_postarchive table that was 3.3gb.
Come to think of it, he also tried to combine all the tables back to one db and we ran into a few problems.
Mike also mentioned that. Although my host always mentions that there is major overload on my db.
This message is from them last month...
and This...
Member — 11th November 2010 #
Just thinking, that won't solve much if I havent detected the corrupt tables, would it?
Member — 12th November 2010 #
Just to keep this all under the same thread, I've seen a few posts on fixing Corrupt tables using myisamchk:
http://www.thegeekstuff.com/2008/09/how-to-repair-corrupted-mysql-tables-using-myisamchk/
http://www.ghacks.net/2010/02/04/check-and-repair-corrupted-tables-on-mysql-database/
Would this work for a do it yourself approach?
Doesn't look like I'm going to find anyone real soon that can assist with this and not sure if I trust (after a few bad incidents) anyone from odesk, elance, etc.
Keeper of the Dark Chocolate — 12th November 2010 #
In that case, it;s probably an index problem for that table. A repair from phpmyadmin should fix that issue is that's what the problem is.
I;m lost. Where's wp_postarchive coming from? What plugin? 3.3gigs is huge though. The (ah heck, i forget the name of it) index of the hard drive and how it finds what sectors files are in (whatever it;s called. And I had coffee this morning even.) is probably failing.
Doubt it;s causing the issue but running optimize once in a while may help. Especially if that 3.3gig you quote up there is full of wasted space.
Member — 13th November 2010 #
Just had them do a hardware test and came back with no errors.
OK, would i use something like myisamchk mentioned above? (not sure if that can be done in phpmyadmin)
No idea how it got there, but we deleted it so its gone now.
Apologies for all the Q's as I'm new to db stuff, how would I go ahead with optimization?
Thanks!
George
Member — 13th November 2010 #
OK, a simple Google search got me this...
Keeper of the Dark Chocolate — 13th November 2010 #
FAT index. Knew I would remember it after awhile.
That's good. Gotta admit though that I've deal with techs in the past who a hardware test is "Yup, that's hardware." I'm going to assume that you trust these folks.
Google doesn;t pull anything up for it so I;m still at a loss. The file size would cause issues though. I know FAT32, it;s 4 gigs. But that's with a perfect system. Like the number of files you can have within a directory. The limit is 32k but after 12-15k, things start to get affected.
That would actually be a host question as I;m not aware of what you;re running for software. You should have a program called phpmyadmin as you mention up there. You can do it within that program. Dealing with the corruption issue is a higher priority though.
Here's some pretty pics though as to how to do it. Please see if you understand it: (edit: No offense intended by the way how I worded that.)
http://www.siteground.com/tutorials/phpmyadmin/phpmyadmin_optimize_database.htm
Optimizing gets rid of all the left over space from deleted information and it;s supposed to double check the index for the database.
edit: I don;t know what level of access you have to this box but mysql does have an error logs, much like the webserver does. Should be something like /logs/mysql/error.log. Something under the /logs/ or /log/ root directory and with mysql in the name.
Member — 14th November 2010 #
Hi MIke, that's just awesome help thanks a lot!
All good, pretty pics do help, I'm learning here! :)
Thanks for all the help.
I found a great Database Administrator that's going to check things out for me. Not familiar with wpmu, but sure that's fine.
Or anything I should warn him about other than multi-db which I've done?
Keeper of the Dark Chocolate — 14th November 2010 #
I raised this question on another forum. I;ve been asked a few questions about the type of database you're running:
http://www.lowendtalk.com/topic/mysql-corruption-issues
Sounds like it's the same table all the time, right?
Not sure if you;re running Innodb or MyISAM. I forget what CPanel defaults to. (We're a Direct Admin house.)
As to the amount of writing, do you have any sort of caching in place? If so, which one is it?
Multidb shouldn;t be an issue but I would mention it to him as well as the location of the alt config files. I;m sure (hopefully) that he's aware of how wordpress works but I doubt he or she knows how mutiltdb handles things.
Member — 15th November 2010 #
I'm quite sure it is.
Just asked my host and they said it's configured with MyISAM
Thanks for all the help here!
Member — 15th November 2010 #
I had WP Super Cache installed, but deactivated it and had someone try to configure W3 Total Cache, but that didnt work due to a clash with the multi-db plugin as discussed here in a thread somewhere.
I need to reconfigure WP Super Cache again, but at this time, I have no caching in place.
Member — 19th November 2010 #
UPDATE: The guy that worked on my system mentioned that most of my tables were without index?
Server cant handle the load and then slows down or shuts down.
He successfully restored all the db's into one and now working on the rest to fix up the tables.
Let's see how we go.
Become a member