Re: How to reduce writing on disk ? (90 gb on pgsql_tmp)

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(at)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+s1045698n5852331h9(at)n5(dot)nabble(dot)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.