My shared-host tells me that they're throttling my website because of MySQL, what can I do?

Question

My shared-host tells me that they're throttling my website because of MySQL, what can I do?

I'm not familiar enough with performance tuning of MySQL, generally MySQL just works for me. What questions do I need to ask to figure out how to fix this? Is this memory? Is this processor? Is this configuration of MySQL? Assuming I get access to the slow query log, what will it take for me to make sense of it?

I'm asking this as a generic, but I have run into this many times and generally it's a substandard host. Changing to a better host and implementing caching usually does the trick. But what are the most important settings to consider in order of priority?

UPDATE: I'm hoping to get this to be a checklist of "how can I tell if MySQL is set up okay on my shared hosting account?" type issues.

Answer 1

Probably the biggest thing to check if is you have keys on the fields you are querying on. Having the right keys and fields will make your queries considerably faster. You can determine if your keys are being used by running the query with EXPLAIN in front of it, such as EXPLAIN SELECT * FROM table WHERE id = 2; You can add EXPLAIN on queries in the slow query log to see what they are or aren't using.

You may also want to considering caching data if you are selecting the same data out of a table often. This can be done by storing the resulting page (such as the actual HTML or a portion of) or storing the result of the query (such as the IDs, so you don't have to query on a text field).

You will also want to avoid querying text fields with LIKE %text% too often. These can be quite slow.

Also, search the web for MySQL query optimization (or combination thereof). There are a tonne of different methods and not every method will work for you site, so it's most trial an error, but keys will definitely improve the speed of MySQL and reduce it's load.