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

On 06/03/15 16:06, ben.play wrote:
> The query is (unfortunately) generated by Doctrine 2 (Symfony 2).
> We can’t change the query easily.

Well, then you'll probably have to buy more RAM, apparently.

> 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 :)

I don't see why you think you have less than 3GB used. The output you
posted clearly shows there's only ~300MB memory free - there's 15GB
shared buffers and ~45GB of page cache (file system cache).

But you still haven't shows us the query (the EXPLAIN ANALYZE of it), so
we can't really give you advice.

--
Tomas Vondra                   http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services