Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 372AF632FD8 for ; Wed, 4 Mar 2009 16:09:50 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 20655-03 for ; Wed, 4 Mar 2009 16:09:37 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from EXHUB018-3.exch018.msoutlookonline.net (exhub018-3.exch018.msoutlookonline.net [64.78.17.18]) by mail.postgresql.org (Postfix) with ESMTP id 71AE06326B5 for ; Wed, 4 Mar 2009 16:09:37 -0400 (AST) Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by EXHUB018-3.exch018.msoutlookonline.net ([64.78.17.18]) with mapi; Wed, 4 Mar 2009 10:18:49 -0800 From: Scott Carey To: Flavio Henrique Araque Gurgel , pgsql-performance Date: Wed, 4 Mar 2009 10:18:45 -0800 Subject: Re: work_mem in high transaction rate database Thread-Topic: [PERFORM] work_mem in high transaction rate database Thread-Index: Acmcx0zkHDNDAwxPTcyjpbW0uyqW4AALlq/g Message-ID: In-Reply-To: <13904207.99291236170787203.JavaMail.root@mail.4linux.com.br> Accept-Language: en-US Content-Language: en X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/alternative; boundary="_000_C5D406052F56scottrichrelevancecom_" MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200903/44 X-Sequence-Number: 32966 --_000_C5D406052F56scottrichrelevancecom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable You may have decreased performance in your batch jobs with the lower work_m= em setting. Additionally, the fact that you haven't had swap storm issues so far means = that although there is certain risk of an issue, its probably a lot lower t= han what has been talked about here so far. Without a change in client behavior (new queries or large change in data) a= change in load alone is very unlikely to cause a problem. So take your ti= me to do it right. I disagree with the knee-jerk "change it now!" response= . The very fact you have gotten this far means it is not as risky as the b= are settings indicate. Definitely plan on improving and testing out work_mem variants soon, but a = hasty change to a small value might cause your batch jobs to take much long= er - what is your risk if they take too long or don't complete in time? Th= at risk is yours to assess - if its not much of a risk, then by all means l= ower work_mem soon. But if it is business critical for those batch jobs to= complete within some time frame, be careful. If you know what you are doing, and are careful, your work_mem is probably= , but not necessarily too high. It all depends on how much you know about your clients. For example 2010 connections with 500MB work_mem is not always a problem. = If you know 2000 of those are from an application that accesses with a user= that can only see small tables, and you know what queries those are, it ma= y be perfectly safe. For example, I've dealt with an application that had = a couple thousand connections, 95% were idle at any time (connection pool m= uch like those likely from your Jboss apps). The queries these ran were a = limited set of about 20 statements that all accessed by unique key on small= -ish sized tables (<30MB) with few joins. There were tons of connections, = but they: 1: hardly did anything, most were idle. On 75% of the connections, a query= set was run exactly once every 15 minutes selecting * from small (sub 1MB)= tables. 2: the more active connections (20%) did small select queries on single row= s accessed by primary key. So, the calculation max connections * work_mem is utterly inappropriate fo= r that sort of workload. Yes, in theory, those connections could use work= _mem * some factor of memory - if they changed their queries, and accessed = other tables. In practice - nowhere close. The remaining few connections(~5) were batch jobs that needed ~800MB of wor= k_mem or else the performance would stink. And they didn't need 800MB of w= ork_mem for real (the hashes used ~250MB) they needed a SETTING of 800MB be= cause the planner is incapable of estimating row counts properly with parti= tioned table access. Both applications were not able to configure their own work_mem for quite s= ome time (getting client applications to change is not always a quick proce= ss). But the risk of having a large setting, even with 2000 connections was low.= The risk of changing it too low was very high (batch jobs taking 3 hours = instead of 10 minutes). Only 5 ish connections even accessed schemas/table= s with lots of data. The remaining couple thousand were constrained in man= y ways other than work_mem. In the end I did have swap storms... However it was not caused by work_mem.= It was the query planner, which can use GBs of memory per connection plan= ning a query on partitioned tables. So, my point is that if you don't know a lot about the database or its clie= nts be very wary of large work_mem settings. If you do, and have a lot of = control or knowledge about your clients, the work_mem * max_connections cal= culation is inappropriate. The detailed formula is along the lines of: SUM_i [work_mem_i * active_connecions_i] (for each 'type' of connection = i). If you don't know enough about your connections, then the conservative esti= mate is work_mem * max_connections. A single query has the potential of using multiples of work_mem depending o= n how many concurrent hashes / sorts are in a query, so the above is not qu= ite right either. Is there a way to make a particular database user have a user-local work_me= m setting without having the client change their code? You could then have = each application have its own user, with its own default setting. The batc= h jobs with few connections can get much larger work_mem than the Jboss one= s. This would be especially powerful for applications that can't change or= that use higher level tools for db access that make it impossible or very = difficult to send non-standard commands like "SET". On 3/4/09 4:46 AM, "Flavio Henrique Araque Gurgel" w= rote: ----- "Scott Marlowe" escreveu: > Oh my lord, that is a foot gun waiting to go off. Assuming 2k > connections, and somehow a fair number of them went active with big > sorts, you'd be able to exhaust all physical memory with about 8 to > 16 connections. Lower work_mem now. To something like 1 to 4 Meg. Do > not pass go. If some oddball query really needs a lot of work_mem, > and benchmarks show something larger work_mem helps, consider raising > the work_mem setting for that one query to something under 1G (way > under 1G) That makes it noticeably faster. Don't allocate more than a > test shows you helps. Thanks a lot Scott. That's what I thought in the beginning but was very dou= btful since the documentation is a bit odd regarding this point and several= bloggers talk about increasing this value up to 250MB. I really think that= separating regular non pooled distributed applications and pooled applicat= ion servers makes a lot of difference in this point. > Vacuum does not block transactions. unless you're dropping tables or som= ething. I'll try to separate things and check if the DELETE queries have something = related here. (...) > what you think they mean. Post the first 20 or so lines from top to > show us. Unfortunately I can't do it. The data there is very sensitive (it's a publi= c company here in Brazil) and the server is operated only by selected perso= nal. I just ask for information and give written recomendations. Anyway, I'= m going to pay some more attention in this topic. This is a very interesting implementation of PostgreSQL (3 large databases,= heavy load, things growing all the time) and I'll let you all know what ha= ppened when tuning it. I'll feedback you after lowering work_mem and changi= ng related settings. Thanks Flavio --_000_C5D406052F56scottrichrelevancecom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Re: [PERFORM] work_mem in high transaction rate database You may have decreased performance in your batch jobs with the lower = work_mem setting.
Additionally, the fact that you haven’t had swap storm issues so far = means that although there is certain risk of an issue, its probably a lot l= ower than what has been talked about here so far.
Without a change in client behavior (new queries or large change in data) a= change in load alone is very unlikely to cause a problem.  So take yo= ur time to do it right.  I disagree with the knee-jerk “change i= t now!” response.  The very fact you have gotten this far means = it is not as risky as the bare settings indicate.
Definitely plan on improving and testing out work_mem variants soon, but a = hasty change to a small value might cause your batch jobs to take much long= er — what is your risk if they take too long or don’t complete = in time?  That risk is yours to assess — if its not much of a ri= sk, then by all means lower work_mem soon.  But if it is business crit= ical for those batch jobs to complete within some time frame, be careful.
If you know what you are doing, and are careful,  your work_mem is pro= bably, but not necessarily too high.
It all depends on how much you know about your clients.  
For example 2010 connections with 500MB work_mem is not always a problem. &= nbsp;If you know 2000 of those are from an application that accesses with a= user that can only see small tables, and you know what queries those are, = it may be perfectly safe.  For example, I’ve dealt with an appli= cation that had a couple thousand connections, 95% were idle at any time (c= onnection pool much like those likely from your Jboss apps).  The quer= ies these ran were a limited set of about 20 statements that all accessed b= y unique key on small-ish sized tables (<30MB) with few joins.  The= re were tons of connections, but they:
1: hardly did anything, most were idle.  On 75% of the connections, a = query set was run exactly once every 15 minutes selecting * from small (sub= 1MB) tables.  
2: the more active connections (20%) did small select queries on single row= s accessed by primary key.

