Level 61
Haven't tried it, but would love to hear more.
Question for you is why do you care so much about 25ms? How many visitors do you have? What kind of app is it since you need that 25ms?
Cause maybe then PHP is not the best choice.
Dec 10, 2019
14
Level 1
PHP 7.4 preloading for pre-warming database connections?
With PHP 7.4 introducing OPCache preloading, I was wondering whether it would be possible to abuse this feature to "pre-warm" a database connection. My Postgres database is hosted on a different server and establishing an SSL connection to it adds ~25ms of latency to my requests. Now, if 1. preloading allows us to execute any PHP commands in the preload script (in particular e.g. https://www.php.net/manual/en/function.pg-pconnect.php) and 2. if the preload script gets run individually for each request (when preparing a new per-request handling process for the FPM pool) it might be possible to call pg_pconnect with my connection in the preload script. This should have the connection connected and ready for use once the pre-loaded process is actually used for serving a request — similar to database connection pooling in other languages that use a "persistent" server process serving many requests.
However, I might be misunderstanding how preloading works and/or my two assumptions above are wrong. Has anyone tried this or conclusive evidence that it would not work?
Level 1
I would be interested to see if someone maybe gives this a try, it'd be nice to see it in use.
That said, going to echo what @bugsysha said. That saving is so minimal, you could achieve the same result with some really minor tweaks to some elements of your project. As a "time-saving" measure, this seems like it'll be a lot more work than it is worth.
But none the less, would be good to see in action.
Level 1
The total backend latency of my application (according to Laravel DebugBar) is 70-100 ms, of which 15-25 are spent on the first database query (which includes the SSL handshake etc.). To me, that is a substantial portion of all the potential savings, especially if preloading manages to shave a few more ms off the overall latency. And if I could achieve that by just adding one line to a preload file I would be incentivized to use, anyway, that sounds like a good deal to me.
In terms of why I am looking to reduce latency, I am using Turbolinks and jQuery-UJS to make my site feel as "app-like" as possible (similar to Livewire and InertiaJS). And reducing backend latency as much as possible helps achieve the illusion of working with a local app, of course. The app itself does not do much heavy processing, so the performance of PHP itself should be sufficient. This is underlined by the latency numbers above.
/update: I had looked at the wrong latency numbers; updated those. But the potential savings would still be substantial.
Level 61
To me GitHub looks OK with same approach (Turbolinks) so maybe you are going an extra mile that you do not need to. If you want SPA feel, then you need SPA. Everything else will always have some issues to address.
Level 1
I don't want to build a full SPA; I just want to achieve the best-possible latency achievable with server-side rendering. I think that's a reasonable goal, and saving up to 25ms is nothing to sneeze at no matter what.
Let's get back to the topic of whether one could use a PHP preload script to pre-warm a database connection.
Level 61
Don't get me wrong, that is totally valid if it is that important to you. I would love to see you shave whole 25ms cause that would prove I could use same approach and avoid overcomplicating things with SPA.
Can you disclose what is the website and ping me here if you succeed?
Level 1
The website is https://web.timingapp.com. Unfortunately, to try it out, you currently need to register for a free trial account from within my app (https://timingapp.com), or ask me via https://timingapp.com/contact to create one for you. At least from Germany (which is also where the site is hosted), it already feels fairly performant, but one can always do better — especially for all the users who are not located as close to the server as I am myself.
Level 61
@mrmage I love that app, I've purchased previous version. It is most perfect time tracking app there is. Stopped using it when I've changed jobs cause I didn't need it anymore.
Like I said, please ping me if you make it work.
Level 1
To answer my own question, a year later:
The much easier solution is to add the \PDO::ATTR_PERSISTENT attribute to your connection. For example, these are my pgsql options in config/database.php:
'options' => [
\PDO::ATTR_PERSISTENT => true,
\PDO::PGSQL_ATTR_DISABLE_PREPARES => true,
],
While at it, \PDO::PGSQL_ATTR_DISABLE_PREPARES prevents the driver from creating a prepared SQL statement for each query. This saves a whole database roundtrip per query, which can add up over several queries.
Level 61
@mrmage that speeds up the single query, but if you have same query with different arguments it will slow down things in a longer run. Also this opens door to the SQL injection. So I would not do that and I think that you were not presented with that kind of option cause of this reason.
Level 1
Reading https://github.com/doctrine/dbal/issues/2162, it sounds like ATTR_EMULATE_PREPARES would be prone to SQL injection, while PGSQL_ATTR_DISABLE_PREPARES isn't. Or am I misreading that?
Also, I would expect the queries used to serve an individual request to all look fairly different. Repetition would be more expected in a batch job, while this is about serving latency. (In any case, Postgres is quite fast at parsing queries like SELECT * FROM users WHERE id = ?.)
Level 1
P.S.: It also sounds like Doctrine sets PGSQL_ATTR_DISABLE_PREPARES by default, which I wouldn't expect them to do if it were unsafe.
Level 61
It is written very poorly so I'm not sure, but now I do not have time to investigate. I'm sure you can find something that will clear things up for you. Maybe someone will see this topic and answer your concerns.
Level 1
I've found https://www.php.net/ChangeLog-5.php, which is definitive enough for me:
"Added PDO::PGSQL_ATTR_DISABLE_PREPARES constant to execute the queries without preparing them, while still passing parameters separately from the command text using PQexecParams."
Please or to participate in this conversation.