database optimization and server errors

Question

I've a self hosted wordpress blog. The server showing internal (500) server error and usually the system admins ask me to optimize my database. The database is relatively small in size and fits in few MBs. The error usually displaying is Fast CGI process.

The usual reply from admins are as below

As we have checked and found that you are using Mysql database and fetching database through php code from database server which is at remote location and in this process when queries are being more then PHP fast cgi application get hanged and that's why you are getting this issue, For this you should check code or database optimization

How can I optimize my database? I've installed a wordpress plugin to optimize my tables. Or is it a server problem?

Answer 1

It probably is a host issue. Assuming that you're relatively stock Wordpress, the queries shouldn't be so inefficient that they're causing timeouts.

When it says a "remote mysql database", does that mean a remote database server with the same hosting company, or does it mean you're using a database server that's physically located somewhere else? If the latter, that could be your problem. Negotiating a MySQL connection then running a query requires a fair number of roundtrips, so if there's a lot of latency it could add up fast to approach the timeout.

Based on the rather canned-looking reply from the host, I'm willing to bet you're using some low-priced shared hosting. Unfortunately, almost all of those are terrible. After a year or two of having problem after problem with shared hosts, I gave up and went to a good VPS and have had no issues since. This may not be a problem for you though, if you're not willing and able to manage an entire virtual server for one small blog.

Answer 2

Are your database and web servers on same hosting and same hosting account? If you are trying to use remote database from another party this might indeed be way too slow.

If this is all in same account then your hosting is probably doing it wrong. For cheaper hosting database usually resides on same server, for more advanced hosting separate database servers resides nearby (reached by local network or whatever). If hosting cannot provide database access that is sufficiently fast and reachable - it is their problem with configuration, not your problem with tables/queries.

Answer 3

May be the problem not in your database size, but in your query. May be you use uneffective query or use sub-query that containt recursive recordset, could be your database server down.