Thread: 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
Magnus,
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?
> However, there is no limit to it as there often is on Unix - you can map up to whatever the virtual RAM
--
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.
> 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?
> 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 ...
I'll keep on searching...
Harald
> 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 ...
I'll keep on searching...
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.
> > 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
Mark,
That would be of no use ... I am quite sure that PostgreSQL allocates the memory as specified; that is: as much as is written in postgresql.conf.
The interesting part is usage of shared memory through the workload.
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.
> 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).
The interesting part is usage of shared memory through the workload.
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.
Performance readers ....
I follow up my promise:
I went even further down,
max_connections = 200 #
shared_buffers = 400 # min 16 or max_connections*2, 8KB each
to the minimum allowed value of shared_buffers. And the response times are better then ever before (with 10.000, 20.000, 5.000 and 40.000 shared_buffers):
An application-level response dropped from 16 / 9.5 seconds (first run / cached run) to 12 / 6.5 average runtime. That response time is mainly dependend on SQL performance, and esp. the drop is caused by this change.
Moreover, the columns "swapped out memory" in task manager stay low at ~26k per postgres.exe, compared to ~106k as my shared_buffers where at 10.000.
The "memory" column of postgres.exe in task manager process still grows up to > 10.000K, while now "virtual memory" stays ~3.600k per process
So: in this configuration / workload:
-> windows 2k3
-> small (~0,4GB) Database
-> rather complex queries
-> ~1 GB memory
-> running in virtual machine
and
-> windows xp
-> small (~0,4GB) Database
-> ~0,5 GB memory
-> rather complex queries
-> running native (laptops)
I could verify a substantial speed gain in single and many user situations by lowering shared_buffers to the allowed minimum.
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.
I follow up my promise:
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
I went even further down,
max_connections = 200 #
shared_buffers = 400 # min 16 or max_connections*2, 8KB each
An application-level response dropped from 16 / 9.5 seconds (first run / cached run) to 12 / 6.5 average runtime. That response time is mainly dependend on SQL performance, and esp. the drop is caused by this change.
Moreover, the columns "swapped out memory" in task manager stay low at ~26k per postgres.exe, compared to ~106k as my shared_buffers where at 10.000.
The "memory" column of postgres.exe in task manager process still grows up to > 10.000K, while now "virtual memory" stays ~3.600k per process
So: in this configuration / workload:
-> windows 2k3
-> small (~0,4GB) Database
-> rather complex queries
-> ~1 GB memory
-> running in virtual machine
and
-> windows xp
-> small (~0,4GB) Database
-> ~0,5 GB memory
-> rather complex queries
-> running native (laptops)
I could verify a substantial speed gain in single and many user situations by lowering shared_buffers to the allowed minimum.
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.
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 ;)