Skip site navigation (1) Skip section navigation (2)

Re: Errors on missing pg_subtrans/ files with 9.3

From: J Smith <dark(dot)panda+lists(at)gmail(dot)com>
To: Andres Freund <andres(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Errors on missing pg_subtrans/ files with 9.3
Date: 2013-11-12 18:45:01
Message-ID: CADFUPgdU62AgYisfFci9CJA1u15mpEtqZE2D375HrjGzp0VEdw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, Nov 12, 2013 at 11:55 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> Hi,
>
> On 2013-11-12 11:46:19 -0500, J Smith wrote:
>> > * Does SELECT count(*) FROM pg_prepared_xacts; return 0?
>>
>> Yes it does.
>
> Could you show the output? Do you actually use prepared xacts actively?

jay:dev(at)jagger=# select * from pg_prepared_xacts;
 transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)

We don't actively use prepared transactions, no. We use a lot of
prepared statements, but no two-phase committing.

> Do you actively use row level locking?

Not to my knowledge, no. I've enabled statement logging for a little
while so we can get a snapshot of what our average queries look like,
but I'm not seeing any PREPARE TRANSACTION or explicit LOCK
statements.

> Is there high concurrency in that environment? In short, could you describe your database usage a bit?

This particular database is used for an aggregation service. There are
a handful of Hibernate-based Java processes that manipulate and
massage the data from a number of data feeds we receive 24-7  From a
quick grepping of the statement log file we captured the following
over the course of approximately 13 minutes:

167452 SELECTs
72388 UPDATEs
6782 INSERTs
2 DELETEs

> Did you restart the database since getting the errors?

Yes, we've restarted several times.

>> Latest checkpoint's NextXID:          0/5138174
>> Latest checkpoint's NextOID:          3574036
>> Latest checkpoint's NextMultiXactId:  5762623
>> Latest checkpoint's NextMultiOffset:  11956824
>> Latest checkpoint's oldestXID:        1673
>> Latest checkpoint's oldestXID's DB:   1
>> Latest checkpoint's oldestActiveXID:  5138174
>> Latest checkpoint's oldestMultiXid:   1
>
> Interesting. Pretty high multixact usage.
>

Part of our process involves a collecting several records and
aggregating them within a single transaction. There will usually be a
handful of SELECTs to fetch the data, a bit of processing in the Java
backend, then a set of either UPDATEs or INSERTs into two tables to
store the results of the processing or in rarer cases a couple of
DELETEs.

Our Java guy would have more details, as I just have the output of the
log files and a high-level view of how our backend processes work, but
this is the general gist of things as I understand it.

Cheers


In response to

Responses

pgsql-hackers by date

Next:From: Kohei KaiGaiDate: 2013-11-12 19:19:30
Subject: Re: What's needed for cache-only table scan?
Previous:From: Teodor SigaevDate: 2013-11-12 18:35:31
Subject: nested hstore patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group