Android Question Vacuum SQLite DB with SQLCipher

[RB Smissaert]

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

 

[Erel]

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

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

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?

This is managed by SQLCipher.

 

[RB Smissaert]

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


This is managed by SQLCipher.

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

 

[RB Smissaert]

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

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

 

[RB Smissaert]

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

Have made it so that the DB file vacuum runs at the very startup of the app, just to reduce memory taken up by the app.
It all runs fine with no errors, but the DB file remains exactly the same before and after the vacuum, that is 4860067840
Time to run the vacuum is always the same as well, just under 5 minutes.
The vacuum is done by simply running this on the main database file (no other DB files are initialized yet at this stage):

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

I have no idea why the file size remains the same.

 

[sirjo66]

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

 

[BlueVision]

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

 

[RB Smissaert]

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

Thanks, will have a look into that.

RBS

 

[RB Smissaert]

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

Can't find anything about that compact command.
How is that done?

RBS

 

[RB Smissaert]

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

The thing is that DB Browser for SQLite reduces the file size by 16%, simply by running vacuum where as nil happens in my B4A app, running the same vacuum sql taking 5 minutes.
I guess something must be going wrong.
Trouble is there is no error message at all.

RBS

 

[RB Smissaert]

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

Did have a look as well at your mentioned VACUUMED INTO, but the SQLite version of my SQLCipher is 3.25.2, so that can't work.
I understand there is community version (free) of SQLCipher with a newer version of SQLite, but I can't see where I can get that to be used with B4A.

RBS

 

[RB Smissaert]

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

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

 

[RB Smissaert]

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

Have a feeling that the file size is the problem.
Tried with an unencrypted DB file with size of just under 7 Gb and unable to do the vacuum with silent crashes.
As the crashes are silent (no error message in the IDE) I have a feeling that lack of memory is the problem.

It looks then that the only option to vacuum these large DB files is to copy to Windows PC and do the vacuum in
DB Browser (for SQLCipher).

RBS

 

[BlueVision]

Have a feeling that the file size is the problem.

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.

 

[RB Smissaert]

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.

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

 

[RB Smissaert]

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

I forgot to say that on disk space seems plenty on this phone as internal storage shows 171 Gb free out of 222 Gb.
Are you referring to non-disc space?

RBS

 

[BlueVision]

Are you referring to non-disc space?

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.

 

[RB Smissaert]

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.

Strange indeed.
Will investigate further.

I would be very interested if anybody has managed to vacuum an encrypted file of say 5Gb in a B4A app, using SQLCipher.

RBS

 

[BlueVision]

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.

 

[RB Smissaert]

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.

I could try that but it is quite a bit of work and simpler just to copy to the PC and vacuum in DB Browser (for SQLCipher).

RBS