Inter-Level Experience Reset - A Possible Solution

[Jpec07]

So I know this will probably be lost, but as a data engineer, I saw the most recent notice and thought that there has to be a better way.

I don’t know what SE’s backend looks like and I don’t know what the data storage for FFXIV looks like. I can only assume it’s a relational database. Given that, it seems like there’s an easy solution to this problem that wouldn’t reset people’s experience. The post made it seem like they’d need to do a mathematical operation for every row representing accumulated experience, with a specific constant to multiply the value by for each level.

This is expensive from a performance standpoint, but also incredibly inefficient, because math operations are costly when you’re trying to do a data operation, especially across what’s probably several hundred million data points.

Instead, just make a table ahead of time that indexes the existing values and maps them to a new one. The table only needs three columns: level, old_exp, and new_exp. Create a compound index on level and old_exp, and then add indices and FK references to wherever those values are stored in the character data. Then, when you do the squish, rather than doing a math operation, you’re just doing an update. The math operation I can imagine taking 2-3 days to calculate every single column, but doing the in advance and storing every possible solution is both relatively cheap and the most efficient way to do it.

I understand if the team doesn’t have bandwidth for it, but this would be a good solution to a complex data problem.

[Jpec07]

How do you do it in advance? The game is up and running until the very end, the maintenance in case of the SHB expansion lasted only one day: https://na.finalfantasyxiv.com/lodes...e2e18da8fd6962

They have to do everything in one day, I'm pretty sure they considered all possible solutions, and reset to 0 xp was simply the most feasible one.
Imo it's not a big deal, we know it in advance, just stop leveling after reaching a full level.

The calculation is the part that takes a long time, and if they’re doing it for every data point, there’s bound to be repeat calculations in there. So instead, make a solution lookup table.

You calculate every possible value and what it maps to in advance and store them in a separate table. Going by this chart, the table would only need ~325M rows (not nothing, but also not unreasonable, especially if you partition it right), and would be fully static. This would mean that it could be indexed extremely easily, and the indices would have zero chance of going stale. Rather than calculating everything, you’re just joining another table, which is extremely simple in terms of design, implementation, operational complexity, and execution.

Doing a full table update over ~2B rows without any math would take maybe 15 minutes.

And perhaps they did consider it, but perhaps not. I just saw the operational complexity and know there’s an easier way to do it (because I’ve done it).

[Souljacker]

Just throw a hash map at the problem, ya? CompSci 101.

I gotta say, I'm with you on this one. It doesn't seem like their explanation really tracks, so either it's not the real problem they are facing or they had already decided this was how they were handling it no matter what. Either way, we're losing the xp.

[Krojack]

IMO, resetting the experience bar back to 0% of the current level is an extremely small price to pay.

Even if I had a job at 90% though level 79 and it got reset to 0% I wouldn't care. It takes no time at all to get that level anyways. Hell, you can get little over half of that level being afk 70% of the time in an MSQ Roulette.

If people care that much then push all your jobs just over the line now and your loss will be almost nothing.

[RitsukoSonoda]

They're likely recalculating the entire exp value scaling through the whole chart along with the amounts earned from the game's activities. Individually scaling partial progress shouldnt be too much of an issue. But when it's an inconsistent value that has to be done seperately for every job, on every character, on every datacenter, for every account it adds up.

In the end I don't expect any change in speed to how fast we lvl up.

[Jpec07]

You realize they are still updating the entire database, right? They may no longer be updating the character experience, but they are still updating the HP and damage dealt, Recipe progress and quality, Required and earned EXP. The math is still happening. And this needs to be done for monsters too, not just players, their damage, the experience rewarded upon kill etc.

Wow okay, there’s a lot to unpack here. Fortunately for you, I feel like educating a complete stranger. HP and other stats are calculated realtime based on a collection of constant values by class/job and level, and then combined together to get the number that is displayed. The values for, e.g. HP, how much damage an attack will do, or how much a spell will heal for are calculated in realtime, and are not stored anywhere. All of this—every example you cite here—is updated with a client update. There’s nothing they need to do on the backend to accomplish this stat squish. I promise you this work is basically already done.

I think you have a fundamental misunderstanding about what data is actually stored about characters, and what’s involved in the stat squish. It’s very likely that, in terms of stats, the only values that are stored are things like:

• The level of each class and job.
• What gear each character has equipped at the moment, what it’s glamoured to, and what its condition is.
• What items each character has in their inventory.
• How much EXP each character has past each level for each job.

There are likely many more data points, but the only things they store are things that need to persist between play sessions. Everything else can be calculated by the client based on that data.

You cannot separate these operations.

Actually, you’d be really foolish not to.

Doing it your way would imo require:

1. To download the database numbers for every player and their all experience points across all affected jobs. There's over 24 million registered players, how many characters are there? 100m? More?
   
2. Doing what you are suggesting separately in another database.
   
3. Running the downscaling update to the game database.
   
4. Uploading the xp numbers from the separate database into the newly downscaled game database, replacing the original untouched xp values.