So, the calculation  max connections * work_mem is utterly inappropria= te for that sort of workload.   Yes, in theory, those connections= could use work_mem * some factor of memory — if they changed their q= ueries, and accessed other tables.  In practice — nowhere close.=

The remaining few connections(~5) were batch jobs that needed ~800MB of wor= k_mem or else the performance would stink.  And they didn’t need= 800MB of work_mem for real (the hashes used ~250MB) they needed a SETTING = of 800MB because the planner is incapable of estimating row counts properly= with partitioned table access.
Both applications were not able to configure their own work_mem for quite s= ome time (getting client applications to change is not always a quick proce= ss).
But the risk of having a large setting, even with 2000 connections was low.=  The risk of changing it too low was very high (batch jobs taking 3 h= ours instead of 10 minutes).  Only 5 ish connections even accessed sch= emas/tables with lots of data.  The remaining couple thousand were con= strained in many ways other than work_mem.

In the end I did have swap storms... However it was not caused by work_mem.=  It was the query planner, which can use GBs of memory per connection= planning a query on partitioned tables.

So, my point is that if you don’t know a lot about the database or it= s clients be very wary of large work_mem settings.  If you do, and hav= e a lot of control or knowledge about your clients, the work_mem * max_conn= ections calculation is inappropriate.

