No announcement yet.

Server load on mysql scary

  • Filter
  • Time
  • Show
Clear All
new posts

  • [Forum] 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 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.


    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

  • #2
    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.

    Baby, I was born this way


    • #3
      I thought it was 48g of memory


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


        • #5
          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 ?
          Baby, I was born this way


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

            I think I need professional help!!!!


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

              Translations provided by Google.

              Wayne Luke
              The Rabid Badger - a vBulletin Cloud demonstration site.
              vBulletin 5 API - Full / Mobile
              Vote for your favorite feature requests and the bugs you want to see fixed.


              • #8
                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.
                Baby, I was born this way


                • #9
                  Running superb now as pictured from top command.

                  What did I do?

                  Keepalive ON
                  Fine tune mqsql with - 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


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