Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VbAq2-00064y-Bb for pgsql-admin@arkaria.postgresql.org; Tue, 29 Oct 2013 15:04:10 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VbAq1-0007F7-Ea for pgsql-admin@arkaria.postgresql.org; Tue, 29 Oct 2013 15:04:09 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VbAq0-0007El-7f for pgsql-admin@postgresql.org; Tue, 29 Oct 2013 15:04:08 +0000 Received: from mail3.neteven.com ([37.59.178.241]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VbApu-0003G6-Ga for pgsql-admin@postgresql.org; Tue, 29 Oct 2013 15:04:07 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by mail3.neteven.com (Postfix) with ESMTP id 0C7A6140B45 for ; Tue, 29 Oct 2013 16:03:58 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at mail3.neteven.com Received: from mail3.neteven.com ([127.0.0.1]) by localhost (mail3.neteven.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id y6X6UcDIsL-x for ; Tue, 29 Oct 2013 16:03:57 +0100 (CET) Received: from [192.168.1.34] (62-255-180-159.abo.celeste.fr [159.180.255.62]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client did not present a certificate) by mail3.neteven.com (Postfix) with ESMTPSA id C0224140809 for ; Tue, 29 Oct 2013 16:03:57 +0100 (CET) Message-ID: <526FCE7F.5010907@neteven.com> Date: Tue, 29 Oct 2013 16:04:31 +0100 From: Thomas SIMON User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130803 Thunderbird/17.0.8 MIME-Version: 1.0 To: "pgsql-admin@postgresql.org" Subject: Re: Huge increase of database partition References: <52692CCC.8080305@neteven.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------060304020301060904000604" X-Pg-Spam-Score: 0.3 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------060304020301060904000604 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit You're right, indeed. I saw same phenomenon today, and i have seen that the pgsql_tmp directory was full of temporary files linked to a particular query. After i killed the query , postgres cleaned directory. So, if i understand right, for avoid that the temp files crash data partition (and my db), i should create a new tablespace, and change param "temp_tablespaces" in postgresql configuration, right ? Thomas Le 24/10/2013 16:26, Plugge, Joe R. a écrit : > > Yes, did you perhaps have a long running query that was doing lots of > sorts, if so it creates as many 1 GB temp files as it takes to > complete the sort, and they go into your data partition by default. > Once the query completes, postgres cleans them up. > > *From:*pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org] *On Behalf Of *Thomas SIMON > *Sent:* Thursday, October 24, 2013 9:21 AM > *To:* pgsql-admin@postgresql.org > *Subject:* [ADMIN] Huge increase of database partition > > Hi the list, > > Yesterday, i have had a big problem of disk space on my server, > running postgresql 9.0.13; with streaming replication. > > Between 6pm and 7-30pm, i went from 45% used space to 99% (100% ?) > used space on my /data partition (who contains my > postgresql/9.0/main/base ; but does not contains the WAL; they are > deported on an other partition). - seen only after with munin - > > Juste after that - still according to munin- , the partition suddently > decreases to its normal value (around 45%) > This peak represents around 165 Go of data ! ; beacause my partition > size is 315 Go. > > I can't explain why, it's the first time i have something like this. > Usually, the size of the partition is very stable ; and grows very slowly. > Still with munin, i see that in this period, the blocks read per > second increases from around 20k to 80k, and i see that the disk > utilisation is around 100%, which confirms that postgres wrote a lot > of data. > > I don't understand how this can happen, have you ever seen things like > this ? > > Thanks in advance, > Thomas > --------------060304020301060904000604 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
You're right, indeed.
I saw same phenomenon today, and i have seen that the pgsql_tmp directory was full of temporary files linked to a particular query.
After i killed the query , postgres cleaned directory.
So, if i understand right, for avoid that the temp files crash data partition (and my db), i should create a new tablespace, and change param "temp_tablespaces" in postgresql configuration, right ?


Thomas
Le 24/10/2013 16:26, Plugge, Joe R. a écrit :

Yes, did you perhaps have a long running query that was doing lots of sorts, if so it creates as many 1 GB temp files as it takes to complete the sort, and they go into your data partition by default.  Once the query completes, postgres cleans them up.

 

From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomas SIMON
Sent: Thursday, October 24, 2013 9:21 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Huge increase of database partition

 

Hi the list,

Yesterday, i have had a big problem of disk space on my server, running postgresql 9.0.13; with streaming replication.

Between 6pm and 7-30pm, i went from 45% used space to 99% (100% ?) used space on my /data partition (who contains my postgresql/9.0/main/base ; but does not contains the WAL; they are deported on an other partition). - seen only after with munin -

Juste after that - still according to munin- , the partition suddently decreases to its normal value (around 45%)
This peak represents around 165 Go of data ! ; beacause my partition size is 315 Go.

I can't explain why, it's the first time i have something like this. Usually, the size of the partition is very stable ; and grows very slowly.
Still with munin, i see that in this period, the blocks read per second increases from around 20k to 80k,  and i see that the disk utilisation is around 100%, which confirms that postgres wrote a lot of data.

I don't understand how this can happen, have you ever seen things like this ?

Thanks in advance,
Thomas

 


--------------060304020301060904000604--