The detailed formula is along the lines of:
SUM_i [work_mem_i * active_connecions_i]    (for each ‘= ;type’ of connection i).
If you don’t know enough about your connections, then the conservativ= e estimate is work_mem * max_connections.

A single query has the potential of using multiples of work_mem depending o= n how many concurrent hashes / sorts are in a query, so the above is not qu= ite right either.

Is there a way to make a particular database user have a user-local work_me= m setting without having the client change their code? You could then have = each application have its own user, with its own default setting.  The= batch jobs with few connections can get much larger work_mem than the Jbos= s ones.  This would be especially powerful for applications that can&#= 8217;t change or that use higher level tools for db access that make it imp= ossible or very difficult to send non-standard commands like “SETR= 21;.

On 3/4/09 4:46 AM, "Flavio Henrique Araque Gurgel" <flavio@4linux.com.br> wrote:

----- "Scott Marlowe" <scott.marlowe@gmail.com> escreveu:
> Oh my lord, that is a foot gun waiting to go off.  Assuming 2k > connections, and somehow a fair number of them went active with big > sorts, you'd be able to exhaust all physical memory  with about 8= to
> 16 connections.  Lower work_mem now. To something like 1 to 4 Meg= .  Do
> not pass go.  If some oddball query really needs a lot of work_me= m,
> and benchmarks show something larger work_mem helps, consider raising<= BR> > the work_mem setting for that one query to something under 1G (way
> under 1G) That makes it noticeably faster.  Don't allocate more t= han a
> test shows you helps.

Thanks a lot Scott. That's what I thought in the beginning but was very dou= btful since the documentation is a bit odd regarding this point and several= bloggers talk about increasing this value up to 250MB. I really think that= separating regular non pooled distributed applications and pooled applicat= ion servers makes a lot of difference in this point.

> Vacuum does not block transactions.  unless you're dropping table= s or something.

I'll try to separate things and check if the DELETE queries have something = related here.

(...)
> what you think they mean.  Post the first 20 or so lines from top= to
> show us.

Unfortunately I can't do it. The data there is very sensitive (it's a publi= c company here in Brazil) and the server is operated only by selected perso= nal. I just ask for information and give written recomendations. Anyway, I'= m going to pay some more attention in this topic.

This is a very interesting implementation of PostgreSQL (3 large databases,= heavy load, things growing all the time) and I'll let you all know what ha= ppened when tuning it. I'll feedback you after lowering work_mem and changi= ng related settings.

Thanks
Flavio

--_000_C5D406052F56scottrichrelevancecom_--