Re: checkpointer continuous flushing

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Cc: Andres Freund <andres(at)anarazel(dot)de>, Heikki Linnakangas <hlinnaka(at)iki(dot)fi>, PostgreSQL Developers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: checkpointer continuous flushing
Date: 2015-08-19 06:43:56
Message-ID: alpine.DEB.2.10.1508190812290.5936@sto
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

> Sure, I think what can help here is a testcase/'s (in form of script file
> or some other form, to test this behaviour of patch) which you can write
> and post here, so that others can use that to get the data and share it.

Sure... note that I already did that on this thread, without any echo...
but I can do it again...

Tests should be run on a dedicated host. If it has n cores, I suggest to
share them between postgres checkpointer & workers and pgbench threads so
as to avoid thread competition to use cores. With 8 cores I used up to 2
threads & 4 clients, so that there is 2 core left for the checkpointer and
other stuff (i.e. I also run iotop & htop in parallel...). Although it may
seem conservative to do so, I think that the point of the test is to
exercise checkpoints and not to test the process scheduler of the OS.

Here are the latest version of my test scripts:

(1) <name> <test>

Run "test" with setup "name". Currently it runs 4000 seconds pgbench with
the 4 possible on/off combinations for sorting & flushing, after some
warmup. The 4000 second is chosen so that there are a few checkpoint
cycles. For larger checkpoint times, I suggest to extend the run time to
see at least 3 checkpoints during the run.

More test settings can be added to the 2 "case"s. Postgres settings,
especially shared_buffers, should be set to a pertinent value wrt the
memory of the test host.

The test run with postgres version found in the PATH, so ensure that the
right version is found!

(2) one-test-output.log

For rate limited runs, look at the final figures and compute the number of
late & skipped transactions. This can also be done by hand.


For full speed runs, compute stats about per second tps:

sh> grep 'progress:' one-test-output.log | cut -d' ' -f4 | \
./ --limit=10 --length=4000
warning: 633 missing data, extending with zeros
avg over 4000: 199.290575 ± 512.114070 [0.000000, 0.000000, 4.000000, 5.000000, 2280.900000]
percent of values below 10.0: 82.5%

The figures I reported are the 199 (average tps), 512 (standard deviation
on per second figures), 82.5% (percent of time below 10 tps, aka postgres
is basically unresponsive). In brakets, the min q1 median q3 and max tps
seen in the run.

> Ofcourse, that is not mandatory to proceed with this patch, but still can
> help you to prove your point as you might not have access to different
> kind of systems to run the tests.

I agree that more tests would be useful to decide which default value for
the flushing option is the better. For Linux, all tests so far suggest
"on" is the best choice, but for other systems that use posix_fadvise, it
is really an open question.

Another option would be to give me a temporary access for some available
host, I'm used to running these tests...


Attachment Content-Type Size application/x-sh 5.3 KB text/x-python 552 bytes text/x-python 3.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message 'Victor Wagner *EXTERN*' 2015-08-19 06:51:47 Re: Proposal: Implement failover on libpq connect level.
Previous Message Michal Šalko 2015-08-19 06:04:58 Missing equivalent xpath_table in xml2