search

sonata-project / SonataDoctrineORMAdminBundle

Integrate Doctrine ORM into the SonataAdminBundle
https://docs.sonata-project.org/projects/SonataDoctrineORMAdminBundle
MIT License
445 stars 345 forks source link

Performance degradation with the new Paginator #1582

Closed ossinkine closed 2 years ago

ossinkine commented 2 years ago

Environment

Sonata packages

show

``` $ composer show --latest 'sonata-project/*' sonata-project/admin-bundle 4.2.2 4.2.2 The missing Symfony Admin Generator sonata-project/block-bundle 4.8.0 4.8.0 Symfony SonataBlockBundle sonata-project/cache 2.2.0 2.2.0 Cache library sonata-project/doctrine-extensions 1.15.0 1.15.0 Doctrine2 behavioral extensions sonata-project/doctrine-orm-admin-bundle 4.2.0 4.2.0 Integrate Doctrine ORM into the SonataAdminBundle sonata-project/exporter 2.9.1 2.9.1 Lightweight Exporter library sonata-project/form-extensions 1.12.1 1.12.1 Symfony form extensions sonata-project/twig-extensions 1.9.0 1.9.0 Sonata twig extensions ```

Symfony packages

show

``` $ composer show --latest 'symfony/*' symfony/asset v4.4.27 v5.3.4 Manages URL generation and versioning of web assets such as CSS stylesheets, JavaScript files and image files symfony/browser-kit v4.4.27 v5.3.4 Simulates the behavior of a web browser, allowing you to make requests, click on links and submit forms prog... symfony/cache v4.4.33 v5.3.10 Provides an extended PSR-6, PSR-16 (and tags) implementation symfony/cache-contracts v2.4.0 v2.4.0 Generic abstractions related to caching symfony/config v4.4.33 v5.3.10 Helps you find, load, combine, autofill and validate configuration values of any kind symfony/console v4.4.33 v5.3.10 Eases the creation of beautiful and testable command line interfaces symfony/css-selector v4.4.27 v5.3.4 Converts CSS selectors to XPath expressions symfony/debug v4.4.31 v4.4.31 Provides tools to ease debugging PHP code symfony/dependency-injection v4.4.33 v5.3.10 Allows you to standardize and centralize the way objects are constructed in your application symfony/deprecation-contracts v2.4.0 v2.4.0 A generic function and convention to trigger deprecation notices symfony/doctrine-bridge v4.4.31 v5.3.8 Provides integration for Doctrine with various Symfony components symfony/dom-crawler v4.4.30 v5.3.7 Eases DOM navigation for HTML and XML documents symfony/dotenv v4.4.33 v5.3.10 Registers environment variables from a .env file symfony/error-handler v4.4.30 v5.3.7 Provides tools to manage errors and ease debugging PHP code symfony/event-dispatcher v4.4.30 v5.3.7 Provides tools that allow your application components to communicate with each other by dispatching events a... symfony/event-dispatcher-contracts v1.1.9 v2.4.0 Generic abstractions related to dispatching event symfony/expression-language v4.4.30 v5.3.7 Provides an engine that can compile and evaluate expressions symfony/filesystem v4.4.27 v5.3.4 Provides basic utilities for the filesystem symfony/finder v4.4.30 v5.3.7 Finds files and directories via an intuitive fluent interface symfony/flex v1.17.2 v1.17.2 Composer plugin for Symfony symfony/form v4.4.33 v5.3.10 Allows to easily create, process and reuse HTML forms symfony/framework-bundle v4.4.31 v5.3.10 Provides a tight integration between Symfony components and the Symfony full-stack framework symfony/http-client-contracts v2.4.0 v2.4.0 Generic abstractions related to HTTP clients symfony/http-foundation v4.4.33 v5.3.10 Defines an object-oriented layer for the HTTP specification symfony/http-kernel v4.4.33 v5.3.10 Provides a structured process for converting a Request into a Response symfony/inflector v4.4.27 v5.3.4 Converts words between their singular and plural forms (English only) Package symfony/inflector is abandoned, you should avoid using it. Use use `EnglishInflector` from the String component instead instead. symfony/intl v4.4.31 v5.3.8 Provides a PHP replacement layer for the C intl extension that includes additional data from the ICU library symfony/maker-bundle v1.34.1 v1.36.1 Symfony Maker helps you create empty commands, controllers, form classes, tests and more so you can forget a... symfony/mime v4.4.31 v5.3.8 Allows manipulating MIME messages symfony/monolog-bridge v4.4.27 v5.3.7 Provides integration for Monolog with various Symfony components symfony/monolog-bundle v3.7.1 v3.7.1 Symfony MonologBundle symfony/options-resolver v4.4.30 v5.3.7 Provides an improved replacement for the array_replace PHP function symfony/phpunit-bridge v4.4.33 v5.3.10 Provides utilities for PHPUnit, especially user deprecation notices management symfony/polyfill-php80 v1.23.1 v1.23.1 Symfony polyfill backporting some PHP 8.0+ features to lower PHP versions symfony/polyfill-php81 v1.23.0 v1.23.0 Symfony polyfill backporting some PHP 8.1+ features to lower PHP versions symfony/process v4.4.30 v5.3.7 Executes commands in sub-processes symfony/property-access v4.4.30 v5.3.8 Provides functions to read and write from/to an object or array using a simple string notation symfony/property-info v4.4.31 v5.3.8 Extracts information about PHP class' properties using metadata of popular sources symfony/proxy-manager-bridge v4.4.27 v5.3.4 Provides integration for ProxyManager with various Symfony components symfony/psr-http-message-bridge v2.1.2 v2.1.2 PSR HTTP message bridge symfony/routing v4.4.30 v5.3.7 Maps an HTTP request to a set of configuration variables symfony/security-acl v3.2.0 v3.2.0 Symfony Security Component - ACL (Access Control List) symfony/security-bundle v4.4.27 v5.3.8 Provides a tight integration of the Security component into the Symfony full-stack framework symfony/security-core v4.4.33 v5.3.10 Symfony Security Component - Core Library symfony/security-csrf v4.4.27 v5.3.4 Symfony Security Component - CSRF Library symfony/security-guard v4.4.27 v5.3.7 Symfony Security Component - Guard symfony/security-http v4.4.30 v5.3.10 Symfony Security Component - HTTP Integration symfony/serializer v4.4.33 v5.3.10 Handles serializing and deserializing data structures, including object graphs, into array structures or oth... symfony/service-contracts v2.4.0 v2.4.0 Generic abstractions related to writing services symfony/stopwatch v4.4.27 v5.3.4 Provides a way to profile code symfony/string v5.3.10 v5.3.10 Provides an object-oriented API to strings and deals with bytes, UTF-8 code points and grapheme clusters in ... symfony/swiftmailer-bundle v3.5.2 v3.5.2 Symfony SwiftmailerBundle symfony/translation v4.4.32 v5.3.10 Provides tools to internationalize your application symfony/translation-contracts v2.4.0 v2.4.0 Generic abstractions related to translation symfony/twig-bridge v4.4.27 v5.3.7 Provides integration for Twig with various Symfony components symfony/twig-bundle v4.4.30 v5.3.10 Provides a tight integration of Twig into the Symfony full-stack framework symfony/validator v4.4.33 v5.3.10 Provides tools to validate values symfony/var-dumper v4.4.33 v5.3.10 Provides mechanisms for walking through any arbitrary PHP variable symfony/var-exporter v4.4.31 v5.3.8 Allows exporting any serializable PHP data structure to plain PHP code symfony/web-profiler-bundle v4.4.31 v5.3.8 Provides a development tool that gives detailed information about the execution of any request symfony/workflow v4.4.27 v5.3.4 Provides tools for managing a workflow or finite state machine symfony/yaml v4.4.29 v5.3.6 Loads and dumps YAML files ```

