(501 - Server not reachable or broken connection) due to slow 'vacuum'

edwinsn · 2023-02-24 02:27:12

Hi ab,

I have a situation and I'm wondering if you can give me some advises.

I have an interface-based service (IMyDbApi) and it has a method `VacuumDb` which calls `vacuum` on the SQLite db. The db file is around 10 GB and the vacuum operation took very much long time until a "(501 - Server not reachable or broken connection)" error is raised:

exception class    : EInterfaceFactoryException
exception message  : TInterfacedObjectFakeClient.FakeCall(IMyDbApi.VacuumDb) failed: 'URI root/IMyDbApi.VacuumDb/8308 [] returned status 'Not Implemented' (501 - Server not reachable or broken connection)'.

main thread ($1f4c):
main thread ($1f4c):
00a14386 MyProgram1.exe mORMot               53552 RaiseError
00a14713 MyProgram1.exe mORMot               53638 InternalProcess
00a14ac7 MyProgram1.exe mORMot               53707 TInterfacedObjectFake.FakeCall
010dd674 MyProgram1.exe BaseAccountEditFormU   245 TfrmBaseAccountEdit.TryDeleteAccount

Where is the issue do you think? Is there some kind of locking happening? The service method is quite simple:

function IMyDbApi.VacuumDb: Boolean;
var
  mySvr: TSQLRestServer;
begin
  Result := False;
  mySvr := self.fServer;
  if mySvr.TransactionActiveSession <> 0 then
    Exit;

  Result := mySvr.Execute('vacuum'); // note: vacuum is slow!
end;

Last edited by edwinsn (2023-02-24 06:54:35)

edwinsn · 2023-02-24 06:51:23

Update 1: Was trying to see if the auto_vacuum pragma can get around the hanging issue, but obviously the pragma cannot be changed in an existing db with existing tables: https://www.sqlite.org/pragma.html#pragma_auto_vacuum

Update 2: Is it possible that the custom collation used by mORMot affecting this issue? When I trying to execute 'VACUUM' with Sqlite Studio, I got the following error (we usually see this when we try to modify a database created by mORMot with an external tool):
Error while executing SQL query on database 'db1.sqlitedb': no such collation sequence: SYSTEMNOCASE

Last edited by edwinsn (2023-02-24 07:12:45)

ab · 2023-02-24 08:41:14

You should better use VACUUM on an external tool, if you can.
An alternative could be to backup the database in the background using the corresponding mORMot command: a backup database is already VACCUMed IIRC.

And to support the mORMot collations, use the SynDBExplorer tool of mORMot 1.

First enure you really need the VACUUM, because in practice, it only is effective if you made a lot of deletes/updates with various content. It is not worth it in most cases.

edwinsn · 2023-02-24 10:04:14

Thanks ab.

So tried executing VACUUM with SynExplorer, the result is similar - it took a long time (over 15 minutes I guess) and returned, but checking the db disk file the size didn't change...

Maybe I'll have to try the backup API...

Yes, I use VACUUM only after deleting a lot of records.

ab · 2023-02-24 11:08:19

If VACUUM on SynDBExplorer didn't change the disk file size, it means that it was not worth it.

Even after deleting a lot of records, it is likely that SQLite3 would manage to not waste any space.

edwinsn · 2023-02-24 12:18:15

ab wrote:

If VACUUM on SynDBExplorer didn't change the disk file size, it means that it was not worth it.
Even after deleting a lot of records, it is likely that SQLite3 would manage to not waste any space.

But in this case, I'm sure there were a lot record has been deleted and a lot of spaces should be freed by VACUUM.

edwinsn · 2023-03-20 09:39:45

ab wrote:

An alternative could be to backup the database in the background using the corresponding mORMot command: a backup database is already VACCUMed IIRC.

Strange, backup works, but the file size is the same...

mORMot Open Source

#1 2023-02-24 02:27:12

(501 - Server not reachable or broken connection) due to slow 'vacuum'

#2 2023-02-24 06:51:23

Re: (501 - Server not reachable or broken connection) due to slow 'vacuum'

#3 2023-02-24 08:41:14

Re: (501 - Server not reachable or broken connection) due to slow 'vacuum'

#4 2023-02-24 10:04:14

Re: (501 - Server not reachable or broken connection) due to slow 'vacuum'

#5 2023-02-24 11:08:19

Re: (501 - Server not reachable or broken connection) due to slow 'vacuum'

#6 2023-02-24 12:18:15

Re: (501 - Server not reachable or broken connection) due to slow 'vacuum'

#7 2023-03-20 09:39:45

Re: (501 - Server not reachable or broken connection) due to slow 'vacuum'

Board footer