Android Question How to avoid OS killing app or process when running large vacuum?

[RB Smissaert]

When running a large (large SQLite DB file) vacuum I have problems with the Android OS killing the app or perhaps just the vacuum process.
I say "perhaps just the vacuum process" as sometimes the app is not killed but the vacuum has done nothing, meaning the size of the DB file has not reduced
and also running:
pragma freelist_count
gives the same (non-zero) number before and after the vacuum.
There is no error message at all, so either the app or the vacuum process are killed silently.

As mentioned in a previous thread (getting too long now) regarding this I have a solution by making use of this SQLCipher function:
SELECT sqlcipher_export
but I am still interested to make the vacuum work.

RBS

 

[Erel]

Does it happen while the app is on the foreground?

If so then it is the OS killing the process because the main thread is occupied for too long. Switch the async method.

 

[RB Smissaert]

Does it happen while the app is on the foreground?

If so then it is the OS killing the process because the main thread is occupied for too long. Switch the async method.

Yes, it does happen when the app is in the foreground.

I thought running a SQL in async mode would put the SQL in a transaction and that that wouldn't work with a vacuum.
Will have a look at this again.

RBS

 

[OliverA]

Ee

I thought running a SQL in async mode would put the SQL in a transaction and that that wouldn't work with a vacuum.

