how to control sqlite log table size?

[audricd]

Hello,

Im running a two node cluster, using sqlite for logging.

My master node size is.. big.

whereas the second node is okay

I have set this sql procedure:

CREATE DEFINER=`root`@`%` PROCEDURE `dns`.`purge`()
BEGIN
	DECLARE affected INT DEFAULT 1;
  	WHILE affected > 0 DO
		DELETE FROM dns_logs
		WHERE timestamp < DATE_SUB(NOW(), INTERVAL 90 DAY)
		LIMIT 2000;
		SET affected = ROW_COUNT();
    	DO SLEEP(0.05);
	END WHILE;
END

unfortunately it doesnt seem to do much, on the final size?

dbeaver reports the table size to be 14gb, it was 20gb before any prunning (about of year of running?) so thats not possible. and on disk still 19gb. what am i missing?

thank you!

[FabienD74]

/var/lib/mysql/dns ..... => MYSQL not sqlite.
search for a vacuum . I never used mysql....

[arthur-2sheds-jackson]

Assuming innodb under mysql (to which the evidence points and not sqlite), the following is relevant:

https://dev.mysql.com/doc/refman/8.4/en/innodb-file-space.html:

When you delete data from a table, InnoDB contracts the corresponding B-tree indexes. Whether the freed space becomes available for other users depends on whether the pattern of deletes frees individual pages or extents to the tablespace. Dropping a table or deleting all rows from it is guaranteed to release the space to other users, but remember that deleted rows are physically removed only by the purge operation, which happens automatically some time after they are no longer needed for transaction rollbacks or consistent reads.

Also, check your binlogs.

[audricd]

yes, sorry, typo on my end. its the mysql log table. not sqlite.

so i must resize the table not only drop data on it.

thank you for the input.

[arthur-2sheds-jackson]

Consider a more conservative query log limit as well, so deleting+purging is automatically handled before space becomes an issue.

[ShreyasZare]

Thanks for the post. The Query Logs (MySQL) app already provides option in its config to limit the number of records based on count or number of days. You need to make sure that those options are configured to automatically remove older records from the db.

[audricd]

thank you team.
indeed, i should have begun with setting that option in the mysql plugin itself. i would have the need today to do a purge a DB maintainance. the issue as usual; when not dns, is the user.
for those in my same situation, the "vacuum" discussed above, in mysql is "OPTIMIZE TABLE"

careful, a consideration to have: you need a lot of space ahead on the volume. apparently, what it does in the background, is to recreate in a temporary space, the table. if i understood what ive been researching. otherwise, the operation will fail (this ive see with my own eyes).

so full steps:

1. configure how many days you want via the mysql plugin on technitium
2. purge data on mysql with this

DECLARE affected INT DEFAULT 1;
  	WHILE affected > 0 DO
		DELETE FROM dns_logs
		WHERE timestamp < DATE_SUB(NOW(), INTERVAL 90 DAY)
		LIMIT 2000;
		SET affected = ROW_COUNT();
    	DO SLEEP(0.05);
	END WHILE;

(change 90 days to whatever you want)
3. give plenty of headroom to the whatever you are running technitium db on, i did 2x but it may not be needed as much
4. run OPTIMIZE TABLE dns_logs; be patient, its a long operation. it took 25 minutes in my case
5. you may shrink again your hard drive / partition for technitium mysql logs