2

I recently got a "Out of shared memory - You might need to increase max_locks_per_transaction" error.

This is a long running process where I import a bunch of CSV files after proper processing. I loop through them and open a transaction for each file, process it and then when I'm done with the file, close the transaction and move to the next file.

These files are not big, the biggest clocking in at around 12K lines.

The processing I do, as far as PG is concerned, amounts to doing a few SELECTs (a couple per line, no joins or anything) and then an UPDATE of one existing row (at most one per line).

The problem is, pg_locks fills up with transactions that have no relation, are of type transactionid and have a mode of ExclusiveLock. I got as many as 20K rows like that in pg_locks and, besides the ones that, as expected, refer ti the two tables I'm using, an overwhelming majority looks like this (sorry, the indentation will be screwed up, I'm posting a link to pastebin too):

"locktype" "database" "relation" "page" "tuple" "virtualxid" "transactionid" "classid" "objid" "objsubid" "virtualtransaction" "pid" "mode" "granted" "fastpath"

"virtualxid" "" "" "" "" "11/18291" "" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "t"
"transactionid" "" "" "" "" "" "61840165" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61843843" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61833173" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61835511" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61846000" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61838308" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61832936" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"

http://pastebin.com/V4K3AvHD

Basically, none of those records have either a database or a relation, only a transaction id.

The only different row is the first one, which I take as coming from the transaction acquiring a lock on itself.

All the locks are being acquired by the same connection, and apparently only when executing the UPDATE, which really is no harder than

UPDATE t SET foo = COALESCE(foo, 0) + 23.4 WHERE bar = 'hey' and baz = 'ho'

and can affect at most one row.

When I'm done processing the file and commit the transaction, all those thousands of records just disappear from pg_locks, going back to a more normal number of between 15 and 30. It's as if each UPDATE somehow acquired a lock, making the record count of pg_locks increase to the tune of one every one or two seconds.

I assume I must be doing something wrong, but I can't figure out what.

As a temporary workaround I indeed increased max_locks_per_transaction, but I would really like to actually solve the problem. I don't think an increase of max_locks_per_transaction is warranted for my use case, which is quite simple.

Also, note that nobody else is accessing the two tables I'm using. Nobody is even accessing any table in the same schema.

I'm on Windows Web Server 2008 R2.

PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit

Connecting with Npgsql 2.2.5

Can anyone shed some light?

I thought about possible indexing issues, but the table in question is not huge by any means (fewer than 50K rows), and doing a SELECT on it with the same criteria used by the UPDATE is very fast.

Thanks in advance.

Improve this question
2
  • 2
    Are you using subtransactions? i.e. SAVEPOINT? If so, do you RELEASE SAVEPOINT? Or doing it in PL/PgSQL? Please show the code.
    – Craig Ringer
    Commented Jul 1, 2015 at 8:08
  • @CraigRinger I just checked the code, and indeed it is using a savepoint to catch duplicated key errors to allow the transaction to continue, but not releasing it. I guess that's not good.
    – s.m.
    Commented Jul 1, 2015 at 8:16

2 Answers 2

Reset to default
4

It sounds like the code might be using SAVEPOINTs to handle errors, and not releasing the savepoints before proceeding. That would explain the large number of virtual xid locks.

RELEASE SAVEPOINT after you're done with a step.

You might also want to consider batching the work into smaller chunks, as the:

  • SAVEPOINT
  • Try it
  • ROLLBACK TO SAVEPOINT if it fails, RELEASE SAVEPOINT if it succeeds

pattern works, but has some performance costs that scale with number of savepoints in a transaction.

This applies to PL/PgSQL BEGIN ... EXCEPTION blocks too.

Improve this answer
2
  • Thank you very much Craig. Just to clarify, does one need to RELEASE SAVEPOINT only when the operation succeeds? Does ROLLBACK TO SAVEPOINT also free up any resources used by the savepoint? In other words, if the operation fails and I rollback, won't I also need to release the savepoint, since I don't need it anymore? AFAICT, the docs don't cover this.
    – s.m.
    Commented Jul 2, 2015 at 14:58
  • @s.m. Yes, rollback to savepoint does too
    – Craig Ringer
    Commented Jul 2, 2015 at 21:22
1

It is difficult to explain your precise case without explain plans and engine profiling information. You may use pg_stat_statements module or PostgreSQL perf for tracing, but I am convinced your issue is the transaction size.

It is known that the pattern of one transaction per row must be avoided for performance reason as commit overhead will slow down the whole process. The same way, too large transactions (around 12K select and updates in your case) have drawbacks in term of undo information (tablespace may increase and fragment) and redo information (transaction log stored on a limited file system, with rotation processing).

For such mass import, the best way is to create batch of rows (per 1000 for instance) to process small affordable transactions, reducing the commit overhead and globally improve import throughput. There is no "best" batch size, but you should get a quick good value with a dichotomous approach.

Improve this answer
10
  • I'll go back and re-read my question, but I'm pretty sure I specified that I'm opening a transaction per file, not per row. Or am I not understanding you correctly?
    – s.m.
    Commented Jul 1, 2015 at 7:35
  • I'll improve the wording, it might actually convey the wrong idea. Sorry.
    – s.m.
    Commented Jul 1, 2015 at 7:37
  • I agree. But it means you may create. in worst case, a transaction updating 12K rows, right ?
    – Yves Martin
    Commented Jul 1, 2015 at 7:38
  • 3
    @YvesMartin I think you've confused PostgreSQL for Oracle. PostgreSQL does not have any undo and redo logs. It uses a single write-ahead log followed by storing new rows in the heap and cleaning up old rows later. Yes, there are issues with xlog size, checkpointing, and bloat, but they're not all that similar to Oracle's issues and do not involve any undo/redo logs. Also, batches are often better with many thousands of rows. It shouldn't be necessary to batch a 20k row operation.
    – Craig Ringer
    Commented Jul 1, 2015 at 8:11
  • 2
    @YvesMartin Only available disk space, and since PostgreSQL can write and checkpoint incomplete transactions to the heap, you can do bigger transactions than you have available space in pg_xlog.
    – Craig Ringer
    Commented Jul 1, 2015 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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