Fixing Pterodactyl's activity_logs Database Ballooning

Diagnosing The Issue

Last month, my main server that runs my Minecraft network (using Pterodactyl as the management interface for the servers) and a few other services crashed. Upon remotely connecting in, I found that some services had crashed and others were behaving weirdly. After a few minutes of investigation, I found that the main disk was completely full (use the command df -h to see disk usage on most Linux machines).

After deleting just a few gigabytes of files and restarting the machine to fix crashed and broken services, I installed and used the utility NCDU from the root of the filesystem to determine that the activity_logs table in Pterodactyl's database was taking up over half of the disk! After looking around the internet, it wasn't particularly obvious what this table was for. According to Pterodactyl's support (you have to join their Discord server and search through its history), it seemed that data was supposed to be cleared from this database after 90 days.

This issue caused a lot of files that were being used at the time to corrupt, particularly files storing user data, world data and statistics in the Minecraft servers. These had to be recovered from backups (make sure you have backups running regularly, and ideally a warning for when your disks are nearly full)!

Halting The Root Cause

After further searching on Discord, it seemed that the fast filling up of the activity_logs table was some kind of bug, and the advice to clear it seemed to be to stop the Wings service, nagivate to /var/lib/pterodactyl/, delete a file named wings.db and then start Wings again. See the included image of this advice from their Discord.

The above stopped further rapid growth of the table, but didn't delete what was already there. I searched further and found various other bits of advice, including how to reduce the duration that activity logs are kept, and using a prune command in artisan to manually trigger the purge. Somebody suggested trying to run the truncate table command in MariaDB, but this failed due to a warning about a foreign key present in the table. Also interestingly, when querying the table size, it only showed up at about 5MB, despite taking up nearly 300GB on disk.

The Fix

After some digging through the MySQL documentation, I found the optimize table command. After taking a backup of this and running it against the activity_logs table, suddenly the disk space was freed. I also chose to run the command against the activity_log_subjects table, as this was also a little larger than I'd expect. The optimize command seemingly removed millions of stale entries and rebuilt the table with only the valid entries in it. Before doing this, it is strongly recommended that you create a backup of your database!

• First enter the MySQL command line using: sudo mysql (you may need to enter a password here).
• Use the command use panel; to switch to Pterodactyl's database.
• Optionally, you can use the command show tables; to confirm you're in the right place. Look for the activity_logs table.
• Run the command: optimize table activity_logs;

Hopefully this helps if you're reading this. The issue seems to be a long standing one with Pterodactyl, with Discord messages dating back to 2022 being easily findable, and a GitHub issue for this from March 2023. The developers (who often seemed to ignore people with this particular issue) don't appear to have ever provided a full answer to this, despite many, many people having the issue. From my understanding, this issue still isn't fixed and can occur randomly.