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] <[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="" 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
View this message in context: Re: How to reduce writing on disk ? (90 gb on pgsql_tmp)
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
