"Delete messages from device" doesn't free up any memory

[STPKITT]

• Operating System: Android 7.1.2 (LineageOS 14.1)

• Delta Chat Version: 1.12.5

• Expected behavior: When DeltaChat automatically deletes messages from the device after a certain time period according to how the user has setup that option in DeltaChat's settings it should not only reflect visually inside DeltaChat but it should actually also free up memory space on the device obviously :-)

• Actual behavior: deleted messages indeed do not show up inside DeltaChat anymore however the amount of memory used by DeltaChat on the device does not decrease at all. DeltaChat on my device used an enormous amount of 660 MB in total despite only very seldom using DeltaChat and after enabling automatic deletion it still uses 660 MB! I already rebooted the device and even deleted it's cache and Dalvik cache but that made no difference. Btw I've set DeltaChat to delete messages from device after 1 year and to never delete messages from server. It deleted 2200+ messages when I enabled deleting from device. I also wonder where those 2200+ message came from, I certainly didn't use DeltaChat to that amount. Far from it!

• Steps to reproduce the problem:

1. check memory used by DeltaChat via Android's settings
2. set DeltaChat option to "Delete messages from device" to a timeframe which would result in DeltaChat needing to delete a decent amount of messages (perhaps including media ideally) so that it should clearly reflect in the amount of memory being used by DeltaChat
3. wait some time and then go back into Android's settings to check the amount of memory being used by DeltaChat now

[r10s]

thanks for the report :)

first, an overview about how things are expected to work:

1. delta chat cleans up files attached to messages in a housekeeping-job that is triggered mainly on message-deletion and on import/export. EDIT: it should be triggered also automatically, i filed an issue for that, https://github.com/deltachat/deltachat-core-rust/issues/2090

2. deletion does not affect the size of the database. deleted messages in the database are wiped, but the database won't shrink. however, the space is re-used when adding new database-records.

3. if you have ever done an export/import, the size of the database may be much too large. reason is that by export all files are copied to the database and on import removed from there - however, shrinking does not always work.

especially 3. is unfortunate and not really fixable as the VACCUM sqlite provides needs lots of free space on its own (several times the size of the database). we already fixed that by a new backup format, that does not add files to the database but use an archive format for that, see https://github.com/deltachat/deltachat-core-rust/pull/1749, however, this is not shipped yet.

in your case - maybe you can check if 1. works - eg. record some videos that simnifically enlarge the space needed for delta - say up to 770 mb :) and delete these videos - either manually or automatically. once housekeeping is through - please have a look at the log - you should be back to 660 mb. if this is not the case - as eg. housekeeping crashes or so - there is a bug and we have some actionable item that we can fix.

if you can - maybe you have also access to the blob-dir and can see what is happening here before/after housekeeping (the path is shown at settings/advanced/log)

[Hocuri]

How did you setup your device: Did you import a backup or did you newly login?

[STPKITT]

I've now found time to come back to this. Actually since creating this bug report it seems that after some days, how many I don't know, Delta Chat actually managed to reduce the memory space used by a bit. From the 660 MB used before it's now down to 506 MB without any manual actions done by myself, it's still set to autodelete messages older than 1 year. Still though it's using way too much memory for the low amount of messages I have in Delta Chat. But let's go ahead to answer your comments:

1. automatic housekeeping-job: that's good if that is now implemented in current Delta Chat versions but I won't be able to check it's effect until I have got back enough memory space on my device to actually be able to upgrade to the latest Delta Chat version

2. that is very bad indeed, a no-go basically. Unless there is already an issue opened for that I think I'D open one

3. yes, I did that at least once or twice when switching between Custom ROMs on this particular device

