Posts by DynamoGeek

    Thanks for the replies, fellows!


    Alexander, that sounds awesome! That’ll definitely get me to upgrade more quickly. 😄


    If I recall, I can safely rebuild at anytime, though it’ll slow down my forum a bit?

    I have a large forum (~100 million posts) that I want to migrate from Woltlab Core 3.0 to 3.1 and from Burning Board 5.0 to 5.1. What troubles am I likely to encounter during the upgrade based on my forum size and what can I do to mitigate those issues?

    Wowrack looks to be a hosting company. Chances are a (potentially) nefarious third party is 'shotgunning' (brute force scanning and crawling huge portions of) the internet looking for weaknesses in websites so they can do whatever it is nefarious third parties do.


    IP blocks have become less and less effective in recent years mostly due to the exhaustion of the IpV4 address space (IPs need to be constantly changing to help alleviate the exhaustion).


    In the case of blocking a hosting company, though, you can generally (but not always) block the entire range of IP addresses that a hosting company uses. A brief Google search didn't immediately reveal Wowracks IP range. If you do find they have an IP address range, TheSonic's suggestion of IPTables (or some other firewall software) is the most performant choice for blocking an IP range.


    It's not very likely to do much good, but you could also contact the abuse email for Wowrack, found here.

    Yep, I'm satisfied with the end results. It's a problem that can be alleviated through the (drop dead) simple plugin. Throwing a little more hardware at it would help even more (but I'm unlikely to do that; this is one of the very few things that would benefit).


    Always a pleasure working with *you*, my good man! I think I'll have another slow query or two, but we'll find out after the recent updates I made (that extra 30GB of RAM will probably help :D).

    Actually, you could write a plugin that listens on the calculateNumberOfPages event, which is the last event before counting items, which takes place before reading any actual data. You can then override the public properties $sqlOrderBy and $sqlLimit (defined in \wcf\page\MultipleLinkPage) with your own values and you're done. Boom, update-proof.

    Indeed. That was pretty obvious to me after I posted stating that I still wanted to trigger the event. :D Sometimes one misses the forest for the trees.


    This query

    SQL
    SELECT post.postID AS objectID
    FROM wcf.wbb1_post post
    JOIN wcf.wbb1_thread thread
    WHERE post.userID = {user_id} AND post.isDisabled = 0 AND post.isDeleted = 0 AND thread.threadID = post.threadID AND thread.boardID IN ({board ids})
    ORDER BY post.postID DESC LIMIT 20 OFFSET 200;

    results in this profile

    As you supposed, it looks quite like the previous, but without the 1-5 minutes of Creating sort index. :D

    On second thought, I assume that it is much faster to have only the last 1k posts available for browsing, so you could just cap it at 1k in order to achieve low OFFSET values.

    This surely helps. Though, given the test user I mentioned, the query begins to fail to return in the 30's (page 30-39). Occasionally (for other users) the query will fail on the first page, given the need to grab too much new data off the disk.

    However, removing the order by post.time(as you mentioned) from the query also helps substantially. I don't really like modifying the core, but given the options, I guess updating UserPostListPage::$sqlOrderBy and overriding UserPostListPage::countItems isn't the worst. Even if it gets overwritten by a future update, it isn't like it'll wipe out a bunch of functionality or make my forum unusable. :D The countItems override calls the parent to make sure 1) the events are still fired correctly and 2) how many pages we actually have (maybe it's less than the maximum we're willing to display :P )

    I definitely agree that it's still unusable; just as a matter of improvement, it was dramatic. :D


    I also definitely agree that depending on the internal MySQL/Inno DB counts would be pretty much pure botchery.


    I'll take a poke at the ORDER BY stuff tonight (8 hours from now or so ) and let you know if I find anything dramatic.


    As always, thanks for your quick and effective assistance. :)

    The effect of the above is dramatic. Additionally, the pages are left in the cache, and the follow up queries occur in a few tenths of a second.


    Anything you need, my good man! Looks like it's all in sort index creation.

    Profile:


    For a user with ~76,000 posts):

    Code
    EXPLAIN SELECT post.postID AS objectID
    FROM wbb1_post post
    JOIN wbb1_thread thread
    WHERE post.userID = {a user id}
        AND post.isDisabled = 0
        AND post.isDeleted = 0
        AND thread.threadID = post.threadID
        AND thread.boardID IN ({a bunch of board IDs})
    ORDER BY post.time DESC, post.postID DESC LIMIT 20 OFFSET 71780;
    Code
    +----+-------------+--------+------------+--------+-----------------------------------------------------+------------+---------+-------------------+--------+----------+---------------------------------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+--------+------------+--------+-----------------------------------------------------+------------+---------+-------------------+--------+----------+---------------------------------------+
    | 1 | SIMPLE | post | NULL | ref | threadID,threadID_2,isDeleted,isDisabled,userToPost | userToPost | 7 | const,const,const | 249604 | 100.00 | Using index condition; Using filesort |
    | 1 | SIMPLE | thread | NULL | eq_ref | PRIMARY,boardID,boardID_2 | PRIMARY | 4 | wcf.post.threadID | 1 | 100.00 | Using where |
    +----+-------------+--------+------------+--------+-----------------------------------------------------+------------+---------+-------------------+--------+----------+---------------------------------------+

    I don't suppose anything has changed related to the UserPostListPage issue I described above?


    I'm currently working to improve the User experience on my forum in a general way, and being able to fix those would help! It's not a huge increase, but every little bit helps. :)

    Now if it was only the most active. *big dramatic sigh* I'm trying!


    I've run into a few things like this, but Alexander has always been quick to respond and very capable.


    Like Alexander mentioned, for example, one of the things that's dog-slow is that little post indicator Throwholics is mentioning. When you have a few hundred thousand, maybe a million posts, it works fine. With a bajillion, it gets really, really slow to execute. Instead of doing that query "every so often" and caching it, I just fib just a little bit and return the max post ID instead. The alternative is letting a really long query hang up a bunch of queries waiting after it. It's off by a bit, but percentage-wise I'm still quite okay with it. :P I could grab a bit of info from the InnoDB info, but that's more effort than I'm willing to go through. :D

    Overall, I couldn't be happier with Woltlab. The friendliness of the community is far superior to SMF in general, and (granted, due at least in part to its paid nature) the support is immensely superior.

    The error I got was through the ACP. I also have similar failure issues with dailyCleanUp, userGroupAssignment, userBan, and emptyRecycleBin.


    I ran the query you gave and received a count of 182. After attempting to run the query userQuit again (it failed the same way), I ran the query again and received a count of 182 again.


    I sent the results of the InnoDB status command to you via email.

    For awhile now my UserQuitCronjob has failed to execute. Stack trace below. Let me know what information I can provide to help locate the cause.


    I was afraid of that. :P


    I've upped my max_execution_time for queries to 10 seconds to account for the queries that are grabbing data and caching it, but allowing a 90+ second query just isn't going to work out. I'm not quite sure what I'll end up doing about this, but my members aren't beating my door down with complaints about it, so I'll likely just let it be until it becomes an actual problem.


    I have ~40 slow queries in my slow query log from the last ~4 hours. Though my threshhold for a "slow query" is a rather substantial 9.9 seconds. I've attached some aggregate information in the hopes it'll be of use to you in some way in the future.