-1

I'm trying to figure out possible reasons why my database could be causing 100% CPU time.

Its been like this for a while even though I've recently made changes so that pages / queries run much faster.

Heres a video my ISP produced of my site, showing the CPU usage.

Heres some questions I asked my ISP..

Me : would you say its a fast server ?
ISP: yeah it has 4 cpu cores lol and 3.5gb ram
ISP: 4 x intel xeon's 3.4ghz it has
ISP: its also running raid 5 on ultra scsi 320 drivers
Me : what the mysql caching settings ?
ISP: which handles 320 mb/s
ISP: hmm maybe the mysql cache is low
ISP: have emailed it to you. 
Me : was it low ?
ISP: if you do post for advice one thing to mention is that this 
  is not a dedicated mysql server
ISP: so it can't be setup to use the server maximum resources

Heres the My.ini he sent me a copy...

Also heres my phpMyAdmin status page. I think I'm right in saying that theres nothing in the slow log, I think the slow queries is from before my fixes.

Jules
  • 7,568
  • 14
  • 102
  • 186
  • For how long does it stay at 100%? Did you run `show full processlist` to see what queries are being executed? – The Scrum Meister Jan 18 '11 at 08:09
  • This can be caused by a lot of reasons, why are you so sure the problem originates from the database of all things? – Or Weinberger Jan 18 '11 at 08:11
  • Ooops see video, for got to add it earlier... – Jules Jan 18 '11 at 08:12
  • Can you reproduce this on a test server ? If your queries are slow(slow is relative, the slow log might not catch them), tables are unindexd/have wrong indexes etc., you might see this. If theres a realtime virus scanner, that probably doesn't help either. Anyway, try to reprpoduce this. It might not be the DB even, perhaps it's your PHP code. – nos Jan 18 '11 at 08:39
  • In the video, CPU does not even reach 100%. In any case, it'd be pointless to enqueue and delay processes just to have spare CPU... – Álvaro González Jan 18 '11 at 08:39
  • Do you by any chance have a crappy server? please post the specs.. – The Scrum Meister Jan 18 '11 at 08:44
  • @nos, No my test server is quite an old machine. – Jules Jan 18 '11 at 13:55
  • @Scrum, no its a good spec, see the question. – Jules Jan 18 '11 at 13:55

2 Answers2

0

Maybe you'll find something usefull for you here mysql-high-cpu-usage

Community
  • 1
  • 1
cristian
  • 8,676
  • 3
  • 38
  • 44
0

Considering that your long_query time is set to 2, and it looks like there are multiple queries firing per page, but returning reasonably quickly, no wonder you don't know what's slow. (NB you can override this within your code to record more detailled information for the session).

You've not said if this is a dedicated database server or if its running other stuff. Nor is there anything in the video to suggest that the CPU is a direct consequence of mysql (compared, say with a badly configured AV scanner).

There are a whole lot of potential causes, but with a MSWindows platform, its very difficult to diagnose most of them, and even less scope for actually fixing a lot of them.

But if you're happy with the time it takes for the pages to be generated, why do you care about CPU usage?

Its also interesting to note that you've got approx twice as many change db ops as select ops - suggests your data has been split across 2 databases?

symcbean
  • 47,736
  • 6
  • 59
  • 94
  • does long_query time, the max time before its killed ? Yes there are multiple queries. I'm not sure I understand, why you say 'no wonder I don't know what slow' ? Its a shared database server, but I have the only database on it at the moment. The page load time isn't too bad, its my ISP that cares about the CPU time. Yes there is an offline server to handle data processing, I had to split it up due the performance on the live server. – Jules Jan 18 '11 at 13:58
  • long_query_time is the the amount of time a query has to run for before its flagged as a candidate for writing to the slow query log. If you set it to a lower value then you should be able to see in the logs what's causing problems. So its a dedicated database server - the webserver runs somewhere else? – symcbean Jan 18 '11 at 20:54