Announcement

Collapse
No announcement yet.

Server load on mysql scary

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • ashy
    started a topic [Forum] Server load on mysql scary

    Server load on mysql scary

    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



    Attached Files

  • ashy
    replied
    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

    Leave a comment:


  • Paul M
    replied
    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.

    Leave a comment:


  • Wayne Luke
    replied
    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/

    Leave a comment:


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

    I think I need professional help!!!!

    Leave a comment:


  • Paul M
    replied
    Originally posted by ashy View Post
    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 ?

    Leave a comment:


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

    Leave a comment:


  • ashy
    replied
    I thought it was 48g of memory

    Leave a comment:


  • Paul M
    replied
    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.



    Leave a comment:

widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
Working...
X