7

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.

3
  • Are you at Dreamhost? take my advice. RUN!!! Jul 13, 2010 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, 2010 at 7:33
  • Find a new host :-D Oct 11, 2017 at 10:40

1 Answer 1

8

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.

3
  • 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. Jul 13, 2010 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, 2010 at 2:40
  • Config for MySQL, config for the applications or general server config, or? Jul 15, 2010 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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