Обсуждение: Modeling consumed shmem sizes, and some thorns
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
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
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