Author Topic: Forum arthritis  (Read 4529 times)

DrXavierTColtrane

  • Dark Lord
  • *****
  • Posts: 737
Forum arthritis
« on: August 24, 2016, 09:04:31 AM »
This will be somewhat curt because of not wanting to struggle with editing, given the way the forum is acting. It is not meant to be unhelpful.

1) I am skeptical of what the host company has offered as its diagnosis and "cure." The game server is hosted on Google IIRC. Is the forum hosted somewhere free? Because if the provider is being paid their service level is atrocious.

2) The forum is not busy enough to cause performance issues due to too many processes with only a couple of caveats. One caveat is a process leak, but given that SMF is a widely used platform that it would have a major process leak seems unlikely. If the site were actually busy, then limiting us to 1 process would make the situation much worse than it already is. It would be unusable.

3) Otherwise, the only reason I can see for this site causing a database performance is the sheer quantity of posts that have built up over the years. It is possible that SMF does not scale (i.e., its queries are not optimized) to deal with so many topics, posts, and paraphernalia that have accumulated over time.

All that said, the response by the hosting service is just unacceptable (assuming they're not free). I have never witnessed any serious performance problem on the forum *until* they did this. Hence, I find it very hard to believe the forum was causing problems before now.

Does the hosting service provide any shell access or other means for Blue etc. to investigate the issue and try to fix it? For example, using database tools to clean up and optimize the database?

If the service is free, I would take this action as a sign the landlord either wants to start charging for better treatment or is nudging me toward the door.
For everything that's lovely is
But a brief, dreamy, kind delight.

Purist

  • Red Academy
  • Dark Power
  • ******
  • Posts: 2277
Re: Forum arthritis
« Reply #1 on: August 24, 2016, 10:50:29 AM »
This happens from time to time. Always have. I don't know how they fix, but they always do it.

DrXavierTColtrane

  • Dark Lord
  • *****
  • Posts: 737
Re: Forum arthritis
« Reply #2 on: August 24, 2016, 09:46:14 PM »
Since perhaps 95 percent of the content of this forum is legacy information that is useful as a reference but no longer needs to be dynamic, one idea would be to dump that portion to static HTML Web pages and then keep only the 5 remaining percent living in the database.

I've looked around for an existing tool that does this for SMF, but haven't found one. Several users, however, have asked over the years for such functionality.
For everything that's lovely is
But a brief, dreamy, kind delight.

Bluebomber4evr

  • Head DM, Developer and Ravenloft Trivia Guru/Community Council
  • Administrator
  • Dark Power
  • *
  • Posts: 20622
    • http://www.nwnravenloft.com
Re: Forum arthritis
« Reply #3 on: August 24, 2016, 10:49:56 PM »
The forums are not hosted on Google and are hosted separately from the game server.

The simple fact is that we probably didn't do proper maintenance and let some parts of the forums get far too big over the last ten years, so the backups just got out of control. That's on me and I'm sorry. I have people working on it right now so it shouldn't be a problem much longer.

Bluebomber4evr: The Justice, not you, since 2002

DrXavierTColtrane

  • Dark Lord
  • *****
  • Posts: 737
Re: Forum arthritis
« Reply #4 on: August 25, 2016, 08:51:38 AM »
BB4: I don't intend any criticism of you guys because clearly you're having to put a lot of work into fixing this.

I do question a service provider who throttles your database process count in response to backups becoming too big.
For everything that's lovely is
But a brief, dreamy, kind delight.

Bluebomber4evr

  • Head DM, Developer and Ravenloft Trivia Guru/Community Council
  • Administrator
  • Dark Power
  • *
  • Posts: 20622
    • http://www.nwnravenloft.com
Re: Forum arthritis
« Reply #5 on: August 25, 2016, 09:30:34 AM »
I looked at our logs and our database is causing system lag once a week, which is when automated backups occur.

One of my best friends, who works in IT, described it this way:

Quote
My best guess is that you’re I/O, CPU or memory bound during the backup and DBs react negatively to running out of resources more so than other programs. Backups cause the data in the DB to balloon to 2x-5x times the size before compression occurs and a lot of data means a lot of compression by the CPU. So the system probably starts swapping threads and thrashing which would set off the alarms at your hosting provider

Bluebomber4evr: The Justice, not you, since 2002

DrXavierTColtrane

  • Dark Lord
  • *****
  • Posts: 737
Re: Forum arthritis
« Reply #6 on: August 26, 2016, 08:50:16 AM »
BB4:

Quote
Addendum: I've already made a backup of the database so it will be possible, though difficult, to retrieve older content that has been deleted. This will only be done in special circumstances, however.

Is the underlying database MySQL? How big in gigs is the backup?

I have loads of available storage and could likely host a "read-only" version of the forum/db to keep this old content accessible, provided that's something you want. If you do, we could probably work out the transfer via Google drive.

Otherwise here's something you may already be aware of:

https://www.siteground.com/tutorials/smf/optimize_smf.htm

Quote
I had to delete the search index. Now ordinarily, an index makes searches faster and thus easier on the database. But it was huge because our forums were huge. I will recreate it after the pruning is done and the restriction is lifted, but until this is all squared away, please use the search function sparingly, as index-less searches are a lot slower.

An alternative that I find usually works better than the site's search function is to go to Google and enter

whatever search term I am looking for site:www.nwnravenloft.com

(A drawback is I assume the above works only on public threads.)
« Last Edit: August 26, 2016, 08:53:26 AM by DrXavierTColtrane »
For everything that's lovely is
But a brief, dreamy, kind delight.

DM Tarokka

  • Dark Power
  • ******
  • Posts: 6923
  • Fata volentem ducunt, nolentem trahunt
    • Facebook page
Re: Forum arthritis
« Reply #7 on: August 26, 2016, 09:03:30 AM »
The forum has been the mirror of our server. If some contents could go into "only read" format elsewhere, linked to the forum somehow, would be the best thing. I know we're on some emergency, but I feel quite bad about how much content could be irreversibly lost in this. Useful stuff, but also "memory" stuff. I believe the best choice will be made.
http://www.facebook.com/CiaranII
Pokemon Go! 5688 6574 4676

Soren / Zarathustra217

  • Lead director, main scripter, nutty geek, Community Council
  • Administrator
  • Dark Power
  • *
  • Posts: 12979
Re: Forum arthritis
« Reply #8 on: August 26, 2016, 10:01:11 AM »
DrXavierTColtrane, I agree that it's a bit of an unusual approach the webhost deployed here, but at least they've already lifted the limitations somewhat.

In principle, using google for searching is a good idea, but it has limitations here as most forums are not visible to non-registered users. You wouldn't (forwardly) be able to search for posts by a specific user either or in a specific forum section.

DrXavierTColtrane

  • Dark Lord
  • *****
  • Posts: 737
Re: Forum arthritis
« Reply #9 on: August 26, 2016, 12:16:25 PM »
Quote
DrXavierTColtrane, I agree that it's a bit of an unusual approach the webhost deployed here, but at least they've already lifted the limitations somewhat.

One problem with their brute response is that it has out of necessity caused quick reaction in admins and a panic among users by making the situation so much worse. If one month ago, say, the forum was fine, then perhaps it's reached a performance tipping point...but only in the last month. In other words, we need to go on a diet--and we also don't want to be in this situation again in a few months, so let's lose some pounds--but we ought not need an amputation or two either.

IMO (and operating only on what I can see and guess, whereas I don't see any faction threads or user mailboxes), I would suggest:

1) Making everyone get rid of PMs more than 30 days old and making that time limit enforced going forward.

2) Doing as BB4 has done and locking the more monstrous threads that are "of the moment"--such as Internet Hilarity. Tavern stuff. Then purging those. They're fun but many links no longer work, and for those that do work...it's external data that isn't dependent on the server for its existence. I'm torn about the Positive Roleplay thread because that's effervescent too, but all the gratitude expressed there is a warming memento to the community.

