The query is (unfortunately) generated by Doctrine 2 (Symfony 2). 
We can’t change the query easily.

This is my config : 
max_connections = 80
shared_buffers = 15GB
work_mem = 384MB
maintenance_work_mem = 1GB
#temp_buffers = 8MB 
#temp_file_limit = -1 
effective_cache_size = 44GB

If I put a temp_file_limit …Are all my queries (who have to write on disk) will 
crash ?

As you can see… I have 64 gb of Ram, but less than 3 Gb is used !

ben@bdd:/home/benjamin# free -m
             total       used       free     shared    buffers     cached
Mem:         64456      64141        315      15726         53      61761
-/+ buffers/cache:       2326      62130
Swap:         1021         63        958


Thanks guys for your help :)


> Le 3 juin 2015 à 15:51, Tomas Vondra-4 [via PostgreSQL] 
> <ml-node+s1045698n585233...@n5.nabble.com> a écrit :
> 
> 
> 
> On 06/03/15 15:27, chiru r wrote: 
> > Hi Benjamin, 
> > 
> > It looks you are facing disk space issue for queries. 
> > In order to avid the disk space issue you can do the following. 
> > 1) Increase the work_mem parameter session level before executing the 
> > queries. 
> > 2) If you observe diskspace issue particular user queries,increase the 
> > work_mem parameter user level. 
> 
> The suggestion to increase work_mem is a bit naive, IMHO. The query is 
> writing ~95GB to disk, it usually takes more space to keep the same data 
> in memory. They only have 64GB of RAM ... 
> 
> In the good case, it will crash just like now. In the worse case, the 
> OOM killer will intervene, possibly crashing the whole database. 
> 
> 
> > 3) Check with developer to tune the query. 
> 
> That's a better possibility. Sadly, we don't know what the query is 
> doing, so we can't judge how much it can be optimized. 
> 
> -- 
> Tomas Vondra                   http://www.2ndQuadrant.com/ 
> <http://www.2ndquadrant.com/>
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services 
> 
> 
> -- 
> Sent via pgsql-performance mailing list ([hidden email] 
> <x-msg://4/user/SendEmail.jtp?type=node&node=5852331&i=0>) 
> To make changes to your subscription: 
> http://www.postgresql.org/mailpref/pgsql-performance 
> <http://www.postgresql.org/mailpref/pgsql-performance>
> 
> 
> If you reply to this email, your message will be added to the discussion 
> below:
> http://postgresql.nabble.com/How-to-reduce-writing-on-disk-90-gb-on-pgsql-tmp-tp5852321p5852331.html
>  
> <http://postgresql.nabble.com/How-to-reduce-writing-on-disk-90-gb-on-pgsql-tmp-tp5852321p5852331.html>
> To unsubscribe from How to reduce writing on disk ? (90 gb on pgsql_tmp), 
> click here 
> <http://postgresql.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5852321&code=YmVuamFtaW4uY29oZW5AcGxheXJpb24uY29tfDU4NTIzMjF8LTE0OTE4NTc4Ng==>.
> NAML 
> <http://postgresql.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>




--
View this message in context: 
http://postgresql.nabble.com/How-to-reduce-writing-on-disk-90-gb-on-pgsql-tmp-tp5852321p5852332.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

Reply via email to