Reducing database load by doing away with ticks

[marginoferror]

I have an idea for reducing database load. This is a bit of a technical issue. But it also has significant implications for gameplay, so I'm posting it publicly as a first step.

Cantr server lag is caused almost exclusively by database load. Computational load is not a significant issue.

There are two general ways of reducing database load: Minimizing the need for writes through elegant design, and minimizing the need for reads by caching. This addresses the former.

The current "tick" system of progress on various things (projects, travel, etc) made every in-game hour was designed to "minimize" database writes. Every hour, the state of every project is calculated and advanced in turn. This means one database write per project (and per traveler, and per ship) per hour. If this system was accelerated to three times an hour, then it would require three times as many database writes. This system is suboptimal for both performance and gameplay.

However, there is another way to handle these issues without resorting to "ticks" at all. Instead of database writes per tick, we could compute progress on the fly (as observed) and instead make database writes per change. By "change", I mean adding a person to a project, or changing the heading of a ship, or whatnot.

Let's use the example of a project:

Under the proposed system, the database would store the total amount of work required in the project, the number of people working on it and their skills and tools, the amount of progress at the last update, and the precise time of the last update. Updates would occur when something changes that affects the rate of progress or travel, and in no other circumstance. Imagine a 20-day project that has one worker from inception to completion - under the current system, there would be one initial write and some 80 intermediate writes (each preceded by a read), one each hour. Under the proposed system, there would only be two writes: one at the beginning of the project and one at the end.

Every time the project data needs to be viewed, the client would read this information from the database, compare the time recorded in the database with the current time, and calculate how much work has been done in that time period. This information would not be written to the database - every new view would require a new computation. This sounds onerous, but in fact the number of database reads won't increase over the current system and the computation is trivial for a modern computer (and even if it were a strain on the server, these computations could be offloaded to the client computers without creating a security issue). This means that *every time a project, ship, etc is viewed, the precise progress, location, etc. will be displayed* without having to wait for a certain number of minutes after the hour.

Not only will this reduce the total number of writes and do away with an annoyance to players, but it will also make caching database reads a real possibility. Since the actual information in the database won't change every game hour, it will be much more plausible to store some information in memory. Unfortunately, any caching system would probably require a huge rewrite of the game code, so that idea will have to be shelved for now.

[Solfius]

If the work rate is variable, as in people can join and leave projects at will, how do we measure progress?

For example, if you have one person working on a project for 5 out of 10 days, and a 2nd person joins the project, the work rate is changed, instead of taking 5 days, the project will take half that time. How will this be reflected?




If I understand you correctly, this is the solution:

We don't store the current progress, we store the starting progress (progress at time of last change), time of the last change, and the current work rate after last change.

From that information we can calculate the current progress.

Every time something happens to change the work rate (for example people leaving and joining projects)

1. Current progress is calculated, and stored as "starting progress" (eg, amount of progress at the last change)

2. the time the change occurred is stored

3. the new work rate is calculated and stored.


The biggest problem is detecting the ends of projects.

For resource gathering, there is no definite end, you simply stop working and the correct amount of resources are placed in your inventory (this results in "real-time" resource gathering, where you get resources according to the time spent working rather than the number of ticks)

Projects with a definite end are more difficult, as there still needs to be some kind of "tick" to check for project completion.

Although, reading from a database is faster than writing, the main issue is how often to check if a project is complete, or if a ship has travelled into a land mass, or if a traveller has reached the end of a road.

That said, the main goal of reducing writes would be achieved, and if nothing else, keeping the current 8 tick system to detect project completion would still work - in fact that may be the preferred option as the gameplay will not alter, it'd merely be a behind-the-scenes improvement

[SekoETC]

It has been suggested that resource gathering projects wouldn't have a solid end time, the person would just continue gathering until they cancel the project or someone drags them away with this. In that case there wouldn't be a need for calculating when it's finished, but it might give the worker resources if enough time has passed from the previous check.

With other kind of projects, completion could be checked when someone looks at the project - when the player of a participator loads their character page or someone loads the activities page or someone loads the character description of a participator.

Advancement speed is naturally calculated when a person joins or quits but one problem I see is that it should also be recalculated also if a participator drops a tool needed on the project and doesn't have an extra, or if they didn't have all the required tools and they get handed what they're missing. So basically it needs to run a check every time an item is dropped or handed away, given to a participator or crumbles away.

[marginoferror]

Solfius, I didn't add this in the original post for simplicity, but I was thinking we would simply have a new "reminder" table where the server sets reminders for when projects will be complete (or when other important things will happen like a ship hitting shore, a traveler reaching town, etc). This table will be completely cached in memory as part of the tiny C kernel that runs Cantr (we might be able to do without the table altogether and have the program store data in memory and recalculate all values on restart) and will run a loop with low priority that checks for reminders every so often (preferably on the minute). If this is done cleverly it should take minimal server resources. This part of the implementation is a little bit fuzzy so we should hash out the details, particularly since it will affect gameplay. I don't think a "tick" where the computer runs through and computes and individually checks every single existing project is a good solution, although it would still be less problematic than the current implementation.

Seko, I didn't think of the tool issue. That's a good point. There are a few ways to handle that. One possibility would be to simply not allow people to join projects if they don't have the proper tool (like they can't join projects if not all the materials are committed) and every time an item is dropped, traded, or broken, check the character's current project to see if it was important. We could also mark the items themselves as "in use" in the database, and outright prevent characters from doing anything with them unless they leave the project (like characters can't move out of the area without leaving the project).

[Solfius]

I prefer marking tools as in use, so they cannot be dropped or passed if they are in use, it seems simpler.

