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

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