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).

[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.

[Anonicat]

--snip--

First. HP isn't all calculated on the client side. The items are being stat squished so a calculation is being ran against those stats for sure. And HP is calculated from items, levels etc. Levels arent being stat squished per say.

They are just taking out the calculation in the loop. If im honest a gut feeling tells me what you are suggesting may be more time expensive. It'd be easier to run through and just normalize everything.

Say level 79 with 37,650,000. if you have 16,500,000 EXP out of 37,650,000 , just divide and multiple. 16.5/37.65 = .438. Take that value and multiply it too the max value of the new exp value table would only need 90 btw. Just a tad smaller than 325 Million rows. This calculation is easily performed in an UPDATE SQL statement im sure, or an SQL script for that matter.

^ That would be easier.

But now take away that last part, and its just Number of Characters times the # of datasets/points they have to affect with it, and just set to 0 that is no calculation, and its faster.
If you start adding in joins, you will start incurring huge time penalties.

[Anonicat]

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).

You're not suggesting making a table row for for literally every experience point are you?.... then trying to join it... with other tables are you....

[Jpec07]

You're not suggesting making a table row for for literally every experience point are you?.... then trying to join it... with other tables are you....

That’s precisely what I’m suggesting. A nonvolatile table with 325.8M rows, fully indexed and partitioned, will be incredibly performant for a join like this.

[Anonicat]

That’s precisely what I’m suggesting. A nonvolatile table with 325.8M rows, fully indexed and partitioned, will be incredibly performant for a join like this.

No read my other response. That's too much. Why join a table that big.... there is still a performance hit. Just cause the algorithm for joins is optimal doesn't mean it won't take some time.

See my other response.