You cannot cross run these two different operations simultaneously, that would be too risky imo.

So first, let’s clarify some terms here:

• Database: An application designed to store, retrieve, and serve data. These fall into two categories: relational and non-relational (or SQL and NoSQL), the main difference between them being that relational databases tend to segregate like data into separate tables (see below), while non-relational databases tend to keep data together as much as possible (I haven’t worked a lot with the latter, so I fully expect that someone may correct me).
• Schema: A collection of linked tables within a relational database, usually representing all of the data for a unique system.
• Table: A spreadsheet containing similar data points within a relational database, but with added features like indexing (to speed up lookups on big tables), foreign keys (to show how different tables relate to one another), and column constraints (e.g. this column can only be up to this value, this other column can only contain strings, etc.).

So for example, if you wanted to map data about all the inhabitants of all the houses in Milwaukee, you’d have a database for Milwaukee, a schema for housing, and tables for buildings, neighborhood, inhabitants, people, pets, and vehicles respectively. If you wanted to find out how many houses in Milwaukee housed people with at least one vehicle and at least one pet, you’d run a query that looked at the buildings, filtered by which ones are houses, and joined to inhabitants and pets.

When you say “database,” I’m assuming you mean “table.”

Second, with databases, you don’t have to download the data in order to modify it. All databases have support for full CRUD operations: Create, Read, Update, and Delete. Some administrators may disable some of these operations for some profiles, but these are core to every database in existence. All applications that interact with databases need to be able to perform these operations. Because of this, there is no need for them to download the data and re-upload it. They can just do an update statement into a non-active schema, validate that it’s correct, and then promote it. This is probably their plan, they’re just going about it in a silly fashion.

They are on record as saying there’s approximately 2 billion data points that they’d need to update, representing about 10 GB of data. Speaking in terms of relational databases, 2 billion really isn’t that big of a number unless you’re doing computations. This is where I say they’re being silly: based on that update, it seems like their plan was to do 2 billion computations in three steps: (1) look up the scaling coefficient by level, (2) scale the EXP value according to the coefficient, and (3) round the resulting value to an integer. This plan is somewhat straightforward to a layperson, but has a computational complexity of potentially n * (79 + 2), or in our terms, 2B * (79 + 2) = 160 billion (160,000,000,000). That is absurdly complex, and would take days to accomplish, which is likely why they opted to simply not.

However, the total amount of possible values for how much EXP a person can have is fixed. With the current scaling, there is a total of just 325,868,400 values. They can computationally determine what those values will become after the scaling in advance of the maintenance window. It would take one developer maybe 3-4 hours of work to set up the program to do the math and store the results, and then maybe a day to validate that the math was done correctly by the computer. At that point, it then becomes a simple lookup, where you can take the ~2B data points and compare them to the table where the math was done to determine what the value should be. During the maintenance window, the operational complexity goes down to just 2 Billion (2,000,000,000). A transaction like that will not take that long—again, 2B records and 10GB of data are not big numbers when we’re talking databases. What’s more, it’s very likely that these databases are broken up by data center, so they can likely run the update in parallel, in addition to everything else they’re doing, so the actual numbers involved will actually be smaller than the whole thing.

So really, the way I’m suggesting—effectively a hashmap—would be much faster than what I can only presume their plan was. I’m not sure if they considered this route or not, but it could be accomplished during the maintenance window without too much difficulty.

[Arrhin]

I expect the reasons for the reset are not likely anything more than time and money. We're talking about a team that is stretched to the breaking point that is already dealing with what is likely a laundry list of last minute issues (2 week delay?). We've got hardware shortages affecting server roll outs on top of all that. I'm not educated nor experienced in the field but I have some experience managing very large projects and sometimes things just don't make the cut. Must do. Should do. Nice to do. It just didn't make the cut.

[Jpec07]

I expect the reasons for the reset are not likely anything more than time and money. We're talking about a team that is stretched to the breaking point that is already dealing with what is likely a laundry list of last minute issues (2 week delay?). We've got hardware shortages affecting server roll outs on top of all that. I'm not educated nor experienced in the field but I have some experience managing very large projects and sometimes things just don't make the cut. Must do. Should do. Nice to do. It just didn't make the cut.

That’s true, and I hadn’t thought of that perspective. Depending on what issues they’re hammering out, it’s entirely possible they may not have the 3-4 hours it would take to implement something like this.

[MistakeNot]

That’s true, and I hadn’t thought of that perspective. Depending on what issues they’re hammering out, it’s entirely possible they may not have the 3-4 hours it would take to implement something like this.

3-4 hours only if you skip testing. For something like this it would require thorough testing. Updating the character database is something that just isn't allowed to go wrong. So add a few days (at least) for testing.

[SomeRandomHuman]

Solution: Just make sure you have as close to 0 exp on a level as possible by the 1st. Problem solved with no issue on ether end.

Spending days to try to fix about 1-2 hours of grinding otherwise is a colossal waste of time. Sure, it's fun to think how it could be addressed, but the above solution is just the easiest possible method.