Обсуждение: measuring shared memory usage on Windows

Hello,

Shridhar Daithankar and Josh Berkus write on
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

shared_memory

"""
There is one way to decide what is best for you. Set a  high value of
this parameter and run the database for  typical usage. Watch usage of
shared memory using ipcs  or similar tools. A recommended figure would
be between  1.2 to 2 times peak shared memory usage.
"""

I tried to find a way to do this on windows. Scanning all the lines of
perfmon memory options, I could not see anithing like "shared memory
usage".

Googling for "shared memory usage" just drove me to some ancient WRONG
information that PostgreSQL is not possible on Windows because of
lacking shared memory. (guess that was for Windows 95 or similiar)

So: has anybody a hint how I can check how much shared_memory is
really used by PostgreSQL on Windows, to fine tune this parameter?

I learned the hard way that just rising it can lead to a hard
performance loss :)

Harald


--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.

> Hello,
>
> Shridhar Daithankar and Josh Berkus write on
> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
>
> shared_memory
>
> """
> There is one way to decide what is best for you. Set a  high
> value of this parameter and run the database for  typical
> usage. Watch usage of shared memory using ipcs  or similar
> tools. A recommended figure would be between  1.2 to 2 times
> peak shared memory usage.
> """
>
> I tried to find a way to do this on windows. Scanning all the
> lines of perfmon memory options, I could not see anithing
> like "shared memory usage".
>
> Googling for "shared memory usage" just drove me to some
> ancient WRONG information that PostgreSQL is not possible on
> Windows because of lacking shared memory. (guess that was for
> Windows 95 or similiar)
>
> So: has anybody a hint how I can check how much shared_memory
> is really used by PostgreSQL on Windows, to fine tune this parameter?
>
> I learned the hard way that just rising it can lead to a hard
> performance loss :)

Not really sure :) We're talking about anonymous mapped memory, and I
don't think perfmon lets you look at that. However, there is no limit to
it as there often is on Unix - you can map up to whatever the virual RAM
size is (2Gb/3Gb dependingo n what boot flag you use, IIRC). You can
monitor it as a part of the total memory useage on the server, but
there's no way to automatically show the difference between them.

//Magnus

>     > So: has anybody a hint how I can check how much shared_memory
>     > is really used by PostgreSQL on Windows, to fine tune
> this parameter?
>     >
>     > I learned the hard way that just rising it can lead to a hard
>     > performance loss :)
>
>     Not really sure :) We're talking about anonymous mapped
> memory, and I
>     don't think perfmon lets you look at that.
>
>
> thanks for the clarification. However,
>
> "anonymous mapped memory"  site:microsoft.com
>
> turns out 0 (zero) results. And even splitting it up there
> seems to be nearly no information ... is the same thing by
> any chance also known by different names?

Hmm. Yeah, most likely :) I may have grabbed that name from something
else. THe documentation for the call is on
http://windowssdk.msdn.microsoft.com/en-us/library/ms685007(VS.80).aspx,
we specifu INVALID_HANDLE_VALUE for hFile, which means:

If hFile is INVALID_HANDLE_VALUE, the calling process must also specify
a mapping object size in the dwMaximumSizeHigh and dwMaximumSizeLow
parameters. In this scenario, CreateFileMapping creates a file mapping
object of a specified size that the operating system paging file backs,
instead of by a named file in the file system.



> > However, there is no limit to it as there often is on Unix
> - you can
> > map up to whatever the virtual RAM size is (2Gb/3Gb
> dependingo n what
> > boot flag you use, IIRC). You can monitor it as a part of the total
> > memory useage on the server, but there's no way to
> automatically show the difference between them.
>
> So the "performance shock" with high shared memory gets
> obvious: memory mapped files get swapped to disk. I assume
> that swapping is nearly transparent for the application,
> leading to a nice trashing ...

Yes :-)
There is a performance manager counter for pages swapped out to disk. If
that one goes above very low numbers, you're in trouble...

//Magnus

Magnus,

> > "anonymous mapped memory"  site:microsoft.com
> > turns out 0 (zero) results. And even splitting it up there
> > seems to be nearly no information ... is the same thing by
> > any chance also known by different names?
>
> Hmm. Yeah, most likely :) I may have grabbed that name from something
> else. THe documentation for the call is on
> http://windowssdk.msdn.microsoft.com/en-us/library/ms685007(VS.80).aspx,
> we specify INVALID_HANDLE_VALUE for hFile, which means:

[...]
CreateFileMapping creates a file mapping object of a specified size
that _the operating system paging file backs_
[...]

I assume that DWORD dwMaximumSizeHigh and  DWORD dwMaximumSizeLow
get filled with whatever I configure in shared_memory?

My reading of that function gives me the impression, that this kind of
shared *memory* is essentially a shared disk file - "_the operating
system paging file backs_"

Especially documentation lines like "If an application specifies a
size for the file mapping object that is larger than the size of the
actual named file on disk, the file on disk is increased to match the
specified size of the file mapping object."

really makes me think that that area is just a comfortable way to
access files on disk as memory areas; with the hope of propably better
caching then not-memory-mapped files.

That would explain my disturbing impressions of performance of
PostgreSQL on win32 rising when lowering shared_memory...

Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.

> > > "anonymous mapped memory"  site:microsoft.com turns out 0 (zero)
> > > results. And even splitting it up there seems to be nearly no
> > > information ... is the same thing by any chance also known by
> > > different names?
> >
> > Hmm. Yeah, most likely :) I may have grabbed that name from
> something
> > else. THe documentation for the call is on
> >
> http://windowssdk.msdn.microsoft.com/en-us/library/ms685007(VS.80).asp
> > x, we specify INVALID_HANDLE_VALUE for hFile, which means:
>
> [...]
> CreateFileMapping creates a file mapping object of a
> specified size that _the operating system paging file backs_ [...]
>
> I assume that DWORD dwMaximumSizeHigh and  DWORD
> dwMaximumSizeLow get filled with whatever I configure in
> shared_memory?

Yes. See the code in src/backend/port/win32 for details ;)


> My reading of that function gives me the impression, that
> this kind of shared *memory* is essentially a shared disk
> file - "_the operating system paging file backs_"

Yes. Note that this does *not* mean that it actually stores anything in
the file. All it means that *if* windows needs to *page out* this data,
it will do so to the pagefile, so the pagefile has to have enough room
for it. With a normal file, it would be paged out to the file instead of
the pagefile. But as long as there is enough free memory around, it will
stay in RAM.

If a specific part of shared memory (the mmaped pagefile) is not
accessed in a long time, it will get swapped out to the pagefile, yes.
And I don't beleive there is a way to make that not happen.


> Especially documentation lines like "If an application
> specifies a size for the file mapping object that is larger
> than the size of the actual named file on disk, the file on
> disk is increased to match the specified size of the file
> mapping object."

This is irrelevant, because we are not mapping a file.


> really makes me think that that area is just a comfortable
> way to access files on disk as memory areas; with the hope of
> propably better caching then not-memory-mapped files.

That shows that you don't really know how the memory manager in NT+
works ;-) *ALL* normal file I/O is handled through the memory manager
:-) So yes, they are both different access methods to the memory
manager, really.


> That would explain my disturbing impressions of performance
> of PostgreSQL on win32 rising when lowering shared_memory...

Not exactly. I can still see such a thing happening in some cases, but
not because all our shared memory actually hit disks. We'd be *dead* on
performance if it did.

//Magnus

> I learned the hard way that just rising it can lead to a hard
> performance loss :)

I looked back in the list archives to try to find your post on the
underlying problem, but could only find this rather terse sentence.
If you have more detailed information please post or point me at it.

But...my first thought is that you have configured the shared memory
region so large that the system as a whole can not fit all the working set
sizes for all running processes in to physical memory. This is a common
pitfall for databases with caches implemented as mapped shared
user space regions (which is basically all databases).

For example, if you have 1G of RAM on the box, you can't
configure a cache of 900 meg and expect things to work well.
This is because the OS and associated other stuff running on
the box will use ~300megs. The system will page as a result.

The only sure fire way I know of to find the absolute maximum
cache size that can be safely configured is to experiment with
larger and larger sizes until paging occurs, then back off a bit.

> really makes me think that that area is just a comfortable way to
> access files on disk as memory areas; with the hope of propably better
> caching then not-memory-mapped files.

No, absolutely not. CreateFileMaping() does much the same thing
as mmap() in Unix.

> That would explain my disturbing impressions of performance of
> PostgreSQL on win32 rising when lowering shared_memory...

I don't know what your disturbing impressions are, but no it
doesn't explain them.

David,

> For example, if you have 1G of RAM on the box, you can't
> configure a cache of 900 meg and expect things to work well.
> This is because the OS and associated other stuff running on
> the box will use ~300megs. The system will page as a result.

Overcommitting of memory leads to trashing, yes, that is also my experience.

> The only sure fire way I know of to find the absolute maximum
> cache size that can be safely configured is to experiment with
> larger and larger sizes until paging occurs, then back off a bit.

Yeah, I know the trial and error method. But I also learned that
reading the manuals and documentation often helps.

So after fastreading the various PostgreSQL tuning materials, I came
accross formulas to calculate a fine starting point for shared memory
size; and the recommendation to check with shared_memory information
tools if that size is okay.

