Disk full / help cleaning database

[lmilcent]

Ah yes, I understand. And isn’t there a way to run it regularly to avoid having to delete a huge amount of data at once?

In my case, I have 68GB of disk, a 22GB database and 22GB free. Except the VACUUM never finishes, due to lack of disk space.

I end up with a .db-wal file of almost 10GB in addition to the 22 GB of the db. I don’t really know what to do anymore.
Running the task manually doesn’t work, I get a « database locked » error. Even when specifying a different TMPDIR path that has 800GB free.

[pierre-gilles]

I’ve moved this topic into a new topic

@\"lmilcent

And isn’t there a way to do it regularly to avoid having to delete a huge amount of data at once?

No, because VACUUM is slow on large databases/slow disks, even if it was run recently.

In your case, what I would do:

• Stop Gladys
• Copy your DB to your PC

[lmilcent]

I hesitated to create a dedicated thread, thanks!

[lmilcent]

In my case I had to

[lmilcent]

What I did was move the DB to a partition with more space.

But I can’t explain the following

Retention not being deleted?

My DB is 22GB, with unlimited retention for all my devices. To reduce the size, I set a 6-month retention for states and unlimited for aggregated values.

Despite everything, even with a VACUUM after all Gladys purge tasks, I’m at 17GB. While searching, I found a query to list the number of values per table, and I don’t understand why there’s so little difference between aggregated values and raw values.

Vacuum not working?

@lmilcent

[EDIT]
When Gladys launches a « Cleanup » task (so a Vacuum), the .db-wal file grows more and more, until it reaches the same size as the db.
I therefore end up with two huge files (task in progress here):

Once the vacuum task is finished, I end up with two huge files that are not deleted.

Yet the task is finished:

Only if I force Gladys to stop or restart is the .db-wal file removed. But I still have 17GB used.

[cicoub13]

If you have 3 months of state retention and 6 months of aggregated states, that means you’ll have:

• 1 row for each state of each feature of each device for the last 3 months
• 1 row for each minute (hour view), 1 row for every 5 minutes (day view), 1 row for each day (week, month, 3 months, year views) for the last 6 months for each feature of each device

So aggregated states still produce a lot of rows

This command lets you know the devices/features that are recording the most states.

SELECT COUNT(*) as total, tdf.id, tdf.name, td.name 
FROM t_device_feature_state tdfs 
JOIN t_device_feature tdf ON tdf.id = tdfs.device_feature_id 
JOIN t_device td ON td.id = tdf.device_id 
GROUP BY device_feature_id ORDER BY total DESC;

PS: did you expect more reduction by keeping only 6 months?

[lmilcent]

Thanks for your message and the request.

Here’s what it looks like :

GROUP BY device_feature_id ORDER BY total DESC;
4081384|8ede0b40-31b8-42a1-b6d8-fdfceaea35e1|Formaldehyd (Décimale)|capteurQualitéDeLair
2426183|631724ce-6fca-48c8-8588-5598f5a736ee|COV (Décimale)|capteurQualitéDeLair
2425895|b1289ec3-87f7-4dfc-bc95-80a9fa2bd840|Niveau de CO2|capteurQualitéDeLair
2000636|b0eb1e68-2b18-4c9d-b20b-0547a0641698|Intensité du signal|PriseBureau
941126|30d10a22-d247-49b9-873e-9aec623f886f|Puissance consommée|PriseBureau
785583|2c8ced8b-e581-497d-b247-e6cb884d45da|Intensité consommée|PriseBureau
611299|c1a94fd6-3413-46ef-86df-33214fb74328|Puissance consommée|PriseOnduleur
544435|4d88bc30-dbf3-4165-919d-fba0c54b728e|Intensité du signal|PriseVideoproj
512281|6f91c791-fd31-43ad-89d6-91418fffdaf1|Puissance consommée|PriseFrigoTS0121
313060|6b907ebe-c011-4dd1-af54-339b51add63f|Intensité du signal|InterrupteurSalon
302039|d9d990a1-6b0a-4d58-bc19-9a7c591ecf71|Intensité du signal|CapteurFumeeEntree
278423|3cf063a8-8ced-4134-954f-e1b81f930a16|Détection mouvement Oui/Non|CapteurMouvementEntree

On closer inspection, I do have states that are older than the expected 3 months. Isn’t the « Faire du ménage » task supposed to remove all of that?

