From: Ryan Johnson <ryan.johnson@cs.utoronto.ca>
To: pgsql-performance@postgresql.org
Subject: Re: SSI slows down over time
Date: Mon, 14 Apr 2014 16:30:56 -0400
Message-ID: <534C4580.40700@cs.utoronto.ca> (raw)
In-Reply-To: <1397484864.73109.YahooMailBasic@web122302.mail.ne1.yahoo.com>
References: <1397484864.73109.YahooMailBasic@web122302.mail.ne1.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 14/04/2014 10:14 AM, Kevin Grittner wrote:
> 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
Attachments:bjadcjjf.png (image/png, 12.4K) downloadview image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ryan.johnson@cs.utoronto.ca
Subject: Re: SSI slows down over time
In-Reply-To: <534C4580.40700@cs.utoronto.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox