[BUG]: Significant performance issue in database queries after upgrading Phalcon v3.4 to v5.4

[ZanMaticPratnemer]

Describe the bug
I recently upgraded our application from PHP 7.3 and Phalcon v3.4 to PHP 8.2 and Phalcon v5.4, expecting a performance improvement, but the whole application was slower for approximately 40%. I made some changes to the code that were necessary for it to work on newer versions of the language and the framework, but no functional changes, only some optimizations. I made the optimizations only after noticing the problem described bellow.

After turning on the profiling tools I noticed that the main reason was the function `\PDO->prepare'. In PHP 8.2 and Phalcon v5.4 it took anywhere from 400x to 1000x more time to execute compared to the old version of the application (PHP 7.3 and Phalcon v3.4).

Since our application sometimes needs to make a lot of database queries for a single request, the performance issue of this function can add up to multiple seconds of additional loading time.

To Reproduce
I created a cli task that clearly shows the problem. This task contains two actions, one to prepare 10000 statements with \PDO and another to prepare the same amount of statements with \Phalcon\Db\Adapter\Pdo\Mysql. I ran these tasks on 4 different environments: our staging server with PHP 7.3 and Phalcon v3.4, our staging server with PHP 8.2 and Phalcon v5.4, development docker environment with PHP 7.3 and Phalcon v3.4 and development docker environment with PHP 8.2 and Phalcon v5.4. Only the two cases of preparing statements with \Phalcon\Db\Adapter\Pdo\Mysql on Phalcon v5.4 had performance issues.

<?php

namespace LyteeAPI\Tasks;

use Phalcon\Cli\Task;
use Phalcon\Db\Adapter\Pdo\Mysql;
use Phalcon\Db\Dialect\Mysql as MysqlDialect;

class TestTask extends Task
{
    use TaskOutput;

    public function PreparepdoAction()
    {
        $di = $this->getDi();
        $config = $di->get('config');

        try
        {
            $pdo = new \PDO(
                "mysql:host={$config->database->host};dbname={$config->database->dbname}",
                $config->database->username,
                $config->database->password
            );
        }
        catch (\Exception $e)
        {
            print_r($e);

            exit;
        }

        $t = microtime(true);
        for ($i = 0; $i < 10000; ++$i)
        {
            $stmt = $pdo->prepare('SELECT * FROM `page` WHERE ((`page`.`parent_id` = :APR0:)) AND `page`.`tenant_id` = :tenant_id: AND `page`.`deleted` = 0 ORDER BY `page`.`position`');
        }
        echo microtime(true) - $t . ' - seconds to prepare ' . $i . " queries\n";
    }

    public function PreparephalconAction()
    {
        $di = $this->getDi();
        $config = $di->get('config');
        $params = [
            'host'         => $config->database->host,
            'username'     => $config->database->username,
            'password'     => $config->database->password,
            'dbname'       => $config->database->dbname,
            'charset'      => $config->database->charset,
            'dialectClass' => MysqlDialect::class,
        ];

        $conn = new Mysql($params);

        $t = microtime(true);
        for ($i = 0; $i < 10000; ++$i)
        {
            $statement = $conn->prepare("SELECT * FROM `page` WHERE ((`page`.`parent_id` = ':APR0:')) AND `page`.`tenant_id` = ':tenant_id:' AND `page`.`deleted` = 0 ORDER BY `page`.`position`");
        }
        echo microtime(true) - $t . ' - seconds to prepare ' . $i . " queries\n";
    }
}

Output of these tasks on our staging server with PHP 8.2 and Phalcon v5.4:

[root@dx1 private]# php -v
PHP 8.2.13 (cli) (built: Nov 21 2023 09:55:59) (NTS gcc x86_64)
Copyright (c) The PHP Group
Zend Engine v4.2.13, Copyright (c) Zend Technologies
    with Zend OPcache v8.2.13, Copyright (c), by Zend Technologies
    with Xdebug v3.3.0, Copyright (c) 2002-2023, by Derick Rethans
[root@dx1 private]# php --ri phalcon | grep Version
Version => 5.4.0
Powered by Zephir => Version 0.17.0-$Id$
[root@dx1 private]# php cli.php test preparepdo
0.014276027679443 - seconds to prepare 10000 queries
[root@dx1 private]# php cli.php test preparephalcon
8.1416730880737 - seconds to prepare 10000 queries

The results were similar if I ran these tasks inside the docker container. With PHP 7.3 and Phalcon v3.4 both actions took approximately the same amount of time to execute (server and docker container).

Expected behavior
Both actions should take approximately the same amount of time to execute.

Details

• Phalcon version:

phalcon


Phalcon is a full stack PHP framework, delivered as a PHP extension, offering lower resource consumption and high performance.
phalcon => enabled
Author => Phalcon Team and contributors
Version => 5.4.0
Build Date => Dec 13 2023 02:35:23
Powered by Zephir => Version 0.17.0-$Id$

Directive => Local Value => Master Value
phalcon.db.escape_identifiers => On => On
phalcon.db.force_casting => Off => Off
phalcon.orm.case_insensitive_column_map => Off => Off
phalcon.orm.cast_last_insert_id_to_int => Off => Off
phalcon.orm.cast_on_hydrate => Off => Off
phalcon.orm.column_renaming => On => On
phalcon.orm.disable_assign_setters => Off => Off
phalcon.orm.enable_implicit_joins => On => On
phalcon.orm.enable_literals => On => On
phalcon.orm.events => On => On
phalcon.orm.exception_on_failed_save => Off => Off
phalcon.orm.exception_on_failed_metadata_save => On => On
phalcon.orm.ignore_unknown_columns => Off => Off
phalcon.orm.late_state_binding => Off => Off
phalcon.orm.not_null_validations => On => On
phalcon.orm.resultset_prefetch_records => 0 => 0
phalcon.orm.update_snapshot_on_save => On => On
phalcon.orm.virtual_foreign_keys => On => On
phalcon.orm.dynamic_update => On => On
phalcon.warning.enable => On => On

• PHP Version:

PHP 8.2.13 (cli) (built: Nov 21 2023 09:55:59) (NTS gcc x86_64)
Copyright (c) The PHP Group
Zend Engine v4.2.13, Copyright (c) Zend Technologies
    with Zend OPcache v8.2.13, Copyright (c), by Zend Technologies
    with Xdebug v3.3.0, Copyright (c) 2002-2023, by Derick Rethans

• Operating System:

[root@dx1 private]# cat /etc/*-release | grep PRETTY_NAME
PRETTY_NAME="Fedora Linux 39 (Thirty Nine)"

• Installation type: Installation via pecl
• Zephir version (if any): //
• Server: Apache (not relevant here)
• Other related info (Database, table schema): Mariadb 10.5

Additional context
I took a quick look into Phalcon source code and saw that the way that I use the Phalcon PDO in the above tasks, the following options were also set (maybe I missed some):

• options[\PDO::ATTR_STRINGIFY_FETCHES] = false
• options[\PDO::ATTR_EMULATE_PREPARES] = false
• options[\PDO::ATTR_ERRMODE] = \PDO::ERRMODE_EXCEPTION
  After setting these options in PreparepdoAction(), performance didn't change.

I also tried using the Phalcon PDO without the dialect and without named binds, again - nothing changed.

Even tried using the Phalcon PDO with a simpler query : SELECT 'id' FROM 'page'. It still took about 8.5 seconds to execute.

Disabling XDebug only improved the performance by about 0.5 seconds.

---

Let me know if I should provide any other environment info, profiler outputs or anything else.

[ZanMaticPratnemer]

Of course I figured out my problem right after opening this issue.

First of all those options that Phalcon PDO sets by default do make a difference, but I used them in the wrong place while testing with \PDO - I passed them into \PDO->prepare() instead of \PDO constructor. I realized my mistake and tried using these options again.

And now with the option \PDO::ATTR_EMULATE_PREPARES set to false, \PDO->prepare() loop in PreparepdoAction() takes about 8.5 seconds to execute.

This option is set to true by default in PDO_MYSQL (https://www.php.net/manual/en/ref.pdo-mysql.php) since non emulated prepares have a poor performance with MySQL. I learned this from this comment on php.net. Didn't find anything else regarding this which is a shame.

Also this option is set to false by default in the MySQL Phalcon PDO in Phalcon v5.4 but not in v3.4 and I didn't find any mentions regarding this in upgrade notes - both 4.0 and 5.0 upgrade notes.

It was kind of weird upgrading the framework version and getting this kind of a performance drop without any warning in the upgrade notes.
So my question here is: Is there any specific reason why this option is set by default for MySQL PDO?

It would be good to either not set this to false by default for MySQL or mention this performance issue with MySQL in the upgrade notes/database docs.

If the option should just not be set by default for MySQL, let me know and I will open a pull request. If possible I would like to contribute to Phalcon by more than just opening this issue.

[s-ohnishi]

I suspect that turning off emulation mode is to align the behavior with PostgreSQL.
(In MySQL, if emulation mode is on, all retrieved values will be strings)
However, many people will choose Phalcon because they expect high performance, so I think it is a disadvantage to "turn it off" against those expectations.

[ZanMaticPratnemer]

I agree.
There is also the castOnHydrate model feature if we want the values that we work with to not be strings, but it is probably harder to make it turned on by default if MySQL is used.

[s-ohnishi]

Phalcon's model has a schema, so Phalcon should know what type it is, right?
I don't know which method is faster in the end, but I would like you to avoid using methods that are said to reduce performance.
If it is faster to turn off emulation mode, I think the evidence should also be disclosed.

[niden]

Related: #15361

[s-ohnishi]

@niden
If your reason for turning off PDO::ATTR_EMULATE_PREPARES is that you do not want MySQL to retrieve integer and floating point types in the result set as strings, then you do not need to turn it off in PHP 8.1 and later versions.
Migrating from PHP 8.0.x to PHP 8.1.x
Now that PHP 8.0.x has reached its EndOfLife, there seems to be no reason to turn off PDO::ATTR_EMULATE_PREPARES at the expense of performance.

[niden]

Resolved in #16486

Thank you @ZanMaticPratnemer and @s-ohnishi