ZLL.DB has grown (too) huge #4730
Comments
|
Can you check how many devices is listed in your db? |
|
Well, I've restored a backup I did back in january, so this version shows 117 devices. However, just after the restore the size of the zll.db was 326656. Now, a few hours later, it's grown to 7154688. Is that normal? I've backed up the 802 megabyte zll.db as well so I can put it back if need be. |
|
No this is not normal. While I'm not going to score points with the SQL elite for proposing this, this elaborate oneliner will hopefully give us an idea if there's a specific table containing all this (bogus) data: This is what my output looks like: Should you find a suspect here, you can look at the content by running something like: This will hopefully help us pinpoint what is going on. |
|
Thanks for helping :) Today the size has grown to 19715072. Output from the sql-statement: nodes looks suspicious? Not sure what to look for, but here is the start of the output:
|
|
To see what keeps getting appended to the table, try this: |
|
Right, this seems to be repeating:
So - delete it and re-add? Or is it a device I should throw away? |
|
I don't know if anything is wrong with the bulb tbh. I would start with what you propose, deleting this particular bulb via Phoscon, then check if DB size have improved. If it's better, re pair this bulb. If the DB is still huge even after deleting this bulb via Phoscon, we'll need to delete all duplicate nodes with id 84 from the nodes table by running: I don't know if this is an issue that have been fixed in newer versions of deCONZ, but you might consider upgrading to tha latest stable version once you have this problem under control. |
|
Let's also check if id 84 is the only one with duplicates, or if there are others: |
|
Deleted the bulb, but the database keeps increasing. Output from your last sql: |
|
@SwoopX This issue reminds me of tht issue with the fake nodes. I forgot what issue it was. |
|
😓 Doesn't seem like we have a pretty solution for this... this is the closest I can find: |
|
OK, so the datbase is FUBAR then, great...:) I don't quite understand when I put back a backup from january, with a small database of 326656 bytes, it still includes 'something' that makes the database grow exponentially? What would be the sensible way forward? Stopping deconz, deleting the database alltogether, restart deconz and re-add everything? |
|
Oh, and I do not have 176 devices, more like 90. |
|
Had another backup 4 days before I put back now. File size hasn't grown much, and the sql output looks a lot better: sqlite3 zll.db "SELECT id FROM nodes;"|sort -n|uniq -c |
|
Table output: auth: 52224 |
|
Bugger...: |
|
Table output: auth: 52224 |
|
Count: |
|
Have you tried upgrading to 2.10.04 ? |
|
Yes, running that since restoring backup #2 :) |
|
Now running a backup I made dec. 1 2020. Still holding file size after a couple of hours. |
Hey, are things still holding up after the upgrade? |
Yep. Must be something really funky stuff in those newer backups I guess. :) This might all be caused by a faulty SD-card. I had to replace it and restore a backup back in february (I think). All went well after the restore, but I never checked the database back then (why would I :) ). |
|
As there has not been any response in 21 days, this issue has been automatically marked as stale. At OP: Please either close this issue or keep it active It will be closed in 7 days if no further activity occurs. |
|
As there has not been any response in 28 days, this issue will be closed. @ OP: If this issue is solved post what fixed it for you. If it is not solved, request to get this opened again. |
Describe the question or issue you are having
My zll.db has grown huge, making adding/removing devices extremely slow:
-rw-r--r-- 1 pi pi 802650112 Apr 8 13:00 zll.db
What can I do about that?
Screenshots
Environment
deCONZ Logs
Additional context
The text was updated successfully, but these errors were encountered: