Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
MySQL CPU Usage off the charts!
We've been using Vanilla Forums for awhile now without any real issues.
The only thing we've changed recently is our theme -- to Bootstrap -- but that was weeks ago. Now our server is constantly slowing down. Looking in WHM the problem is mysql is consuming huge amounts of CPU. Today 180%, yesterday over 300%.
We've had no meaningful increase in traffic numbers. We use CloudFlare on the domain to thrwart attacks.
Any ideas on what we could do to fix this? It appears to have come out of no where.
0
Comments
I'd start by looking in the mysql slow queries log, to see if there is a specific part of the table causing an issue.
Try
SHOW PROCESSLIST;
to see what's going on right at the moment when you experience the high CPU usageI've just run it a few times, this entry keeps appearing:
Copying to tmp table select a.*, r.InsertUserID as
UserID
, r.DateInserted asDateInserted
from GDN_Action a
join GDN_
...is it normal for the slow queries log to be over 300MB? Downloading now to take a look.
Activity table?
grep is your friend.
Errrr what about it?
I is usually large, activity is mostly crap you can purge it periodically.
in crontab add this
grep is your friend.
@whu606 Slow log has heaps of different slow queries logged, what should I be looking for?
Wow really?
GDN_Activity ~642,897 293.4 MiB
350339 rows deleted. (Query took 110.6879 sec)
!!
Performance limitations I'm aware of related to MySQL:
Also: MySQL can hit a tipping point without comparative increase in traffic. Once queries start backing up, it can stack very quickly and bring down everything. So you might also look at APC/Zend OPcache, memcached and Varnish if you're not currently using those. memcached is probably the lowest fruit :: biggest MySQL performance gain of those.
Also note that I'm mentioning all this with no insight to what your traffic numbers and hardware are, so these are mostly blind suggestions. There's a hundred variables to tracking down something like this.
Thanks for the extra info. Rough numbers of our traffic...
...but it sounds like this may be no simple fix. Those fixes you've suggested are 'above my pay grade'. I'll have to investigate them, or find someone who knows more about them.
It's just very annoying that site worked fine for so long and now has this uncontrollable issue.
Nice pageview count. I really do think you could install memcached and configure it in Vanilla without it being a major project. I'd start looking at Sphinx only if you see search queries hanging.
The plugin ActivityPurge helps you keep the place cleaner...
❌ ✊ ♥. ¸. ••. ¸♥¸. ••. ¸♥ ✊ ❌
Thanks, I'll look into it. Though it's CPU, and not memory, that mysql is now consuming.
Ta, I'll grab that.
Memory caching will reduce the dependence on quering which uses up cpu, as well as storage i/o
grep is your friend.
Oh, and would upgrading MySQL help? Currently running 5.5.42.
Great. I've just installed it ... is there a particularly good way to monitor how it's 'working'?
Running WHM on CENTOS.
Make sure you have installed
php-memcahed
notphp-memcache
enable caching in vanilla:that is all.
You can check it is running with
watch "echo stats | nc 127.0.0.1 11211"
Ctrl+C to exit.
grep is your friend.