Re: SSI slows down over time

On 14/04/2014 10:14 AM, Kevin Grittner wrote:
> Ryan Johnson <ryan(dot)johnson(at)cs(dot)utoronto(dot)ca> wrote:
>
>> every time I shut down a database and bring it back up, SSI seems
>> to go slower.
> There's one thing to rule out up front -- that would be a
> long-lived prepared transaction.
>
> Please post the output of these queries:
>
> select version();
> show max_prepared_transactions;
> select * from pg_prepared_xacts;
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
(1 row)

max_prepared_transactions
---------------------------
0
(1 row)

transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)

FYI, here's a plot of performance over time. Each point in the graph is
throughput (in tps) over a 10-second measurement (~20 minutes total),
against a 12 WH TPC-C dataset with 24 clients banging on it. I issued a
pg_ctl stop/start pair between each run:

The downward trend is clearly visible, almost 30% performance loss by
the end. The data directory went from 1.4GB to 3.8GB over the lifetime
of the experiment. Final pg_serial size was 144kB, so the 8GB pg_serial
anomaly was not responsible for the drop in performance over time (this
time). I forgot to do an SI run at the beginning, but historically SI
performance has remained pretty steady over time. I don't know what
causes those big dips in performance, but it happens with SI as well so
I assume it's checkpointing or some such.

Now that I have a degraded database, any suggestions what should I look
for or what experiments I should run? I'm currently re-running the same
experiment, but deleting pg_serial/* in between runs; there was some
indication last week that this prevents the performance drop, but that
was nowhere near a rigorous analysis.

BTW, this is actually a TPC-C++ implementation I created, based on the
description in Cahill's thesis (and starting from the oltpbenchmark
TPC-C code). It turns out that normal TPC-C never spills to pg_serial
(at least, not that I ever saw). If you want to put hands on the code, I
can tar it up and post it somewhere.

Thoughts?
Ryan