Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

The database I'm working on is having frequent issues.

Almost every day there is a problem.

Things like tables crashing or becoming corrupt.

So far, they've been repairable in one way or another, however I'm curious what the cause is exactly.

Bad hardware is always a possibility, but because I'm using Amazon's Web Services, I'm doubtful that is the case.

I've been monitoring the CPU usage on the machine and I've noticed that it frequently maxes out. On average, it hovers around 40%, but has frequent spikes.

Would constant high usage be a cause for my issues? If so, what is a good way of handling the high usage apart from optimizing queries; I'm currently working on that and the high usage is likely to stay. Would replication be beneficial?

EDIT -- I went ahead and bit the bullet and decided to upgrade the resources. So far, I've been able to run all my queries without any issues. It looks like I was just working it too hard.

share|improve this question

closed as off-topic by Michael Green, mustaccio, Shawn Melton, Tom V, RLF Dec 31 '15 at 16:03

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Michael Green, mustaccio, Shawn Melton, Tom V, RLF
If this question can be reworded to fit the rules in the help center, please edit the question.

What MySQL version are you using, and what storage engine(s) do you use? – Derek Downey Aug 30 '12 at 14:00
I haven't tried using a new instance--I could probably try that although it's several GB in size so I'm reluctant to creating a new instance and copying it. Also, I'm using MySQL 5.5.20 – Sterling Aug 30 '12 at 14:17

High CPU usually means inefficient queries. Let's see the slow queries, together with SHOW CREATE TABLE and EXPLAIN.

The solution may be as simple as adding an index. Or perhaps reformulating the query. A common inefficiency is using a subquery instead of a JOIN.

You can't tune your way out of CPU troubles. Throwing hardware at it is, at best, a temporary solution.

share|improve this answer
I don't think inefficient queries are the main perpetrator here. I've added indexes on all columns used for lookups and don't use any subqueries--just joins. I suspect the main issue is just the sheer volume of queries performed (there are a lot and they run basically all day everyday). I think this may be a case where a hardware upgrade is necessary. – Sterling Aug 31 '12 at 20:51
No, adding indexes does not necessarily cure such a problem. In fact, it may aggravate it. Turn on the slowlog; then let's discuss the worst query that it finds. – Rick James Aug 31 '12 at 23:06
Well I've turned on slow logs and let it run for about a day and no queries have come up. Now I'm going to drop long_query_time down to 1 sec to see if I can get some queries to show up. – Sterling Sep 1 '12 at 16:30
I'd lower the long_query_time to 0.0 seconds (yes zero) for a whole day or for 30-60 minutes (so you don't have a huge query log). – ypercubeᵀᴹ Nov 18 '12 at 20:16

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