Announcement

Collapse
No announcement yet.

Slow queries and maxed out CPU - vB 3.8.7

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

  • Slow queries and maxed out CPU - vB 3.8.7

    Been fighting with my board over the last 2 months it has slowly gotten worse. Seeing massive amounts of slow queries (~3000 every 24 hours) and cpu loads spiking well above the 20.00 mark randomly on a quad Xeon server. No changes to plugins/etc, issue started with v3.8.6, since been to 3.8.6pl1 and now 3.8.7 no change. upgraded the server 3 times to bigger and bigger setups just to make things work occasionally, Disabled all the plugins, etc. doesnt seem to correlate to one link/function/script in particular.

    Need some suggestions on what to troubleshoot and where to look here.


    basic details:
    site: http://forums.turbobricks.com
    average users online: 250
    Threads: 220,950, Posts: 3,617,574, Members: 35,464

    MySql: 5.0.92-community
    PHP: 5.2.8
    Apache: Apache v1.3.41

  • #2
    Hey there,

    I would recommend all innodb tables; sphinx search engine and big board index - make sure you have it. (check out my signature for links)

    Also, because of the QUAD server - upgrade to Mysql 5.5!
    anders | vbulletin team | check out the new vbulletin facebook app
    Proudly vBulletin'ing since 2001
    Please be my friend!
    http://www.twitter.com/inetskunkworks
    vBulletin Performance Articles:
    Click here to read

    Comment


    • #3
      thanks for the tips. Im looking at tossing in the towel and doing a vb4 install with mysql 5.5 and custom my.cnf. For now though, it seems as though its a bandaid on an axe wound to do performance tweaks to fix a problem thats relatively new... right? I mean the utilization and avg users online and db size was essentially the same this time last year on a much smaller server running smoothly with no problems. This really only got bad in the last 6 weeks.

      Comment


      • #4
        Originally posted by tryckjävel View Post
        thanks for the tips. Im looking at tossing in the towel and doing a vb4 install with mysql 5.5 and custom my.cnf. For now though, it seems as though its a bandaid on an axe wound to do performance tweaks to fix a problem thats relatively new... right? I mean the utilization and avg users online and db size was essentially the same this time last year on a much smaller server running smoothly with no problems. This really only got bad in the last 6 weeks.
        I understand your point - but that's not entirely correct, in my opinion. And as much as I would love to sell you on a vB4 upgrade - that will not solve your problem. The issue is that you've just reached critical point on your POST table, 3.6 million posts is considered a "big board" - so you need to make big-board provisions, such as the ones I've suggested. The issue, most likely, was not an overnight glitch but something compounding over time.
        anders | vbulletin team | check out the new vbulletin facebook app
        Proudly vBulletin'ing since 2001
        Please be my friend!
        http://www.twitter.com/inetskunkworks
        vBulletin Performance Articles:
        Click here to read

        Comment


        • #5
          It's weird. The first load took ages for me but after that it was fine.

          I run a similar sized board on probably less than half the server power than you. I didn't know innodb was ok on 3.8 tbh - certainly upgrade mysql not sure about going to vb4 (I haven't.) You've probably hit the wall on posts at 3 million, I'm ok so far at about 2.5mil. Post a server optimization request - you've defo got the horsepower.

          Do you have any of the slow queries logged?

          Anders and Eva are the big experts - I'm just an interested observer btw.

          Comment


          • #6
            Originally posted by IBxAnders View Post
            I understand your point - but that's not entirely correct, in my opinion. And as much as I would love to sell you on a vB4 upgrade - that will not solve your problem. The issue is that you've just reached critical point on your POST table, 3.6 million posts is considered a "big board" - so you need to make big-board provisions, such as the ones I've suggested. The issue, most likely, was not an overnight glitch but something compounding over time.
            Ok. i'll knock those out this weekend. The server im on was a choice made to combat this problem to a point. I maxed it out at 117.86 the other day... and its the biggest VPS my host offers - my forum should run on a much smaller box imo. agreed?

            The vB4 "upgrade" (more like start from scratch) was attempted back when it first came out and i backed out of it after realizing just how much work it was going to be to get it to fill in. Its something i did want to do for the features and fixing the searching problems everyone hated in vb3. I've already paid for it, just not using it. Figure this would be a great opportunity to set things up right from the start and then import my old board? I think you're saying best to fix it as is before going to 4 - right?

            I did pull out 1.5mil posts and stuck it in a hidden forum and marked that forum as non-searchable and it had no affect on this problem. maybe have to reindex the search engine? i dunno.

            Originally posted by punchbowl View Post
            It's weird. The first load took ages for me but after that it was fine.

            I run a similar sized board on probably less than half the server power than you. I didn't know innodb was ok on 3.8 tbh - certainly upgrade mysql not sure about going to vb4 (I haven't.) You've probably hit the wall on posts at 3 million, I'm ok so far at about 2.5mil. Post a server optimization request - you've defo got the horsepower.

            Do you have any of the slow queries logged?

            Anders and Eva are the big experts - I'm just an interested observer btw.
            Yeah thats the fun of it, its on/off constantly. I dont have slow queries logged... im actually on a "managed" VPS where i dont have root access just WHM and read only to the /etc/

            i want to say we were at maybe 3.3mil posts on smaller server slice (dont remember the stats but we upgraded back in nov) at the turn of the year and slowly this got worse and then started falling on its face in feb.

            Yeah i'd be glad to pay someone to write up a my.cnf php.ini and httpd.conf to send to the host to load up... and make the other tweaks to the board itself.

            Comment


            • #7
              Ouch; your forum is really under monetized and you should not be hiding crawlable content - you could be generating an impressive income with a board your size. Make sure you have a GOOD, CONFIRMED backup of your database and site files and start implementing the tweaks; ie - sphinx will off-load a bunch of load from the database, the innodb will prevent further bottlenecks and MYSQL5.5 will give you a nice performance boost - especially because of native performance patches and innodb plugin. Let me know and I can try to put together a plan for you so you can tackle one issue at a time.
              anders | vbulletin team | check out the new vbulletin facebook app
              Proudly vBulletin'ing since 2001
              Please be my friend!
              http://www.twitter.com/inetskunkworks
              vBulletin Performance Articles:
              Click here to read

              Comment


              • #8
                yeah the money thing is always a "wtf why?" when i show people that we're 100% donation funded. lol

                I would love any plans you put together. I'll do whatever you suggest to the best of my ability - or pay for it.

                Comment


                • #9
                  Originally posted by tryckjävel View Post
                  yeah the money thing is always a "wtf why?" when i show people that we're 100% donation funded. lol

                  I would love any plans you put together. I'll do whatever you suggest to the best of my ability - or pay for it.
                  Okay I will get back to this thread tomorrow to give you some pointers!
                  anders | vbulletin team | check out the new vbulletin facebook app
                  Proudly vBulletin'ing since 2001
                  Please be my friend!
                  http://www.twitter.com/inetskunkworks
                  vBulletin Performance Articles:
                  Click here to read

                  Comment


                  • #10
                    also one last bit, unless you see otherwise... once i get this whole thing back "under control" im looking to downgrade my server back to what it was:
                    1 CPU Core
                    3GB Memory
                    100GB Storage
                    2000GB Bandwidth
                    100GB Off-Site Server Backups

                    Comment


                    • #11
                      Sorry I didn't realise you were on a vps not your own server. Here's my optimization from 2008 haven't changed much since: http://www.vbulletin.com/forum/showt...-especially%29

                      CPU AMD Opteron 146
                      Core single core
                      RAM 2048 MB DDR2 RAM
                      Hard Drive 2x 120 GB SATA/ 7200 RPM (mysql on second drive)
                      Most users ever online was 2,622, 15-02-2011 at 09:39 PM.
                      May have hit 20 on that day

                      Anyway Anders has got his teeth into this one. I'll be peeking in as we're very similar in terms of wol numbers.

                      good luck

                      Comment


                      • #12
                        cheers mate - i can already see a few things just with mysql that need some major adjustments based on your final my.cnf. Im starting to think i need to just stick with the massive server i've got.

                        Also the host said something about mysql 5.5 and cpanel dont work so is it imperative that i upgrade mysql as well?

                        Comment


                        • #13
                          meant to say: leave out the line
                          Code:
                          skip-innodb
                          from my.cnf as panels all seem to use innodb (plus Anders is suggesting you go full tilt at innodb anyway)

                          Best to follow whatever advice Anders comes back with - sphinx is the big one because of the size of your post table. I'm just about running ok without it so far but it's only a matter of time!

                          If you could get all the info posted required for a server optimization then the guys here will fix you up.

                          Cheers!

                          Comment


                          • #14
                            If you want to get to the root of the problem with the slow queries, you should start logging the slow queries so you can see exactly what queries are the culprit. It will save you a lot of time chasing other things that might not even be the issue.
                            Sphinx Search for vBulletin 4: https://marketplace.digitalpoint.com...tin-4.870/item
                            Someone send me a message on Twitter when this site is usable again. https://twitter.com/digitalpoint

                            Comment


                            • #15
                              Originally posted by punchbowl View Post
                              If you could get all the info posted required for a server optimization then the guys here will fix you up.

                              Cheers!
                              yup, i'll try and grab as much as i can of that i have access to and post it up tonight.

                              Originally posted by digitalpoint View Post
                              If you want to get to the root of the problem with the slow queries, you should start logging the slow queries so you can see exactly what queries are the culprit. It will save you a lot of time chasing other things that might not even be the issue.
                              yeah since i dont have root i have to have the host restart mysql with the log-slow-queries option ... I think i'll do the performance tweaks first and then have them do mysql slow queries which i would think should give me a more accurate list of which ones really are slow. seems that most people are complaining about PM's... and i've captured a few from show full processlist that indicate some slow query on scanning uid's. we'll see.

                              Comment

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