2

We recently setup a PostgreSQL Cluster with PGPool in front of it. This is our first go at such a thing. We setup our QA environment to use the new PGPool, and we cranked up the logging on the Postgresql servers so we could point pgBadger at it and see how things are going.

After minimal use, and not quite 24 hours of logging, pgBadger showed me this report this morning:

enter image description here

That seems absurd to me. The first three top queries don't even appear to have anything to do with our application nor application user, yet they all accuse the application of instigating them.

We assume that PGPool is doing this in the background when the application instigates it, but even then ... there is no way our QA application instigated 369K times. Admittedly, the time spent on these are negligible, all of 24 seconds over the course of a day.

However, we would like to grasp:

  • Why are we seeing this amount of traffic from pgPool?
  • Can we expect this to scale? Currently we have a single QA DB here with very light usage. What do we expect when we get 200 production databases running in this cluster?
  • Is there an easy way to prevent posgtresql from logging these types of queries from pgpool in order for us to more easily parse queries that are truly instigated by our application?
  • Is there another question we should ask that we haven't?
Improve this question
  • 1
    The deallocate is "normal" behaviour when you use prepared statements, that shouldn't be a problem. Judging by the name of the statement that was deallocated pdo_statement I would look into whatever ORM layer you are using. This seems to be the typical (stupid) behaviour of ORMs – a_horse_with_no_name Apr 9 '13 at 18:28
  • That was my first thought as well; however, it doesn't appear that there are any equivalent number of legit queries, I expected to see something like 120 legit queries; I'm struggling with the idea that even a poor ORM would increase that to these numbers, and we don't see equivalent results in our non-clustered db's. – bubba Apr 9 '13 at 18:38
  • Prepared statements are often at a lower level than the ORM. For example, in Perl, DBD::Pg by default uses them (you can turn them off though). – Chris Travers Sep 17 '13 at 12:04
1

I don't think these are issued by PGPool. I think they are instigated by your application (which I am guessing is written in PHP and running over some sort of ORM).

The first entry is DEALLOCATE which means effectively to tell the server to free up memory from a prepared statement. These look like they are issued through some PDO module.

The second and third queries look like they are ORM-related mapping queries. Your application is probably unware that these queries are being issued by the framework it is running on, but these queries only make sense really in an ORM or similar environment.

Improve this answer
  • 2nd and 3rd seem to be pgPool queries - at least the usage of the function pgpool_regclass could be an indicator of that. – a_horse_with_no_name Sep 17 '13 at 11:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.