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.