[Forum] Server load on mysql scary

[ashy]

Dedicated rackspace server, 16 core, and ssh / top gives below.

CPU% for mysql jumps between 50% and over 666%. Is this normal, is it just using available cpu as its there?

I've run tuning-primer.sh and increased some cache sizes as recommended and starting to monitor. Biggest issues reported there I felt were:
1. Of 516815 temp tables, 45% were created on disk - I've increased tmp_table_size to 256m from 16m
2. You have had 3989 queries where a join could not use an index properly (out of 4068156 in last 36 hours) - increased join_buffer_size to 4m

A lot of the basics have been done like attachments as files, no cache headers, update thread views only once an hour - but may change to once a night,

vb db is 17g
biggest tables
searchcore 5.9g
posts 4.4g
threads 2.8

I've 11m records in vb_attachmentviews ?? Can I empty that table.Or any others.

Also using memcached with vb.

In a 60 minute period typically 1600 - 2500 users.

Question.

I'd be happy to be given any pointers of aspects worth investigating. Site has been going for years!!! probably about 15 years

[Paul M]

MySQL does seem to be your issue, its definitely not normal to see values like that, you only have 4GB of memory, you could probably do with at least double that.

[ashy]

I thought it was 48g of memory

[In Omnibus]

It is but you're using virtually all of it. You also have over 31.17GB cached. That might be a problem.

[Paul M]

I thought it was 48g of memory

Wow, so it is !

I've never seen that much before, so mis-read it as 4G.

You should get your mysql settings checked out (my.cnf).
With 48G you should not be using any swap file, and have some to spare.
You seem to have touched your swap, and have nothing free / spare, so something seems to be using a lot.

Also, you seem to have 500 tasks running - that seems rather a lot.
You only have about 40 httpd in that s/shot, what are the other 460 ?

[ashy]

Thought I would just monitor today without further changes, seemed a bit better until now!

I think I need professional help!!!!

[Wayne Luke]

You probably need to optimize your MySQL configuration. The out of the box settings aren't very good. If you post in the Server Configuration forum, maybe someone can help you there. Might also post on StackExchange for help.

http://dba.stackexchange.com/

[Paul M]

Check the drives your databases are physically located on as well - one cause of massive CPU is poor disk performance, Ive seen it kill MySQL servers.

[ashy]

Running superb now as pictured from top command.

What did I do?

Keepalive ON
Fine tune mqsql with tuning-primer.sh - increased tmp table sizes
Monitored mysql slow processes and identified one forum with over 2m threads - old stuff so pruned down that forum
implemented ddos attack software
implemented fail2ban
looked at server stats on the heaviest site and found biggest users in last few months were range of ip's from norway and ukraine so firewalled them out

Tuning was always needed but to be honest I think the clue was in the high number of processes running and it was the heavy usage from other countries that has made the biggest difference.

The other tasks I did sure did improve performance watching "top" running - but still peaked again without warning. But not since I banned those IP's