From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: How to reduce writing on disk ? (90 gb on pgsql_tmp)
Date: Wed, 03 Jun 2015 16:56:45 +0200
Message-ID: <556F15AD.4040801@2ndquadrant.com> (raw)
In-Reply-To: <9DED5F42-2C51-4CDE-A179-56A8A9370338@playrion.com>
References: <1433334092106-5852321.post@n5.nabble.com>
<1433337082772-5852328.post@n5.nabble.com>
<CA+RSxMiSwse+YfMoFT8128PoWG3kifmrvg4rhdBHxUTE3HPH-g@mail.gmail.com>
<556F0649.9000402@2ndquadrant.com>
<9DED5F42-2C51-4CDE-A179-56A8A9370338@playrion.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com
Subject: Re: How to reduce writing on disk ? (90 gb on pgsql_tmp)
In-Reply-To: <556F15AD.4040801@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox