I've been blaming AWS Aurora Postgres for intermittent cases where our logs are showing the method used to instantiate our DB connection via PDO::__construct() to take multiple seconds, even tens of seconds sometimes. However, adding the PDO::ATTR_TIMEOUT setting in the constructor's additional params array with a small value made no difference: I still get occasional long init times. I also tried adding this right before the PDO instantiation, per another forum post I saw, but also with no effect:

ini_set("default_socket_timeout", 1);
$this-dbh = new PDO(/* bunch of args */);

Just wondering if anyone else ever had a problem like this, and if so -- and more importantly 🙂 -- a solution?

PS: That's all that method does: set a few variables from the global config array, then use them to instantiate PDO.

    I haven't seen it myself... my first thought was "maximum connection limit" but traffic wouldn't get backed up THAT far.

    Can you tell if the delay is at PostgreSQL's end or PDO's? Log connections and disconnections. Run the PDO connection in isolation so that the only thing the script does is open a connection (which is then immediately closed because the script is finished). Comparing the log timestamps may tell you, between "connection received" "connection authorized" and "disconnection", on which side the delay is.

      Thanks...I shall definitely take a look at that, though maybe not until Friday (sprint review/planning tomorrow); but I definitely would love to nail down what/where the actual delay is, rather than just trying things until something seems to "fix" it. 🙂

        'rather than just trying things until something seems to "fix" it. 🙂'

        --- but aren't you the one who posted my new favorite wall art a while back? That cute puppy .... 😃

        Hope you get it sorted. I'd blame AWS too ;-)

          Yep, that's me. 🙂

          I just created a little test script that has a timed function that does nothing but instantiate a PDO object and then unset() it, recording the time for the new PDO(...) to run. It confirms that maybe less than 1% of such instantiations take > 0.5 seconds, and maybe 1 in a few hundred takes > 4 seconds. It's almost never in between: it's either a fraction of a second, or at least 4.5 seconds. (shrug)

            4 days later

            I'd definitely think "infrastructure" there. These VM's are better than time-shared systems, but I really sometimes question how MUCH better. I'm not at all certain that somebody (program/OS, etc.) can't still tie up a thread that somebody else might need running ...

              I'm starting to look at pgbouncer as a possible remedy, but it's making my head spin so far. 🙁

                That does look kind of interesting. Are you in a situation where you could spin up multiple instances to run a cluster sort of situation?

                I've often wondered about it, but $$$ is generally lacking for hardware (although there are new workstations coming soon, YAY!)

                  I got one of our DB experts interested, and he helped me add some additional postgres logging params (as Weedpacket referred to above), and we'll start diving into the results tomorrow (or whenever) to see if we can narrow down where the actual lag is. He said he'd set up pgbouncer a couple times, so if we decide that's a good option, I'll have a good head start.

                  We use Docker containers for our application, so I suspect either it would be installed on each web container, or else as its own container on AWS/ECS.

                    2 years later

                    Hello @NogDog,

                    I'm having the exact same issue, but I'm using azure instead aws, I would like to know if you ever find a solution at this issue?

                    thanks in advance,

                    Rémi

                    rlg34

                    We ended up adding a PGBouncer container to the stack. Our app then points to it as the "database", and PGBouncer then maintains connections to the PostgreSQL DB. It has effectively eliminated those intermittent lags.

                      Thanks a lot for your answer,
                      we'll try to investigate this but we're using Mysql instead of Postgre,
                      But really, thanks again for you're answer

                      rlg34 we're using Mysql instead of Postgre

                      Oh, yeah, it's definitely Postgres-only. I have no idea if (a) MySQL has the same connection lag issue, and (b) if there is a PGBouncer-like alternative. Sorry. 🙁

                          Write a Reply...