So on saturday I changed the autodelete option to delete messages after 4 weeks. That deleted 6454 messages (I wonder where do that many messages come from, I just chat occasionally via Delta Chat, so I wonder if Delta Chats holds all other emails of that account on the device too, which not only myself would need a disable-option for) and initially memory used by Delta Chat went up to 729 MB!!! I then waited until now and memory usage decreased again but it'S still higher than before deleting those 6454 messages. It's now at 583 MB compared to 506 MB before as mentioned at the beginning of this comment. I basically now can't use my device anymore in that state. So is the only solution to bring memory usage of Delta Chat back down again by deinstalling it?

[r10s]

2. is already kind of fixed as newer versions will not add files to the database for export and import so that the file only contains "small" data pieces (only text and metadata, no images/files/documents) and should never grow that big.

this does not help in your case, sorry.

on-device cleaning up as it would be required in your case requires as much as twice the size of the original database file - maybe plus the size of the exported file, so better calculate 3x. this is too much, this is known and discussed, and this is the reason why we changed the backup and went away from requiring VACUUM to work somehow, also there were other issues with VACUUM, https://github.com/deltachat/deltachat-core-rust/issues/1756, but that should be fixed in 1.14.

i could not follow all the additional explanations you did above, this is hard to do by seeing only some numbers :)

beside the already filed issues linked above, i do not see another actionable item currently.
however, to sum up:

• with chance, an export -> import on 1.14 with enough free space will do the VACCUM and finally result in a smaller space usage.

• if that is not doable on your device, once there is a version out that uses the new tar export format, things should be better as there is a chance to do the VACCUM on a machine that has enough free space. you will still need once the of data usage, but not multiple times.

• after-1.14 versions will work around these VACCUM pitfalls

[r10s]

btw current progress wrt tar-backup is visible at https://github.com/deltachat/deltachat-core-rust/pull/2023

[cauerego]

deletion does not affect the size of the database.

being new to sqlite and fossil, but very old on coding, i must say this is rather surprising!

indeed vacuum is not very aligned with the spirit in both projects. look at these for instance:

https://fossil-scm.org/forum/forumpost/de71fb7dad?t=c

https://fossil-scm.org/forum/forumpost/b93da8f166?t=c

https://fossil-scm.org/forum/forumpost/5a7a072f07?t=c

i haven't read the whole entirety of any link, but just the very few first posts already give you a good idea. drh is one of the main devs behind fossil. the 3rd link is very interesting, about page size, but they're still mostly instances to my point below.

so, perhaps a better idea for delta chat would be using sqlite only for text, pikchr and alikes such as some small svg perhaps, with links to another way to store all other big files.

then it would be easier to make this feature to work as expected. 😘

[Hocuri]

so, perhaps a better idea for delta chat would be using sqlite only for text, pikchr and alikes such as some small svg perhaps, with links to another way to store all other big files.

Yes, on the current master no blobs are put into the SQL at all anymore (before they were only put there when importing/exporting, but then afterwards VACUUM sometimes failed)

[csb0730]

@STPKITT @cauerego @Hocuri @cauerego

Unfortunately I just see this issue right now. But I think I need to share some (old) information with you:

At first a few tip's for STPKITT:

1. If you wonder how many messages are in your database you can use log file (extended settings). In some of the header lines you can see the total number of messages into database.
2. If you do an export, the first action of DC is to do VACUUM of message DB. So regardless if backup gets completed or not, vacuum should be done! But, requirement for vacuum is that you have at least more memory free at your device than databases file size. If you say you have 660MB occupied by DC in total then I assume that the database itself is much smaller. It depends on how many pictures or other files you have in DC. So a few hundred of MB free of space should be ok for at least vacuum the DB.
3. To give you an impression about memory consumption: At my device I've around 22000 messages in database (db size around 180MB) and full DC data consumption of 2.1G!
4. If you are able to export a backup (current approach) you can examine the backup file at a PC machine by using an sqlite browser software (e.g. sqlitebrowser at linux) to examine the database and even doing the vacuum manually for emergency handling. But to put it back to device you need to use adb tool. Tricky but possible!
5. IMHO in logfile there is a hint when housekeeping is done

