While working on my recent post about why crash-consistent VM backups aren\u2019t always enough, I ran into an unexpected but very useful side effect of adding application-aware database backups.<\/p>\n\n\n\n
Once I started creating regular database dumps for my phpIPAM instances, I noticed something that had been completely invisible when relying solely on full VM backups: the database backups themselves were wildly different sizes.<\/p>\n\n\n\n
That observation kicked off a short investigation that ultimately led to cleaning up unnecessary data, shrinking backups, and better understanding what was actually stored in the application.<\/p>\n\n\n\n
I run multiple phpIPAM instances in my lab. Functionally, they\u2019re similar and store roughly comparable types of data. When I began dumping their databases as part of a snapshot freeze workflow, I expected the backups to be in the same general size range. They weren\u2019t.<\/p>\n\n\n\n
At the VM level, this difference was completely masked. A full-VM backup doesn\u2019t make it obvious whether one application\u2019s data is growing abnormally or not\u2014it all just looks like blocks on disk.<\/p>\n\n\n\n
The database-level backups, however, made the discrepancy impossible to ignore.<\/p>\n\n\n\n
This is one of those cases where VM backups were doing their job perfectly\u2014and still hiding a problem.<\/p>\n\n\n\n
From the perspective of the hypervisor:<\/p>\n\n\n\n
But VM backups don\u2019t provide visibility. They protect everything equally, whether the data is critical, redundant, or no longer useful.<\/p>\n\n\n\n
Application-aware backups, by contrast, force you to look directly at what\u2019s being protected. In this case, the size difference alone was enough to raise questions.<\/p>\n\n\n\n
With the size discrepancy in hand, the next step was to look at the database itself.<\/p>\n\n\n\n
By inspecting table sizes and row counts, it quickly became clear that one instance was retaining a significant amount of historical or log-related data that the other was not.<\/p>\n\n\n\n
To connect to the database, which was running in a container, I ran:<\/p>\n\n\n\n
docker compose exec devipam-mariadb \/bin\/bash<\/code><\/pre>\n\n\n\nOnce I was inside the container, I connected to the database with<\/p>\n\n\n\n
mariadb -u root -p<\/code><\/pre>\n\n\n\nFrom here, ChatGPT helped me with some SQL queries. The one to find the largest table was:<\/p>\n\n\n\n
SELECT\n table_schema as `Database`, \n table_name AS `Table`, \n round(((data_length + index_length) \/ 1024 \/ 1024), 2) `Size in MB` \nFROM information_schema.TABLES \nORDER BY (data_length + index_length) DESC\nLIMIT 5;<\/code><\/pre>\n\n\n\nThis was pointing me at the phpipam.logs<\/code> table, and to get a feel for some of the events it contained I ran:<\/p>\n\n\n\nSELECT *\nFROM phpipam.logs\nLIMIT 5;<\/code><\/pre>\n\n\n\nA few more investigative queries, grouping my username and command, led me to an existing phpIPAM issue:<\/p>\n\n\n\n
\nphpIPAM GitHub Issue #3545 \u2013 Excessive database growth due to retained data<\/p>\n<\/blockquote>\n\n\n\n
The issue documents how certain tables can grow unbounded over time, particularly with historical scan and discovery data enabled. This issue (https:\/\/github.com\/phpipam\/phpipam\/issues\/3545<\/a>) even provided a sample query to aid with cleanup. The issue showed creating this as a recurring job, but based on my data this issue was no longer occurring on a regular basis, it was an issue that happened in the past.<\/p>\n\n\n\nCleaning Up the Data<\/h2>\n\n\n\n
Armed with that context, I ran a small number of targeted queries to understand and then remove old, unnecessary entries. The goal wasn\u2019t to blindly delete data, but to:<\/p>\n\n\n\n
\n- Identify logs events responsible for the majority of the growth<\/li>\n\n\n\n
- Confirm the data was no longer operationally useful<\/li>\n\n\n\n
- Reduce backup size without impacting functionality<\/li>\n<\/ul>\n\n\n\n
The following query tested the logic I was going to use for removals:<\/p>\n\n\n\n
SELECT\n COUNT(*) AS rows_to_delete,\n MIN(date) AS oldest,\n MAX(date) AS newest\nFROM phpipam.logs\nWHERE (command = 'user login' or command like 'users object % edit' or details like '% in ipaddresses edited. hostname: %')\n AND date < NOW() - INTERVAL 60 DAY;<\/code><\/pre>\n\n\n\nThis showed about 2.8m rows, dating back nearly 3 years, that I thought would be safe to delete. Changing the statement (replacing the SELECT with a DELETE) resulted in the final cleanup query:<\/p>\n\n\n\n
DELETE FROM phpipam.logs\nWHERE (command = 'user login' or command like 'users object % edit' or details like '% in ipaddresses edited. hostname: %')\n AND date < NOW() - INTERVAL 60 DAY;<\/code><\/pre>\n\n\n\nThis query took about 20 seconds to execute and deleted the expected 2.8m rows. The functionality of phpIPAM is unchanged, but the backup related results were immediate.<\/p>\n\n\n\n
\n- Database sizes across instances were now much closer<\/li>\n\n\n\n
- Compressed backup sizes dropped significantly<\/li>\n\n\n\n
- Backup and restore operations became faster<\/li>\n<\/ul>\n\n\n\n
The Secondary Win: Smaller, Faster Backups<\/h2>\n\n\n\n
Reducing database size isn\u2019t just about saving disk space. Smaller application backups mean:<\/p>\n\n\n\n
\n- Faster freeze-script execution<\/li>\n\n\n\n
- Shorter snapshot windows<\/li>\n\n\n\n
- Less data to validate during restores<\/li>\n\n\n\n
- Lower risk during recovery<\/li>\n<\/ul>\n\n\n\n
In other words, improving the quality of the data improved the reliability of the backup process itself.<\/p>\n\n\n\n
Lessons Learned<\/h2>\n\n\n\n
This entire chain of events started with a simple goal: making sure I had a known good copy of application data. What I didn\u2019t expect was that application-aware backups would act as a diagnostic tool:<\/p>\n\n\n\n
\n- They exposed abnormal data growth<\/li>\n\n\n\n
- They encouraged closer inspection of the database<\/li>\n\n\n\n
- They led to tangible improvements in backup efficiency<\/li>\n<\/ul>\n\n\n\n
It\u2019s a good reminder that backups aren\u2019t just about recovery… they\u2019re also a feedback mechanism. When you actually look at what you\u2019re backing up, problems that were previously hidden at the VM layer become much easier to spot.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
Crash-consistent VM backups remain a solid foundation, especially in lab environments. But once you add application-aware backups, you may gain another layer of visibility.<\/p>\n\n\n\n
In this case, that visibility surfaced unnecessary data growth in phpIPAM, reduced backup sizes, and improved overall reliability. That\u2019s a win well beyond the original goal of \u201cjust\u201d having a safer backup.<\/p>\n\n\n\n
If nothing else, this experience reinforced one idea: when you back up data at the application level, you\u2019re forced to understand the application better.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
While working on my recent post about why crash-consistent VM backups aren\u2019t always enough, I ran into an unexpected but very useful side effect of adding application-aware database backups. Once I started creating regular database dumps for my phpIPAM instances, … Continue reading