Gianni Ceccarelli wrote:
> 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.
Any insights on where the current algorithms don't perform well
would be welcome. Of course, the subject line gives me some pause
-- I'm aware of many uses of SSI in non-trivial production
environments, including multi-terrabyte databases with thousands of
concurrent users. In some cases the performance hit compared to
REPEATABLE READ, which would allow anomalies, is about 1%.
> 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.
Using SSI to manage a database queue is known to be a "worst case"
workload. Since there is no blocking beyond what is present in
snapshot isolation level techniques (which is what you get at the
REPEATABLE READ level), all concurrent attempts will pull the same
item off the queue and attempt to process it, and all but one will
be rolled back with a serialization failure. Efficient management
of a queue in the database really requires blocking techniques to
actually serialize the accesses to the ends of the queue.
That said, it sounds like you are not using SSI for that, but
leaving the queue management to ActiveMQ, which presumably handles
this correctly, and your problems are with the access to the
documents based on the requests pulled from the queue. Is that
correct?
> 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;
I'm afraid that one of the most interesting and pertinent bits of
the code is written as ellipses. Can you show or describe what the
REPEATABLE READ transaction is doing in more detail?
> 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.
What percentage of transactions are rolled back? What is the
processing time in such transactions as a percentage of the total
load?
> 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 = 1``, 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
> tra
