When debugkit SQLite size is after some size it's never cleared again

[Harfusha]

When debugkit SQLite file size is bigger than 500 MB (On our system), rows are deleted, but vacuum is never called successfully.

When we tried to call it manualy this error occured: sqlite> VACUUM; Error: out of memory

Maybe instead of just VACUUM; should be called VACUUM INTO {FILE}; to reduce memory requirements.

And another problem: When we were debugging this issue we added counter of requests between garbage collection and it normally reached ~1000 requests and in this moment the database was over 2GB in size -> so VACUUM throwed out of memory error

[LordSimal]

If you don't care about the data inside debug_kit (so saved request data) then you can just remove that tmp/debug_kit.sqlite and you are fine. It gets automatically recreated.

I have not yet run into this problem. My current tmp/debug_kit.sqlite is 223MB big and it vacuums just fine.

Garbage collection/vacuum happens automatically after around 100 requests as you can see here

Also you can configure how many requests are being preserved while doing GC by setting DebugKit.requestCount to e.g. 10 instead of the default 20

If you don't want SQLite to be used for debug kit then you can easily just configure another DBMS to be used for saving request data for debug kit

[Harfusha]

But problem is that is is growing until no more space on server is left

Garbage collection/vacuum happens automatically after around 100 requests as you can see here I know but when the issue i described happened it was 1156 requests from last GC

[LordSimal]

The Debug Kit is only meant to be used when developing the website (aka in your local setup), not on your live website/server.

And besides: Not enough diskspace is not the same as your reported out of memory problem.

[Harfusha]

I know that 1% chance of GC should call it ~1 in 100 requests, but when it doesnt the file can get to size where it causes issues. Shouldn't there be some hardlimit when to call GC forcefully (without the 1% hit?).

And shouldn't there be atleast log that vacuum command failed and the some other action with is? Like deleting the database so it can restore to normal state automatically?

I dont want to watch the debug_kit file all the time so it doesnt get to size when it cannot be automatically rotated.

The Debug Kit is only meant to be used when developing the website (aka in your local setup), not on your live website/server.

And besides: Not enough diskspace is not hte same as your reported out of memory problem.

I know, but we have testing server, where testers are testing the app and they have debug_kit enabled. When the debug_kit gets after the 500 MB size, the vacuum command starts failing and then it is only growing until someone notice (Normally when the server run out of memory)

[LordSimal]

It does log on error as you can see here

---

A rather simple solution would be to show the sqlite filesize in the toolbar. When clicking on it, a separate window will popup with a delete button (if sqlite is used)

Then you can easily recreate/delete the old sqlite database from the browser.

[Harfusha]

Will change the driver from sqlite to mysql so i dont have to deal with manual deletions

[Harfusha]

I would like to reopen this issue with new solution i found. We tried saving the debugkit in mysql, but we have binlog enabled on our server so i was much bigger with the logs.

I've changed DebugKit/Model/Entity/Panel.php so it deflates and inflates data using getter and setter. (Variable panel content was deflated ~70%)

Another changes i made was changing the GC chance from 1% to 10% and adding PRAGMA auto_vacuum = 1; before VACUUM;.

This resulted in more frequent but faster GCs. To make sure i will make full vacuum from time to time i added the 10% chance of every GC to execute full vacuum. If any error occure when full vacuuming the database i will delete the database file.

On same benchmark when the database was 12 GB in size, with these changes was 400 MB.

If you want to implement any of these i will make PR (And make it more pretty, not just hardcoded for my usecase)

[markstory]

I think these could be good improvements, and a pull request would be appreciated. With gzip encoding, we also need to be backwards compatible with existing database rows. If we can handle both gzipped and plain encoded data that would be ideal.

[Harfusha]

Can i backport it for cakephp 4 when PR will be finished?

[markstory]

Can i backport it for cakephp 4 when PR will be finished?

Of course, that would be appreciated.