[Performance] Long polling with mysql during a periodic interval

[lucascesconetto]

I need to do a long polling, while handling an http request from a client of my API, to give an adequate response.

Until this data is provisioned by a CDC flow from Kafka to my database, the application does long polling at periodic ReactPHP intervals in my database using your mysql client.

I would like suggestions on how I can optimize this flow of many queries in my database, what is the best way to configure your mysql client so as not to generate bottlenecks in my database or in my application?

The Application runs in multiple containers.

Thank you for suggestions!

Example:

private function startPolling(string $id): TimerInterface
    {
        return $this->loop->addPeriodicTimer(
            interval: $this->pollingConfig->getPeriodicTimerInterval(),
            callback: function () use ($id) {
                $task = $this->asyncListenerDao->find(id: $id);
                $this->asyncTaskManager->run(task: $task, completionRule: new RuleExecutable(),  canceller: fn() => null);
            }
        );
    }

[WyriHaximus]

There is no configuration to tweak this on the client it self. To improve performence here is what I would do:

Combine all periodic timers into one per container polling all outstanding long polls in one query so you have one bigger query instead of countless many small queries and timers. This will make both the event loop and the database server less busy. However I also would consider doing the queries in groups of 1000, or another number after some research which gives you the best performance vs busyness ratio.