Can anyone optimize my database ?

[AndyA]

I need some help, my webhost has currently disabled my database as they say it's killing the apache server what do I do ?
Here's a copy of the support chat:

User: I'm still getting the database error so my webpage must be accessable, the database must now be faulty ?

Stacy: hmm checking

User: ok, my old phpbb forum is viewable too, as the forum went down I was in progress installing Carp rss, this was uploaded to my 'home' directory (not public_html), that wouldn't be the cause would it ?

User: back in a few minutes

Stacy: we have disabled your database

Stacy: and apache started working

Stacy: your database is killing apache server

User: so how do I get it working ?

Stacy: hmm if we enable your database its killing http server

User: what happens now ?

Stacy: we will have to wait for our mysql admins to check

Stacy: they will decide what is causing issues

User: how soon will that happen

Stacy: allow some 12 to 24 hrs

User: I don't understand this, the database was imported from phpbb to vb and has been working great, much better than the previous phpbb

Stacy: yes but the queries has to be optimized as thats what taking http service down

Stacy: For optimizing your mysql queries

Stacy: follow

Stacy: http://dev.mysql.com/doc/mysql/en/my...imization.html

User: that's beyond me

Stacy: you will have to contact some programmer for doing this

User: Are you telling me that you can't get my forum back online ?

Stacy: yes unless you have optimized your queries

Stacy: as this is causing all sites going down

User: why has this happened since you blocked ip's ?

Stacy: nope

User: ?

Stacy: we removed those ips

Stacy: its your database causing issues

User: I'm sorry but I do not understand, my forum was fine before and now it's not.

Stacy: see its something wrong with your databse

User: ifyou enable my database then I'll be able to remove some posts and threads, won't that help ?

Stacy: its not about post

Stacy: its about sql queries that your forum does with mysql server

Stacy: the queries need to be optimized

Stacy: or it can cause server issues

User: ok but I'm not able to do that, as you are my web hosts and I pay for the hosting surely that's something you can do.

[Steve Machol]

You will have to ask them exactly what they mean. It's not clear from that transcript thatt they have any idea what is going on.

vB queries are already 'optimized'.

[AndyA]

It's getting to be rediculous, my web hosts will now look at my database over the next 12-24 hours and then decide if it can be un-suspended !

Thanks for your reply Steve.

[Steve Machol]

How busy are your forums? Fill out a support ticket at:

http://members.vbulletin.com/members...ontactform.php

Be sure to include the login info to your Admin CP, phpMyAdmin and FTP. We can take a look as the db for you.

[AndyA]

The forums only have 93 members, not that busy. I've filled out the support ticket.


Thanks again.

[Scott MacVicar]

Its possible that another application is unoptimized.

They should be able to give you the queries causing the problems, ask them to run SHOW PROCESSLIST;

as root when it gets busy and it will tell you whats causing the problem. Also mysql queries can't kill apache, thats factually wrong.

[AndyA]

Thanks Scott,

I've just emailed them with the info, I'm not too hopefull of getting a quick response unfortunately.

[AndyA]

I've just had a look in phpmyadmin and see the following (see attached), is this because they have suspended the database ?

[AndyA]

my host just re-enabled my database for a while but it caused them problems so they disabled it again, they have given me a backup to optimise but it's a tiny file of 964 bytes. (I thought it should be 20-30 megs in size but they say it's much smaller in text form?).

I have to get it optimised before they will re-enable it. Can I attach the file here ?

[Floris]

I've just had a look in phpmyadmin and see the following (see attached), is this because they have suspended the database ?

That error is probably because the host suspended your account.

To be honest, it strikes me that a hosting provider isn't able to run a small to medium web site. Perhaps you should reconsider your hosting provider.

A 30mb database won't be below 1 kb, not even in text form or zipped up. make sure all your data is still in the database, and that you aren't just dumping the structure, but also the data.

And no, please don't attach your sql dump here, it has the usernames, emails and passwords and all the private forum data in it - I am sure your customers or forum members wouldnt appriciate the privacy breach.

[AndyA]

The latest from Web host support chatKevin: i have figured out the problem that you are facing

User: oh, what is it

Kevin: your database is corrupted and that is why it is causing problems

Kevin: in fact one table is corrupted

Kevin: named attachment

User: ok, how did it get corrupted, is it fixable Kevin ?

Kevin: this is what is causing this problem

[chaeberle]

If the attachment table is corrupted, and they've suspended your database, then THEY have to run the tool to fix the attachment table.

If they re-enable your database, then you can run a similar utility yourself from Admin CP - Import & Maintenance - Repair / Optimize Tables

[AndyA]

This was the result after I ran 'optimise' in cPanel.

http://www.zen66311.zen.co.uk/SQL%20result%20-.htm

[chaeberle]

Dont run optimize. Run repair.

[Steve Machol]

I just checked your support ticket and your host has not restored your DB. There's nothing we can do untill they do this.