On Thursday 20th July XIVDB had to be shutdown emergency maintenance, this lasted 18 hours. This was unplanned and due to not properly planning for Stormblood's release with regards to the character features on XIVDB. I have been working on improving the tracking system as it scales very large and becomes very unwieldy however the speed of new data far surpassed what I expected.
In the coming months I hope to finally have a better solution for this and at that point I can enable mass character tracking (auto-adding characters), I will announce more on that when the time comes.
If you're interested in the details, they're below!
XIVDB's Character Tracking
This is roughly how the character tracking setup looks:
XIVDB continuously grabs data from the Sync Database which in turn is continuously parsing data from The Lodestone. As this is always on-going if for whatever reason XIVDB fails to fetch data from Sync it will attempt to refetch the data during the next update cycle. if this fails then it tries again, and again, and again, and again, etc. This can cause a build up since the SyncDB will always have new data.
This build up causes all resources to hit 100% and due to disk space maxing out it failed to fetch new data (it couldn't store it) and I ended up getting locked out the server as it couldn't even write a login session to let me on the server!
After a few restarts to clear temp storage I finally got on and I needed to resize the DB Storage Node. Due to XIVDB continuously trying to grab new data I kept getting locked out again and so I ended up shutting down the database service by killing the process (mistake #1). This caused the achievements table to be malformed and became rendered in a "locked" state (it was likely being updated when I forcefully shutdown the database)
So ok, I sit back and try to calm down... I do backups and there is storage replication so the plan was to create a new storage capacity and re-import the data from replication to the new capacity...
This would have taken about 9-10 hours however the import process just died part way through (still unsure why...). I upped the server resources and attempted to run the process manually. I left it running over night and it the site reported successfully re-importing the database at around 6am GMT. Taking a total time of roughly 18 hours.
I did expect a lot more traffic during Stormblood and with that a growth in character tracking data. What I did not expect was just how fast that data would arrive.
Some things to note:
- Stormblood level cap increased and this introduced 70 million more required EXP to earn.
- Stormblood has been a lot more successful for XIVDB compared to Heavensward.
- Popular community tools using the XIVDB API have helped grow character engagement.
As of 21st July 2017 (34 days ago) there have been
- 22.9 million new EXP events
- 10.4 million new Level events.
- 26.1 million new Achievements tracked.
The growth (over 6 years):
- Total EXP Events: 185 million.
- Expected yearly growth: 15%
- Stormblood Growth: 12%
- Total level Events: 95 million.
- Expected yearly growth: 15%
- Stormblood Growth: 11%
- Total achievements recorded: 61 million.
- Expected yearly growth: 10%
- Stormblood Growth: 43%
As shown the tracking nearly hit the yearly growth in the first month of Stormblood for EXP/Level tracking and for Achievements it was completely blown away. 6 Months ago there were ~186,000 characters who had their achievements public, this is now ~211,000 characters. Achievements caused the biggest spike since when a character enables privacy settings it will immediately add all their achievements, unlike EXP/levels which is a gradual slow growth.
The database is stored on a separate storage node (known as Block Storage) and in March I set the capacity to 80GB, the database at this time was 49GB in total. While it did grow to max capacity it is not always going to represent the same outcome and improvements can be made in this area, for example; the database I am using (MySQL InnoDB) does not release disk usage when you delete data. So any character data removed doesn't release that data. (Naturally it would overwrite the empty data but it only does this for the same table; eg: Delete achievements = new achievements take up their space, but new EXP events would not)
After restoring the database from replication it is now on 61GB and not hitting capacity, I've also thrown it on a 200GB storage node... Hopefully giving me enough time to implement better tracking systems.
For those more technical in this area; I have looked at a lot of solutions and I know this isn't the best but it is running and migrating to a new setup involves a lot of work that cannot be done in 2 minutes, transition is the biggest hurdle!
I will keep an eye on the increase more thoroughly this time (probably should have setup alerts) and continue to look into better solutions for this feature!
If anyone is more technical in the area of servers/databases then don't hesitate to fire off your suggestions in the Discord chat, I'm not going to get upset when you "lol at XIVDB's setup", servers/databases is not my strongest skill.
Thank you and deeply sorry for the downtime!