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