From: Roberto Grandi <roberto.grandi@trovaprezzi.it>
To: pgsql-performance@postgresql.org
Cc: Kevin Grittner <kgrittn@ymail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Subject: Re: COPY TO and VACUUM
Date: Thu, 12 Sep 2013 08:14:11 +0200 (CEST)
Message-ID: <519968828.69982.1378966451438.JavaMail.root@trovaprezzi.it> (raw)
In-Reply-To: <CAMkU=1ygDqcwQR8rn7PAymmRVhnXK8mWFQ_58_X+FCFP4Kqk3g@mail.gmail.com>
References: <535715181.700956.1378223895627.JavaMail.root@trovaprezzi.it>
<441706222.701556.1378224973704.JavaMail.root@trovaprezzi.it>
<1378240470.79541.YahooMailNeo@web162905.mail.bf1.yahoo.com>
<1680047223.718929.1378275308275.JavaMail.root@trovaprezzi.it>
<CAMkU=1ytUAZbDcXcBC6MAN+wkP=La7duTuEAYe+yU9UZJFQAgg@mail.gmail.com>
<2031102275.783807.1378397108337.JavaMail.root@trovaprezzi.it>
<CAMkU=1ygDqcwQR8rn7PAymmRVhnXK8mWFQ_58_X+FCFP4Kqk3g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi Guys,
we found a suitable solution for our process we run every 5-6 hours a CLUSTER stement for our big table: this "lock" activities but allow us to recover all available space.
When testing this task we discover another issues and that's why I'm coming back to you for your experience:
duting our process we run multiple simoultaneously "COPY... FROM" in order to load data into our table but a t the same time we run also "COPY ... TO" statement in parallel to export data for other clients.
We found that COPY .. TO queries sometimes are pending for more than 100 minutes and the destination file continues to be at 0 Kb. Can you advise me how to solve this issue?
Is it here a best way to bulk download data avoiding any kind of block when running in parallel?
Many thanks in advance
----- Messaggio originale -----
Da: "Jeff Janes" <jeff.janes@gmail.com>
A: "Roberto Grandi" <roberto.grandi@trovaprezzi.it>
Cc: "Kevin Grittner" <kgrittn@ymail.com>, pgsql-performance@postgresql.org
Inviato: Giovedì, 5 settembre 2013 20:14:26
Oggetto: Re: [PERFORM] COPY TO and VACUUM
On Thu, Sep 5, 2013 at 9:05 AM, Roberto Grandi
<roberto.grandi@trovaprezzi.it> wrote:
> Hi Jeff,
>
> the proble is that when continously updloading vendors listing on our "big" table the autovacuum is not able to free space as we would.
It might not be able to free it (to be reused) as fast as you need it
to, but it should be freeing it eventually.
> Secondarly, if we launch a Vacuum after each "upload" we collide with other upload taht are running in parallel.
I wouldn't do a manual vacuum after *each* upload. Doing one after
every Nth upload, where N is estimated to make up about 1/5 of the
table, should be good. You are probably IO limited, so you probably
don't gain much by running these uploads in parallel, I would try to
avoid that. But in any case, there shouldn't be a collision between
manual vacuum and a concurrent upload. There would be one between two
manual vacuums but you could code around that by explicitly locking
the table in the correct mode nowait or with a timeout, and skipping
the vacuum if it can't get the lock.
>
> Is it possible, form your point of view, working with isolation levels or table partitioning to minimize table space growing?
Partitioning by vendor might work well for that purpose.
Cheers,
Jeff
--
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: roberto.grandi@trovaprezzi.it, kgrittn@ymail.com, jeff.janes@gmail.com
Subject: Re: COPY TO and VACUUM
In-Reply-To: <519968828.69982.1378966451438.JavaMail.root@trovaprezzi.it>
* 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