0

I'm running a website on a cloud server. The website is functioning completely around a (rather large) database. Over the last two weeks I've noticed steady rise in the CPU load of the MYSQL and I'm not sure why. It has been 15-16% for a while and then it started climbing by 1-2% a day. Currently we are at 27% and thought there has been a rise in traffic, it wasn't that big.... What could be causing this?

Thanks!

user2704687
  • 185
  • 3
  • 11
  • Have you tried to restart it. Maybe result set by select query is getting bigger and bigger. See slow log query, like blue mentioned. – broadband Mar 25 '14 at 10:37
  • Slow log wasn't enabled, so did that yesterday. So far nothing out of the ordinary. However, it keeps climing! The CPU usages rises about .1 percent per minute. It's already up to 40%! Any ideas? – user2704687 Mar 26 '14 at 14:01

2 Answers2

1

Check you MySQL slow log. Don't forget to add the queries not using indexes in the log.

Fix any queries you find in there.

blue
  • 1,939
  • 1
  • 11
  • 8
  • Thanks for your answer. The slow log (which I had to turn on first) shows nothing strage. However, the CPU usage is rapidly climbing. About .1 percent every minute. It's already up to 40%! Somewhere, something is building up.. – user2704687 Mar 26 '14 at 14:03
  • The [long_query_time](http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_long_query_time) has a default value of 10 seconds. Lower that by 1 until you find the problematic queries. Also, add the queries not using indexes -> [log_queries_not_using_indexes](http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_log-queries-not-using-indexes) – blue Mar 26 '14 at 14:11
  • I lowered it to 2, that gave nothing. Lowering it to 1 won't help much, since the queries are actually pretty complex and large so 1 second is a decent time. Also: the last few weeks, nothing has really changed in the queries. Couldn't it be some cache building up or some temp file or something? – user2704687 Mar 26 '14 at 14:14
  • Are you forking scripts? Are there any scripts in the crontab that are running longer than a few seconds? Anything suspicious in the process list? – blue Mar 26 '14 at 14:17
  • I'm not forking any scripts. When I run SHOW PROCESSLIST in Mysql I don't get anything suspicious besides regular user activity. – user2704687 Mar 26 '14 at 14:31
  • That involves the leap second, but if I'm not mistaken, the last leap second was somewhere 2012 and hasn't occured since? – user2704687 Mar 26 '14 at 15:36
0

The problem actually was the build-up of several caches. If someone else encounters this problem, I suggest to look at the red values under 'Status'-> 'All statusvariables' in your PHPmyAdmin. Enlarging the tmp_table_size and flushing the query cache did wonders for me.

Good luck!

user2704687
  • 185
  • 3
  • 11