And THAT is exactly the challenge of this thread: I am searching for
tools to check shared memory usage on Windows. ipcs is not available.
And neither Magnus nor Dave, both main contributors of the win32 port
of PostgreSQL, and both way wiser concerning Windows internas then me,
know of some :(

The challenge below that: I maintain a win32 PostgreSQL server, which
gets slow every 3-4 weeks. After restarting it runs perfect, for again
3-4 weeks.

The Oracle-guys at the same customer solved a similiar problem by
simply restarting Oracle every night. But that would be not good
enough for my sence of honour :)

Thanks for your thoughts,

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.

Magnus,


> That shows that you don't really know how the memory manager in NT+
> works ;-) *ALL* normal file I/O is handled through the memory manager
> :-) So yes, they are both different access methods to the memory
> manager, really.

"don't really" is a overstatement, I do not know at all how the memory
manager works in NT+. All I learned is "Inside Windows NT" of H.
Custer from 1993 :)

So, just to make sure I understood correctly:

If PostgreSQL reads a file  from disk, Windows NT does this file I/O
though the same memory manager than when PostgreSQL puts parts of this
read file [for example an index segment] into shared memory - which is
nothing but a file, that usually stays in main memory.

Correct so far?

I continued from this thoughts:

lets say there is 500MB memory available, we have 100MB of
shared_memory configured.
Now PostgreSQL reads 100MB from a file - memory manager takes 100MB
memory to fullfill this file access (optimizations aside)

Now PostgreSQL reshuffles that 100MB and decides: "hmmmm, that may be
valuable for ALL of the currently running postgres.exe" and pushes
those 100MB into shared memory for all to use. It caches the 100MB - a
fine chunk of an index.

From this kind of understanding, memory manager has 200MB in use: the
100MB from the file read, and the 100MB of shared memory.

Of course the 100MB of the file in memory manager will get flushed soon.

Now, lets restrict PostgreSQL: I only give the minimum amout of shared
memory. It will NOT cache those 100MB in shared memory.

But: PostgreSQL really was correct. The other 20 postgres.exe access
the same file on a regular basis. Won't memory manager keep that file
"cached" in RAM anyway?

I try my theories :)) and contrary to all wisdom from all PostgreSQL
tuning recommendations reconfigured shared memory nearly to the
minimum: 1000 for maximum of 400 concurrent connections. (800 would be
minimum). Single user performance was fine, now I am looking forward
to full user scenario tomorrow.

I will keep you posted.

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.

Harald Armin Massa wrote:
>
> Yeah, I know the trial and error method. But I also learned that
> reading the manuals and documentation often helps.
>
> So after fastreading the various PostgreSQL tuning materials, I came
> accross formulas to calculate a fine starting point for shared memory
> size; and the recommendation to check with shared_memory information
> tools if that size is okay.
>
> And THAT is exactly the challenge of this thread: I am searching for
> tools to check shared memory usage on Windows. ipcs is not available.
> And neither Magnus nor Dave, both main contributors of the win32 port
> of PostgreSQL, and both way wiser concerning Windows internas then me,
> know of some :(
>
>

Would it help to have the postmaster report what the shared memory
allocation is when it starts up? (this won't help with the activity
stuff, but at least you would know for sure how *much* you have to use).

If I understand this correctly,

ShmemSegHdr->totalsize

is what we have allocated, so making the startup section of bootstrap.c
elog(LOG) could work.

Cheers

Mark

Unfortunately often operating system virtual memory
and filesystem caching code that does exactly the opposite of
what a database application would like.
For some reason the kernel guys don't see it that way ;)

Over the years there have been various kernel features added
with the overall goal of solving problems in this area : O_DIRECT,
ckrm, flags to mmap() and so on. So far I'm not sure any of
them has really succeeded. Hence the real-world wisdom to
'let the filesystem cache do its thing' and configure a small
shared memory cache in the application.

Ideally one would want to use O_DIRECT or equivalent
to bypass the OS's cleverness and manage the filesystem
caching oneself. However it turns out that enabling O_DIRECT
makes things much worse not better (YMMV obviously).
It's hard to achieve the level of concurrency that the kernel
can get for disk I/O, from user mode.

Another approach is to make the application cache size
dynamic, with the goal that it can grow and shrink to
reach the size that provides the best overall performance.
I've seen attempts to drive the sizing using memory access
latency measurements done from user mode inside the application.
However I'm not sure that anyone has taken this approach
beyond the science project stage.

So AFAIK this is still a generally unsolved problem.

NT (Windows) is particularly interesting because it drives the
filesystem cache sizing with a signal that it mesures from the
VM pages evicted per second counter. In order to keep its
feedback loop stable, the OS wants to see a non-zero value
for this signal at all times. So you will see that even under
ideal conditions the system will still page a little.
(Unless that code has changed in Win2003 -- it's been a
while since I checked). So don't drive yourself crazy trying
to get it to stop paging ;)