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...