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

max_connections = 80
shared_buffers = 15GB
work_mem = 384MB
maintenance_work_mem = 1GB
#temp_buffers = 8MB 
#temp_file_limit = -1 
effective_cache_size = 44GB

Le 3 juin 2015 à 15:51, Tomas Vondra-4 [via PostgreSQL] <[hidden email]> 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/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-performance mailing list (<a href="x-msg://4/user/SendEmail.jtp?type=node&amp;node=5852331&amp;i=0" target="_top" rel="nofollow" link="external" class="">[hidden email])
To make changes to your subscription:
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

To unsubscribe from How to reduce writing on disk ? (90 gb on pgsql_tmp), click here.
NAML