3) See where that gets us before further surgery.
For everything that's lovely is
But a brief, dreamy, kind delight.

DM Tarokka

  • Dark Power
  • ******
  • Posts: 6923
  • Fata volentem ducunt, nolentem trahunt
    • Facebook page
Re: Forum arthritis
« Reply #10 on: August 26, 2016, 12:57:05 PM »
For sure posts which can be erased are those in the techincal subforum, suggestions and feedback. 90% of the things are passed away (fixed bugs, but also all the topics for things which cannot be fixed ever)
http://www.facebook.com/CiaranII
Pokemon Go! 5688 6574 4676

Bluebomber4evr

  • Head DM, Developer and Ravenloft Trivia Guru/Community Council
  • Administrator
  • Dark Power
  • *
  • Posts: 20622
    • http://www.nwnravenloft.com
Re: Forum arthritis
« Reply #11 on: August 26, 2016, 01:52:06 PM »
Yeah we'll start with those first for now. If it still doesn't help we'll get more drastic.

As we are using a shared hosting service, they are within their rights to do what they have done, but it would have been nice to get a warning before they placed the restriction. I might look into alternative hosts once we get close to the renewal period.

Bluebomber4evr: The Justice, not you, since 2002

DrXavierTColtrane

  • Dark Lord
  • *****
  • Posts: 737
