Thread: Modeling consumed shmem sizes, and some thorns

Modeling consumed shmem sizes, and some thorns

From
Daniel Farina
Date:
Hello List,

I'd like to share with you some experiences we've had while
investigating what we'd have to do to make very-very tiny databases.

First, the formulae at
http://www.postgresql.org/docs/9.1/static/kernel-resources.html#SHARED-MEMORY-PARAMETERS
(17-2) seem misleading, particularly with regard to the overhead of
supporting a large number of connections: it undercounts by a rather
lot.  I think the estimate of 270 bytes per lock seems too far low on
Linux-amd64.  In addition, this number seem undercounted by 50% or
more because of the addition of predicate locks in 9.1.

Presuming the base cost of 1800-base-cost-per-connection is still
right, experimentally it seems to me that the right numbers are closer
to 700 bytes per max_locks_per_transaction, and 650 for each
max_pred_locks_per_transaction, although there appear to be some
non-linear behavior that make this a hazy projection.

Besides accuracy, there is a thornier problem here that has to do with
hot standby (although the use case is replication more generally) when
one has heterogeneously sized database resources. As-is, it is
required that locking-related structures -- max_connections,
max_prepared_xacts, and max_locks_per_xact (but not predicate locks,
is that an oversight?) must be a larger number on a standby than on a
primary.

In a heterogeneous environment where one uses WAL-based replication,
that means that to obtain unity and full compatibility among
different-sized systems one must always permit a large number of
connections (specifically, the largest number supported by any
database configuration), and those large number of connections can
occupy a large fraction of the overall memory allotted to a small
database, making the amount of lock-related memory consumption on,
say, a database that is intended to only receive 100MB of shmem
approach nearly 50% of the overall total, and that is rather
unfortunate.  I can see why that'd be hard to fix (maybe, instead, a
more logical replication layer is a better investment of time), but I
thought it an interesting consideration that was worth discussing.

-- 
fdr


Re: Modeling consumed shmem sizes, and some thorns

From
Simon Riggs
Date:
On Wed, May 2, 2012 at 9:38 PM, Daniel Farina <daniel@heroku.com> wrote:

> Besides accuracy, there is a thornier problem here that has to do with
> hot standby (although the use case is replication more generally) when
> one has heterogeneously sized database resources. As-is, it is
> required that locking-related structures -- max_connections,
> max_prepared_xacts, and max_locks_per_xact (but not predicate locks,
> is that an oversight?) must be a larger number on a standby than on a
> primary.

>= not >
so you can use the same values on both sides

Predicate locks aren't set in recovery so the value isn't checked as a
required parameter value.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Re: Modeling consumed shmem sizes, and some thorns

From
Daniel Farina
Date:
On Thu, May 3, 2012 at 2:23 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Wed, May 2, 2012 at 9:38 PM, Daniel Farina <daniel@heroku.com> wrote:
>
>> Besides accuracy, there is a thornier problem here that has to do with
>> hot standby (although the use case is replication more generally) when
>> one has heterogeneously sized database resources. As-is, it is
>> required that locking-related structures -- max_connections,
>> max_prepared_xacts, and max_locks_per_xact (but not predicate locks,
>> is that an oversight?) must be a larger number on a standby than on a
>> primary.
>
>>= not >
> so you can use the same values on both sides
>
> Predicate locks aren't set in recovery so the value isn't checked as a
> required parameter value.

I had a feeling that might be the case, since my understanding is that
they are not actually locks -- rather, markers.

In any case, it would be strange to change the *number* of locks per
transaction in such heterogeneous environments because then some
fairly modestly sized transactions will simply not work depending on
one size of system one selects. The more problematic issue is that
small systems will be coerced into having a very high number for
max_connections and the memory usage required by that, if one also
provides a large system supporting a high connection limit and moves
things around via WAL shipping. I'm not sure what there is to be done
about this other than make the absolutely required locking structures
smaller -- I wonder if not unlike the out-of-line storage for PGPROC
patch this might also make some things faster.  All in all, without
having gone in to figure out *why* the size consumption is as it is
I'm a little flabbergasted as to why the locking structures are just
so large.

-- 
fdr