Обсуждение: PostreSQL v9.2 uses a lot of memory in Windows XP
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
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
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
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
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
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 >
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.
*if* Windows XP doesn't add dirtied shared buffers to the working set, then that would be a reasonable approximation.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 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.
And, of course, because PostgreSQL looks like it uses a TON of memory, even when it's really using only a small amount.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.
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
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