Re: Forum arthritis
« Reply #12 on: August 27, 2016, 01:18:11 PM »
Thanks for all the hard work you guys are doing on this. Not to add to your chores, but is this mod a viable option to add to the forum to allow everyone to offload their PMs?

http://custom.simplemachines.org/mods/index.php?mod=627

Particularly if the option for downloading the entire inbox at once works, then that would be a big help.

Thanks.
For everything that's lovely is
But a brief, dreamy, kind delight.

DrXavierTColtrane

  • Dark Lord
  • *****
  • Posts: 737
Re: Forum arthritis
« Reply #13 on: August 30, 2016, 06:42:06 AM »
Another thing I've noticed is that sometimes the main forum page loads in fits. Most of it will load, and then it pauses.

One of the last things on the page is the part at the bottom where it says "Most Online Today: X. Most Online Ever: Y" and gives a date (July 2, 2007).

If that information is cached in some way, then it doesn't much matter to performance. But if it is dynamically checked every single time the front page loads, then I can see that being an extremely database-intensive function on a database that's around a decade old (lots of days to compare and calculate the busiest).

It might be worth looking at commenting that part out of the php script so as to turn it off.
For everything that's lovely is
But a brief, dreamy, kind delight.

BraveSirRobin

  • Dark Power
  • ******
  • Posts: 2028
  • "Common sense is not so common." - Voltaire
Re: Forum arthritis
« Reply #14 on: September 01, 2016, 02:44:02 AM »
If I'm reading this correctly, the Forums are reaching a size during their back-up that is too big for the host to properly process, and thus they've done.. Something to cause an issue?

I might be a little out of place in asking here, but what is the gross size of the Forum's backup pre-compression and post-compression?

DrXavierTColtrane

  • Dark Lord
  • *****
  • Posts: 737
Re: Forum arthritis
« Reply #15 on: September 01, 2016, 06:37:38 AM »
Although I would also be curious about the size of the database, my understanding is that backups being the cause turned out to be a red herring as the backups were not being performed.
For everything that's lovely is
But a brief, dreamy, kind delight.

Iluvatar / Madness

  • Ex-Community Council
  • Developers
  • Dark Power
  • *
  • Posts: 3487
  • The Madness
Re: Forum arthritis
« Reply #16 on: September 01, 2016, 09:34:59 AM »
Another thing I've noticed is that sometimes the main forum page loads in fits. Most of it will load, and then it pauses.

One of the last things on the page is the part at the bottom where it says "Most Online Today: X. Most Online Ever: Y" and gives a date (July 2, 2007).

If that information is cached in some way, then it doesn't much matter to performance. But if it is dynamically checked every single time the front page loads, then I can see that being an extremely database-intensive function on a database that's around a decade old (lots of days to compare and calculate the busiest).

It might be worth looking at commenting that part out of the php script so as to turn it off.

Calculating the "Most Online Today" and "Most Online Ever" isn't really demanding there are 2041 member on the forum. Going through 2041 entry and making a count one field to get the number of online user to then store that value in a table is a matter of milliseconds. That is clearly not a source of lag.

