Database Optimisation Issue
 

Hiii..

I have 2 different vb databases, small enough, just around 10-12 MB each.

Now, issue is that my Site A has a very optimized database BUT my Site B doesn't.
Reason I say this is simple:-

1. Both have same resources, same db size but Site A runs smooth and faster than Site B.

2. When I try to optimize databases from admincp, Site A optimises in not more than 2 seconds and shows "Table is up to date" for almost every table WHEREAS Site B hangs inbetween while optimization, and I have to do it per 10-20 at a time. It hardly shows "Table is up to date" for any table.

3. Now, while investigating the issue, I noticed that tables in Site A show very accurate Data Length for every table BUT Site B has most of the table's Data Length to same 16 KB each (even when they are empty i.e. 0 bytes). Its something similar with both site tables Index Length too.

So, how do I make Site B as good as Site A. That 16 KB is surely some problem.

Thanks

Are both the sites on the same server or on different servers?

Both are on same server!

Lynne, I am sure this has to do with Site B's database only. Something needs to be done manually.

Do both sites have the same modifications installed? Do they have the same number of people online?

These, and more variables, will affect a site's loading time.

Try to run myisamchk from the SSH prompt for each table

yes! Exactly same modifications. Both have light traffic and almost same.

I am not talking about site's loading time BUT the database optimization issue with Site B. Why is it showing 16kb data length for tables that have nothing, whereas, Site A shows correct as 0 bytes.

Sometimes you have to run optimize (or repair) a couple of times.

Ofcourse, running it twice or thrice is not making any difference.

Login via SSH and run myisamchk on each individual table.

You'll have to browse to the mysql folder where the .MYI type files are stored, and run it like

myisamchk -r table_name.MYI

or

myisamchk -r *.MYI

oh I missed your previous post somewhow. Thanks for reply BS.

I forgot to tell that its a shared server and ofcourse, no SSH. But I do have a new server now, so I'll try to get Site B's database up there and run myisamchk. Will post results.

Thanks

No problem!

You may have to do it two to three times.

Try not to use the vBulletin repair tool, not only can it repair tables, but it can also break your database, its usually always best to SSH if available :D

Good luck

Got the database up onto the new server. Could see the forum running as well.

Then when I ran the command "myisamchk -r *.MYI", all tables got corrupted.

Now, when I run "mysqlcheck -r -u USERNAME -p DATABASENAME", it shows the following for ALL tables.

Error : Incorrect information in file: './database/table.frm'
error : Corrupt

How did .frm got corrupt for all tables??

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

ALRIGHT..

So, I tried everything again. Reuploaded the backup, ran a mysqlcheck, then a myisamchk, and all went FINE.

mysqlcheck -r -u USERNAME -p DATABASENAME

myisamchk -r /path/to/database/dir/*.MYI

mysqlcheck -r -o USERNAME -p DATABASENAME


I can now see all tables being optimised quickly! and all show "Table is already upto date" after running optimization a couple of times. I also found 5 tables named something like aggregrate_temp_12515 etc. I deleted all 5.

Looking great uptill now.

Thanks

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

I finally took the backup of the FINE TUNED database and got it up. Everything working as expected. See I told you all it was a database issue :D