Logging - limit disk usage of logs/<username>.sqlite files, remove old records

[zeten30]

Feature Description

Currently, there's no way how to control logs/.sqlite file size. It can grow forever. Although this is usually not an issue, it will be good to have a mechanism that will keep file size under control.

Use-cases

• deployments with many users
• deployments in public/private clouds (on OpenShift), where persistent storage for applications is usually limited

Ideas

• keep max N messages in tables, use maxHistory parameter and run regular cleanups (SQL - delete oldest entries)
• check sqlite vacuum feature and check if this can help to improve disk space usage of sqlite

[richrd]

I think it would be great to be able to limit the messages by how old they are.
For example maybe I'd like to configure it to delete messages that are over 365 days old.

[zeten30]

Or the other way around. Add function to clear history. Something like clear browsing history in browsers. Clear message history older than: .

[richrd]

For the record, this type of query could be used for deleting n days of logs (the example deletes messages that are over 1 year old).

delete from messages where time < strftime('%s', datetime('now', '-365 day'))*1000;

[zeten30]

Agree. I already tried that on my OpenShift deployment -> sqlite dies while executing the query... But I assume it's because resource limits.

[mans3n]

Any news about this topic? I've deployed thelounge in a docker container and trying to get rid of some messages to reclaim some space back. How can i execute this command "delete from messages where time < strftime('%s', datetime('now', '-365 day'))*1000;" to sqlite? Commandline?

[xPaw]

Add function to clear history.

Latest pre-release has the feature to clear history of a channel.

[Stiefmeister]

Would be great to have an option to set maximum amount of lines per channel to log - currently having troubles with fast growing log-files

[alekna]

ssages to reclaim some space back. How can i execute this command "delete from messages where time < strftime('%s', datetime('now', '-365 day'))*1000;" to sqlite? Commandline?

Yes, you need to use sqlite command line tool. You may also want to run vacuum command after a delete.

[richrd]

@alekna Hi, thanks for the follow up!

Just checking if I understand this correctly, this is what I assume:

1. DELETE somehow marks the rows as deleted, but doesn't actually purge them from the file
2. vacuum actually alters the file to remove the deleted rows from disk

Did I get that right?

[alekna]

@richrd, not exactly. Have a look at https://www.sqlite.org/fileformat.html and https://sqlite.org/lang_vacuum.html if you're curious.

[im-n1]

This feature is highly needed. If you connect to one of those torrent channels where messages are constantly piling up 24/7 you reach 1gig of logs withing a week. Running Lounge on Rpi bricks it within a month.

[Erwyn]

I tried the "clear history" of a channel feature, but I'm not sure it's actually cleaning the sqlite database... It's still more than 600Mo on my side although I actually cleared everyone of my channel's histories

[klausenbusk]

That is expected, you can try to vacuum the database. Please see: https://sqlite.org/lang_vacuum.html

[Erwyn]

Will take a look, thanks Le mar. 16 févr. 2021 à 00:36, Kristian Klausen <notifications@github.com> a écrit :

…

That is expected, you can try to vacuum the database. Please see: https://sqlite.org/lang_vacuum.html — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#2822 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABSPF2TYVPDRW44ID4AA6TS7GVYVANCNFSM4FYWH4SA> .

[orliesaurus]

+1 - I got several gigabytes of logs and I would like to get rid of them natively without having to do it manually

[pbek]

That is expected, you can try to vacuum the database. Please see: https://sqlite.org/lang_vacuum.html

Maybe thelounge could do that periodically for all databases?

[pbek]

A temporary workaround could be to use logrotate to archive log-databases when they grow too big.

[orliesaurus]

I am looking for a solution that doesn't require me to stop thelounge to clean up logs

[pinpox]

Any updates or working workaronuds for this? I'm running an instance with several users and the .sqlite files are growing to a point where this is becoming a serious problem.

[brunnre8]

Sure: https://github.com/thelounge/thelounge/wiki/Purging-logs-older-than-X-days

is a workaround that does just that.

[azeemba]

Is there interest in this feature if someone else provides a solution. I can think of two ways to implement this:

• On startup, the server uses a setInterval to repeatedly run the delete command. This is nice because each delete operation will be relatively cheap.
• We use sql triggers, so on each update, sqlite itself will delete old records. This one is a little hard to configure for the user. We probably would need a new commandline "command" to insert/update this trigger.

[brunnre8]

Triggers are the wrong tool for the job in this case would be my guess, especially as you want to make this configurable and somewhat dynamic.

I'd also like to have the ability to only delete status messages, while keeping the important stuff like privmsgs around.
In other words some sort of timer on the JS side of things.
We do need to be a bit careful not to hang the rest of the system though by running a huge delete.

My plan is to limit this to N rows per iteration