SSI and predicate locks - a non-trivial use case

Hello.

At work we have a program that seems to be stressing the SSI
implementation, and I thought that it could provide useful insights to
better tune it. In particular, there are a few parts that are
described as "chosen entirely arbitrarily (and without benchmarking)",
and we may provide some of that benchmarking.

First of all, we're running "PostgreSQL 9.2.4 on
x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080 704 (Red
Hat 4.1.2-52), 64-bit"

The program consumes messages from a message bus (ActiveMQ in our
case), and uses the data contained in them to update unstructured
documents; some values from those documents are extracted into an
attribute-value table to make it possible to search for them
later. The schema is essentially this::

CREATE TABLE docs (
id VARCHAR(255) PRIMARY KEY,
contents TEXT NOT NULL
);

CREATE TABLE doc_attributes (
document_id VARCHAR(255) NOT NULL REFERENCES docs(id)
ON DELETE CASCADE,
attribute_name VARCHAR(255) NOT NULL,
value VARCHAR(255) NOT NULL
);

CREATE INDEX idx_attribute_doc
ON doc_attributes(document_id);

CREATE INDEX idx_attribute_name_str
ON doc_attributes(attribute_name,value);

The interesting part of the program works like this:

* Figure out which documents to update::

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT id FROM docs WHERE ...;
COMMIT;

* Update each of them in turn::

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT contents FROM docs WHERE id=?;
-- change the contents, in client code
UPDATE docs SET contents=? WHERE id=?;
DELETE FROM doc_attributes WHERE document_id=?;
INSERT INTO doc_attributes(document_id,attribute_name,value)
VALUES (?,?,?); -- for each attribute
COMMIT;

If we receive a serialisation error, we retry the whole transaction,
applying the changes to the new version of the document. Each retry
takes about 0.1 seconds.

We have a few processes doing this in parallel, to keep up with the
amount of messages that are sent. We have an average of 30 rows in
``doc_attribute`` for each row in ``docs``. This is a typical
situation::

SELECT pid, locktype,
COUNT(*)/COUNT(DISTINCT virtualtransaction) AS tl,
COUNT(*) AS total
FROM pg_locks
WHERE mode LIKE 'SI%'
GROUP BY pid, locktype
ORDER BY pid, locktype;

pid | locktype | tl | total
------+----------+-----+-------
445 | page | 5 | 2706
445 | tuple | 1 | 767
446 | page | 14 | 28
446 | tuple | 37 | 74
447 | page | 1 | 19
448 | page | 1 | 19
449 | page | 5 | 2759
449 | tuple | 1 | 758
454 | page | 10 | 2209
454 | tuple | 37 | 7663
1113 | page | 5 | 604
1113 | tuple | 4 | 531
1346 | page | 6 | 1557
1346 | tuple | 1 | 454
| page | 174 | 174
| tuple | 236 | 236
(16 rows)

Due to the large number of predicate locks, we have
``max_pred_locks_per_transaction = 10000``, and ``max_connections =
300`` (this is probably going to be reduced, we don't need more than
100).

Questions:

- What are locks without a pid? I thought they were leftover from
transactions of now-disconnected clients, awaiting that all
overlapping transactions complete, but the numbers don't behave as I
would expect in that case (i.e. they don't grow when a client
disconnect)

- Is the large number of page locks to be expected? How long should
we expect them to stay? Some seem to stay around for minutes.

- Can this be of any use to benchmarking / tuning the SSI logic?

--
Dakkar - <Mobilis in mobile>
GPG public key fingerprint = A071 E618 DD2C 5901 9574
6FE2 40EA 9883 7519 3F88
key id = 0x75193F88

Well, I think Perl should run faster than C. :-)
-- Larry Wall in <199801200306(dot)TAA11638(at)wall(dot)org>