On Tue, Nov 12, 2013 at 11:55 AM, Andres Freund <and...@2ndquadrant.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@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
