jessarcher
commented
8 months ago Hi @sebastiaanluca,
I haven't seen this one before with Pulse. You've already mentioned that the issue existed before the custom recorders, but I've looked through their source code anyway and can't see any obvious problems.
I'm trying to nail down what circumstances would cause a deadlock. In the error you've included, the rows being upserted include a specific user ID (users:1:roles), so the chances for a deadlock on those rows should be even smaller than most other data Pulse collects, but perhaps that's a red herring.
A few questions:
- Are you able to provide a few other occurrences of the error? I'm curious to see more examples of the data being upserted (just be sure not to share any sensitive data).
- Given the key in the example references a user ID of "1" (I assume an admin), I'm wondering whether the error only occurs while the Pulse dashboard is open and polling for data?
- I'm also curious about what's happening in your Pulse auth gate, especially given the mention of "roles" in the error message.
- Roughly how many requests does your application receive, and could there be a lot of concurrent requests? For example, is it an SPA that fires off multiple concurrent API requests to your backend?
- How many rows are in your
pulse_aggregatestable? - Can you share the results of running the SQL query:
SHOW CREATE TABLE pulse_aggregates- I'm curious to check if you have the latest indexes, which should reduce the scope of locked rows. - Can you also please run
SHOW ENGINE INNODB STATUSand look for theLATEST DETECTED DEADLOCKsection and share that (again, making sure not to share anything sensitive).
It's also worth mentioning that using a Redis ingest would likely prevent this issue, but I'd still like to get to the bottom of the problem if you're able to help debug it.
sebastiaanluca
Pulse Version
1.0.0-beta8
Laravel Version
10.39.0
PHP Version
8.2.4
Livewire Version
3.3.5
Database Driver & Version
MySQL Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)
Description
On our current setup, we regularly get these deadlock issues reported to Sentry. Happened 14 times now since we added Pulse on the 14th of December. The more visitors, the more deadlocks happen. It's not limited to one type of recorder, but happens for all. And we do have extra plugins/cards added, but it already happened once before we added any extras.
Small note: I've set up a dedicated new MySQL database just for Pulse so the app queries wouldn't interfere with those of Pulse. Check further down for the configuration.
Stack trace
An example exception:
Raw stack trace:
Configuration
config/database.phpexcerpt:config/pulse.php
``` env('PULSE_DOMAIN'), /* |-------------------------------------------------------------------------- | Pulse Path |-------------------------------------------------------------------------- | | This is the path which the Pulse dashboard will be accessible from. Feel | free to change this path to anything you'd like. Note that this won't | affect the path of the internal API that is never exposed to users. | */ 'path' => env('PULSE_PATH', 'debug/pulse'), /* |-------------------------------------------------------------------------- | Pulse Master Switch |-------------------------------------------------------------------------- | | This configuration option may be used to completely disable all Pulse | data recorders regardless of their individual configurations. This | provides a single option to quickly disable all Pulse recording. | */ 'enabled' => env('PULSE_ENABLED', true), /* |-------------------------------------------------------------------------- | Pulse Storage Driver |-------------------------------------------------------------------------- | | This configuration option determines which storage driver will be used | while storing entries from Pulse's recorders. In addition, you also | may provide any options to configure the selected storage driver. | */ 'storage' => [ 'driver' => env('PULSE_STORAGE_DRIVER', 'database'), 'database' => [ 'connection' => env('PULSE_DB_CONNECTION', null), 'chunk' => 1000, ], ], /* |-------------------------------------------------------------------------- | Pulse Ingest Driver |-------------------------------------------------------------------------- | | This configuration options determines the ingest driver that will be used | to capture entries from Pulse's recorders. Ingest drivers are great to | free up your request workers quickly by offloading the data storage. | */ 'ingest' => [ 'driver' => env('PULSE_INGEST_DRIVER', 'storage'), 'trim_lottery' => [1, 1_000], 'redis' => [ 'connection' => env('PULSE_REDIS_CONNECTION'), 'chunk' => 1000, ], ], /* |-------------------------------------------------------------------------- | Pulse Cache Driver |-------------------------------------------------------------------------- | | This configuration option determines the cache driver that will be used | for various tasks, including caching dashboard results, establishing | locks for events that should only occur on one server and signals. | */ 'cache' => env('PULSE_CACHE_DRIVER'), /* |-------------------------------------------------------------------------- | Pulse Route Middleware |-------------------------------------------------------------------------- | | These middleware will be assigned to every Pulse route, giving you the | chance to add your own middleware to this list or change any of the | existing middleware. Of course, reasonable defaults are provided. | */ 'middleware' => [ 'web', Authorize::class, ], /* |-------------------------------------------------------------------------- | Pulse Recorders |-------------------------------------------------------------------------- | | The following array lists the "recorders" that will be registered with | Pulse, along with their configuration. Recorders gather application | event data from requests and tasks to pass to your ingest driver. | */ 'recorders' => [ CacheInteractions::class => [ 'enabled' => env('PULSE_CACHE_INTERACTIONS_ENABLED', true), 'sample_rate' => env('PULSE_CACHE_INTERACTIONS_SAMPLE_RATE', .5), 'ignore' => [ '/\b[a-f0-9]{32}\b/', // Session IDs or something '/^.+@.+\|(?:(?:\d+\.\d+\.\d+\.\d+)|[0-9a-fA-F:]+)(?::timer)?$/', // Breeze / Jetstream authentication rate limiting '/^[a-zA-Z0-9]{40}$/', // Session IDs '/^health:*/', '/^illuminate:/', // Internal Laravel keys '/^laravel-health:check-.*/', '/^laravel:pulse:/', // Internal Pulse keys '/^nova/', // Internal Nova keys '/^telescope:/', // Internal Telescope keys '/^about_application/', // Internal Telescope keys ], 'groups' => [ '/^job-exceptions:.*/' => 'job-exceptions:*', '/^myclic\.content\.participant-dashboard-content\.\d+/' => 'myclic.content.participant-dashboard-content.*', '/^myclic\.feed\.\d+/' => 'myclic.feed.*', '/^throttle-event-myclic\.questionnaire\.started-\d+/' => 'throttle-event-myclic.questionnaire.started-*', '/^users:\d+:roles/' => 'users:*:roles', ], ], Exceptions::class => [ 'enabled' => env('PULSE_EXCEPTIONS_ENABLED', true), 'sample_rate' => env('PULSE_EXCEPTIONS_SAMPLE_RATE', 1), 'location' => env('PULSE_EXCEPTIONS_LOCATION', true), 'ignore' => [ // '/^Package\\\\Exceptions\\\\/', ], ], Queues::class => [ 'enabled' => env('PULSE_QUEUES_ENABLED', true), 'sample_rate' => env('PULSE_QUEUES_SAMPLE_RATE', 1), 'ignore' => [ // '/^Package\\\\Jobs\\\\/', '/^Spatie\\\\Health\\\\Jobs\\\\/', ], ], Servers::class => [ 'server_name' => env('PULSE_SERVER_NAME', gethostname()), 'directories' => explode(':', (string) env('PULSE_SERVER_DIRECTORIES', '/')), ], SlowJobs::class => [ 'enabled' => env('PULSE_SLOW_JOBS_ENABLED', true), 'sample_rate' => env('PULSE_SLOW_JOBS_SAMPLE_RATE', 1), 'threshold' => env('PULSE_SLOW_JOBS_THRESHOLD', 1000), 'ignore' => [ // '/^Package\\\\Jobs\\\\/', ], ], SlowOutgoingRequests::class => [ 'enabled' => env('PULSE_SLOW_OUTGOING_REQUESTS_ENABLED', true), 'sample_rate' => env('PULSE_SLOW_OUTGOING_REQUESTS_SAMPLE_RATE', 1), 'threshold' => env('PULSE_SLOW_OUTGOING_REQUESTS_THRESHOLD', 1000), 'ignore' => [ // '#^http://127\.0\.0\.1:13714#', // Inertia SSR ], 'groups' => [ // '#^https://api\.github\.com/repos/.*$#' => 'api.github.com/repos/*', // '#^https?://([^/]*).*$#' => '\1', // '#/\d+#' => '/*', ], ], SlowQueries::class => [ 'enabled' => env('PULSE_SLOW_QUERIES_ENABLED', true), 'sample_rate' => env('PULSE_SLOW_QUERIES_SAMPLE_RATE', 1), 'threshold' => env('PULSE_SLOW_QUERIES_THRESHOLD', 1000), 'location' => env('PULSE_SLOW_QUERIES_LOCATION', true), 'ignore' => [ '/(["`])pulse_[\w]+?\1/', ], ], SlowRequests::class => [ 'enabled' => env('PULSE_SLOW_REQUESTS_ENABLED', true), 'sample_rate' => env('PULSE_SLOW_REQUESTS_SAMPLE_RATE', 1), 'threshold' => env('PULSE_SLOW_REQUESTS_THRESHOLD', 1000), 'ignore' => [ '#^/_debugbar#', '#^/debug/health#', '#^/debug/horizon#', '#^/debug/pulse$#', '#^/livewire/update#', ], ], UserJobs::class => [ 'enabled' => env('PULSE_USER_JOBS_ENABLED', true), 'sample_rate' => env('PULSE_USER_JOBS_SAMPLE_RATE', 1), 'ignore' => [ // '/^Package\\\\Jobs\\\\/', ], ], UserRequests::class => [ 'enabled' => env('PULSE_USER_REQUESTS_ENABLED', true), 'sample_rate' => env('PULSE_USER_REQUESTS_SAMPLE_RATE', 1), 'ignore' => [ '#^/debug/pulse$#', '#^/debug/horizon#', '#^/debug/health$#', ], ], /* * Third-party */ AppsLoadRecorder::class => [ 'enabled' => env('PULSE_APPS_LOAD_ENABLED', true), 'sample_rate' => env('PULSE_APPS_LOAD_SAMPLE_RATE', 1), 'limit' => env('PULSE_APPS_LOAD_LIMIT', 10), 'ignore' => [ '#^/pulse$#', ], ], FourXxRecorder::class => [ 'enabled' => env('PULSE_4XX_ENABLED', true), 'sample_rate' => env('PULSE_4XX_SAMPLE_RATE', 1), 'ignore' => [ // '#^/wp-admin#', ], ], DiskRecorder::class => [ 'enabled' => env('GEOW_DISK_METRICS', true), ], DatabaseRecorder::class => [ 'connections' => [ 'mysql' => [ 'values' => [ 'Connections', 'Threads_connected', 'Threads_running', 'Max_used_connections', ], 'aggregates' => [ 'avg' => [ 'Threads_connected', 'Threads_running', ], 'max' => [], 'count' => [], ], ], ], ], CommandHistoryRecorder::class => [ 'enabled' => env('GEOW_COMMAND_HISTORY', true), 'ignore' => [ '#^backup:monitor#', '#^cache:prune-stale-tags#', '#^config:cache#', '#^config:clear#', '#^event:cache#', '#^event:clear#', '#^health:check#', '#^health:queue-check-heartbeat#', '#^health:schedule-check-heartbeat#', '#^horizon:pause#', '#^horizon:purge#', '#^horizon:snapshot#', '#^horizon:status#', '#^horizon:work#', '#^icons:cache#', '#^icons:clear#', '#^migrate#', '#^modules:cache#', '#^modules:clear#', '#^operations:process#', '#^package:discover#', '#^pulse:check#', '#^pulse:restart#', '#^purifier:clear#', '#^route:cache#', '#^route:clear#', '#^scout:flush#', '#^scout:import#', '#^storage:link#', '#^view:cache#', '#^view:clear#', ], ], ], ]; ```Steps To Reproduce
Can't really provide a reproducible repository here since it's about a database deadlock issue, but let me know how else I can provide information.
What we did: