Deadlocks on insert (in production)

Question

Deadlocks on insert (in production)

Closed this issue a year ago · 1 comments

joelvh commented a year ago

Pulse Version

1.1.0

Laravel Version

10.48.10

PHP Version

8.3.4

Livewire Version

3.4.12

Database Driver & Version

MySQL 8.0.34 (PlanetScale - 2 vCPU, 16 GB memory)

Description

We are getting regular deadlocks for insert into pulse_aggregates in our production environment. Here's some data from PlanetScale.

We have a lot of writes in a 24 hr period (chart here shows all writes, not just Pulse-related). The table shows only Pulse-related metrics:

Any ideas why so many deadlocks on insert?

And would these deadlocks block regular application requests/jobs from completing, or is this asynchronous from the application running?

Thank you for any help here!

Steps To Reproduce

I imagine the way to reproduce this is via some sort of load testing -- assuming the issue is volume-related. Aside from that, unclear what is causing this only in our production environment

Answer 1 · 2024-05-27T23:28:03.000Z

Hey @joelvh,

Pulse relies on upsert queries to pre-aggregate data into buckets. If you have enough traffic, multiple queries can try to update the same aggregate row at the same time. The upserts are delayed until after the HTTP response has been sent to the browser, so it shouldn't impact the request. However, it can tie up the web worker with extra work before it can handle another request.

In these cases, we recommend using the Redis Ingest. It allows your web workers to quickly dump the metrics onto a Redis stream and then a single background process can batch insert them into the database without having to fight other processes.