Improve purging speed by selecting more than 998 elements

Feature Requests
,
1

Hello,

there is a constant defining the maximum number or rows to be purged at a time. This constant is bound to some “old sqlite” version - while I am unsure how old that is. But maybe that limitation can be “improved” by now?

Also: I am using an external mariadb which should not have that limitation. Maybe the number could be increased for non-sqlite setups?

thanks and keep up the great work!

edit:
sqlite version seems to be this:

>>> import sqlite3
>>> sqlite3.sqlite_version
'3.34.1'

edit2:
I did experiments within a test environment:

  • ha in vscode docker-dev-env
  • mariadb in docker (11.0.2-MariaDB-1:11.0.2+maria~ubu2204)

SQLITE_MAX_BIND_VARS = 50000
=> ERROR: The total number of locks exceeds the lock table size; purge_entity_data not completed.

This could be avoided by increasing innodb_buffer_pool_size - but there is no reason in adapting the config that that special purpose.

A value of 30000 worked, but was a bit slow.
A value of 20000 worked really good.

2

Change the number, test it, submit a PR.

3

I have do not have a sqlite database to test it with.
mariadb tests: see above.

4

PR here:

Testing on real sqlite needed.
Testing on RaspberryPi needed.