Housekeeping in director

edpstiffel · October 21, 2024, 3:23pm

Director version (System - About): 1.11.1
Icinga Web 2 version and modules (System - About): 2.12.1
Icinga 2 version (icinga2 --version): 2.14.2
Operating System and version: Debian 11
Webserver, PHP versions: Apache, PHP 7.4.33

Hey there.

After 3 years of heavily using the director to sync our devices and vms from netbox, our director database has grown to about 50GB.
We are doing the household task regularly, but we have 3 tables in the database which we would like to cut a little since they occupy most of the space:

director_activity_log 19GB
director_generated_file 17GB
director_deployment_log 323MB

There is a Github issue which mentions ways to downsize two of the tables (How do you delete the audit log? · Issue #1442 · Icinga/icingaweb2-module-director · GitHub) but there is no word about the director_generated_file-table.
So my question is: Is there a way to downsize this table also?

Thanks for any enlightenment.
Patric

moreamazingnick · October 21, 2024, 9:06pm

did you try that:
Thomas-Gelf commented on Mar 26, 2018

Oh, sure . No guarantee that this works, but in theory it should:

DELETE FROM director_deployment_log WHERE start_time < NOW() - INTERVAL 30 DAY;
DELETE FROM director_activity_log WHERE change_time < NOW() - INTERVAL 30 DAY;

you can remove the entry in the deployment log but you can also update it

‘config_checksum’=>null

than there is this icingacli command mentioned:

icingacli director housekeeping run

Once you did so, please run:

OPTIMIZE TABLE director_deployment_log;
OPTIMIZE TABLE director_generated_config;
OPTIMIZE TABLE director_generated_config_file;
OPTIMIZE TABLE director_generated_file;
OPTIMIZE TABLE director_activity_log;

if there is no reference to a deployment log anymore the housekeeping task will clean up the generated files table see here :

github.com

Icinga/icingaweb2-module-director/blob/853b6ceb846a86d3fbd52d04fe9f8d1e4882744a/library/Director/Db/Housekeeping.php#L110


      
              $lastActivity = $conn->getLastActivityChecksum();
          
              $sql = 'SELECT COUNT(*) FROM director_generated_config c'
                   . '  LEFT JOIN director_deployment_log d ON c.checksum = d.config_checksum'
                   . ' WHERE d.config_checksum IS NULL'
                   . '   AND ? != ' . $conn->dbHexFunc('c.last_activity_checksum');
          
              return $this->db->fetchOne($sql, $lastActivity);
          }
          
          public function wipeOldUndeployedConfigs()
          {
              $conn = $this->connection;
              $lastActivity = $conn->getLastActivityChecksum();
          
              if ($this->connection->isPgsql()) {
                  $sql = 'DELETE FROM director_generated_config'
                      . ' USING director_generated_config AS c'
                      . ' LEFT JOIN director_deployment_log d ON c.checksum = d.config_checksum'
                      . ' WHERE director_generated_config.checksum = c.checksum'
                      . '   AND d.config_checksum IS NULL'

github.com

Icinga/icingaweb2-module-director/blob/853b6ceb846a86d3fbd52d04fe9f8d1e4882744a/library/Director/Db/Housekeeping.php#L141


      
          
          public function countUnusedFiles()
          {
              $sql = 'SELECT COUNT(*) FROM director_generated_file f'
                   . ' LEFT JOIN director_generated_config_file cf ON f.checksum = cf.file_checksum'
                   . ' WHERE cf.file_checksum IS NULL';
          
              return $this->db->fetchOne($sql);
          }
          
          public function wipeUnusedFiles()
          {
              if ($this->connection->isPgsql()) {
                  $sql = 'DELETE FROM director_generated_file'
                      . ' USING director_generated_file AS f'
                      . ' LEFT JOIN director_generated_config_file cf ON f.checksum = cf.file_checksum'
                      . ' WHERE director_generated_file.checksum = f.checksum'
                      . ' AND cf.file_checksum IS NULL';
              } else {
                  $sql = 'DELETE f FROM director_generated_file f'
                      . ' LEFT JOIN director_generated_config_file cf ON f.checksum = cf.file_checksum'

log1c · October 23, 2024, 1:33pm

There are two (more) issues regarding cleanup.

github.com/Icinga/icingaweb2-module-director

DB Cleanup: imported_*, Import History

opened 06:54AM - 01 Aug 19 UTC

Thomas-Gelf

enhancement ref/NC ref/IP

## Expected Behavior The database should not grow endlessly ## Current Behav…ior `imported_*` tables grow over time, there is no related cleanup. Well, there is - but it isn't triggered unless old entries have been wiped from `import_run`. Even in large environments this should not be noticed. However, as soon as someone creates an Import source with very volatile columns (like "uptime" or similar), growth can become excessive. ## Possible Solution First of all, this isn't such a big problem for most environments. The involved tables are optimized for reading, so even if there are a few hundred million rows - it shouldn't hurt. But it wastes space on disk. ### Cleanup strategies * **a) selectively deleting from those tables**: is incredibly expensive. The Housekeeping Job does so and should continue to do so in the future, but it doesn't scale. It would work fine as long historic Import runs would be wiped from `import_run` regularly, but this doesn't happen right now. * **b) drop and re-create tables**: not an easy task, as there are quite some constraints involved. We could of course provide related queries, but doing so would have an impact on "Purge" during the next Sync run. Not a good idea.. * **c) start with fresh tables, copy recent data, drop the old ones, re-create them, fill them with preserved data**: the only clean and efficient way to deal with environments that have already experienced data growth We should provide **a)** and **c)**. **c)** is important and should be triggered in the background on a future upgrade. No Import/Sync should run at that time. But don't worry, no disaster is to be expected in case they do. And we should provide a (configurable) cleanup strategy for `import_run`. Keeping only the 10 most recent imports might be a reasonable default. As soon as we delete rows in that table, existing cleanup procedures will take care about all `imported_*` tables. > **IMORTANT NOTE** for anyone reading this: YES, deleting old entries and running `housekeeping` will clean up those tables. NO, you shouldn't do so if you have GigaBytes of data in those tables. It will block you for a very long time

github.com/Icinga/icingaweb2-module-director

DB Cleanup: shrink director_generated_file

opened 07:15AM - 01 Aug 19 UTC

Thomas-Gelf

enhancement

## Expected Behavior The database should not grow endlessly ## Current Behav…ior `director_generated_file` grows slowly, but steadily. ## Possible Solution I want to preserve deployment history over a very long time, including statistics and startup logs. However, I do not need all generated flat config files. We should preserve only the most recent ones (like 200 of them, configurable). The frontend must deal with deploment history entries with `config_checksum = NULL`, this should already have been implemented.

Have not tried those myself yet, but they look like they could be implemented.

henning · October 28, 2024, 7:07am

henning · October 28, 2024, 7:09am

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`director`.`director_generated_config`, CONSTRAINT `director_generated_config_activity` FOREIGN KEY (`last_activity_checksum`) REFERENCES `director_activity_log` (`checksum`))``