Slow loading a channel with lots of posts

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • airgun
    New Member
    • Nov 2017
    • 13
    • 5.3.x

    Slow loading a channel with lots of posts

    Just upgraded to VB 5.3.3 from VB4.

    Things like latest activity and todays posts are fast, as is searching for anything.

    Loading the home page is fast.

    But going deeper into the forum is REALLY slow, as much as 15 seconds a page, on those channels with lots of posts. We've been using VB since about 2008 so some channels have over a million posts.

    Non busy channels load in a couple of seconds.

    I notice the same issue on this forum. The popular areas such as presales with 100k posts take 4-5 seconds to load, non busy such as suggestions is almost instant.

    Looking at the process list with mysql I can't see any queries running during that 15 seconds and the slowest queries I see are about 3 seconds. Watching the slow query log whilst going into the large forums there are no slow queries shown.

    So what is the VB code doing to take all that time. It only shows one page no matter if the channel is 100 posts or 1 million.
  • Wayne Luke
    vBulletin Technical Support Lead
    • Aug 2000
    • 73976

    #2
    Run the query:

    Truncate table searchlog

    Does that help it?
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API

    Comment

    • airgun
      New Member
      • Nov 2017
      • 13
      • 5.3.x

      #3
      I was going to try this in 40 minutes as that has grown to 12GB!!!. Taking the board offline for a while at 10pm local time.

      But if it seems to affect this board too, then I'm assuming its a design issue with VB5?

      Comment

      • airgun
        New Member
        • Nov 2017
        • 13
        • 5.3.x

        #4
        Ok, just tried it. Zero reduction in loading.

        Comment

        • airgun
          New Member
          • Nov 2017
          • 13
          • 5.3.x

          #5
          What I don't get is I can search 1.5 million posts and display the contents in about 1.5 seconds, but displaying the static contents of the first page of a forum takes 10 times as long. There has to be design problem somewhere.

          Comment

          • Wayne Luke
            vBulletin Technical Support Lead
            • Aug 2000
            • 73976

            #6
            Run this query:

            update user set maxposts=-1 where maxposts=0;

            Is there any benefit... Both of these queries are for bugs fixed in the next version.

            For good measure. Run truncates on the cache and cacheevent tables as well. For a board of your size, you probably want to offset search to Sphinx Search and use Memcached for your cache storage. Your database tables should be INNODB if they aren't and the buffers in MySQL adjusted properly to give you an appropriate response.
            Translations provided by Google.

            Wayne Luke
            The Rabid Badger - a vBulletin Cloud demonstration site.
            vBulletin 5 API

            Comment

            • airgun
              New Member
              • Nov 2017
              • 13
              • 5.3.x

              #7
              Thanks the query doesn't change any table rows.
              update user set maxposts=-1 where maxposts=0;
              Query OK, 0 rows affected (0.13 sec)
              Rows matched: 0 Changed: 0 Warnings: 0
              Looks like all already -1.

              Truncating those tables sadly hasn't helped either.

              I'm using sphinx and memcache. The mysql config has been honed over the last few years. We've even had the pain of upgrading to php7.

              I don't believe it is a mysql problem. When I'm waiting the 8-20 seconds for a page to load there are no mysql queries taking place after an initial 3 second or so query. Slow query was set to 3 and only one or two queries are showing.

              The time taken to show the first page of a forum is directly proportional to the number of posts its contains. And it shouldn't be as its only showing the one page.

              My belief is there is a design flaw in VB5 that is processing the 100's of k or records even though it only has to show one page. You see it on this very forum. It takes 2-3 seconds longer to show the front page of forums with the most threads

              Can't they put in a limit of 1000 results? If it can't find enough valid posts to fill a page in the first 1000 then their must be something wrong somewhere.

              Or is it processing all the records so it knows how many pages there are? This can't be efficient.

              I've failed to find any VB5 sites with ore than 100k records in one channel.......

              Comment

              • Wayne Luke
                vBulletin Technical Support Lead
                • Aug 2000
                • 73976

                #8
                Search results are actually limited to 500 results in a default installation (Settings -> Options -> Message Searching Options). All content display is a search of some sort or another. That is everything is based on the search engine with different filters and parameters. The more modules, you have on a page, the more overhead that it will have. The more custom permissions you have the more overhead there will be. Some of these issues are the price you pay for a system where every page is infinitely customizable.

                You can limit the results by editing the module options on your site. More arbitrary limits aren't the answer because that just upsets people. Even just limiting initial results to 30 days as we did in the early days of vBulletin 5, upset people. There may also be some issues with your database that exacerbates some issues or exposes a bug but we would need a copy of the database to determine that.
                Translations provided by Google.

                Wayne Luke
                The Rabid Badger - a vBulletin Cloud demonstration site.
                vBulletin 5 API

                Comment

                • airgun
                  New Member
                  • Nov 2017
                  • 13
                  • 5.3.x

                  #9
                  We've cut our losses and gone back to VB4 as VB5 was killing the site. Usage dropped 90%. Shame VB refuse to give a refund as we downloaded the software. But they say we have to download the software to try it. Not just slow, but buggy as hell. So many complaints from members and mods, not fit for purpose.

                  Oh well, at least I can relax and not check hourly to see if its still working.

                  Comment

                  • alfreema
                    Senior Member
                    • Sep 2017
                    • 144
                    • 5.3.x

                    #10
                    Originally posted by Wayne Luke
                    Run this query:

                    update user set maxposts=-1 where maxposts=0;
                    Ah, so I was coming here to post a question about this. I have lots of long term users complaining about having to load an entire (large) topic just to get to the last post. The fix is always tell them to go to their Account tab and change the Posts per Page to 20 or something. So I went to the database and ran a query on the counts of user.maxposts and I see that I have a ton of people with -1 and a ton with 0. Should it ever be 0? I am assuming that 0 = load the entire topic with no pagination, and -1 means use the forum default? Can you confirm?

                    And FWIW, opposite of the OP's conclusion, yes vb5 is a major change for long time users that will result in a lot of backlash -- but if you stick with it and add a few of Glenn Vergara's mods the end result is that it is a much better experience for users overall due to the proliferation of mobile devices. vb5 = short term pain, long term gain! I'm glad I stuck with it!

                    Comment

                    • Wayne Luke
                      vBulletin Technical Support Lead
                      • Aug 2000
                      • 73976

                      #11
                      No user should be set to 0. This will break the site for that user.

                      Before vBulletin 5, the values of 0 and -1 were used with a lot of different options and variables. However, they had different meanings per option. These values could mean that the option is set to the default, turned off, or mean that it is infinite. This causes confusion for customers, support staff, and developers. Over time, we've been working to change the system so that values and variables are more consistent. We are working to show that 0 means Off, False, or Ignore the option. While -1 means use the default. There are hundreds of variables and tens of thousands of combinations to go through with a default install of vBulletin. This results in a lot of extra testing, code refactoring, and other tasks. It is a lot of work. With each release, there are a number of issues related to updating the code used in the software in addition to general bug fixes, improvements, and new features.

                      In vBulletin 4 setting this option to 0 in the database means use the default value from the topic viewing options. In vBulletin 5, that option doesn't exist anymore and has been moved to the module options. Within the modules on each page, this option should be set to -1 in order to use the default from the module configuration for the page. The problem is that some users were not updated for a variety of reasons and therefore are broken. The 0 value tells the system to retrieve all posts in the topic and that breaks things. Running the query above sets the users to the proper value and fixes the display of everything from topics to large forums.
                      Translations provided by Google.

                      Wayne Luke
                      The Rabid Badger - a vBulletin Cloud demonstration site.
                      vBulletin 5 API

                      Comment

                      • alfreema
                        Senior Member
                        • Sep 2017
                        • 144
                        • 5.3.x

                        #12
                        Originally posted by Wayne Luke
                        No user should be set to 0. This will break the site for that user.

                        Before vBulletin 5, the values of 0 and -1 were used with a lot of different options and variables. However, they had different meanings per option. These values could mean that the option is set to the default, turned off, or mean that it is infinite. This causes confusion for customers, support staff, and developers. Over time, we've been working to change the system so that values and variables are more consistent. We are working to show that 0 means Off, False, or Ignore the option. While -1 means use the default. There are hundreds of variables and tens of thousands of combinations to go through with a default install of vBulletin. This results in a lot of extra testing, code refactoring, and other tasks. It is a lot of work. With each release, there are a number of issues related to updating the code used in the software in addition to general bug fixes, improvements, and new features.

                        In vBulletin 4 setting this option to 0 in the database means use the default value from the topic viewing options. In vBulletin 5, that option doesn't exist anymore and has been moved to the module options. Within the modules on each page, this option should be set to -1 in order to use the default from the module configuration for the page. The problem is that some users were not updated for a variety of reasons and therefore are broken. The 0 value tells the system to retrieve all posts in the topic and that breaks things. Running the query above sets the users to the proper value and fixes the display of everything from topics to large forums.
                        Excellent, after the upgrade about 30% of my users were still set to 0, so I just now changed them. That should helpfully stem the tide of users freaking out about super slow load times of entire Topics.

                        Comment

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