On my server (ubuntu 12.04) mysqld started to show some strange CPU spikes. I've logged some of it as a sample:
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).
-------- 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.