The query to count the number of user online from the DB would be something like

SELECT COUNT(onlineStatus) FROM user WHERE onlineStatus = 1

A query like this going through 2041 user is a joke.

Anyway, the problem seems to be solved for now, until the number of post gets to high again.
Iluvatar
Iluvatar-2
Iluvatar NCE
Madness

DrXavierTColtrane

  • Dark Lord
  • *****
  • Posts: 737
Re: Forum arthritis
« Reply #17 on: September 01, 2016, 10:23:24 AM »
Another thing I've noticed is that sometimes the main forum page loads in fits. Most of it will load, and then it pauses.

One of the last things on the page is the part at the bottom where it says "Most Online Today: X. Most Online Ever: Y" and gives a date (July 2, 2007).

If that information is cached in some way, then it doesn't much matter to performance. But if it is dynamically checked every single time the front page loads, then I can see that being an extremely database-intensive function on a database that's around a decade old (lots of days to compare and calculate the busiest).

It might be worth looking at commenting that part out of the php script so as to turn it off.

Calculating the "Most Online Today" and "Most Online Ever" isn't really demanding there are 2041 member on the forum. Going through 2041 entry and making a count one field to get the number of online user to then store that value in a table is a matter of milliseconds. That is clearly not a source of lag.

The query to count the number of user online from the DB would be something like

SELECT COUNT(onlineStatus) FROM user WHERE onlineStatus = 1

A query like this going through 2041 user is a joke.

Anyway, the problem seems to be solved for now, until the number of post gets to high again.

I said it *could* be demanding because you don't know without knowing how the query is written or which table indices exist. The query you provide assumes there is a field indicating online status. Do we know that exists? No, but suppose it does. Then that is in itself creates load.

If such a field exists to be accurate as you portray it, then 2,041 users must be updated to reflect whether they are online or not. We know that the timeframe is "in the last 15 minutes," so how do you do that? Again, there are good and bad ways to keep that info accurate.

