Long running doctrine queries lock other simple queries - MySQL issue excluded

Question

I have a reporting application build with Symfony 2.8.14/ Doctrine. One of my reports takes about 2 minutes to run and executes a series of queries (https://dba.stackexchange.com/questions/157981/reporting-query-blocks-other-query-but-isolation-level-read-uncommitted-set/159495#159495).

I have found that the locking appears to be happening at Symfony level, because the same pages can be loaded no problem if I switch into app_dev.php/ or run the MySQL queries on the command line, while the report is running.

Is there a connection limit or other locking I could have turned on accidentally?

My Doctrine configuration

# Doctrine Configuration
doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                driver:   pdo_mysql
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  UTF8
                options:
                    1001: true
    orm:
        auto_generate_proxy_classes: "%kernel.debug%"
        default_entity_manager: default
        entity_managers:
            default:
                connection: default
                naming_strategy: doctrine.orm.naming_strategy.underscore
                mappings:
                    AppBundle: ~
                    FOSUserBundle: ~
            errorlog:
                connection: default
                naming_strategy: doctrine.orm.naming_strategy.underscore
                mappings:
                    AppBundle: ~

Apache2 configuration - mpm-itk

<IfModule mpm_itk_module>
  StartServers             5
  MinSpareServers          5
  MaxSpareServers         10
  MaxRequestWorkers      150
  MaxConnectionsPerChild   0
</IfModule>

Not using PHP-FPM as stated in comments, but using mod_php:

<FilesMatch \.php$>
    SetHandler application/x-httpd-php
</FilesMatch>

I'm running MySQL 5.7.11 on m4.2xlarge which according to this: http://pushentertainment.com/rds-connections-by-instance-type/ allows for 2500+ connections.

score 2 · Accepted Answer · answered Jan 06 '17 at 12:32

Almost every time I've encountered locking that wasn't in the database, it has been PHP session serialization.

With the default Symfony NativeFileSessionHandler, PHP will wait to obtain a file lock before opening the session file; the lock won't be released until session is closed (ie the request is finished). This helps avoid race conditions between processes reading/writing session data.

If this is the cause then opening 2 tabs in the same browser (print $session->getId to confirm the session ID is shared) will block, but trying with different browsers (different session IDs) will not block. Be aware that depending on the state of ignore_user_abort() previous requests cancelled in the browser but still processing will also block any new requests.

As to why this would work on dev/prod, differences in session handler settings in your config file will do this.

Alternately, if that's not the cause I'd use strace -p PID and /proc/PID to determine what system call the apache/PHP process is blocked on (can be annoying to work out which is the blocked process but you only have 5 apache processes and a 2 minute window to find the right one)

Yes thanks, I had seen this after @Vinicius' comment. This resolves the problem. Strange I haven't come across this problem before — jdog, Jan 07 '17 at 08:04
Ouch! This is one of the worst indictments of 3rd party software "getting in the way" of using MySQL! — Rick James, May 09 '17 at 00:11

score 0 · Answer 2 · edited May 23 '17 at 11:46

0

It's not caused by the symfony, its caused by the webserver. You can refer to my post about the laravel another php mvc frame requests.

To solve this run the time cost process in the background.

edited May 23 '17 at 11:46

Community

1
1

answered Jan 04 '17 at 04:36

LF00

22,077
20
117
225

As stated in my question, I can browser /app_dev.php while the report runs in /app.php and vice versa. Based on this I cannot see how your post applies here. My web server and database should handle a number of database read and output transactions at the same time, at least 2! – jdog Jan 04 '17 at 07:37
I have double checked and increased my php-fpm connections to 50, same problem – jdog Jan 04 '17 at 08:18
Did you try run in two different browsers concorrently? Another option run in the same browser one request in anonymous mode and another normal mode? If it works, so its caused by the webserver as @KrisRoofe said. – Vinícius Fagundes Jan 04 '17 at 20:10
@ViníciusFagundes agree, but I can't find my bottleneck. Any other ideas? Have added configuration details to the question – jdog Jan 04 '17 at 21:38
@jdog In fact, can be multiple reasons check [this related question](http://stackoverflow.com/questions/1430883/simultaneous-requests-to-php-script?noredirect=1&lq=1). – Vinícius Fagundes Jan 06 '17 at 01:04

Long running doctrine queries lock other simple queries - MySQL issue excluded

2 Answers2