I have the 2 consumption calculations that were not progressing very fast and looking at the CPU curves (4 vCPU), it did not exceed 10%.
Are we on single-thread for calculations or am I completely wrong?
The crash is at 22:17 on the graph, the white corresponds to unretrieved information, Gladys was working very well.
On the disk side, I have ups and downs and since the crash I have remained very high!
After investigating, it’s my Proxmox script that sees the RAM is maxed out (5.9GB/6GB) during migration and then restarts the Gladys Docker container.
I’m going to disable that.
But how much RAM is needed to migrate a device without migrating its history?
I get the impression it uses a lot of memory.
I don’t have a lot of scenes/dashboards where I use the anemometer for example (that’s what I was trying to migrate.
My duckDB is almost 10GB, does that matter?
Anyway, yesterday I migrated the device without any history and it caused the RAM to spike until it crashed (6GB) and then manual restart due to OOM.
Today I increased my LXC from 6 to 8GB of RAM, launched the migration without history: super fast!
Oh indeed, if your database is 10 GB and there are many states to migrate, then yes it can take a lot of time and a lot of RAM.
Do you have the possibility to temporarily allocate more RAM just for the duration of the migration?
Regarding the memory that “doesn’t free up” afterward: this is normal and it’s not a leak. DuckDB keeps its pages in cache up to its memory limit and the system doesn’t immediately release it. It is reused by Gladys and freed under pressure.
And I advise you to disable automatic restart on RAM threshold during this kind of operation: restarting Gladys in the middle of a migration leaves the work half done (it’s replayable, but it’s better to avoid it).
already done but I’ll have to go to 10 or 12GB because I’ve been migrating for over an hour and the 8GB is full and Gladys is crashing, the dashboard is no longer refreshing
ok I’ll monitor then to see what happens over time.
@mutmut, No issues on my end with a configuration almost identical to yours.
I migrated my Netatmo thermostat without any problems, no slowdowns, and it was very quick. My database is 6 GB
I managed to upgrade my LXC to 12GB live without rebooting and the migration of my external station (with everything to migrate) took 1h26 and a significant slowdown in consumption calculations during the migration.
I’m switching to the internal probe with everything to migrate.
In any case, for the 4 modules to migrate, one by one, I’m at over 24h
The main issue I see is that as long as the migration is not finished, the consumption calculation is on standby, as well as all those that come after.
It would be necessary to reduce the priority of the migration to allow the consumption calculations to be done and this would also make it possible to always have control over the dashboards.
For example, the consumption calculation has just started, so 2 tasks with the migration, and it slows down my Gladys considerably.
Thanks, these numbers are very useful, and your analysis is correct.
What’s expensive isn’t the history of the device you’re migrating, it’s the total size of your database: each feature is moved by a query that scans and rewrites pieces spread throughout the 10 GB file. An external station = 4 to 6 features = as many full passes, hence your 1h26. That’s also why @Will_71 didn’t see anything: a thermostat is 3 times fewer features on a smaller database. And I’ll warn you right away: the internal probe will be the longest of the four, it’s the module with the most features.
On your suggestion to lower the migration priority: you’re right in principle, but there’s no priority to set: the move is today a single big SQL query, and as long as it’s running nothing can be interleaved. The right fix is to split it into chunks, as the state purge already does: memory remains bounded, and Gladys regains control between each chunk for consumption calculations and dashboards. That’s what I’ll do, with a real progress bar (today you’re stuck at 5% for the entire operation, which helps no one).
The fact that you had to increase to 12 GB isn’t normal: it’s not cache, it’s unbounded transaction memory. After the fix, it should work without adding anything.
@spenceur your deletion case probably has the same root cause and will be looked at at the same time.
Thank you for your measurements, they helped identify the issue, and it has been fixed.
The cause. Moving the history launched a query per feature, and each one scanned your entire database. The actual cost is therefore not proportional to the device history you are migrating, but to the product (number of features × database size). A weather station has 5 or 6 features, so 5 or 6 full passes over your 10 GB: there’s your 1 hour 26 minutes. That’s also why Will_71 didn’t notice anything with his thermostat, fewer features on a smaller database. And your remark about priority was correct: the migration monopolized the DuckDB write connection from start to finish, nothing could intervene.
What has been done. The history is now moved in batches, all features processed in the same query. Gladys yields control between each batch, with a deliberate pause equal to the batch duration, so that consumption calculations and dashboards continue to run. Progress is real, with a live counter of moved states, no more frozen 5%. The device deletion was fixed at the same time, it’s the same mechanism (this should address spenceur).
The measurements, on a 2.9 GB test database with 176 million states, for a device with 6 features:
• duration: 347 s before, 18.6 s after
• DuckDB file size: it doubled during the operation (2.9 GB going to 5.8 GB), it now grows by 6%
On a database 5 times smaller, the gain was only a factor of 4.5, compared to 18 here: the larger the database, the greater the gain, so on your 10 GB it should be at least as good. Expect a factor of 9 in practice, the deliberate pause costs about half the time, that’s the price for Gladys to remain usable.
My reservations, to be honest with you:
I couldn’t reproduce your RAM explosion to 8 GB on my test database, the measured memory gain is modest. What I clearly reproduced is the doubling of the file on disk, and I think that’s your real issue: in an LXC, the disk cache counts in the RAM seen by your Proxmox script, so writing 3 GB more fills the memory as it measures it. This matches your disk curves exactly. But until you’ve retested at your end, it remains a hypothesis.
And well no, « only » 44mn07s but with 12GB of RAM to make it pass.
Great! because it didn’t provide any additional information.
I indeed had it because my LXC had 20GB of disk and I had to quickly increase it to 30GB to no longer be blocked by the space used by the database.
Regarding the cache, now that you mention it, it is 1GB on my LXC and every time the RAM exploded, the cache was completely full. I don’t know if it was before or after, though.
Strange thing when I temporarily switched to 12GB, the RAM went almost to the max and once all tasks were finished, it went back down to 7-7.2GB without ever going lower or higher. The cache was always full so I allowed myself a small reboot and switched back to 8GB.
Currently, I’m running around 5GB of RAM out of the 8GB allocated, the swap is almost empty.
I will try to retest the migration with your next fix on a backup I have on a test instance, to see the difference and I will keep you updated here.
In any case, thank you for your investigation and resolution!
RAM has increased a bit but without reaching the limit.
Great optimization, well done @pierre-gilles
On my Proxmox with LXC (6GB of RAM and a 5.5GB DB): about 500-700MB of disk used during migration, and a DUCKDB_MEMORY_LIMIT=2000MB in my docker compose.