I think that checking for project completion is the hardest part of the suggestion to get right.


Just something that I thought of as I was about to post...


We don't want to duplicate information where possible, why store something we can calculate as required (unless programming overheards are too great)

Would it be simpler to store in some manner, the projected project end, and then use a query to find projects that are about to end, and run the appropriate action?



Having active projects stored in memory may also be an alternative.

[Doug R.]

Let's look at the game-play repercussions of this suggestion. Cantr is a slow-paced game. Right now, "frequent peeking" only gives me the ability to carry on extended conversations with other players that are frequently peeking, or to react to things more quickly that other players.

If we move to a real-time system, which this is, then frequent peekers will have more advantages than they have now.

Example: I'm chasing a pirate ship. With this proposal, I could tweak my course every minute until I'm exactly on top of the pirate ship to dock to it. Under the current system, I have to line up and guess, making chases difficult (and possibly more fair).

I guess the greatest affect would be on sea travel, since after 5 mins of thinking I can't come up with anything else earth-shattering.

I like the concept in general, but it sounds pretty major. Might as well make Cantr III if you're gonna have to gut most of the code.

[marginoferror]

You're right, that would have to be addressed. But I think the current system doesn't solve that problem either, and the "rough edges" of an update every game hour make the problem only less predictable and more confusing to new players, and not easier to manage.

My opinion based on what I've seen of the code is that this wouldn't require a major gutting. Probably the biggest change would be the event system to handle finished projects (depending on what resolution we decide). However, I'd have to get a second or third opinion from a better programmer before I could say that with certainty.

Of course, since nobody seems to have access to the code, even a trivial change, like fixing the link to the forum on the player page (which has been broken for as long as I have been playing) is out of our reach.

[ceselb]

You don't do the same amount of work all the time. Tiredness varies randomly between 0 and ~3% during the day or you might attack someone, you might be injured when you start or become injured while working or eat healing food while working.
The random fluctuation in the day may be removed, but it's still a bit from the 2 writes scenario.
Anyway, this would only remove most of the writes, you'd still have reads every tick though those tend to be less costly.

I think there's something else going on, the ticks can't be so bad imo.

[marginoferror]

I didn't think about tiredness. Unless those gameplay mechanics are changed that could be a problem. Injury is probably manageable, though.

If it's not the ticks I don't know what it is. But something is causing really serious database lag.

[Solfius]

it may not be any one thing, but a combination of unrelated things, that when combined, result in the issues we have observed.

Such is not uncommon in large systems, it's called emergent properties: characteristics of a system that do not emerge until it's assembled

[Tiamo]

I remember the game of Utopia had problems with database access when they started. Every one-hour tick took about 15 minutes to process(!), for a game that is way less complicated than Cantr.
They solved it by using a much more efficient method of database access, not having to establish the database link many times, but just once for the whole process. During processing the database was locked, so users would have to wait until the end of the update process to have access again. That was no problem after the improvements, as the update process took just a few seconds then.

The projects process in Cantr usually runs for at least 10 minutes, not blocking the database, so it must do a lot of internal processing (unlikely), or access the database multiple times (probably the case). Maybe changing this to one big access (doing all updates at once) might help the situation.

Making project update event-driven instead of tick-driven might lower the database load a bit (it won't help for shorter projects because of the increased administration), but it has the disadvantage of a much more complicated (and consequently error-prone) process. Variations in project speed caused by damage/healing, fatigue and random variations (all being project events!) might even annihilate the efficiency gains entirely.

Edit: skill improvement (or decline) is another complicating factor in predicting project progress.

[joo]

I've had a few passing thoughts on this idea, as opposed to the tick system, before, and what always put me off was the need to update the project for every event that could affect it - and the list of events is even longer than I thought. A possible way of minimising the processing involved in checking events (although it might possibly cause more processing, not less) is to monitor each variable that is essential to progress - be it the weight of a ship, the skill of each person participating in the project or someone's tiredness - and each time the variable is changed, which will always be the result of a related event, such as someone being attacked while working on a project, then you know for sure that the speed or direction of the project needs to be updated. As I see it, this would mininize unneccesary database reads and writes. However, never having seen the Cantr code, I don't know whether such a system would be plausible without overhauling every 10th line of code.

This does sound like something which could definitely be done if Cantr III were made - there would be no point not learning from the mistakes of Cantr II - but obviously there is going to be a limit on how far it can be implemented with the current game architecture and level of support from the programming and admin departments.

[marginoferror]

Tiamo, that's a very interesting insight. I didn't realize opening and closing a database thread would be so burdensome. It's something to take a look at if I ever get access to the relevant code.

Ideally, we would have a client-server-database architecture instead of a client-database architecture. The server would keep a connection to the database open at all times and all requests would pass through it. This would be much faster and frankly a much better design in the first place. Unfortunately, this would require a total redesign and probably proficiency in a language other than PHP, so I'm not holding my breath. Maybe it is time to think about Cantr III after all.

[Solfius]

here's something that may be insightful:

http://www.jpipes.com/index.php?/archiv ... hings.html

an article on reducing database calls and unnecessary connections to mysql databases. Perhaps more interesting though, is the brief section on caching towards the bottom

[Razorlance]

Forgive any ignorance on my part but what about maintence of the actual data, i.e.

Does completed projects, redundant data, etc get removed or archived to another database, thus reducing the amount of data to read?
Is the data suitable indexed for efficent reading and are any indexes that are available used appropriately?

If either of those things are overlooked it can massively slow down database access, believe me I've been there, but I have no idea how MySQL handles things like this, so I thought I would ask.