MySQL CPU Usage off the charts!

Simeon_Griggs

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.

whu606

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.

R_J

Try SHOW PROCESSLIST; to see what's going on right at the moment when you experience the high CPU usage

Simeon_Griggs

@R_J said:
Try SHOW PROCESSLIST; to see what's going on right at the moment when you experience the high CPU usage

I've just run it a few times, this entry keeps appearing:

Copying to tmp table select a.*, r.InsertUserID as UserID, r.DateInserted as DateInserted
from GDN_Action a
join GDN_

Simeon_Griggs

@whu606 said:
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.

...is it normal for the slow queries log to be over 300MB? Downloading now to take a look.

x00

Activity table?

Simeon_Griggs

@x00 said:
Activity table?

Errrr what about it?

x00

I is usually large, activity is mostly crap you can purge it periodically.

in crontab add this

00 3 * * * mysql -u username -p pass forumdb -e "DELETE FROM GDN_Activity WHERE ActivityTypeID NOT IN ( SELECT ActivityTypeID FROM GDN_ActivityType WHERE Name = 'WallComment' ) AND TIMESTAMPDIFF(MONTH,DateInserted,NOW())>2"

Simeon_Griggs

@whu606 Slow log has heaps of different slow queries logged, what should I be looking for?

Simeon_Griggs

@x00 said:
I is usually large, activity is mostly crap you can purge it periodically.

in crontab add this

00 3 * * * mysql -u username -p pass forumdb -e "DELETE FROM GDN_Activity WHERE ActivityTypeID NOT IN ( SELECT ActivityTypeID FROM GDN_ActivityType WHERE Name = 'WallComment' ) AND TIMESTAMPDIFF(MONTH,DateInserted,NOW())>2"

Wow really?

GDN_Activity ~642,897 293.4 MiB

Simeon_Griggs

350339 rows deleted. (Query took 110.6879 sec)

!!

Linc

Performance limitations I'm aware of related to MySQL:

• 500K+ Activity records. (solution: prune as suggested - on our list for a better fix)
• Search on 1M+ post sites (solution: Sphinx)
• 3M+ users (solution: already mitigated in core for 2.2, but something to keep an eye on wrt plugins)
• 300+ categories (solution: cache sharding is the only one currently; needs more work)

Linc

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.

Simeon_Griggs

Thanks for the extra info. Rough numbers of our traffic...

• 25,000 posts/month
• 1.5m pageviews/month
• 10,000 users
• 20 categories

...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.

Linc

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.

vrijvlinder

The plugin ActivityPurge helps you keep the place cleaner...

Simeon_Griggs

@Linc said:
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.

Thanks, I'll look into it. Though it's CPU, and not memory, that mysql is now consuming.

@vrijvlinder said:
The plugin ActivityPurge helps you keep the place cleaner...

Ta, I'll grab that.

x00

Thanks, I'll look into it. Though it's CPU, and not memory, that mysql is now consuming.

Memory caching will reduce the dependence on quering which uses up cpu, as well as storage i/o

Simeon_Griggs

Oh, and would upgrading MySQL help? Currently running 5.5.42.

Simeon_Griggs

@x00 said:

Great. I've just installed it ... is there a particularly good way to monitor how it's 'working'?

Running WHM on CENTOS.

x00

Make sure you have installed php-memcahed not php-memcache enable caching in vanilla:

$Configuration['Cache']['Enabled'] = TRUE;
$Configuration['Cache']['Method'] = 'memcached';

that is all.

You can check it is running with

watch "echo stats | nc 127.0.0.1 11211"

Ctrl+C to exit.