Re: SSI slows down over time

Ryan Johnson <ryan.johnson@cs.utoronto.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