ExecNonQueryBatch does use transactions (https://github.com/AnywhereSoftware...are/b4a/objects/sqlcipher/SQLCipher.java#L153)

ExecQueryAsync does not (https://github.com/AnywhereSoftware...are/b4a/objects/sqlcipher/SQLCipher.java#L201)

 

[RB Smissaert]

Ee

ExecNonQueryBatch does use transactions (https://github.com/AnywhereSoftware...are/b4a/objects/sqlcipher/SQLCipher.java#L153)

ExecQueryAsync does not (https://github.com/AnywhereSoftware...are/b4a/objects/sqlcipher/SQLCipher.java#L201)

Yes, you are right and working on this now.

RBS

 

[RB Smissaert]

Yes, you are right and working on this now.

RBS

I can run the vacuum now, but it finishes much too quick and it hasn't worked, judging from running pragma freelist_count.

This is the code that runs from a B4XPage where the user can run SQL from an edittext:

    strSQL2 = cMP.RemoveSQLComments(strSQL) 'takes 0 to 1 milli-seconds only

    If strSQL2.ToLowerCase = "vacuum" Then
        
        cMP.cConn.ExecQuery("PRAGMA busy_timeout = 1000000")
        
        CallSubDelayed(Tracker, "Track")
        Tracker.bNoTracking = True
        
        Sleep(1000)
        
        lblSQLResult.TextColor = Colors.Black
        lblSQLResult.Text = "Running SQL, please wait ..."
        Sleep(0)
        
        cMP.StartSW(4)
        Dim SenderFilter As Object = cMP.cConn.ExecQueryAsync("Vacuum", "VACUUM", Null)
        Wait For (SenderFilter) SQL_QueryComplete(Success As Boolean, oRS As ResultSet)
        
        Return Enums.eRunSQLResult.NonQueryDBAltered
        
    End If

Tracker is a service I run normally for large map downloads and that keeps the app alive with lock.KeepAlive(False)
where lock is PhoneWakeState

This is the code that runs in my connection class:

Sub Class_Globals
    
    Private cMP As B4XMainPage
    Public SQLMain As SQLCipher
    Public SQLNon_Clinical As SQLCipher
    Public SQL_KVS As SQLCipher
    Public SQL_MapnikOSM2 As SQLCipher
    Public SQL_NewDB As SQLCipher 'this is only for temp database copying
    
    Type tDataBase(None As Int, _
                   Main As Int, _
                   NON_CLINICAL As Int, _
                   KVS As Int, _
                   MapnikOSM2 As Int, _
                   NewDB As Int)
                   
    Public eDataBase As tDataBase
    eDataBase.None = 0
    eDataBase.Main = 1
    eDataBase.NON_CLINICAL = 2
    eDataBase.KVS = 3
    eDataBase.MapnikOSM2 = 4
    eDataBase.NewDB = 5

End Sub

Sub ExecQueryAsync(strEventName As String, strSQL As String, lstArgs As List) As Object
    
    Select Case miDataBase
        Case eDataBase.Main
            Return SQLMain.ExecQueryAsync(strEventName, strSQL, lstArgs)
        Case eDataBase.NON_CLINICAL
            Return     SQLNon_Clinical.ExecQueryAsync(strEventName, strSQL, lstArgs)
        Case eDataBase.KVS
            Return SQL_KVS.ExecQueryAsync(strEventName, strSQL, lstArgs)
        Case eDataBase.MapnikOSM2
            Return SQL_MapnikOSM2.ExecQueryAsync(strEventName, strSQL, lstArgs)
        Case eDataBase.NewDB
            Return SQL_NewDB.ExecQueryAsync(strEventName, strSQL, lstArgs)
        Case Else
            Return Null
    End Select
    
End Sub

Sub Vacuum_QueryComplete(bSuccess As Boolean, oRS As ResultSet)
    
    Log("Vacuum_QueryComplete, bSuccess: " & bSuccess)

    Tracker.bNoTracking = False
    CallSubDelayed(Tracker, "StopTracker")
    
    cMP.cSQLEdit.lblSQLResult.Text = "Finished vacuum, " & cMP.FormatMilliSecs(cMP.stopsw(4), False)
    
End Sub

Not sure it is relevant, but I am running (as you can see) SQLCipher.
At least the app or the process is not killed like this, but no vacuum takes place either.

RBS

 

[OliverA]

Dim SenderFilter As Object = cMP.cConn.ExecQueryAsync("Vacuum", "VACUUM", Null)
Wait For (SenderFilter) SQL_QueryComplete(Success As Boolean, oRS As ResultSet)

Should the above be either

'Change first paremeter to match event prefix in Wait For line
Dim SenderFilter As Object = cMP.cConn.ExecQueryAsync("SQL", "VACUUM", Null)
Wait For (SenderFilter) SQL_QueryComplete(Success As Boolean, oRS As ResultSet)

or

Dim SenderFilter As Object = cMP.cConn.ExecQueryAsync("Vacuum", "VACUUM", Null)
'Change event prefix to first parameter above
Wait For (SenderFilter) Vacuum_QueryComplete(Success As Boolean, oRS As ResultSet)

?

 

[RB Smissaert]

Should the above be either

'Change first paremeter to match event prefix in Wait For line
Dim SenderFilter As Object = cMP.cConn.ExecQueryAsync("SQL", "VACUUM", Null)
Wait For (SenderFilter) SQL_QueryComplete(Success As Boolean, oRS As ResultSet)

or

Dim SenderFilter As Object = cMP.cConn.ExecQueryAsync("Vacuum", "VACUUM", Null)
'Change event prefix to first parameter above
Wait For (SenderFilter) Vacuum_QueryComplete(Success As Boolean, oRS As ResultSet)

?

That is OK.
To avoid confusion I changed the first argument to: "DoVacuum".

RBS

 

[OliverA]

To avoid confusion I changed the first argument to: "DoVacuum".

And the Wait For is changed also?

Wait For (SenderFilter) DoVacuum_QueryComplete(Success As Boolean, oRS As ResultSet) ' Notice the changed event prefix from Vacuum to DoVacuum

If so, are you getting a different result?

 

[RB Smissaert]

And the Wait For is changed also?

Wait For (SenderFilter) DoVacuum_QueryComplete(Success As Boolean, oRS As ResultSet) ' Notice the changed event prefix from Vacuum to DoVacuum

If so, are you getting a different result?

Yes, both changed and result the same.

RBS

 

[OliverA]

both changed and result the same

I don't see where you check the Success value of the DoVacuum_QueryComplete. Is it showing a success? Also the Vacuum_QueryComplete in your connection class should be unnecessary.

 

[OliverA]

both changed and result the same

Now, if the phone is running out of memory, the task will be killed, and it looks like VACUUM usage w/a large SQLCipher DB may do just that: https://github.com/sqlcipher/sqlcipher/issues/328

 

[RB Smissaert]

I don't see where you check the Success value of the DoVacuum_QueryComplete. Is it showing a success? Also the Vacuum_QueryComplete in your connection class should be unnecessary.

Yes, the log shows succes is true.
The Vacuum_QueryComplete is used to stop the tracker service and to update the interface.

RBS

 

[RB Smissaert]

Now, if the phone is running out of memory, the task will be killed, and it looks like VACUUM usage w/a large SQLCipher DB may do just that: https://github.com/sqlcipher/sqlcipher/issues/328

Lack of memory was my first thought, but I have seen: app isn't responding messages. Also, there is no trouble at all with the sqlcipher_export function, which I expect requires a similar amount of memory.

RBS

 

[OliverA]

Do you have enough space? For a 4.7 gb file, it looks like you need 9.4 for the vacuum. 4.7 for the new file and 4.7 for the WAL file that is created for the process.
Link: https://www.sqlite.org/lang_vacuum.html

 

[RB Smissaert]

Lack of memory was my first thought, but I have seen: app isn't responding messages. Also, there is no trouble at all with the sqlcipher_export function, which I expect requires a similar amount of memory.

RBS

Could the problem be that we are running
Vacuum with ExecQueryAsync, where as in non-async mode we would run it with ExecNonQuery? It seems there is no ExecNonQueryAsync.

RBS

 

[OliverA]

It seems there is no ExecNonQueryAsync

No, and maybe that is the issue. Time for a wish?

 

[OliverA]

Time for a wish?

This could also be tested using @agraham's threading library. This may be one of the cases where it would be necessary to use (until a permanent fix is offered). Just beware of interacting with code that runs on the main thread. Look at the included example on how to properly raise events from the thread.

Link: https://www.b4x.com/android/forum/threads/threading-library.6775/#content

 

[RB Smissaert]

Do you have enough space? For a 4.7 gb file, it looks like you need 9.4 for the vacuum. 4.7 for the new file and 4.7 for the WAL file that is created for the process.
Link: https://www.sqlite.org/lang_vacuum.html

Yes, there is enough space.

RBS

 

[RB Smissaert]

This could also be tested using @agraham's threading library. This may be one of the cases where it would be necessary to use (until a permanent fix is offered). Just beware of interacting with code that runs on the main thread. Look at the included example on how to properly raise events from the thread.

Link: https://www.b4x.com/android/forum/threads/threading-library.6775/#content

Thanks, will have a look at that.

RBS