On 14/04/2014 10:14 AM, Kevin Grittner
wrote:
versionRyan Johnson <ryan.johnson@xxxxxxxxxxxxxx> 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; ------------------------------------------------------------------------------------------------------------ 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 |