Skip to content

[Messenger]: Doctrine transport seems to be generating excessive number of queries #33659

@mareksuscak

Description

@mareksuscak

Symfony version(s) affected: 4.3.4

Description

We've recently migrated away from JMS Job Queue Bundle towards Messenger as part of our stack standardization effort. We set up 3 async priority transports (Doctrine type) and are running about 4 worker processes that all consume messages from all 3 transports. Immediately after initially deploying it to staging, we started seeing huge spike in the number of MySQL queries (we're using Google Cloud SQL v5.7).

How to reproduce

  1. Create a new Symfony app
  2. Install messenger and configure it with 3 doctrine based transports according to the config file attached below
  3. Run the command attached below
  4. Check the QPS

Possible Solution

N/A

Additional context

# shell command
$ bin/console messenger:consume async_priority_high async async_priority_low --time-limit=86400 --memory-limit=128M -vv
# .env
MESSENGER_TRANSPORT_DSN=doctrine://default
# config/packages/messenger.yaml
framework:
    messenger:
        # Send failed messages to this transport for later handling.
        failure_transport: failed

        transports:
            # https://symfony.com/doc/current/messenger.html#transport-configuration

            # Use for high priority tasks such as search jobs
            async_priority_high:
                dsn: '%env(MESSENGER_TRANSPORT_DSN)%'
                retry_strategy:
                    max_retries: 3
                    # milliseconds delay
                    delay: 1000
                    # causes the delay to be higher before each retry
                    # e.g. 1 second delay, 2 seconds, 4 seconds
                    multiplier: 2
                    max_delay: 0
                options:
                    queue_name: high_priority
            # Use this as a default or when you're not sure
            async:
                dsn: '%env(MESSENGER_TRANSPORT_DSN)%'
                options:
                    queue_name: normal_priority
            # Use for low priority tasks such as sending emails, indexing...
            async_priority_low:
                dsn: '%env(MESSENGER_TRANSPORT_DSN)%'
                options:
                    queue_name: low_priority
            # This is the default
            sync: 'sync://'
            # All failed messages end up here and can be retried
            failed:
                dsn: '%env(MESSENGER_TRANSPORT_DSN)%'
                options:
                    queue_name: failed

        routing:
            # Route your messages to the transports
            'App\Message\ServiceSearchRequest': async_priority_high
            'App\Message\HotelSearchRequest': async_priority_high
            'App\Message\ZumataHotelProcessRequest': async_priority_high
            'App\Message\HotelSearchFinishRequest': async_priority_low
            # Mailer messages
            'Symfony\Component\Mailer\Messenger\SendEmailMessage': async

        # Read more to learn why there are two buses (TL;DR: different middleware on each side - producer and consumer)
        # https://github.com/symfony/symfony/issues/32436#issuecomment-530565499

        default_bus: command.bus
        buses:
            command.bus:
                default_middleware: false
                middleware:
                    - validation
                    - add_bus_name_stamp_middleware: ['command.consumer.bus']
                    - dispatch_after_current_bus
                    - failed_message_processing_middleware
                    - send_message
                    - handle_message

            command.consumer.bus:
                middleware:
                    - doctrine_ping_connection
                    # Will be available as of Symfony 4.4
                    # - doctrine_clear_entity_manager
                    # TODO: we need this middleware for all but HotelSearchRequest and ServiceSearchRequest
                    # we can create two different buses and use a transaction-free one explicitly for these
                    # two cases
                    # - doctrine_transaction

Here's a chart that shows no. of MySQL queries per second. All 4 workers running in a staging environment, no messages are in the queue:

image

Here's a chart that shows no. of MySQL queries per second. Only 1 worker running in a staging environment, no messages are in the queue:

image

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions