Help wanted: very slow queries in production

[odahcam]

Hello! First thanks for this library, it is really useful. After a few changes I was able to configure this in my project and it works really well locally.

When I deployed this to production my queries became slower than they were, and I mean a lot slower. I know Amp implementation is slower than PDO's but I do have a comparison to this Eloquent adapter using Doctrine and it runs almost at the same speed it runs with PDO, meaning Amp implementation is not the issue, but this conector/adapter.

I'd like to have some ideas or tips on how can I debug the bad performance of my application when using this library.

Thanks in advance!

[xpader]

Amphp mysql client single query can not be fast then PDO, because it's C implement, and amphp mysql is php implement.
But amphp mysql concurrency is much greater than PDO, because it's async, and use connection pool.

Even so, there is no obvious performance difference in a single query. Infact, in my actual use, in most cases it is faster than PDO.

What is your php version, what linux distribution and version you used, how did you install your php (apt, docker, or complie from source yourself), what event loop extension you used (event, ev, uv, or native php select)?

[odahcam]

Hey thanks for the quick reply! I'm using a custom Docker image I built from the official php:8.3 image. I'm using Revolt with no extensions, just the native PHP select.

I'm aware of the performance difference but except from connecting (which is taking 10s) I did find Amp Mysql reasonably fast, as you said faster than PDO when running concurrent queries. Sync queries are taking 20% to 30% more time than PDO in my tests with Doctrine DBAL using Amp Mysql DBAL.

My performance issues starts when I use this with Eloquent, each connection does takes 10s to connect but the queries take a long time to run, some take 3 times more than PDO some almost 2 minutes. Which is really weird because de difference to Amp MySQL with Doctrine ORM to Eloquent is really big.

I will have to start digging tomorrow so if you have some tips it would save me much time. I hope I can make a PR if I ever find out any issues.

[xpader]

Why did your query used seconds? In my tests, even through an external network connection, the response was only milliseconds. And 10s is unacceptable. How long do your connections and queries take when using PDO?

[xpader]

I have test in php:8.3 with native PHP select docker image, it's pretty fast. But I found that amphp mysql can not reuse 1 connection in the first two queries at the beginnin.

In most times, it create two connections at least. After two connections created, and reused connection query, the difference between a single query and PDO at most is only about 10 milliseconds.

Keep trying to dig..

[odahcam]

With PDO they connect in milliseconds too. It takes more than I'd like because Digital Ocean's App Platform requires me to route my database connections through outside the VPC, meaning backend server (client) and database server connections are routed through the internet. But still, even if connection takes 10 seconds the queries using Amp without Eloquent are really fast too, taking milliseconds as well, it is only when using Amp through Eloquent we notice slowness.

[odahcam]

Uhmm got it, I'll try inspecting the connections and the pool as well. I believe you tested it locally and I must say, locally this thing is blazing fast, it is crazy how it behaves differently in a different server even though it is the same Docker image.

[xpader]

I test on my local computer connect to cloud server, pretty fast. Can you show the simplified code you're having trouble with?

[odahcam]

Could you share the exact versions you have installed for: Eloquent, Amp, Amp Mysql, Revolt adapter and DBAL?

I have those currently (installed version in parenthesis):

[xpader]

I didn't use react adapter and DBAL.

This my composer info:

amphp/amp                       3.0.2    A non-blocking concurrency framework for PHP applications.
amphp/byte-stream               2.1.1    A stream abstraction to make working with non-blocking I/O simple.
amphp/cache                     2.0.1    A fiber-aware cache API based on Amp and Revolt.
amphp/dns                       2.3.0    Async DNS resolution for Amp.
amphp/mysql                     3.0.0    Asynchronous MySQL client for PHP based on Amp.
amphp/parser                    1.1.1    A generator parser to make streaming parsers simple.
amphp/pipeline                  1.2.1    Asynchronous iterators and operators.
amphp/process                   2.0.3    A fiber-aware process manager based on Amp and Revolt.
amphp/serialization             1.0.0    Serialization tools for IPC and data storage in PHP.
amphp/socket                    2.3.1    Non-blocking socket connection / server implementations based on Amp and Revolt.
amphp/sql                       2.0.1    Asynchronous SQL client for Amp.
amphp/sql-common                2.0.2    Common classes for non-blocking SQL implementations.
amphp/sync                      2.3.0    Non-blocking synchronization primitives for PHP based on Amp and Revolt.
brick/math                      0.12.1   Arbitrary-precision arithmetic library
carbonphp/carbon-doctrine-types 2.1.0    Types to use Carbon in Doctrine
daverandom/libdns               2.1.0    DNS protocol implementation written in pure PHP
doctrine/inflector              2.0.10   PHP Doctrine Inflector is a small library that can perform string manipulations with rega...
illuminate/collections          10.48.25 The Illuminate Collections package.
illuminate/conditionable        10.48.25 The Illuminate Conditionable package.
illuminate/container            10.48.25 The Illuminate Container package.
illuminate/contracts            10.48.25 The Illuminate Contracts package.
illuminate/database             10.48.25 The Illuminate Database package.
illuminate/macroable            10.48.25 The Illuminate Macroable package.
illuminate/support              10.48.25 The Illuminate Support package.
kelunik/certificate             1.1.3    Access certificate details and transform between different formats.
league/uri                      7.5.1    URI manipulation library
league/uri-interfaces           7.5.0    Common interfaces and classes for URI representation and interaction
nesbot/carbon                   2.72.6   An API extension for DateTime that supports 281 different languages.
psr/clock                       1.0.0    Common interface for reading the clock.
psr/container                   2.0.2    Common Container Interface (PHP FIG PSR-11)
psr/http-factory                1.1.0    PSR-17: Common interfaces for PSR-7 HTTP message factories
psr/http-message                2.0      Common interface for HTTP messages
psr/simple-cache                3.0.0    Common interfaces for simple caching
revolt/event-loop               1.0.6    Rock-solid event loop for concurrent PHP applications.
symfony/deprecation-contracts   3.5.1    A generic function and convention to trigger deprecation notices
symfony/polyfill-mbstring       1.31.0   Symfony polyfill for the Mbstring extension
symfony/polyfill-php80          1.31.0   Symfony polyfill backporting some PHP 8.0+ features to lower PHP versions
symfony/translation             6.4.13   Provides tools to internationalize your application