Re: database must be vacuumed with <N> transactions

On Thu, Jun 25, 2015 at 2:23 PM, Keith <keith(at)keithf4(dot)com> wrote:

> On Thu, Jun 25, 2015 at 2:42 PM, Dave Johansen <davejohansen(at)gmail(dot)com>
> wrote:
>
>> On Thu, Jun 25, 2015 at 11:38 AM, John Scalia <jayknowsunix(at)gmail(dot)com>
>> wrote:
>>
>>> The pg_stat_activity table will show you which processes are doing what,
>>> and you'll be able to see their process ID from the O/S. That might help
>>> you.
>>>
>>
>> Yes, but I need to see which process ID is doing a TON of small
>> transactions which is causing the XID to increment at an unexpectedly high
>> rate and so my question is "if there's a way for me to get the XID for
>> every connection/query?".
>>
>
> Look at the log_line_prefix options. To get everything you're looking for
> to try and identify where this is coming from look at using at a minimum: %p,
> %r, %m, %x, %u, %d
>
>
> http://www.postgresql.org/docs/8.4/static/runtime-config-logging.html#GUC-LOG-LINE-PREFIX
>

We found the source of the issue. It's a function that we're using to all a
table to be populated from multiple processes. You can see the details here:
http://www.postgresql.org/message-id/CAAcYxUf9o3NpiKaHdeShO_iJjm92314eX6ko0JvJidmksutkJg@mail.gmail.com

Any ideas on how we could handle this with causing it to increment the XID?

> You'll also have to make sure log_min_duration_statement is set to low
> enough to log the queries you're looking for. Beware setting disabling it
> or setting it too low, though, if you have a very high number of queries
> being run. This can quickly overload your IO and disk space.
>

This probably isn't feasible, because we run a lot of queries with most of
them being very short duration.

> If you get the database running again, you can also look at the
> pg_stat_statements contrib module to log query counts and parameterized
> versions of all queries being run.
>
> http://www.postgresql.org/docs/8.4/static/pgstatstatements.html
>

We got it up and we will be turning this module on to help diagnose issues
like this in the future.

> And I can't let this email go without say you REALLY need to look at
> upgrading your database. 8.4 is no longer being supported and receives no
> security or bug fixes.
>

We are looking into upgrading to RHEL 7 which comes with Postgres 9.2 but
that's a SLOW process.