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

On Wed, Jun 3, 2015 at 8:56 AM, Tomas Vondra
<tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>
> 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(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 :)
>
>
> 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).

Because you subtract cached from used to see how much real spare
memory you have. The kernel will dump cached mem as needed to free up
space for memory usage. So 64141-61761=2380MB used.