search

Simbiat / simbiat.ru

Main repository for simbiat.dev
https://www.simbiat.dev
GNU Affero General Public License v3.0
2 stars 1 forks source link

Figure out what to do with achievements #290

Open Simbiat opened 3 days ago

Simbiat commented 3 days ago

The table grew extremely large: 55+ GBs. Optimization saved around 20GBs but it's still too large. Regardless of the size, though the issue was that it seemed to affect server in general. Or at least that's my assumption, because at some point requests started waiting for more than a minute even for server response. Optimizing the table and stopping writes to it (with https://github.com/Simbiat/simbiat.ru/commit/25b0cfe75879843ad011a7e2836d9afae23e42a6) seemed to have helped, but monitoring for now.

One possible reason is that query for insert was

INSERT INTO `ffxiv__character_achievement` SET `characterid`=:characterid, `achievementid`=:achievementid, `time`=UTC_DATE() ON DUPLICATE KEY UPDATE `time`=:time;

instead of

INSERT INTO `ffxiv__character_achievement` SET `characterid`=:characterid, `achievementid`=:achievementid, `time`=:time ON DUPLICATE KEY UPDATE `time`=:time;

which obviously was causing unnecessary updates, since original inserts were incorrect. That, as result, would cause index rebuild and fragmentation (sense 20GBs difference after optimization).

If in the next week or so, I do not notice any slowdowns without these inserts at all, I will re-enable them with the fix, and see how it goes. If there will be no slowdowns a week after that - then it's good, but probably still a stop-gap.

Reality is that I am not showing all achievements the characters have anyway, and the data is mostly used for 3 things:

  1. Update achievements details. Realistically needs at least 10 not removed and not privated characters.
  2. Get statistics for rarest achievements
  3. Get only 10 latest achievements

1st and 3rd points will definitely overlap significantly (although hard to estimate exactly), but even if it will be thousands of characters per achievements, it will be less than current tens or hundreds of thousands (or even millions for some easy ones). So main issue is 2nd point. Perhaps limit the achievements to be processed on character updates to those achieved after last update and then do a +1 in achievements table? Since transactions are used it should be fine. Problem would be with getting a "fixed point in time" from which I can start doing that, but this can be approach:

  1. Disable character pages entirely due to privating functionality will not work. Disable automatic updates for anything else, including cron jobs. Probably will require a blog post, and notice should link to it
  2. Generate cron jobs for all not-deleted and not private characters
  3. Wait till all of them are completed
  4. Implement the change in logic along with DB updates and cleanup
  5. Re-enable character pages and automatic updates
Simbiat commented 2 days ago

backups processes in the morning may be a factor. at least it looked liked today things were working fine approximately until zipping of the files has started. At the same time daily logical backup also failed

mariadb-dump: Error 2026: TLS/SSL error: unexpected eof while reading when dumping table `ffxiv__character` at row: 940713

It probably also comes down to the size of achievements table, though, since I am not stopping the website during backup (at least not until https://github.com/Simbiat/optimize-tables/issues/1 is done), so it probably takes a toll on performance, when we already are short on RAM. Backups alone need some RAM, right, and then zipping them - too. But if most of the memory (9GB allocated for MariaDB) is taken up by achievements table, there is not much wiggle room for operations, and thus stuff starts to slow down and eventually fail (mariadb literally restarts, although without clear errors in logs).

As such, I am going through with the plan. Instead of a blog post, I will add link to this issue in respective notices.