0

On my server (ubuntu 12.04) mysqld started to show some strange CPU spikes. I've logged some of it as a sample:

http://pastebin.com/4MwKUVwL

The value after the triple dash '---' is the cpu usage (followed by mem usage) as reported by top. Notice for example 3 big spikes at 21:31:33 and 21:37:33 (178%!) and 21:40:25. At these times clients have trouble reaching the server.

Do you know what could cause these?

I did a few things;

Restart whole server: Same thing afterwards

Turn on general log to see queries in real time: I've made an estimation of the amount of queries when the cpu spikes, and when it doesn't. The amount is the same. But, when there are not many visitors, the interval between the spikes seems longer (not 100% sure about this, but it seems so after a quick review).

Ran mysqltuner:

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 5m 28s (50K q [12.899 qps], 25K conn, TX: 22M, RX: 6M)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 208.0M global + 2.7M per thread (400 max threads)
[OK] Maximum possible memory usage: 1.3G (64% of installed RAM)
[OK] Slow queries: 0% (0/50K)
[OK] Highest usage of available connections: 9% (37/400)
[OK] Key buffer size / total MyISAM indexes: 16.0M/100.0K
[OK] Query cache efficiency: 94.0% (23K cached / 25K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[OK] Temporary tables created on disk: 19% (54 on disk / 272 total)
[OK] Thread cache hit rate: 99% (37 created / 25K connections)
[OK] Table cache hit rate: 36% (77 open / 213 opened)
[OK] Open file limit used: 1% (50/4K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB data size / buffer pool: 27.0M/128.0M

Note that it ran a short time after mysql was restarted, but i ran it before and showed the same.

Checked IO problems: Very very low during cpu spikes and in between spikes.

Checked full processlist: Mostly empty, both during and between cpu spikes.

My mysql config file: http://pastebin.com/k5qpVVLR

Other server software: The site runs Apache & PHP. These and other processes show nothing different during mysql cpu spikes. I've checked the relevant php code, and it seems that connections are handled ok. Relevant stuff from my php.ini (tried also with both persistency on and off):

pdo_mysql.cache_size = 2000
mysql.cache_size = 2000
mysql.max_links = -1

I read about optimizing the tables, but the general idea seems that innodb doesn't require it, also my tables not that big (~30K rows).

I'm at loss, I'd appreciate any pointers. I hope some of you have had a similar experience.

Thanks a lot.

  • What do you mean by "At these times clients have trouble reaching the server"? What kind of trouble is this? Your data collection interval is just 5 seconds - does that mean that you have 5 seconds where no new connections can be made? – the-wabbit Sep 11 '14 at 5:26
0

What makes you think these spikes are strange? It could just be mysql working through queries like normal. What you'll need to do is analyse what queries are taking the longest and correlate this with the times of cpu increase. What you should start by doing is turning on slow query logging and looking over the queries that are taking the longest. Once these are tracked down, you can start to think about how viable it is to make these run more efficiently.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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