Take the 2-minute tour ×
Webmasters Stack Exchange is a question and answer site for pro webmasters. It's 100% free, no registration required.

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.

share|improve this question
    
Are you at Dreamhost? take my advice. RUN!!! –  The Disintegrator Jul 13 '10 at 4:15
    
No, I'm on pair.com and have no such problems. The latest would-be client with this problem is on Bluehost. –  artlung Jul 13 '10 at 7:33

1 Answer 1

up vote 8 down vote accepted

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.

share|improve this answer
2  
+1 - in my early days of webmastering I had no idea what a key or an index even was. I just used to ignore those setings. 3 years at university later and I was amazed that I was even able to operate a database correctly as I hadn't done any optimisations, keys, indexes, or anything. Makes a world of difference. –  Mark Henderson Jul 13 '10 at 0:25
    
Do you have any sense of minimum MySQL configurations for a shared host that would cover general web applications (e.g.: Drupal, Joomla, WordPress, phpBB, etc.). –  artlung Jul 13 '10 at 2:40
    
Config for MySQL, config for the applications or general server config, or? –  Darryl Hein Jul 15 '10 at 17:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.