In any case, however, that is not the part I mentioned as being problematic (unless it's cached). The trickier part is "most online ever." A simple  flag such as you posit does not allow that to be calculated. Again, there are faster ways of keeping track of such information and slower ways. Without knowing the internals of the database it's speculation as to whether this value is calculated in an efficient method or not. Two thousand users is not a lot, but when you get to 2,000 users x 365 days x a dozen years, you could conceivably be dealing with almost 9 million records to check. (Besides table scans, something that can crush a database is a JOIN query because of the multiplicative growth involved.)

Frankly, I don't see why this forum should have problems when nowadays fewer than 50 people are on it at any given time. More guests (likely search bots) are on it, than actual users. One way of improving performance, therefore, might be to block all guest access.

The image heavy threads that refer to external sources for their data also can hold a Web server process for a long time, though they shouldn't directly impact the speed of queries.

The reason I offered the footer as a possible problem is as I explained: from observation. I have noticed many times that the first part of the forum loads quickly and then it hangs. I looked at what wasn't loaded yet to see what sorts of queries might still be loading at that point. IMO knowing the most people ever online is fairly useless as it hasn't changed in almost 10 years. So *if* it's a burden to calculate--which I conceded I don't know--I'd punt it.

FWIW, I don't consider myself a database expert, but  I have managed databases with 200 gigs of data and 200,000 users before, 40,000 of whom were still active. It's difficult to guess the causes of a problem in a blackbox situation because we don't even know the resources available to the forum. As was demonstrated last week, if you limit the resources sufficiently (i.e., one process), then even 20 simultaneous users are miserable.
For everything that's lovely is
But a brief, dreamy, kind delight.

DrXavierTColtrane

  • Dark Lord
  • *****
  • Posts: 737
Re: Forum arthritis
« Reply #18 on: September 01, 2016, 11:18:36 AM »
Quote
The image heavy threads that refer to external sources for their data also can hold a Web server process for a long time, though they shouldn't directly impact the speed of queries.[

Actually, I take that back. If the php script opens the connection to the database and then doesn't close the connection except as one of its final actions--which is likely--then the database itself could also have connections held by pages that have longer load times (such as those with links to missing external images).
For everything that's lovely is
But a brief, dreamy, kind delight.

Iluvatar / Madness

  • Ex-Community Council
  • Developers
  • Dark Power
  • *
  • Posts: 3487
  • The Madness
Re: Forum arthritis
« Reply #19 on: September 01, 2016, 01:03:26 PM »
Quote
If such a field exists to be accurate as you portray it, then 2,041 users must be updated to reflect whether they are online or not. We know that the timeframe is "in the last 15 minutes," so how do you do that? Again, there are good and bad ways to keep that info accurate.

A field like this is update on user connection, onConnection the value of "online" would be set to true. It's set to false when a user session ends. It's a very simple operation. It's been a while since I have done web stuff, but I'm pretty sure it was something like that.

IMO, I highly doubt a feature like that is causing lag since it's a basic operation on a table containing only 2041 entry.


Quote
In any case, however, that is not the part I mentioned as being problematic (unless it's cached). The trickier part is "most online ever." A simple  flag such as you posit does not allow that to be calculated. Again, there are faster ways of keeping track of such information and slower ways. Without knowing the internals of the database it's speculation as to whether this value is calculated in an efficient method or not. Two thousand users is not a lot, but when you get to 2,000 users x 365 days x a dozen years, you could conceivably be dealing with almost 9 million records to check. (Besides table scans, something that can crush a database is a JOIN query because of the multiplicative growth involved.)

Dealing with the most user online ever is pretty easy as well. You don't need to record the online status of everyone for every day of every year.

Let's say you have a table containing the most user online ever and containing also the most user online today.

If the "most user online today" is updated and verified every time a user login or logout. The value of "most user online ever" is compared to the "most user online today". If "most user online today" is bigger than "most user online ever" you update the "most user online ever" accordingly. Very simple and not heavy at all on the database. Updating that on every login/logout might be a bit too much and could be check like every 10 or 5 min to reduce the number of query on the database.

That way the "most user online ever" is not calculated every time the footer is loaded, same for the "most user online today".

I have no idea how it actually work, but I highly doubt that record of everyone activity every day is kept only to calculate those values, it would create to much data for no good reason.


IMO since SMF have been there for quite a while I'm pretty those operation have been optimized more than once since they were implemented and I doubt simple feature like these who have been in SMF for a long time are causing enough lag to make a difference.
Iluvatar
Iluvatar-2
Iluvatar NCE
Madness

DrXavierTColtrane

  • Dark Lord
  • *****
  • Posts: 737
Re: Forum arthritis
« Reply #20 on: September 01, 2016, 01:57:30 PM »
Illuvatar: We are arguing in the dark. You may have the last word.
For everything that's lovely is
But a brief, dreamy, kind delight.

BraveSirRobin

  • Dark Power
  • ******
  • Posts: 2028
  • "Common sense is not so common." - Voltaire
Re: Forum arthritis
« Reply #21 on: September 01, 2016, 10:18:48 PM »
I like last words.

Soren / Zarathustra217

  • Lead director, main scripter, nutty geek, Community Council
  • Administrator
  • Dark Power
  • *
  • Posts: 12979
Re: Forum arthritis
« Reply #22 on: September 02, 2016, 03:38:24 AM »
As an attempt to claim the last word: no matter if it's relevant or accurate, we still appreciate your input and your desire to help.

I think (hope) we've improved the situation sufficiently for now. Besides the stuff going on behind the scene, splitting large topics will be the main change in our operation. Hopefully this means we can avoid removing too many of our older topics.

Mark Johansen

  • Developers
  • Dark Power
  • *
  • Posts: 1581
Re: Forum arthritis
« Reply #23 on: September 02, 2016, 06:05:13 AM »

Iluvatar / Madness

  • Ex-Community Council
  • Developers
  • Dark Power
  • *
  • Posts: 3487
  • The Madness
Re: Forum arthritis
« Reply #24 on: September 02, 2016, 08:45:20 AM »
Iluvatar
Iluvatar-2
Iluvatar NCE
Madness