Tell me more ×
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. It's 100% free, no registration required.
up vote 3 down vote favorite

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
Have you tried moving this to a new instance in AWS? – Max Vernon Aug 30 '12 at 13:58
2  
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
The reason for trying a different instance would be to eliminate the hardware as an issue. This also has the side effect of eliminating issues with the file system, software config on your existing instance, etc. – Max Vernon Aug 30 '12 at 15:55

1 Answer

up vote 1 down vote

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

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.