Date: Mon, 16 Sep 2013 17:14:09 +0200 (CEST)
From: Roberto Grandi <roberto.grandi@trovaprezzi.it>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org, Kevin Grittner <kgrittn@ymail.com>
Message-ID: <1359287440.225063.1379344449924.JavaMail.root@trovaprezzi.it>
In-Reply-To: 
 <CAMkU=1wHZZUm9aCTX1ZhqCrGGUacZ-FJ+sOqyzLsskZNADnrCw@mail.gmail.com>
References: <535715181.700956.1378223895627.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>
 <519968828.69982.1378966451438.JavaMail.root@trovaprezzi.it>
 <CAMkU=1wHZZUm9aCTX1ZhqCrGGUacZ-FJ+sOqyzLsskZNADnrCw@mail.gmail.com>
Subject: Re: COPY TO and VACUUM
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Thread-Topic: COPY TO and VACUUM
Thread-Index: UR+SxYz8jKXICqjoolyfIWLKx7zLoQ==
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


Hi Jeff,

thanks for your suggestion, Well test Vacuum instead of Cluster and come ba=
ck with live result.
at the same time i discovered that our COPY (...) TO are really really slow=
, I see 0Kb a t the beginning but at the end they grow by 4Kb each second.
Our export is standard (i.e.: SELECT a, b, c FROM table1) but sometime it's=
 very slow, what could be your suggestion? Is it possible to detect if we a=
re facing problem on IO or Linux systemItself?

Many thank in advance for all your help.
Regards,

Roberto


----- Messaggio originale -----
Da: "Jeff Janes" <jeff.janes@gmail.com>
A: "Roberto Grandi" <roberto.grandi@trovaprezzi.it>
Cc: pgsql-performance@postgresql.org, "Kevin Grittner" <kgrittn@ymail.com>
Inviato: Luned=C3=AC, 16 settembre 2013 2:18:44
Oggetto: Re: [PERFORM] COPY TO and VACUUM

On Wed, Sep 11, 2013 at 11:14 PM, Roberto Grandi <
roberto.grandi@trovaprezzi.it> wrote:

> 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.
>


If you can tolerate the locks, that is fine, but it just doesn't seem like
this should be necessary.  A manual vacuum should get the job done with
weaker locking.  Did you try running a manual vacuum every 5-6 hours
instead (it would probably not reclaim the space, but would make it
available for reuse and so cap the steady-state size of the file, hopefully
to about the same size as the max size under the CLUSTER regime)


> 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 ... T=
O"
> 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?
>

Are your COPY ... FROM also blocking, just in a way you are not detecting
(because there is no growing file to watch the size of)?  What does pg_lock
say?

Cheers,

Jeff


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance