Thread: PostreSQL v9.2 uses a lot of memory in Windows XP

PostreSQL v9.2 uses a lot of memory in Windows XP

From
Wu Ming
Date:
Hi,

I had installed postgreSQL v9.2 in Windows XP SP3.

My PC specs:
Processor: Pentium Dual Core 2.09 GHz
RAM: 2GB

The postgreSQL is run as windows service (manual).

The problem is the postgreSQL service uses a lot of memory and lags
the OS if running in long time (about 2 hours or more) so I had to
restart the postgreSQL service everytime it happened. I never do any
big querying process so far. I only ever run it for adempiere ERP
software and a small struts 2 project.

See this screenshot link from the Process Explorer:

http://i45.tinypic.com/vr4t3b.png

You can see that there are a lot of threads spawned. Is the threads
that caused the high memory usage?

Is there a way to decrease the memory usage?


Thanks & Regards,
WM


Re: PostreSQL v9.2 uses a lot of memory in Windows XP

From
"Albe Laurenz"
Date:
Wu Ming wrote:
> I had installed postgreSQL v9.2 in Windows XP SP3.
>
> My PC specs:
> Processor: Pentium Dual Core 2.09 GHz
> RAM: 2GB
>
> The postgreSQL is run as windows service (manual).
>
> The problem is the postgreSQL service uses a lot of memory and lags
> the OS if running in long time (about 2 hours or more) so I had to
> restart the postgreSQL service everytime it happened. I never do any
> big querying process so far. I only ever run it for adempiere ERP
> software and a small struts 2 project.
>
> See this screenshot link from the Process Explorer:
>
> http://i45.tinypic.com/vr4t3b.png
>
> You can see that there are a lot of threads spawned. Is the threads
> that caused the high memory usage?
>
> Is there a way to decrease the memory usage?

Is the machine dedicated to PostgreSQL?

What did you set the following patameters to:

shared_buffers
max_connections
work_mem
maintenance_work_mem

You probably need to reduce some of these settings.

Yours,
Laurenz Albe


Re: PostreSQL v9.2 uses a lot of memory in Windows XP

From
Merlin Moncure
Date:
On Mon, Nov 12, 2012 at 8:17 AM, Wu Ming <rdyf4ever@gmail.com> wrote:
> Hi,
>
> I had installed postgreSQL v9.2 in Windows XP SP3.
>
> My PC specs:
> Processor: Pentium Dual Core 2.09 GHz
> RAM: 2GB
>
> The postgreSQL is run as windows service (manual).
>
> The problem is the postgreSQL service uses a lot of memory and lags
> the OS if running in long time (about 2 hours or more) so I had to
> restart the postgreSQL service everytime it happened. I never do any
> big querying process so far. I only ever run it for adempiere ERP
> software and a small struts 2 project.
>
> See this screenshot link from the Process Explorer:
>
> http://i45.tinypic.com/vr4t3b.png
>
> You can see that there are a lot of threads spawned. Is the threads
> that caused the high memory usage?
>
> Is there a way to decrease the memory usage?

I don't think memory usage is all that high.  You've got less than
50mb reserved in memory which is not outrageous for a database server
(that said, windows per process memory usage is higher than *nix for
various reasons).  High virtual memory sizes are  due to shared memory
implementation and should not be of large concern.  Albe made some
good suggestions, but you can also disable autovacuum which would
eliminate one of the spawned processes at the expense of making all
vacuum and analyze operations manual. You used to also be able to
disable statistics gathering, but AIUI that's no longer possible.

merlin


Re: PostreSQL v9.2 uses a lot of memory in Windows XP

From
Craig Ringer
Date:
On 11/12/2012 10:17 PM, Wu Ming wrote:
> See this screenshot link from the Process Explorer:
>
> http://i45.tinypic.com/vr4t3b.png
That looks pretty reasonable to me.

The "virtual size" includes the shared memory segment, so the
per-process use is actually much lower than it looks. The real use will
be closer to one of the virtual sizes plus the working sets of all the
rest of the processes. They are processes, not threads.

There may be a genuine issue here, but it isn't demonstrated by the
screenshot.

How do you determine that it's "lagging"? What's the overall system
memory pressure like? Check Task Manager. What's the system's swap
usage? Are there other big processes?

> You can see that there are a lot of threads spawned. Is the threads
> that caused the high memory usage?
PostgreSQL has a process-based architecture. They're processes not
threads. Each process only uses a fairly small amount of memory - the
exact amount depends on settings like work_mem and what the queries
running are doing, but it's usually not much. Most of the apparent use
is shared memory.

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



Re: PostreSQL v9.2 uses a lot of memory in Windows XP

From
Craig Ringer
Date:
Please reply to the list, not directly to me. Comments follow in-line.

On 11/13/2012 11:37 PM, Wu Ming wrote:
> Hi,
>
> What column in Process Explorer to determine memory usage? Currently I
> thought "Working Set" is the correct one.
As I said, it just isn't that simple when shared memory is involved. A
rough measure for PostgreSQL is the "virtual size" of one of the
processes, plus the working sets of all the others. Alternately, you can
reasonably estimate the memory consumption by adding all the working
sets and then adding the value of shared_buffers to that - this will
under-estimate usage slightly because PostgreSQL also uses shared memory
for other things, but not tons of it in a normal configuration.
> The 'lagging' is like when you try to alt+tab or activating/focusing
> other application window, or changing tab in browser, it goes slow or
> lagged in its UI loading.
Sure, that's what you see, but you should really be looking at the
numbers. Swap in and out bytes, memory usage, etc. In Windows 7 or
Win2k8 Server you'd use the Performance Monitor for that; I don't
remember off the top of my head where to look in XP.
> My firefox has many tabs opened (around 30 tabs) and eclipse is well
> known as its high memory usage.
On a 2GB machine? Yup, that'll do it.

You've shown a screenshot that suggests that Pg is using relatively
little RAM, and you're running two known memory pigs. I'd say your
problem has nothing to do with PostgreSQL.
> Then usually I also opened opera and
> chrome with ~10-20 tabs opened.
Time to buy more RAM.
> I saw that chrome also spawned many
> process (I had 4 tabs opened, but it shows 8 child process). They
> might be the big process that probably is the main cause of the
> lagging.
It's going to be everything adding up. Chrome, Eclipse, Firefox, all
fighting for RAM.

BTW, chrome uses a multi-process architecture like PostgreSQL, but
unlike PostgreSQL it does not use shared memory, so you can tell how
much RAM Chrome is using very easily by adding up the working sets.

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



Re: PostreSQL v9.2 uses a lot of memory in Windows XP

From
Wu Ming
Date:
Hi,

> As I said, it just isn't that simple when shared memory is involved. A
> rough measure for PostgreSQL is the "virtual size" of one of the
> processes, plus the working sets of all the others. Alternately, you can
> reasonably estimate the memory consumption by adding all the working
> sets and then adding the value of shared_buffers to that - this will
> under-estimate usage slightly because PostgreSQL also uses shared memory
> for other things, but not tons of it in a normal configuration.

This is interesting. About the "virtual size of one of the process",
which process I should look up? Is the one who has the biggest virtual
size?

http://i45.tinypic.com/vr4t3b.png

For example, from the above screenshot, the biggest virtual size from
all postgresql process is 740004. So can we said the total approximate
of memory usage of the postgresql service is 740004 K +
total_of_working_sets (4844 K + 10056 K + 5408 K + 6020 K + ...) ?


> Sure, that's what you see, but you should really be looking at the
> numbers. Swap in and out bytes, memory usage, etc. In Windows 7 or
> Win2k8 Server you'd use the Performance Monitor for that; I don't
> remember off the top of my head where to look in XP.

I had total paging file size = 3GB.

There is perfmon.exe in windows xp, but don't know how to use and
analyze the graph.


> I'd say your problem has nothing to do with PostgreSQL.

Maybe you're right. If I close one of the memory porks, it gets a bit
better. Maybe I was too quick to blame postgreSQL, it's just that I
can't close and restart other applications because they are either too
important or slow to reload, where postgresql service is very quick in
restarting. I hope it'll understand.



On Wed, Nov 14, 2012 at 6:07 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
> Please reply to the list, not directly to me. Comments follow in-line.
>
> On 11/13/2012 11:37 PM, Wu Ming wrote:
>> Hi,
>>
>> What column in Process Explorer to determine memory usage? Currently I
>> thought "Working Set" is the correct one.
> As I said, it just isn't that simple when shared memory is involved. A
> rough measure for PostgreSQL is the "virtual size" of one of the
> processes, plus the working sets of all the others. Alternately, you can
> reasonably estimate the memory consumption by adding all the working
> sets and then adding the value of shared_buffers to that - this will
> under-estimate usage slightly because PostgreSQL also uses shared memory
> for other things, but not tons of it in a normal configuration.
>> The 'lagging' is like when you try to alt+tab or activating/focusing
>> other application window, or changing tab in browser, it goes slow or
>> lagged in its UI loading.
> Sure, that's what you see, but you should really be looking at the
> numbers. Swap in and out bytes, memory usage, etc. In Windows 7 or
> Win2k8 Server you'd use the Performance Monitor for that; I don't
> remember off the top of my head where to look in XP.
>> My firefox has many tabs opened (around 30 tabs) and eclipse is well
>> known as its high memory usage.
> On a 2GB machine? Yup, that'll do it.
>
> You've shown a screenshot that suggests that Pg is using relatively
> little RAM, and you're running two known memory pigs. I'd say your
> problem has nothing to do with PostgreSQL.
>> Then usually I also opened opera and
>> chrome with ~10-20 tabs opened.
> Time to buy more RAM.
>> I saw that chrome also spawned many
>> process (I had 4 tabs opened, but it shows 8 child process). They
>> might be the big process that probably is the main cause of the
>> lagging.
> It's going to be everything adding up. Chrome, Eclipse, Firefox, all
> fighting for RAM.
>
> BTW, chrome uses a multi-process architecture like PostgreSQL, but
> unlike PostgreSQL it does not use shared memory, so you can tell how
> much RAM Chrome is using very easily by adding up the working sets.
>
> --
>  Craig Ringer                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>


Re: PostreSQL v9.2 uses a lot of memory in Windows XP

From
Craig Ringer
Date:
On 11/14/2012 01:56 PM, Wu Ming wrote:
This is interesting. About the "virtual size of one of the process",
which process I should look up? Is the one who has the biggest virtual
size?

Thinking about this some more, I haven't checked to see if Windows adds dirtied shared_buffers to the process's working set. If so, you'd still be multiply counting shared memory. In that case, since you can't use an approach like Depesz writes about here for Linux:

http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/

then it's going to be pretty hard to actually work it out. pg_buffercache might be of some help ( http://www.postgresql.org/docs/current/static/pgbuffercache.html) but it's not exactly friendly.

Yes, it's absurd that it's so hard to work out how much memory Pg uses. It'd be nice if Pg provided better tools for this by allowing the postmaster to interrogate backends' memory contexts, though that'd only report how much memory Pg thought it was using, not how much memory it was actually using from the OS. Really, OS-specific tools are required, and nobody's written them - at least, I'm not aware of any that've been published.

Most of the problem is that operating systems make it so hard to tell where memory is going when shared memory is involved.

http://i45.tinypic.com/vr4t3b.png

For example, from the above screenshot, the biggest virtual size from
all postgresql process is 740004. So can we said the total approximate
of memory usage of the postgresql service is 740004 K +
total_of_working_sets (4844 K + 10056 K + 5408 K + 6020 K + ...) ?
*if* Windows XP doesn't add dirtied shared buffers to the working set, then that would be a reasonable approximation.

If it does, then it'd be massively out because it'd be double-counting shared memory.

Off the top of my head I'm not sure how best to test this. Maybe if you do a simple query like `SELECT * FROM some_big_table` in `psql` and dump the result to the null device (\o NUL in windows if I recall correctly, but again not tested) or a temp file and see how much the backend grows. If it grows more than a few hundred K then I expect it's probably having the dirtied shared_buffers counted against it.
Maybe you're right. If I close one of the memory porks, it gets a bit
better. Maybe I was too quick to blame postgreSQL, it's just that I
can't close and restart other applications because they are either too
important or slow to reload, where postgresql service is very quick in
restarting. I hope it'll understand.
And, of course, because PostgreSQL looks like it uses a TON of memory, even when it's really using only a small amount.

This has been an ongoing source of confusion, but it's one that isn't going to go away until OSes offer a way to easily ask "how much RAM is this group of processes using in total".
-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

Re: PostreSQL v9.2 uses a lot of memory in Windows XP

From
Peter Geoghegan
Date:
On 14 November 2012 06:47, Craig Ringer <craig@2ndquadrant.com> wrote:
> Yes, it's absurd that it's so hard to work out how much memory Pg uses. It'd
> be nice if Pg provided better tools for this by allowing the postmaster to
> interrogate backends' memory contexts, though that'd only report how much
> memory Pg thought it was using, not how much memory it was actually using
> from the OS. Really, OS-specific tools are required, and nobody's written
> them - at least, I'm not aware of any that've been published.

I wrote a GDB Python script that interrogates a running backend about
memory context information, walking a tree of contexts, which is based
almost entirely on standard infrastructure used by
MemoryContextStats(). It's quite possible. You're quite right to say
that OS-specific tools would probably do a more satisfactory job,
though, particularly if you're not interested in *what* Postgres is
doing with memory, but need to summarise it usefully.

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services