Обсуждение: Estimating HugePages Requirements?

On Thu, Jun 10, 2021 at 12:42 AM Don Seiler <don@seiler.us> wrote:
>
> I'm told other factors contribute to this additional memory requirement, such as max_connections, wal_buffers, etc.
I'mwondering if anyone has been able to come up with a reliable method for determining the HugePages requirements for a
PGcluster based on the GUC values (that would be known at deployment time). 

It also depends on modules like pg_stat_statements and their own
configuration.  I think that you can find the required size that your
current configuration will allocate with:

SELECT sum(allocated_size) FROM pg_shmem_allocations ;

On Wed, Jun  9, 2021 at 01:52:19PM -0500, Don Seiler wrote:
> On Wed, Jun 9, 2021 at 1:45 PM Vijaykumar Jain <vijaykumarjain.github@gmail.com
> > wrote:
> 
>     Please ignore, if you have read the blog below, if not, at the end of it
>     there is a github repo which has mem specs for various tpcc benchmarks.
>     Ofcourse, your workload expectations may vary from the test scenarios used,
>     but just in case.
> 
>     Settling the Myth of Transparent HugePages for Databases - Percona Database
>     Performance Blog
> 
> 
> That blog post is about transparent huge pages, which is different than
> HugePages I'm looking at here. We already disable THP as a matter of course.

This blog post talks about sizing huge pages too:

    https://momjian.us/main/blogs/pgblog/2021.html#April_12_2021

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On Wed, Jun 9, 2021 at 7:23 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> On Thu, Jun 10, 2021 at 12:42 AM Don Seiler <don@seiler.us> wrote:
> >
> > I'm told other factors contribute to this additional memory requirement, such as max_connections, wal_buffers, etc.
I'mwondering if anyone has been able to come up with a reliable method for determining the HugePages requirements for a
PGcluster based on the GUC values (that would be known at deployment time). 
>
> It also depends on modules like pg_stat_statements and their own
> configuration.  I think that you can find the required size that your
> current configuration will allocate with:
>
> SELECT sum(allocated_size) FROM pg_shmem_allocations ;

I wonder how hard it would be to for example expose that through a
commandline switch or tool.

The point being that in order to run the query you suggest, the server
must already be running. There is no way to use this to estimate the
size that you're going to need after changing the value of
shared_buffers, which is a very common scenario. (You can change it,
restart without using huge pages because it fails, run that query,
change huge pages, and restart again -- but that's not exactly...
convenient)

--
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

Magnus Hagander <magnus@hagander.net> writes:
> I wonder how hard it would be to for example expose that through a
> commandline switch or tool.

Just try to start the server and see if it complains.
For instance, with shared_buffers=10000000 I get

2021-06-09 15:08:56.821 EDT [1428121] FATAL:  could not map anonymous shared memory: Cannot allocate memory
2021-06-09 15:08:56.821 EDT [1428121] HINT:  This error usually means that PostgreSQL's request for a shared memory
segmentexceeded available memory, swap space, or huge pages. To reduce the request size (currently 83720568832 bytes),
reducePostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections. 

Of course, if it *does* start, you can do the other thing.

Admittedly, we could make that easier somehow; but if it took
25 years for somebody to ask for this, I'm not sure it's
worth creating a feature to make it a shade easier.

            regards, tom lane

On Wed, Jun 9, 2021 at 9:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Magnus Hagander <magnus@hagander.net> writes:
> > I wonder how hard it would be to for example expose that through a
> > commandline switch or tool.
>
> Just try to start the server and see if it complains.
> For instance, with shared_buffers=10000000 I get
>
> 2021-06-09 15:08:56.821 EDT [1428121] FATAL:  could not map anonymous shared memory: Cannot allocate memory
> 2021-06-09 15:08:56.821 EDT [1428121] HINT:  This error usually means that PostgreSQL's request for a shared memory
segmentexceeded available memory, swap space, or huge pages. To reduce the request size (currently 83720568832 bytes),
reducePostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections. 
>
> Of course, if it *does* start, you can do the other thing.

Well, I have to *stop* the existing one first, most likely, otherwise
there won't be enough huge pages (or indeed memory) available. And if
then doesn't start, you're looking at extended downtime.

You can automate this to minimize it (set the value in the conf, stop
old, start new, if new doesn't start then stop new, reconfigure, start
old again), but it's *far* from friendly.

This process works when you're setting up a brand new server with
nobody using it. It doesn't work well, or at all, when you actually
have active users on it..


> Admittedly, we could make that easier somehow; but if it took
> 25 years for somebody to ask for this, I'm not sure it's
> worth creating a feature to make it a shade easier.

We haven't had huge page support for 25 years, "only" since 9.4 so
about 7 years.

And for every year that passes, huge pages become more interesting in
that in general memory sizes increase so the payoff of using them is
increased.

Using huge pages *should* be a trivial improvement to set up. But it's
in my experience complicated enough that many just skip it simply for
that reason.

--
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

Magnus Hagander <magnus@hagander.net> writes:
> On Wed, Jun 9, 2021 at 9:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Just try to start the server and see if it complains.

> Well, I have to *stop* the existing one first, most likely, otherwise
> there won't be enough huge pages (or indeed memory) available.

I'm not following.  If you have a production server running, its
pg_shmem_allocations total should already be a pretty good guide
to what you need to configure HugePages for.  You need to know to
round that up, of course --- but if you aren't building a lot of
slop into the HugePages configuration anyway, you'll get burned
down the road.

            regards, tom lane

On Wed, Jun 9, 2021 at 9:28 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Magnus Hagander <magnus@hagander.net> writes:
> > On Wed, Jun 9, 2021 at 9:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Just try to start the server and see if it complains.
>
> > Well, I have to *stop* the existing one first, most likely, otherwise
> > there won't be enough huge pages (or indeed memory) available.
>
> I'm not following.  If you have a production server running, its
> pg_shmem_allocations total should already be a pretty good guide
> to what you need to configure HugePages for.  You need to know to
> round that up, of course --- but if you aren't building a lot of
> slop into the HugePages configuration anyway, you'll get burned
> down the road.

I'm talking about the case when you want to *change* the value for
shared_buffers (or other parameters that would change the amount of
required huge pages), on a system where you're using huge pages.
pg_shmem_allocations will tell you what you need with the current
value, not what you need with the new value.

But yes, you can do some math around it and make a well educated
guess. But it would be very convenient to have the system able to do
that for you.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/