Обсуждение: Performance tuning on RedHat Enterprise Linux 3
Executive summary: We just did a cutover from a RedHat 8.0 box to a RedHat Enterprise Linux 3 box and we're seeing a lot more swapping on the new box than we ever did on the old box ... this is killing performance ... Background: Old Box: RedHat 8.0 2GB Memory Dual PIII 600MHz Postgres 7.3.4 SHMMAX = 1073741824 (1 GB) shared_buffers = 65536 (roughly 0.5 GB) max_fsm_relations = 1000 max_fsm_pages = 1000000 vacuum_mem = 131072 Roughly 25 - 30 connections open (mostly idle) at any given time (connection pools) New Box: RedHat Enterprise Linux ES 3 2GB Memory Dual P4 Xeon 2.7 GHz Postgres 7.3.4 SHMMAX = 1610612736 (1.5 GB) shared_buffers = 131072 (roughly 1GB) max_fsm_relations = 10000 max_fsm_pages = 10000000 sort_mem = 4096 vacuum_mem = 262144 Roughly 25 - 30 connections open (mostly idle) at any given time (connection pools) Both boxes are dedicated DB servers ... With the new configuration, we were seeing swap rates of 1000-5000 KB/s (according to vmstat) ... with the old configuration, we never saw any swapping ... I turned the shared_buffers and sort_mem down on the new box to match the settings of the old box and found that it reduced the swapping significantly (roughly 0-2000 KB/s) but didn't eliminate it completely ... when looking at 'top' on the new box, the list of postgres processes all seem to be indicating a different amount of memory usage ... under the periods of heavy swapping, one or more of the postgres processes would be way up there (between 500MB and 1000MB (which would easily explain the swapping)) ... the question is: why aren't all of the processes sharing the same pool of shared memory since I thought that's what I'm doing when adjusting the shared_buffers property? Here's an example of my 'top' (not under heavy load) demonstrating the different memory usage by each postgres process ... I unfortunately don't have the same snapshot data from the old configuration, but I seem to recall that all of the postgres processes had PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 16966 postgres 15 0 107M 107M 105M S 0.0 5.3 0:39 1 postmaster 20198 postgres 15 0 40448 39M 37752 S 0.2 1.9 0:07 0 postmaster 18801 postgres 15 0 21932 21M 19616 S 0.0 1.0 0:01 0 postmaster 19210 postgres 16 0 21276 20M 19008 S 0.0 1.0 0:01 0 postmaster 19507 postgres 15 0 15504 14M 13580 S 0.0 0.7 0:00 3 postmaster 20308 postgres 15 0 12832 12M 11248 S 0.0 0.6 0:00 3 postmaster 20456 postgres 15 0 12500 12M 10920 S 0.0 0.6 0:00 1 postmaster 20403 postgres 15 0 11572 11M 9928 S 0.2 0.5 0:00 1 postmaster 20251 postgres 15 0 10796 10M 9260 S 0.0 0.5 0:00 0 postmaster 20398 postgres 15 0 10792 10M 9256 S 0.0 0.5 0:00 2 postmaster 20306 postgres 21 0 9100 8808 7796 S 0.0 0.4 0:00 1 postmaster 20425 postgres 16 0 9100 8808 7796 S 0.0 0.4 0:00 0 postmaster 20360 postgres 15 0 9096 8804 7792 S 0.0 0.4 0:00 3 postmaster 20383 postgres 21 0 9096 8804 7792 S 0.0 0.4 0:00 0 postmaster 20434 postgres 21 0 9096 8804 7792 S 0.0 0.4 0:00 1 postmaster 20305 postgres 15 0 9108 8796 7804 S 0.0 0.4 0:00 2 postmaster Can anyone think of a reason as to why I'm seeing such heavy swapping? According to Bruce Momjian's performance tuning guide, he recommends roughly half the amount of physical RAM for the shared_buffers ... I tried turning UP the shared_buffers even higher (to 180,000 i believe; roughly 1.5GB) and that seemed to make the problem even worse ... Thanks in advance, Dave
On Mon, Dec 06, 2004 at 09:08:02AM -0500, David Esposito wrote: > shared_buffers = 131072 (roughly 1GB) > max_fsm_relations = 10000 > max_fsm_pages = 10000000 > sort_mem = 4096 > vacuum_mem = 262144 > Roughly 25 - 30 connections open (mostly idle) at any given time > (connection pools) I'd suggest reducing shared_buffers to maybe a few thousand, there's really no point reserving so much memory that way, it just a waste. Secondly, up your sort_mem a bit to reflact how big your sorts are likely to be. How's your effective_cache_size? -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Вложения
"David Esposito" <pgsql-general@esposito.newnetco.com> writes: > New Box: > shared_buffers = 131072 (roughly 1GB) This setting is an order of magnitude too large. There is hardly any evidence that it's worth setting shared_buffers much above 10000. regards, tom lane
On Mon, Dec 06, 2004 at 09:08:02AM -0500, David Esposito wrote: > According to Bruce Momjian's performance tuning guide, he recommends roughly > half the amount of physical RAM for the shared_buffers ... Does he? The guide I've seen from him AFAIR states that you should allocate around 10% of physical RAM to shared_buffers. And this advice goes against common Postgres folklore. Maybe it's a document that needs to be updated. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "There is evil in the world. There are dark, awful things. Occasionally, we get a glimpse of them. But there are dark corners; horrors almost impossible to imagine... even in our worst nightmares." (Van Helsing, Dracula A.D. 1972)
Thanks for the replies guys ... The copy of Bruce's book I was reading is at: http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/node8.html and I was mistaken, it recommends 25% of physical memory be allocated to the shared cache .. Is there a better resource (even a commercial publication) that I should've been looking through? Bruce's book is a little too high-level and obviously leaves out some of the detail about the fact that there is a practical maximum ... I will crank my shared_buffers down ... But how do I know what my sort_mem setting should be? Are there statistics tables that indicate cache hits/misses like in Oracle? Lastly, about the effective_cache_size ... If I cut down my shared buffers to 10,000 like Tom recommended, and I assume that the OS itself and some overhead for the sort_mem and vacuum mem takes up about 512MB total, should I set the effective_cache_size to assume that the remaining 1.5 GB of physical memory is being allocated for the file cache by the kernel? Thanks, Dave > -----Original Message----- > From: Martijn van Oosterhout [mailto:kleptog@svana.org] > Sent: Monday, December 06, 2004 10:39 AM > To: David Esposito > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3 > > On Mon, Dec 06, 2004 at 09:08:02AM -0500, David Esposito wrote: > > shared_buffers = 131072 (roughly 1GB) > > max_fsm_relations = 10000 > > max_fsm_pages = 10000000 > > sort_mem = 4096 > > vacuum_mem = 262144 > > Roughly 25 - 30 connections open (mostly idle) at any given time > > (connection pools) > > I'd suggest reducing shared_buffers to maybe a few thousand, there's > really no point reserving so much memory that way, it just a waste. > > Secondly, up your sort_mem a bit to reflact how big your sorts are > likely to be. > > How's your effective_cache_size? > -- > Martijn van Oosterhout <kleptog@svana.org> > http://svana.org/kleptog/ > > Patent. n. Genius is 5% inspiration and 95% perspiration. A > patent is a > > tool for doing 5% of the work and then sitting around > waiting for someone > > else to do the other 95% so you can sue them. >
<snip> > ... under the periods of heavy swapping, one or more of the postgres >processes would be way up there (between 500MB and 1000MB (which would >easily explain the swapping)) ... the question is: why aren't all of the >processes sharing the same pool of shared memory since I thought that's what >I'm doing when adjusting the shared_buffers property? > > <snip> I seem to remember hearing that the memory limit on certain operations, such as sorts, is not "enforced" (may the hackers correct me if I am wrong); rather, the planner estimates how much a sort might take by looking at the statistics for a table. If the statistics are wrong, however, the sort doesn't actually stay within sort memory, and so the process consumes a very large amount of memory, much more than the sort_mem configuration parameter should allow it to. If the other suggestions given (to reduce the shared buffers) don't fix it, I suggest running ANALYZE all your tables and see if the erratic memory usage goes away. If that doesn't help, then try to figure out what query is causing the high memory usage, and run EXPLAIN ANALYZE on just that query to see if it is returning drastically more rows than the planner thinks it will. Paul Tillotson
On Mon, 2004-12-06 at 19:37 -0500, Paul Tillotson wrote: > I seem to remember hearing that the memory limit on certain operations, > such as sorts, is not "enforced" (may the hackers correct me if I am > wrong); rather, the planner estimates how much a sort might take by > looking at the statistics for a table. > > If the statistics are wrong, however, the sort doesn't actually stay > within sort memory, and so the process consumes a very large amount of > memory, much more than the sort_mem configuration parameter should allow > it to. AFAIK this is not the case. sort_mem defines the in-memory buffer used _per_ sort operation. The problem you may be referring to is that multiple concurrent sort operations (possibly within a single backend) will each consume up to sort_mem, so the aggregate memory usage for sort operations may be significantly higher than sort_mem. -Neil
On Tue, Dec 07, 2004 at 12:02:13PM +1100, Neil Conway wrote: > On Mon, 2004-12-06 at 19:37 -0500, Paul Tillotson wrote: > > I seem to remember hearing that the memory limit on certain operations, > > such as sorts, is not "enforced" (may the hackers correct me if I am > > wrong); rather, the planner estimates how much a sort might take by > > looking at the statistics for a table. > AFAIK this is not the case. AFAIK this is indeed the case with hashed aggregation, which uses the sort_mem (work_mem) parameter to control its operation, but for which it is not a hard limit. I concur however that multiple concurrent sorts may consume more memory than the limit specified for one sort. (Just last week I saw a server running with sort_mem set to 800 MB ... no wonder the server went belly up every day at 3.00am, exactly when a lot of reports were being generated) -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Acepta los honores y aplausos y perderás tu libertad"
On Mon, 2004-12-06 at 22:19 -0300, Alvaro Herrera wrote: > AFAIK this is indeed the case with hashed aggregation, which uses the > sort_mem (work_mem) parameter to control its operation, but for which it > is not a hard limit. Hmmm -- I knew we didn't implement disk-spilling for hashed aggregation, but I thought we had _some_ sane means to avoid consuming a lot of memory if we got the plan completely wrong. AFAICS you are right, and this is not the case :-( We definitely ought to fix this. -Neil
Alvaro Herrera wrote: >On Tue, Dec 07, 2004 at 12:02:13PM +1100, Neil Conway wrote: > > >>On Mon, 2004-12-06 at 19:37 -0500, Paul Tillotson wrote: >> >> >>>I seem to remember hearing that the memory limit on certain operations, >>>such as sorts, is not "enforced" (may the hackers correct me if I am >>>wrong); rather, the planner estimates how much a sort might take by >>>looking at the statistics for a table. >>> >>> > > > >>AFAIK this is not the case. >> >> > >AFAIK this is indeed the case with hashed aggregation, which uses the >sort_mem (work_mem) parameter to control its operation, but for which it >is not a hard limit. > >I concur however that multiple concurrent sorts may consume more memory >than the limit specified for one sort. (Just last week I saw a server >running with sort_mem set to 800 MB ... no wonder the server went belly >up every day at 3.00am, exactly when a lot of reports were being >generated) > > Does postgres actually do multiple concurrent sorts within a single backend? I didn't think it would ever do this, since each backend has only a single thread. David says that he sees a particular process start to consume very large amounts of memory, and from my understanding of postgres, this must be one single query taking a lot of memory, not "multiple concurrent sorts." Paul Tillotson
Neil Conway <neilc@samurai.com> writes: > On Mon, 2004-12-06 at 22:19 -0300, Alvaro Herrera wrote: >> AFAIK this is indeed the case with hashed aggregation, which uses the >> sort_mem (work_mem) parameter to control its operation, but for which it >> is not a hard limit. > Hmmm -- I knew we didn't implement disk-spilling for hashed aggregation, > but I thought we had _some_ sane means to avoid consuming a lot of > memory if we got the plan completely wrong. The *sort* code is fairly good about respecting sort_mem. The *hash* code is not so good. > We definitely ought to fix this. Bear in mind that the price of honoring sort_mem carefully is considerably far from zero. (Or, if you know how to do it cheaply, let's see it ...) The issue with the hash code is that it sets size parameters on the basis of the estimated input row count; the memory usage error factor is basically inversely proportional to the error in the planner's row estimate. The seriously bad cases I've seen reported were directly due to horribly-out-of-date planner table size estimates. A large part of the rationale for applying that last-minute 8.0 change in relpages/ reltuples handling was to try to suppress the worst cases in hashtable size estimation. regards, tom lane
Paul Tillotson <pntil@shentel.net> writes: > Does postgres actually do multiple concurrent sorts within a single > backend? Certainly. Consider for example a merge join with each input being sorted by an explicit sort step. DISTINCT, ORDER BY, UNION, and related operators require their own sort steps in the current implementation. It's not difficult to invent queries that require arbitrarily large numbers of sort steps. regards, tom lane
On Mon, 2004-12-06 at 23:55 -0500, Tom Lane wrote: > Bear in mind that the price of honoring sort_mem carefully is > considerably far from zero. I'll do some thinking about disk-based spilling for hashed aggregation for 8.1 > The issue with the hash code is that it sets size parameters on the > basis of the estimated input row count; the memory usage error factor > is basically inversely proportional to the error in the planner's row > estimate. Right. But I don't think it's acceptable to consume an arbitrary amount of memory to process a query, even if we only do that when the planner makes a mistake (regrettably, planner mistakes occur with some regularity). As a quick hack, what about throwing away the constructed hash table and switching to hashing for sorting if we exceed sort_mem by a significant factor? (say, 200%) We might also want to print a warning message to the logs. This assumes that aggregation-by-sorting can be used in a superset of the cases where aggregation-by-hashing can be used, and that the semantics of both nodes are the same; I believe both conditions hold. And of course, performance will likely suck -- but (a) since the planner has guessed wrong performance is probably going to suck anyway (b) it is better than running the machine OOM. -Neil
Neil Conway <neilc@samurai.com> writes: > As a quick hack, what about throwing away the constructed hash table and > switching to hashing for sorting if we exceed sort_mem by a significant > factor? (say, 200%) We might also want to print a warning message to the > logs. If I thought that a 200% error in memory usage were cause for a Chinese fire drill, then I'd say "yeah, let's do that". The problem is that the place where performance actually goes into the toilet is normally an order of magnitude or two above the nominal sort_mem setting (for obvious reasons: admins can't afford to push the envelope on sort_mem because of the various unpredictable multiples that may apply). So switching to a hugely more expensive implementation as soon as we exceed some arbitrary limit is likely to be a net loss not a win. If you can think of a spill methodology that has a gentle degradation curve, then I'm all for that. But I doubt there are any quick-hack improvements to be had here. regards, tom lane
There are many reports of kernel problems with memory allocation (too agressive) and swap issues with RHEL 3.0 on both RAID and non-RAID systems. I hope folks have worked through all those issues before blaming postgresql. Tom Lane wrote: > > If I thought that a 200% error in memory usage were cause for a Chinese > fire drill, then I'd say "yeah, let's do that". The problem is that the > place where performance actually goes into the toilet is normally an > order of magnitude or two above the nominal sort_mem setting (for > obvious reasons: admins can't afford to push the envelope on sort_mem > because of the various unpredictable multiples that may apply). So > switching to a hugely more expensive implementation as soon as we exceed > some arbitrary limit is likely to be a net loss not a win. > > If you can think of a spill methodology that has a gentle degradation > curve, then I'm all for that. But I doubt there are any quick-hack > improvements to be had here. > > regards, tom lane -- P. J. "Josh" Rovero Sonalysts, Inc. Email: rovero@sonalysts.com www.sonalysts.com 215 Parkway North Work: (860)326-3671 or 442-4355 Waterford CT 06385 ***********************************************************************
On Tue, 07 Dec 2004 07:50:44 -0500, P.J. Josh Rovero <rovero@sonalysts.com> wrote: > There are many reports of kernel problems with memory allocation > (too agressive) and swap issues with RHEL 3.0 on both RAID > and non-RAID systems. I hope folks have worked through all > those issues before blaming postgresql. We have seen several boxes have kswapd go crazy (near 100% CPU) on RHEL 3 boxes. Upgrading to kernel 2.4.21-4 fixed this. Tony Wasson
--=======67E74690======= Content-Type: text/plain; x-avg-checked=avg-ok-39E01936; charset=us-ascii; format=flowed Content-Transfer-Encoding: 8bit But isn't the problem when the planner screws up and not the sortmem setting? There was my case where the 7.4 planner estimated 1500 distinct rows when there were actually 1391110. On 7.3.4 it used about 4.4MB. Whereas 7.4 definitely used more than 400MB for the same query ) - I had to kill postgresql - didn't wait for it to use more. That's a lot more than 200%. Maybe 3x sort_mem is too low, but at least by default keep it below server RAM/number of backends or something like that. Even if the planner has improved a lot if cases like that still occur from time to time it'll be a lot better for stability/availability if there's a limit. Doubt if I still have the same data to test on 8.0. Link. At 12:35 AM 12/7/2004 -0500, Tom Lane wrote: >Neil Conway <neilc@samurai.com> writes: > > As a quick hack, what about throwing away the constructed hash table and > > switching to hashing for sorting if we exceed sort_mem by a significant > > factor? (say, 200%) We might also want to print a warning message to the > > logs. > >If I thought that a 200% error in memory usage were cause for a Chinese >fire drill, then I'd say "yeah, let's do that". The problem is that the >place where performance actually goes into the toilet is normally an >order of magnitude or two above the nominal sort_mem setting (for >obvious reasons: admins can't afford to push the envelope on sort_mem >because of the various unpredictable multiples that may apply). So >switching to a hugely more expensive implementation as soon as we exceed >some arbitrary limit is likely to be a net loss not a win. > >If you can think of a spill methodology that has a gentle degradation >curve, then I'm all for that. But I doubt there are any quick-hack >improvements to be had here. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly --=======67E74690=======--
Tom Lane wrote: > Paul Tillotson <pntil@shentel.net> writes: >> Does postgres actually do multiple concurrent sorts within a single >> backend? > > Certainly. Consider for example a merge join with each input being > sorted by an explicit sort step. DISTINCT, ORDER BY, UNION, and > related operators require their own sort steps in the current > implementation. It's not difficult to invent queries that require > arbitrarily large numbers of sort steps. Tom, in Bruce's document on performance tuning, the page titled "Multiple CPUs" states: "POSTGRESQL uses a multi-process model, meaning each database connection has its own Unix process...POSTGRESQL does not use multi-threading to allow a single process to use multiple CPUs." I took this to mean that PostgreSQL was not multi-threaded at all, and that each connection was serviced by a single, non-threaded process. Have I interpreted this incorrectly? Are you saying that the backend process actually is multi-threaded? In the example you site, multiple sorts could be accomplished serially in a non-threaded process. -- Guy Rouillier
Tom Lane <tgl@sss.pgh.pa.us> writes: > Paul Tillotson <pntil@shentel.net> writes: > > Does postgres actually do multiple concurrent sorts within a single > > backend? > > Certainly. Consider for example a merge join with each input being > sorted by an explicit sort step. DISTINCT, ORDER BY, UNION, and related > operators require their own sort steps in the current implementation. > It's not difficult to invent queries that require arbitrarily large > numbers of sort steps. I think there's a bit of misunderstanding here. He's talking about two sorts actually being executed in parallel. I don't think Postgres actually does that even if there are multiple sorts in the plan. Postgres isn't threaded (either manually or via OS threads) and Postgres's sort isn't incremental and doesn't return any tuples to the outer nodes until it's completely finished sorting (it's not bubble sort or selection sort:). However a sort step still takes up memory after it's finished executing because it has to store the ordered tuples. So a merge join joining two sorted tables needs to do the sort on one and then keep around the tuples, and do the sort on the second and keep around the tuples for that one too. I think the actual sort algorithm used can consume up to 3x the space of just the sorted tuples. But I'm not really sure on that, nor am I sure whether that space is reclaimed once the actual execution is done. -- greg
>>>Does postgres actually do multiple concurrent sorts within a single >>>backend? >>> >>> >>Certainly. Consider for example a merge join with each input being >>sorted by an explicit sort step. DISTINCT, ORDER BY, UNION, and >>related operators require their own sort steps in the current >>implementation. It's not difficult to invent queries that require >>arbitrarily large numbers of sort steps. >> >> > >Tom, in Bruce's document on performance tuning, the page titled >"Multiple CPUs" states: > >"POSTGRESQL uses a multi-process model, meaning each database connection >has its own Unix process...POSTGRESQL does not use multi-threading to >allow a single process to use multiple CPUs." > >I took this to mean that PostgreSQL was not multi-threaded at all, and >that each connection was serviced by a single, non-threaded process. >Have I interpreted this incorrectly? Are you saying that the backend >process actually is multi-threaded? In the example you site, multiple >sorts could be accomplished serially in a non-threaded process. > > Guy, You understand correctly. Each process is only running one query at once, but in terms of memory usage, several sorts are executing in parallel. For example, a merge join requires that both the left and right tables be sorted; as the join is being executed, both the left and right tables are being sorted. (Why doesn't it sort one and then the other? It would be a waste of memory to require that one of the [sorted] tables be kept in memory or written completely to the disk and then fetched later. Instead, it just sorts them both as it goes along.) However, this does mean that the amount of per-process memory being used for sorting will not vary with the "workload" of the database or number of people running that query (as each process only runs the query once). The amount of per-process memory used will vary with the complexity of the query and the plan chosen by the planner. Paul Tillotson