Re: some longer, larger pgbench tests with various performance-related patches

On Wed, Jan 25, 2012 at 9:09 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Jan 25, 2012 at 12:00 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>> On Tue, Jan 24, 2012 at 12:53 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> Early yesterday morning, I was able to use Nate Boley's test machine
>>> do a single 30-minute pgbench run at scale factor 300 using a variety
>>> of trees built with various patches, and with the -l option added to
>>> track latency on a per-transaction basis.  All tests were done using
>>> 32 clients and permanent tables.  The configuration was otherwise
>>> identical to that described here:
>>>
>>> http://archives.postgresql.org/message-id/CA+TgmoboYJurJEOB22Wp9RECMSEYGNyHDVFv5yisvERqFw=6dw@mail.gmail.com
>>
>> Previously we mostly used this machine for CPU benchmarking.  Have you
>> previously described the IO subsystem?  That is becoming relevant for
>> these types of benchmarks.   For example, is WAL separated from data?
>
> I actually don't know much about the I/O subsystem, but, no, WAL is
> not separated from data.  I believe $PGDATA is on a SAN, but I don't
> know anything about its characteristics.

I think the checkpointing issues that Greg is exploring are important,
and I'm pretty sure that that is what is limiting your TPS in this
case. However, I don't think we can make much progress on that front
using a machine whose IO subsystem is largely unknown, and not
tweakable.

So I think the best use of this machine would be to explore the purely
CPU based scaling issues, like freelist, CLOG, and XLogInsert.

To do that, I'd set the scale factor small enough so that entire data
set is willing to be cached dirty by the kernel, based on the kernel
parameters:

egrep . /proc/sys/vm/dirty_*

Then set shared_buffers to be less than the needs for that scale
factor, so freelist gets exercised.

And neutralize checkpoints, by setting fsync=off, so they don't
generate physical IO that we can't control for given the current
constraints on the machine set up.

Cheers,

Jeff