sqlite\u003e SELECT * FROM t_device_feature_state WHERE device_feature_id = "8ede0b40-31b8-42a1-b6d8-fdfceaea35e1" ORDER BY created_at ASC LIMIT 10;
id|device_feature_id|value|created_at|updated_at
afee0e90-6084-4571-bfec-1acd4b7d4a8e|8ede0b40-31b8-42a1-b6d8-fdfceaea35e1|3.0|2023-08-31 01:23:29.486 +00:00|2023-08-31 01:23:29.486 +00:00
4b836770-b2da-47a0-bc5f-abd2c24f65ee|8ede0b40-31b8-42a1-b6d8-fdfceaea35e1|3.0|2023-08-31 01:23:33.090 +00:00|2023-08-31 01:23:33.090 +00:00
c1b9ec79-663a-4730-8de6-1f226d39c336|8ede0b40-31b8-42a1-b6d8-fdfceaea35e1|3.0|2023-08-31 01:23:36.497 +00:00|2023-08-31 01:23:36.497 +00:00
a7697c08-c7b5-4282-8a54-b98071e56deb|8ede0b40-31b8-42a1-b6d8-fdfceaea35e1|3.0|2023-08-31 01:23:40.002 +00:00|2023-08-31 01:23:40.002 +00:00
927c202e-fae5-4c8a-9e97-efe9db7a01d5|8ede0b40-31b8-42a1-b6d8-fdfceaea35e1|3.0|2023-08-31 01:23:43.512 +00:00|2023-08-31 01:23:43.512 +00:00
423cdb73-33a6-4dac-a0e2-a397184f7277|8ede0b40-31b8-42a1-b6d8-fdfceaea35e1|3.0|2023-08-31 01:23:47.010 +00:00|2023-08-31 01:23:47.010 +00:00
c7114d81-8740-4c42-bcde-790ec4ecbe02|8ede0b40-31b8-42a1-b6d8-fdfceaea35e1|3.0|2023-08-31 01:23:50.638 +00:00|2023-08-31 01:23:50.638 +00:00
59a722ee-71c2-44a9-ae8b-a9912d79a3b3|8ede0b40-31b8-42a1-b6d8-fdfceaea35e1|3.0|2023-08-31 01:23:54.175 +00:00|2023-08-31 01:23:54.175 +00:00
91435e9b-3c4f-4866-9940-4d360949d099|8ede0b40-31b8-42a1-b6d8-fdfceaea35e1|3.0|2023-08-31 01:23:57.655 +00:00|2023-08-31 01:23:57.655 +00:00
9a2f224f-16dc-4752-84bf-735823ec9303|8ede0b40-31b8-42a1-b6d8-fdfceaea35e1|3.0|2023-08-31 01:24:01.066 +00:00|2023-08-31 01:24:01.066 +00:00

[lmilcent]

In the end, by doing it manually, I’ve probably already saved 3.3GB if I judge by the .db-wal file that has grown.

sqlite\u003e DELETE FROM t_device_feature_state WHERE device_feature_id = \"8ede0b40-31b8-42a1-b6d8-fdfceaea35e1\" AND created_at \u003c \"2023

[cicoub13]

I have 3 months of retention and my

[lmilcent]

Once again, my database hasn’t shrunk despite changes to the WAL file.

[cicoub13]

We should look at this Write-Ahead Logging

[GBoulvin]

I had a full partition issue a few months ago and the solution for me was to… delete the db-wal which was larger than the DB…
Radical but I didn’t see any problems afterwards…
Edit: I see that in fact your db-wal has indeed been deleted so I’m off-topic. Sorry…

[pierre-gilles]

Hi @lmilcent

I think there’s a misunderstanding about how cleaning works in Gladys.

You’re talking about two different things:

• Database cleanup : This button is just a shortcut to the SQLite VACUUM query. The VACUUM command rebuilds the database file to free the space occupied by deleted tuples. For more information, the SQLite documentation is very clear: VACUUM
• Cleanup of old device states : This task runs every day at 4 AM, and purges all sensor states older than the chosen retention period. (See code: Gladys/server/config/scheduler-jobs.js at master · GladysAssistant/Gladys · GitHub )

If you change the retention duration in the UI, the cleanup of old states will take place at the next 4 AM (so as not to disturb your production too much during the day).

That could be shown in the interface, I guess…

So, if you run VACUUM while the states are still there, it won’t clean anything — that’s normal

You need to wait until 4 AM, then run VACUUM

[lmilcent]

@pierre-gilles

You need to wait until 4 a.m., then run VACUUM

Argh, I suspected that was it, but I think I was in too much of a hurry ^^
We’ll see this morning how it goes then! Thanks for taking the time to reply

[guim31]

I’m bumping this topic back up because I’m facing a low disk space issue.

However, I’m not keen to tinker with my database (BDD), nor to use my terminal because I’d be afraid of breaking everything (and incidentally because it’s always very time-consuming!)

Can any of you give me a simpler step-by-step to follow? If one exists?

This is getting critical ^^

[pierre-gilles]

@guim31

Can any of you give me a simpler procedure to follow? If one exists?

Hi @guim31

You can go to « Systems », and edit this setting:

Set something smaller than what you currently have.

Then, at 4 AM, Gladys will come and clean up old states older than the chosen duration.

However, since you’ve reached a fairly « serious » stage (no disk space left at all..), it’s possible that this will fail, and then you’ll have no choice but to free up space manually — it’s no longer a Gladys issue, it’s at the Linux system level ^^

[guim31]

I finally went brute-force: resized my partitions!! Went from 60GB to 200GB to have some breathing room