Thelounge: 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.

[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 [e-mail]
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
https://github.com/thelounge/thelounge/issues/2822#issuecomment-779494457,
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