[csb0730]

Second a few general considerations:

1. The existing problems with backup and vacuum are not related to sqlite itself. They are related in how to use it. For myself I was running into the same issues two years ago and I did many examinations around backup and vacuum processes, resulting in some important changes for my own build DC fork (now 1.2.14 !!)

I can say, that using an approach now which doesn't requires the vacuum at IMPORT is the right way. But this is not a problem of sqlite, it is a problem of how to use it (!) => I'm storing all blob files AND the database itself into a new file and use it as an archive (sqlar). Easy and working :-)

2. Next bottleneck for backup is device's internal memory! With current approach backup is done to internal memory's "Downloads" folder. This approach leads sooner or later to a point of no return! => You cannot do a backup any more. You never get your data from device. No chance (!) To attack this IMHO very critical bottleneck I rerouted backup to external SD card. This is much slower than internal storage, but secures backup at any time!

3. I think there should be a manual function in settings to trigger vacuum (housekeeping) if desired. Many applications (even WA, Outlook, Thunderbird) are using this approach for compressing database files. Why not DC?

[csb0730]

What's needed, IMHO ;-)

• [in progress] backup export/import process without vacuum at import (tar, sqlar)
• [ ] backup to external sd card (internal only if not existing or not working)
• [ ] manual trigger for housekeeping (vacuum)

[Hocuri]

the first point is already done, just not released yet. And the last one is also done-not-released, just that we run housekeeping once a day.

[csb0730]

that's great news :+1:

But I'm critical to "doing housekeeping once a day". You know hat you are copying the whole database (!) every time you'll do it (battery!!?) It should be worth to do it when triggered ;-)

And please think about second point: external backup target!

[Hocuri]

Letting the user choose a target is on the long-term-wishlist, but there are so many things to do.

I didn't measure that but a big db should not be bigger than, like, 5 MB (it's only text, no images etc.) and copying that will take about a second, which shouldn't make a difference in battery use if done once a day.

[r10s]

But I'm critical to "doing housekeeping once a day". You know hat you are copying the whole database (!) every time you'll do it (battery!!?) It should be worth to do it when triggered ;-)

housekeeping currently does not include VACUUM, it only deletes unused files as videos, pdf, images etc.

so, the database is not copied as a whole. with the new backup-approach, new databases should also not become that large anyway (probably just a few 10mb if it gets big)

[csb0730]

housekeeping currently does not include VACUUM, it only deletes unused files as videos, pdf, images etc.

so, the database is not copied as a whole. with the new backup-approach, new databases should also not become that large anyway (probably just a few 10mb if it gets big)

Ah I just see it. Vacuum has been taken out of the function housekeeping(). Then this is another game :+1:

But how could we have a so small database of 10MB? Evidence: In my case I have around 180MB in practice (after vacuum)! See above: 22000 messages * 2048 page size is 40MB. And many messages need more than one page per message! +indexes and +other stuff!

So I would calculate with 200MB for performance testing!

Vacuum is an expensive operation. It sould not be done every day. This is not necessary.

Ok, all this is off topic for an android issue. It's more for the core ;-)

[Hocuri]

Did I see it correctly that there is nothing that should still be done in this issue? Version 1.20 is released, which exports files in the tar format by default, so that no images will be put into the database anymore.

I'll just go ahead and close this issue; feel free to re-open it yourself or tell me and I'll re-open :)

If someone says that we should e.g. execute VACUUM from time to time, it's probably better to open a new issue for this in the core.

[STPKITT]

