I updated my old Symfony 4.3 application to 4.4 and i moved the application on a new server with PHP8.1 and mySQL8. Before i used a Server with PHP7.4.
I noticed now that my application is very slow on the new setup. Some routes are 1 second slower other 4-5 seconds.And a very big command which takes 7 minutes to execute on the old system, takes now multiple hours.
My code is defenetly not perfect but such an increase cant be normal.
For example, i found this simple database request:
$queryBuilder = $this->em->createQueryBuilder()
->select("k")
->from(KVP::class, "k")
->andWhere("k.deleted = false")
->andWhere("(k.accept = 1 or k.deniedFrom is NOT NULL) and (k.done is NOT NULL or k.until is NULL)")
->addOrderBy('k.createdAt', 'DESC');
$queryBuilder->getQuery()->getResult();
When i change getResult to getArrayResult, the route loads 500ms faster. On the old setup, there is no differnce in execution time between getResult and getArrayResult.
Do i need to make some specific configurations for PHP8 or do i need to change the code?
Here is an excerpt of the KVP entity from the example:
namespace App\Entity\KVP;
use App\Annotation\Acl;
use App\Entity\Auth\User;
use App\Entity\Employee\Department;
use App\Traits\TableExtensionTrait;
use Doctrine\ORM\Mapping as ORM;
use Gedmo\Mapping\Annotation as Gedmo;
use Symfony\Component\Validator\Constraints as Assert;
/**
* KVP entity
*
* @ORM\Entity(repositoryClass="App\Repository\KVP\KVPRepository")
* @ORM\Table(name="t_kvp")
*
*
* @Gedmo\Loggable
* @Acl
*/
class KVP
{
// Includes create/update information
use TableExtensionTrait;
/**
* @ORM\Id
* @ORM\Column(type="integer")
* @ORM\GeneratedValue(strategy="AUTO")
* @var int
*/
protected $id;
/**
* @ORM\OneToMany(targetEntity="App\Entity\ToDos\ToDos", mappedBy="kvp")
*/
protected $todos;
/**
* @ORM\OneToMany(targetEntity="App\Entity\News\News", mappedBy="kvp")
*/
protected $newsKVP;
/**
* @ORM\OneToOne(targetEntity="App\Entity\Chat\ChatGroup")
*/
private $chatGroup;
/**
* @var User
* @ORM\ManyToOne(targetEntity="App\Entity\Auth\User", inversedBy="kvp")
* @ORM\JoinColumn(name="user_id", referencedColumnName="id", nullable=true)
*/
private $user;
/**
* @var User
* @ORM\ManyToOne(targetEntity="App\Entity\Auth\User", inversedBy="kvpResponsible")
* @ORM\JoinColumn(name="userResponsible_id", referencedColumnName="id", nullable=true)
*/
private $userResponsible;
/**
* @var Department
* @ORM\ManyToOne(targetEntity="App\Entity\Employee\Department", inversedBy="kvpDepartment")
* @ORM\JoinColumn(name="department_id", referencedColumnName="id", nullable=true)
*/
private $department;
/**
* @var User
* @ORM\ManyToOne(targetEntity="App\Entity\Auth\User", inversedBy="kvpAcceptedFrom")
* @ORM\JoinColumn(name="acceptedFrom_id", referencedColumnName="id", nullable=true)
*/
private $acceptedFrom;
/**
* @var User
* @ORM\ManyToOne(targetEntity="App\Entity\Auth\User", inversedBy="kvpDeniedFrom")
* @ORM\JoinColumn(name="deniedFrom_id", referencedColumnName="id", nullable=true)
*/
private $deniedFrom;
/**
* @var string
* @ORM\Column(name="title", type="text", nullable=true)
* @Assert\NotBlank(message="field.not.blank")
*/
private $title;
And here is my composer.json:
{
"type": "project",
"license": "proprietary",
"require": {
"php": "^8.1",
"ext-ctype": "*",
"ext-iconv": "*",
"beberlei/doctrineextensions": "^1.2",
"cboden/ratchet": "^0.4.2",
"doctrine/annotations": "^1.13",
"doctrine/doctrine-bundle": "^2.7.0",
"doctrine/doctrine-migrations-bundle": "^3.2.2",
"doctrine/orm": "^2.13",
"friendsofsymfony/user-bundle": "~2.2",
"gedmo/doctrine-extensions": "^3.9",
"knplabs/knp-paginator-bundle": "^5.0",
"knplabs/knp-snappy-bundle": "^1.7",
"phpdocumentor/reflection-docblock": "^5.3.0",
"sensio/framework-extra-bundle": "^5.1",
"stof/doctrine-extensions-bundle": "^1.3",
"symfony/asset": "4.4.*",
"symfony/console": "4.4.*",
"symfony/dotenv": "4.4.*",
"symfony/expression-language": "4.4.*",
"symfony/flex": "^1.3.1",
"symfony/form": "4.4.*",
"symfony/framework-bundle": "4.4.*",
"symfony/http-client": "4.4.*",
"symfony/intl": "4.4.*",
"symfony/monolog-bundle": "^3.1",
"symfony/process": "4.4.*",
"symfony/property-access": "4.4.*",
"symfony/property-info": "4.4.*",
"symfony/proxy-manager-bridge": "4.4.*",
"symfony/security-bundle": "4.4.*",
"symfony/serializer": "4.4.*",
"symfony/swiftmailer-bundle": "^3.1",
"symfony/translation": "4.4.*",
"symfony/twig-bundle": "4.4.*",
"symfony/validator": "4.4.*",
"symfony/web-link": "4.4.*",
"symfony/webpack-encore-bundle": "^1.7",
"symfony/yaml": "4.4.*",
"twig/extensions": "^1.5",
"umulmrum/holiday": "^0.6.0",
"michaeldegroot/doctrine-encrypt-bundle": "5.1.*"
},
"require-dev": {
"phpunit/phpunit": "^9.2",
"symfony/browser-kit": "4.4.*",
"symfony/css-selector": "4.4.*",
"symfony/debug-bundle": "4.4.*",
"symfony/maker-bundle": "^1.0",
"symfony/phpunit-bridge": "4.4.*",
"symfony/stopwatch": "4.4.*",
"symfony/var-dumper": "4.4.*",
"symfony/web-profiler-bundle": "4.4.*",
"symfony/web-server-bundle": "4.4.*"
},
"config": {
"preferred-install": {
"*": "dist"
},
"sort-packages": true,
"allow-plugins": {
"symfony/flex": true
}
},
"autoload": {
"psr-4": {
"App\\": "src/"
}
},
"autoload-dev": {
"psr-4": {
"App\\Tests\\": "tests/"
}
},
"replace": {
"paragonie/random_compat": "2.*",
"symfony/polyfill-ctype": "*",
"symfony/polyfill-iconv": "*",
"symfony/polyfill-php71": "*",
"symfony/polyfill-php70": "*",
"symfony/polyfill-php56": "*"
},
"scripts": {
"auto-scripts": {
"cache:clear": "symfony-cmd",
"assets:install %PUBLIC_DIR%": "symfony-cmd"
},
"post-install-cmd": [
"@auto-scripts"
],
"post-update-cmd": [
"@auto-scripts"
]
},
"conflict": {
"symfony/symfony": "*"
},
"extra": {
"symfony": {
"allow-contrib": false,
"require": "4.4.*"
}
}
}
Does someone had the same experience or an idea what i am missing here?
EDIT
I think i could narrow it down to doctrine hydration. When i i make getResult with enitites that has multiple connection to other entities then i get the performance problem. On the old setup/version is everything fine.
This is the SQL from the request above as requested:
SELECT t0_.id AS id_0, t0_.title AS title_1, t0_.problemState AS problemState_2, t0_.solution AS solution_3, t0_.accept AS accept_4, t0_.until AS until_5, t0_.inWork AS inWork_6, t0_.accepted AS accepted_7, t0_.comment AS comment_8, t0_.commentInWork AS commentInWork_9, t0_.deleted AS deleted_10, t0_.greenKvp AS greenKvp_11, t0_.done AS done_12, t0_.reminderSupervised AS reminderSupervised_13, t0_.lastReminderSupervised AS lastReminderSupervised_14, t0_.reminderAdmins AS reminderAdmins_15, t0_.lastReminderAdmins AS lastReminderAdmins_16, t0_.reminderSuperAdmins AS reminderSuperAdmins_17, t0_.lastReminderSuperAdmins AS lastReminderSuperAdmins_18, t0_.reminderOpen AS reminderOpen_19, t0_.lastReminderOpen AS lastReminderOpen_20, t0_.created_at AS created_at_21, t0_.updated_at AS updated_at_22, t0_.update_version AS update_version_23, t0_.chat_group_id AS chat_group_id_24, t0_.user_id AS user_id_25, t0_.userResponsible_id AS userResponsible_id_26, t0_.department_id AS department_id_27, t0_.acceptedFrom_id AS acceptedFrom_id_28, t0_.deniedFrom_id AS deniedFrom_id_29, t0_.created_by AS created_by_30, t0_.updated_by AS updated_by_31
FROM t_kvp t0_
WHERE t0_.deleted = 0
ORDER BY t0_.created_at DESC
And the SHOW CREATE TABLE:
CREATE TABLE `t_kvp` (
`id` int NOT NULL AUTO_INCREMENT,
`chat_group_id` int DEFAULT NULL,
`user_id` int DEFAULT NULL,
`department_id` int DEFAULT NULL,
`created_by` int DEFAULT NULL,
`updated_by` int DEFAULT NULL,
`title` longtext COLLATE utf8mb4_unicode_ci,
`problemState` longtext COLLATE utf8mb4_unicode_ci,
`solution` longtext COLLATE utf8mb4_unicode_ci,
`accept` int DEFAULT NULL,
`until` datetime DEFAULT NULL,
`inWork` datetime DEFAULT NULL,
`accepted` datetime DEFAULT NULL,
`comment` longtext COLLATE utf8mb4_unicode_ci,
`commentInWork` longtext COLLATE utf8mb4_unicode_ci,
`deleted` tinyint(1) NOT NULL,
`greenKvp` tinyint(1) NOT NULL,
`done` datetime DEFAULT NULL,
`reminderSupervised` int DEFAULT NULL,
`lastReminderSupervised` date DEFAULT NULL,
`reminderAdmins` int DEFAULT NULL,
`lastReminderAdmins` date DEFAULT NULL,
`reminderSuperAdmins` int DEFAULT NULL,
`lastReminderSuperAdmins` date DEFAULT NULL,
`reminderOpen` int DEFAULT NULL,
`lastReminderOpen` date DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime DEFAULT NULL,
`update_version` int NOT NULL DEFAULT '1',
`userResponsible_id` int DEFAULT NULL,
`acceptedFrom_id` int DEFAULT NULL,
`deniedFrom_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQ_34FC256BCA760E77` (`chat_group_id`),
KEY `IDX_34FC256BA76ED395` (`user_id`),
KEY `IDX_34FC256BEC9046B3` (`userResponsible_id`),
KEY `IDX_34FC256BAE80F5DF` (`department_id`),
KEY `IDX_34FC256BB6A8B8C4` (`acceptedFrom_id`),
KEY `IDX_34FC256B36D52A95` (`deniedFrom_id`),
KEY `IDX_34FC256BDE12AB56` (`created_by`),
KEY `IDX_34FC256B16FE72E1` (`updated_by`),
CONSTRAINT `FK_34FC256B16FE72E1` FOREIGN KEY (`updated_by`) REFERENCES `t_user` (`id`) ON DELETE RESTRICT,
CONSTRAINT `FK_34FC256B36D52A95` FOREIGN KEY (`deniedFrom_id`) REFERENCES `t_user` (`id`),
CONSTRAINT `FK_34FC256BA76ED395` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`),
CONSTRAINT `FK_34FC256BAE80F5DF` FOREIGN KEY (`department_id`) REFERENCES `t_department` (`id`),
CONSTRAINT `FK_34FC256BB6A8B8C4` FOREIGN KEY (`acceptedFrom_id`) REFERENCES `t_user` (`id`),
CONSTRAINT `FK_34FC256BCA760E77` FOREIGN KEY (`chat_group_id`) REFERENCES `t_chat_group` (`id`),
CONSTRAINT `FK_34FC256BDE12AB56` FOREIGN KEY (`created_by`) REFERENCES `t_user` (`id`) ON DELETE RESTRICT,
CONSTRAINT `FK_34FC256BEC9046B3` FOREIGN KEY (`userResponsible_id`) REFERENCES `t_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=228 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
When i select only a few fields with no connection to other entities then the server response is fine.
This is only an example. The problem is every where where i grap data that has connections to multiple entities. (Sorry for the bad explanation :) )
EDIT 2
I have looked at the Symfony performance results on both setups and there it seems the problem is not Doctrine/Database. I seams is PHP after all, or did i interpret the screenshots wrong?
Symfony 4.4 PHP 8.1
Symfony 4.3 PHP 7.4
