Grocy add-on becoming slower and slower over time

[psa-anddev]

Problem/Motivation

I am running version 0.19.1 of the add-on using Home Assistant Core 2023.5.3 in Home Assistant OS 2023.04.1. This is running on a VM in a Proxmox server where I am running other VMs. At first, the add-on worked as expected, showing pages quickly and behaving as expected. However, over time, the add-on has become slower and slower. When I click on the web ui, it takes over a minute to load the stock overview. It also takes a long time to do anything such as consume products, mark chores as done or virtually anything. Restarting the add-on makes it work well for a while but in minutes it starts behaving slowly again. Funnily enough, restarting the add-on in an of itself is slow. I use the Grocy Android app to be able to update the stock overview quickly but, again, it is slow and unresponsive. Eventually, I see myself forced to log out and go through the whole process again and again.

Other add-ons, such as Zigbee2MQTT work flawlessly and they respond quickly as expected. That makes me think it is not a problem with HA itself. I wanted to isolate where the problem is so I took a back up I had from a week ago and extracted the files that Grocy use. With these, I replicated the same set up in a Docker container using the linuxserver image for Grocy, which I was using without any problems before I moved Home Assistant to Proxmox. What I found is that with the exact same database that I had a week ago, the application behaves in a responsive manner. I also decided to try to configure my Grocy Android app to use the container and it is, again, as responsive as expected.

I have a port open because I use the Grocy HACS integration to have Grocy sensors in Home Assistant, I tried to log in accessing it from outside Home Assistant and after waiting for 3 minutes, I got a database locked error in the file /var/www/grocy/vendor/morris/lessql/src/LessQL/Database.php:493

I have looked at other issues that have been brought up regarding the performance of both the add-on and Grocy itself. The ones I've seen in this repository make the argument that this add-on only wraps Grocy so if there is a performance issue, it should be reported to Grocy itself. However, given that the exact same database works with the same version of Grocy running on Docker, I think there is something going on with the add-on itself. I have checked the database and it is not that big (around 300K).

Expected behavior

Grocy should behave in the same manner when using the add-on as it does when using Docker to configure it.

Actual behavior

Grocy add-on keeps becoming slower and slower up to the point that it cannot be used. Using the same database, running Grocy on Docker works flawlessly.

Steps to reproduce

Just install Home Assistant OS in a VM and use the Grocy add-on for a while (it is around 1-2 months that I've been using it).

Proposed changes

I am really not sure since I don't know how the add-on is put together.

More info

Here is the stack trace for the database locked issue I mentioned before. Error source:

/var/www/grocy/vendor/morris/lessql/src/LessQL/Database.php:493

Error message:

SQLSTATE[HY000]: General error: 5 database is locked (View: /var/www/grocy/views/stockoverview.blade.php)

Stack trace:

#0 /var/www/grocy/vendor/illuminate/view/Engines/PhpEngine.php(60): Illuminate\View\Engines\CompilerEngine->handleViewException()
#1 /var/www/grocy/vendor/illuminate/view/Engines/CompilerEngine.php(61): Illuminate\View\Engines\PhpEngine->evaluatePath()
#2 /var/www/grocy/vendor/illuminate/view/View.php(139): Illuminate\View\Engines\CompilerEngine->get()
#3 /var/www/grocy/vendor/illuminate/view/View.php(122): Illuminate\View\View->getContents()
#4 /var/www/grocy/vendor/illuminate/view/View.php(91): Illuminate\View\View->renderContents()
#5 /var/www/grocy/vendor/berrnd/slim-blade-view/src/Blade.php(173): Illuminate\View\View->render()
#6 /var/www/grocy/vendor/berrnd/slim-blade-view/src/Blade.php(65): Slim\Views\Blade->fetch()
#7 /var/www/grocy/controllers/BaseController.php(180): Slim\Views\Blade->render()
#8 /var/www/grocy/controllers/BaseController.php(203): Grocy\Controllers\BaseController->render()
#9 /var/www/grocy/controllers/StockController.php(119): Grocy\Controllers\BaseController->renderPage()
#10 /var/www/grocy/vendor/slim/slim/Slim/Handlers/Strategies/RequestResponse.php(38): Grocy\Controllers\StockController->Overview()
#11 /var/www/grocy/vendor/slim/slim/Slim/Routing/Route.php(358): Slim\Handlers\Strategies\RequestResponse->__invoke()
#12 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(65): Slim\Routing\Route->handle()
#13 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(65): Slim\MiddlewareDispatcher->handle()
#14 /var/www/grocy/vendor/slim/slim/Slim/Routing/Route.php(315): Slim\MiddlewareDispatcher->handle()
#15 /var/www/grocy/vendor/slim/slim/Slim/Routing/RouteRunner.php(68): Slim\Routing\Route->run()
#16 /var/www/grocy/middleware/LocaleMiddleware.php(16): Slim\Routing\RouteRunner->handle()
#17 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(269): Grocy\Middleware\LocaleMiddleware->__invoke()
#18 /var/www/grocy/middleware/AuthMiddleware.php(76): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#19 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(269): Grocy\Middleware\AuthMiddleware->__invoke()
#20 /var/www/grocy/vendor/slim/slim/Slim/Middleware/RoutingMiddleware.php(45): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#21 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(121): Slim\Middleware\RoutingMiddleware->process()
#22 /var/www/grocy/vendor/slim/slim/Slim/Middleware/ErrorMiddleware.php(76): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#23 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(121): Slim\Middleware\ErrorMiddleware->process()
#24 /var/www/grocy/middleware/CorsMiddleware.php(30): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#25 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(269): Grocy\Middleware\CorsMiddleware->__invoke()
#26 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(65): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#27 /var/www/grocy/vendor/slim/slim/Slim/App.php(199): Slim\MiddlewareDispatcher->handle()
#28 /var/www/grocy/vendor/slim/slim/Slim/App.php(183): Slim\App->handle()
#29 /var/www/grocy/app.php(106): Slim\App->run()
#30 /var/www/grocy/public/index.php(45): require_once('...')
#31 {main}

System info:

{
    "grocy_version": {
        "Version": "3.3.2",
        "ReleaseDate": "2022-11-12"
    },
    "php_version": "8.1.19",
    "sqlite_version": "3.41.2",
    "os": "Linux 6.1.25 #1 SMP PREEMPT_DYNAMIC Tue Apr 25 12:19:31 UTC 2023 x86_64",
    "client": "Mozilla\/5.0 (X11; Linux x86_64) AppleWebKit\/537.36 (KHTML, like Gecko) QtWebEngine\/5.15.9 Chrome\/87.0.4280.144 Safari\/537.36"
}

[psa-anddev]

After refreshing the page twice, I got the stock overview to show up using the open port to the add-on but the part where it loads the number of overdue products and so on took more than a minute to be populated.

[psa-anddev]

One update on this issue. Yesterday, I backed up the add-on data as a way to preserve the latest version of the database. After doing that, suddenly Grocy worked as it should for the whole day. This morning, I tried to access to it again both from Grocy Android (which it failed with a NetworkError) and using the web UI which worked but it went really slow. That makes me thing that there might be a problem in the way the add-on is handling either the database or any of the files that conforms the configuration of the add-on.

[psa-anddev]

After working with my Home Assistant set up a bit longer, I'm noticing the same symptoms in other places such as when restarting the Home Assistant host. Since I no longer thing there is a problem with the add on, I'm going to close the issue. Thank you for your good work putting up this add on.