sqlite DNS logs on tmpfs

[ackstorm23]

I would like to poll logs using the API, which requires the sqlite database app. However one of my main goals with polling logs is to avoid excessive wear and tear on the flash memory of the router where I run DnsServer.

I was thinking that I could add a tmpfs mount in the the container, where the sqlite database is written, to prevent it from causing that media wear.

However I have two concerns:

1. What happens if the container is restarted and the folder container the database is now empty upon startup. Will it automatically regenerate all of the files and start the "app" with fresh database, or will it fault and require manual intervention to start the 'app' again?
2. Is the database circular such that old records will be pruned to make way for new ones and retain a maximum database size, it will it simply grow indefinitely? (I know it has "maxLogDays": but that is not the same as a size limit, and any day with an excessively large amount of requests could easily overflow disk storage. This true with log files as well)

ps: it would be fantastic to have the option to run this database in-memory only so that the more advanced manual configuration of a tmpfs mount was not a blocker for less experienced users

[ShreyasZare]

Thanks for the post. As you mentioned, you can mount the tmpfs in the container as a volume and then set the database file path for that volume from the app config. The db file gets created if it does not exists.

The maxLogDays is the only option at the moment to remove old data. Another option can be planned to limit the number of records too. Will think on this and plan something.

What is the usecase for in-memory database that you have since any restart would mean you lose the data?

[ackstorm23]

Thank you for confirmation that it will automatically recreate and populate the database with new entries upon restart.

My use-case for in-memory database has two parts:

1. By itself, it allows for anyone to see recent DNS queries without having available disk space, concerns over disk performance or media wear, or requiring external systems to push logs to.

2. When paired with the app, logs can be pulled in intervals from the API into another system for long-term storage.

[ShreyasZare]

That's interesting usecase and I think with small changes will be doable as sqlite supports in-memory db. Will plan this to be implemented.

[WestFarmer]

@ShreyasZare maxLogDays, how to specify this ? don't see this in environment variables.

I allocated 1G PV for technitium, now service down due to no more space. I can't access web UI anymore.

[ShreyasZare]

@ShreyasZare maxLogDays, how to specify this ? don't see this in environment variables.

Just switch to the Apps section on the GUI and click on Config button for the app to edit it.

I allocated 1G PV for technitium, now service down due to no more space. I can't access web UI anymore.

You will need to delete the sqlite db file manually from disk. You can find it in the /etc/dns/apps/Query\ Logs\ \(Sqlite\)/ folder.

[ShreyasZare]

I have released Query Logs (Sqlite) v4.1 which supports in-memory database and also includes a new option to limit the number of records the db can contain. Do update the app and let me know your feedback.

[ackstorm23]

so far, so good. 🤞

[ShreyasZare]

Thanks for letting me know.