Hoarding is a problem. Even data hoarding is, as it turns out.
In case you do not know, Final Fantasy XIV Tracker tracks quite a few things from official Lodestone and it's been doing that for a long time. One of the things it tracks are achievements for all characters, that are put into a separate junction table. It's used mainly for respective achievements statistics, and also to cross-link achievements with characters on respective pages. The problem is, it got too large.
You see, there are over 8 million characters in the game. Quite a few are deleted and, probably, even more do not share achievements (by making them private on Lodestone). But those who do can hundreds of them. Some can have close to 2 thousands of achievements. All that data adds up over time, and it added up to 55GBs of data (including indexes, though). And that caused problems.
This website is clearly not a large enterprise, so my server is small. I have only 10GBs of RAM on it, and that includes some extra RAM that was bought to accommodate this table, that at the time was around 8GBs. I obviously was not checking the size regularly, but when I started getting eternal loads on the website in the beginning of November, relatively soon I deduced, that that table might have been the problem. Because nothing else made much sense.
Well, there is a possibility that it was older version of Crowdsec hanging for some reason (which I got 2 on my test environment accidentally), but since I throw errors when there are still deadlocks after 5 retries, and the hang-ups were coinciding with spikes of those errors, especially during daily backups, the size was the most obvious culprit. And even if not - it needed to be solved anyway.
And solving this took awhile. You can see at least some of the investigation and solution process here since I am all for transparency on what is going on with the service, even if no-one is really using it. But long story short - it was a pain, and mostly because it required to process huge amount of data with little available resources. It even brought to light some issues in other components, for example performance degradation in Cron library, when you have 8 million lines its schedule.
It is solved, though, and I definitely need to be careful with what I store and how I store it in the future, so this does not happen again. I even have some ideas for further improvement of current database structure and one of them may help me with implementing a proper full-site search instead.