Doctrine packages

show

``` $ composer show --latest 'doctrine/*' doctrine/annotations 1.13.2 1.13.2 Docblock Annotations Parser doctrine/cache 1.12.1 2.1.1 PHP Doctrine Cache library is a popular cache implementation that supports many different drivers such as red... doctrine/collections 1.6.8 1.6.8 PHP Doctrine Collections library that adds additional functionality on top of PHP arrays. doctrine/common 3.2.0 3.2.0 PHP Doctrine Common project is a library that provides additional functionality that other Doctrine projects ... doctrine/data-fixtures 1.5.1 1.5.1 Data Fixtures for all Doctrine Object Managers doctrine/dbal 2.13.4 3.1.4 Powerful PHP database abstraction layer (DBAL) with many features for database schema introspection and manag... doctrine/deprecations v0.5.3 v0.5.3 A small layer on top of trigger_error(E_USER_DEPRECATED) or PSR-3 logging with options to disable all depreca... doctrine/doctrine-bundle 2.3.2 2.4.3 Symfony DoctrineBundle doctrine/doctrine-fixtures-bundle 3.4.1 3.4.1 Symfony DoctrineFixturesBundle doctrine/doctrine-migrations-bundle 3.0.3 3.2.1 Symfony DoctrineMigrationsBundle doctrine/event-manager 1.1.1 1.1.1 The Doctrine Event Manager is a simple PHP event system that was built to be used with the various Doctrine p... doctrine/inflector 2.0.4 2.0.4 PHP Doctrine Inflector is a small library that can perform string manipulations with regard to upper/lowercas... doctrine/instantiator 1.4.0 1.4.0 A small, lightweight utility to instantiate objects in PHP without invoking their constructors doctrine/lexer 1.2.1 1.2.1 PHP Doctrine Lexer parser library that can be used in Top-Down, Recursive Descent Parsers. doctrine/migrations 3.3.0 3.3.2 PHP Doctrine Migrations project offer additional functionality on top of the database abstraction layer (DBAL... doctrine/orm 2.8.5 2.10.2 Object-Relational-Mapper for PHP doctrine/persistence 2.2.3 2.2.3 The Doctrine Persistence project is a set of shared interfaces and functionality that the different Doctrine ... doctrine/sql-formatter 1.1.2 1.1.2 a PHP SQL highlighting library ```

