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.TAA11638@wall.org>