I got a new device in the meantime since opening this issue in 2020 and this device has much more storage space than the old one but I now found that the device storage was already near full again. Going through my app list I once again found Delta Chat to consume above 700 Mb despite very minor usage of the app by myself. As I did in 2020 I then actived deleting messages older than one year from the device and as in 2020 it only did so visually (claimed to have deleted 971 messages [about 900 of those must've been just normal emails which seem to get stored despite not being turned into a chat]. The database filesize didn't change at all and all the blob files are still there, even some from the very end of 2020. I don't even get how Delta Chat user data on Android blows up to 700 Mb when the blobs not even sum up to 100 Mb. The Delta Chat installation on my PC which I use with the exact same E-Mail-account currently has a user data size of "only" 350 Mb despite that it was always set to never automatically delete messages. So that discrepancy between the Android built and the desktop built of Delta Chat seems to be an issue by itself. For reference: I'm running Delta Chat version 1.30.2 installed from the F-Droid repo on my device running LineageOS 16 which is based on Android 9. So I'm reopening this issue since it still exists.

EDIT Well, I'd like to reopen this issue but can't figure out how to do it. So please @Hocuri do it for me if you can. Thanks!

[Hocuri]

I re-opened, but we still have no idea about what could cause this issue...

Are you able to browse Delta Chat's files directory and see what takes up the space? (probably using adb)

Alternatively, you could export a backup, and see if it's also 700 Mb big.

Also, see if you have multiple accounts (by tapping on your avatar in the top left corner or "Switch Account")?

The database filesize didn't change at all

How big is the database?

and all the blob files are still there, even some from the very end of 2020.

Blobs are only deleted after a day or so.

when the blobs not even sum up to 100 Mb.

So, the blobs aren't actually the problem?

[STPKITT]

I re-opened, but we still have no idea about what could cause this issue...

Thanks!

Are you able to browse Delta Chat's files directory and see what takes up the space? (probably using adb)

yes

Also, see if you have multiple accounts (by tapping on your avatar in the top left corner or "Switch Account")?

I have only one account.

The database filesize didn't change at all

How big is the database?

I just checked again and it is now 410 Mb. So contrary to what I thought 3 days ago it did now shrink a good amount, but it took one if not more days to do so. However, compared to my DeltaChat on PC, which was never set to delete anything but uses the same E-Mail-account, that 410 Mb is still enormous compared to 108 Mb on PC.

and all the blob files are still there, even some from the very end of 2020.

Blobs are only deleted after a day or so.

I just checked if the blobs were removed in the last days but they are all still there.

when the blobs not even sum up to 100 Mb.

So, the blobs aren't actually the problem?

The main of even only issue is the database filesize. I just made a deep dive into old messages and I think I can now say for sure that the blobs which are much older than one year and still not deleted from my device are not just ones which are in "Saved messages" (or however that is called in the English translation of DeltaChat, I mean the "chat" in which messages end up which I sent to myself). EDIT I just saw that all those blobs, that should've been deleted due to age, have the exact same timestamp down to the minute. Since some of them are also autocrypt setup message html files I suspect that on that date I installed DeltaChat. I still can't imagine though why those blobs are not deleted. Maybe just a one time issue or due to those blobs being part of an old DeltaChat installation which landed on the new device via a backup-restore of my previous Android system? Can I safely delete those blobs manually including the autocrypt setup message html files?

EDIT2 I just found one difference in setup between my DeltaChat installs on Android and PC which is that the PC is set to show normal E-Mails just for accepted contacts while on Android I had set it to all for the majority of time. However whenever I set it to show only accepted contacts or even only chats that change never reflected in the GUI which to me looks like an issue in itself. So maybe because on Android DeltaChat always showed all E-Mails that made the database grow so big? Just speculation by myself, since I don't know the inner workings of DeltaChat.

[Hocuri]

Can you copy the sqlite database to the computer (or extract it from a backup, it's just a TAR file), run VACUUM on it, and see if the size goes down? But it's possible that the messages that are still on your device just take up this much space.

FWIW, my own database with about 60.000 messages (which take up the majority of the space) has about 200 Mb.

So contrary to what I thought 3 days ago it did now shrink a good amount, but it took one if not more days to do so.

The shrinking taking some days seems OKish, and we shouldn't execute VACUUM more often since this would increase battery use.

So maybe because on Android DeltaChat always showed all E-Mails that made the database grow so big?

Non-chat emails are often HTML emails and therefore bigger, which would explain this.

What definitely stays is the question why your blobs were not deleted, I suspect a bug there. They are deleted during "housekeeping" which can be triggered manually by exporting a backup; can you export a backup and send a log?

Can I safely delete those blobs manually including the autocrypt setup message html files?

Yes, but still make sure you have a backup (also, then we won't find out why they were not deleted automatically)

[hpk42]

A long shot but something to look at: can you open the sqlite database (with sqlbrowser) and check if you have a "blobfiles" table? In my phone, i had an out-of-proportion DB and finally found that during some older version transition the "blobfiles" table was still there. This stems from an old backup format but usually the blobfiles table should be transferred to the filesystem and then deleted. You can safely drop the "blobfiles" table if it exists.

On Thu, Jun 09, 2022 at 08:03 -0700, STPKITT wrote:

I re-opened, but we still have no idea about what could cause this issue...

Thanks!

Are you able to browse Delta Chat's files directory and see what takes up the space? (probably using adb) yes

Also, see if you have multiple accounts (by tapping on your avatar in the top left corner or "Switch Account")? I have only one account.

The database filesize didn't change at all

How big is the database? I just checked again and it is now 410 Mb. So contrary to what I thought 3 days ago it did now shrink a bit, but it took one if not more days to do so. However, compared to my DeltaChat on PC, which was never set to delete anything but uses the same E-Mail-account, that 410 Mb is still enormous compared to 108 Mb on PC.

and all the blob files are still there, even some from the very end of 2020.

Blobs are only deleted after a day or so. I just checked if the blobs were removed in the last days but they are all still there.

when the blobs not even sum up to 100 Mb.

So, the blobs aren't actually the problem? The blobs are only part of the problem, the main issue is the database filesize. I just made a deep dive into old messages and I think I can now say for sure that the blobs which are much older than one year and still not deleted from my device are not just ones which are in "Saved messages" (or however that is called in the English translation of DeltaChat, I mean the "chat" in which messages end up which I sent to myself). So I think the blobs remaining there is an issue, too, though in my case they don't even consume 100 Mb.

-- Reply to this email directly or view it on GitHub: https://github.com/deltachat/deltachat-android/issues/1742#issuecomment-1151238576 You are receiving this because you are subscribed to this thread.

Message ID: @.***>

[STPKITT]

EDIT found out how to attach the log in a file instead of pasting it into the comment field.

Hi, so now I found the time to do all that.

I copied the database file to my PC and did a vacuum run. That reduced the filesize from 409 Mb to 348 Mb. Still seems huge to me.

I then triggered a backup export and saved the log which you'll find attached to this comment.

I then manually deleted the old blob files. So far DeltaChat didn't complain about them missing.

I finally also opened the database file (in it's original state prior to the vacuum process) in a SQLite Browser but did not find a "blobfiles" table.

DeltaChat Backupexport Log.txt

[r10s]

That reduced the filesize from 409 Mb to 348 Mb. Still seems huge to me.

so, VACUUM seems to work and the rows of the msgs table are not totally unexpected - so the result may just be correct, even if appearing huge.

i just checked my database, i have 830mb for 190,000 messages.

that, indeed, seems unexpected for only text messages.

i therefore ran sqlite3-analyzer and did some tests: 90% of the size belongs to the msgs table and there 70% to the mime_headers column. in your case, that may mean that more than 200mb are used in that column.

that part is easily overseen:mime_headerssaves the raw message for various reasons for many messages, among html display and debug.

while it may be worth to think about how the mime_headers column can be optimized, we should also see the overhead in relation to the blobs directory - 3gb in my case - so doing eg. deduplication there is probably more worthful.

i am closing this issue here as there is no actionable item left on android, if there are new findings that point to a bug, we can reopen a new one in the core.

please note, that closing here does not mean the discussion is finished!