PHP version

$ php -v
PHP 7.4.16 (cli) (built: Apr 15 2021 00:56:09) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.16, Copyright (c), by Zend Technologies
    with Xdebug v3.0.4, Copyright (c) 2002-2021, by Derick Rethans

Subject

After updating this package from 3.30.0 to the latest 3.* and later to 4.2.0 we've faced with a huge performance degradation. Looks like this relates to Paginator update https://github.com/sonata-project/SonataDoctrineORMAdminBundle/pull/1444

Seems to be the problem with this code https://github.com/sonata-project/SonataDoctrineORMAdminBundle/blob/f0ca9d91cc2244ff4bd8e13b475e19e1bf1d793d/src/Util/SmartPaginatorFactory.php#L65-L68

Admin class generates the following query (simplified):

SELECT f0_.id AS id_0, f0_.foo AS foo_1 FROM foo f0_ LEFT JOIN bar b1_ ON f0_.id = b1_.foo_id ORDER BY id_0 DESC LIMIT 257

Since it has a join $paginator->setUseOutputWalkers(false); wasn't call and Doctrine Paginator uses LimitSubqueryOutputWalker and converts the query to

SELECT DISTINCT id_0, MIN(sclr_2) AS dctrn_minrownum FROM (
    SELECT f0_.id AS id_0, f0_.foo AS foo_1, ROW_NUMBER() OVER (ORDER BY t0_.id DESC) as sclr_2 FROM foo f0_ LEFT JOIN bar b1_ ON f0_.id = b1_.foo_id                                       
) GROUP BY id_0 ORDER BY dctrn_minrownum ASC LIMIT 257

Actually this means full scan foo table which contained over billion records.

Query plan looks like

 Limit  (cost=10130996966.59..10130996966.60 rows=1 width=16)
   ->  Unique  (cost=10130996966.59..10282834876.19 rows=20245054613 width=16)
         ->  Sort  (cost=10130996966.59..10181609603.12 rows=20245054613 width=16)
               Sort Key: (min(dctrn_result.sclr_120)), dctrn_result.id_0
               ->  GroupAggregate  (cost=5500480962.56..5854769418.29 rows=20245054613 width=16)
                     Group Key: dctrn_result.id_0
                     ->  Sort  (cost=5500480962.56..5551093599.10 rows=20245054613 width=16)
                           Sort Key: dctrn_result.id_0
                           ->  Subquery Scan on dctrn_result  (cost=0.71..1224253414.26 rows=20245054613 width=16)
                                 ->  WindowAgg  (cost=0.71..1021802868.13 rows=20245054613 width=24)
                                       ->  Index Only Scan Backward using foo_id_idx on foo f0_  (cost=0.71..667514412.41 rows=20245054613 width=16)

$paginator->setUseOutputWalkers(false); call reverts behavior and fixes the issue.

I suggest to fix this behavior or add an option to switch old Paginator behavior.

ossinkine commented 2 years ago

@alfabetagama could you please explain what exactly you did fix? Because the old behavior is pretty quickly and I have no idea why the current behavior should be faster.

ossinkine commented 2 years ago

I dug deeper and realized that @alfabetagama was solving the exact same problem but apparently with a different request. Creating the SmartPaginatorFactory was a good solution to me but I don't understand why such conditions are used for setUseOutputWalkers(false);. According to https://github.com/doctrine/orm/issues/8278#issue-705517756 it allows to have joins so the following code might be a fix

// it is only safe to disable output walkers for really simple queries
- if (!$hasHavingPart && !$hasJoins && $hasSingleIdentifierName) {
+ if (!$hasHavingPart && $hasSingleIdentifierName) {
    $paginator->setUseOutputWalkers(false);
}

If you approve this I'll create a PR

VincentLanglet commented 2 years ago

Indeed, seems like they don't check for join in EasyAdmin either https://github.com/EasyCorp/EasyAdminBundle/blob/master/src/Orm/EntityPaginator.php#L55-L63