Android Question Vacuum SQLite DB with SQLCipher

RB Smissaert said:

File size remains the same though at 4.9 Gb (B4A File.Size, 4.5 Gb in Windows

Click to expand...

RB Smissaert said:

I am guessing that the file has indeed been vacuumed OK, but that for some reason Android (or B4A) sticks to the old file size.
Any suggestions what might be going on here?

Click to expand...

Erel said:

You need to divide the returned size with 1024 / 1024 / 1024. This will return the value in gigabytes.


This is managed by SQLCipher.

Click to expand...

RB Smissaert said:

OK, my number of bytes formatter was a bit out as I used 1000 rather than 1024.
Thanks for correcting that, but that will give the value of 4.5 Gb and not 3.8 Gb as on the Windows PC after
doing the vacuum in DB Browser for SQLite.

> This is managed by SQLCipher
Not sure what you mean by that. When the phone is connected to the PC with a USB cable, Windows can see the file
and sees the file size as 4.5 Gb.

RBS

Click to expand...

RB Smissaert said:

OK, I can see that the DB file is not vacuumed.
I take it that Android stopped the process either because of lack of memory or due to lack of activity for too long.
I had no app crash and the app was still functioning normal and maybe that was because only the vacuum
process was stopped. Just guessing here.
I am thinking that I may need to run the vacuum from a service that won't be stopped.
The other thing to think of might be to run the vacuum from a separate app, just to reduce the memory overhead.

Thanks for any suggestions how to tackle this.
Has anybody successfully vacuumed a SQLite DB file of this size from an Android B4A app?

RBS

Click to expand...

Sub VacuumDB As ResumableSub
    
    Dim lDBFileBytes1 As Long
    
    lDBFileBytes1 = File.Size(File.DirInternal, Enums.arrDBFileNames(1))
    Log("VacuumDB, lDBFileBytes1: " & lDBFileBytes1)
    
    cConn.ExecNonQuery("VACUUM")
    
    Return True
    
End Sub

sirjo66 said:

Are you sure that you don't need to execute a "compact" command ?

Click to expand...

sirjo66 said:

Are you sure that you don't need to execute a "compact" command ?

Click to expand...

BlueVision said:

My thoughts on this:
If there is no more air in the database, you can't squeeze any air out...
Maybe you can have a look at the encrypted database with a viewer, you have the key (a challenge with this amount of data though). To be honest, I've never looked at a database encrypted with SQLCipher with a viewer.
I do have a few Android apps using SQL, but all without encryption.
However, I do have such an application under B4J. However, I don't use SQLCipher there (a paid library, unlike Android). I encrypt the data beforehand with AES and pack the encrypted string into the database.

I noticed the following, it may not apply to SQLCipher, but you could check it once:
If I encrypt a "SPACE" with AES, it becomes a longer string. As a result, there are no empty database entries in the database that could be eliminated using the VACUUM function. It is quite possible that this is different with SQLCipher. However, it should be easy to check.

I also have no explanation for the different file sizes. This may have something to do with different file systems, but such a large difference is very unlikely even with this explanation.
Another explanation would be if the database is decrypted on the PC and then re-encrypted on Android. This may cause the overhead you have noticed.

Edit:
VACUUM is not a complex function. You can do that with a programmed function too. Simply copy every dataset with data into a new database, while skipping empty records. VACUUM is doing nothing else. Then compare the filesize of the original and newly created database.

Additionally look here

Click to expand...

BlueVision said:

My thoughts on this:
If there is no more air in the database, you can't squeeze any air out...
Maybe you can have a look at the encrypted database with a viewer, you have the key (a challenge with this amount of data though). To be honest, I've never looked at a database encrypted with SQLCipher with a viewer.
I do have a few Android apps using SQL, but all without encryption.
However, I do have such an application under B4J. However, I don't use SQLCipher there (a paid library, unlike Android). I encrypt the data beforehand with AES and pack the encrypted string into the database.

I noticed the following, it may not apply to SQLCipher, but you could check it once:
If I encrypt a "SPACE" with AES, it becomes a longer string. As a result, there are no empty database entries in the database that could be eliminated using the VACUUM function. It is quite possible that this is different with SQLCipher. However, it should be easy to check.

I also have no explanation for the different file sizes. This may have something to do with different file systems, but such a large difference is very unlikely even with this explanation.
Another explanation would be if the database is decrypted on the PC and then re-encrypted on Android. This may cause the overhead you have noticed.

Edit:
VACUUM is not a complex function. You can do that with a programmed function too. Simply copy every dataset with data into a new database, while skipping empty records. VACUUM is doing nothing else. Then compare the filesize of the original and newly created database.

Additionally look here

Click to expand...

RB Smissaert said:

Have a large SQLite DB, file size is 4.9 Gb, this is according to B4A File.Size (In Windows it shows at about 4.5 Gb).
The file is in File.DirInternal.
I copied the file to external storage (after closing the DB connection) and then copied the file to my Windows PC.
I then did a vacuum in DB Browser for SQLite and this took a bit over 3 minutes and reduced the file size to 3.8 Gb.

I then did a vacuum in the B4A app (connecting with SQLCipher) and this ran all smoothly with no errors.
It took 5 minutes.
File size remains the same though at 4.9 Gb (B4A File.Size, 4.5 Gb in Windows).

I closed the app and restarted and also shut down the phone and restarted, but the sizes for that DB remain the same.

I am guessing that the file has indeed been vacuumed OK, but that for some reason Android (or B4A) sticks to the old file size.
Any suggestions what might be going on here?

RBS

Click to expand...

RB Smissaert said:

I tried this on on a much smaller and also unencrypted DB and the vacuum worked fine (file size reduced some 10%).
Still connecting with SQLCipher.
So it looks there is a problem vacuuming either very large DB's or encrypted DB's.
Will investigate further.

RBS

Click to expand...

RB Smissaert said:

Have a feeling that the file size is the problem.

Click to expand...

BlueVision said:

Don't forget, that you need a lot of free space for vacuuming such large databases. As said, it's nothing else than copying the content into a new database, deleting the old one and renaming the new one. So if the source is about 5GB, you need a minimum of 5GB free space for vacuuming. Probably a little bit more...
Sounds a little bit like a teacher. Sorry for this, pls. don't interpret my statement in this way. Just pay attention to that.

Click to expand...

RB Smissaert said:

Is there any way to find out what happens when the app completely silently crashes?

When I vacuum the 4.5 Gb encrypted file it all runs fine, no crash, but also no file reduction at all.
When I vacuum the nearly 7 Gb, unencrypted file it very quickly crashes with no error message at all.
When I vacuum a 6.7 Mb, unencrypted file it runs all fine and file size is reduced by 10%.

It is difficult to fix this (not sure it is fixable) without knowing what exactly is going on.

RBS

Click to expand...

RB Smissaert said:

Are you referring to non-disc space?

Click to expand...

BlueVision said:

No. Referring only to disc space.
All other should be arranged by the operating system, controlling available ressources. Lack of ressources should only result in a longer time needed for the operation.
Strange thing indeed...

I am not that familiar with the logging functions of sqlite, maybe there is way to switch something on/off (probably depending on the library used)?
Looks to me that the function is aborted for some reason, without notifiying you or damaging the database or its structure.

Click to expand...

BlueVision said:

Well, everything has it's limitations. When modifiying such large databases (encrypted or not) with a single command (the command to vacuum it), could result in a problem with the algorithm of that command itself, when it comes to garbage collection effects or similar during execution of that command. I think there is a complex algorithm behind the vacuum command, eventually struggeling by the amount of data and additionally encryption.

Maybe worth trying:
When you know there are "large gaps" within the database and the database should get a "deflation", try it by replacing the vacuum command by a self-written routine. Copy the content from the old database to a new one, record by record. This should eliminate the gaps too. Then compare the size of both databases. This is only a workaround for testing, you have more control to see what happens. Should not take much longer. This splits the whole process into smaller pieces and gives you more control compared to the vaccuum command.

Click to expand...