Hi Jeff,
thanks for your suggestion, Well test Vacuum instead of Cluster and come back 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
theygrow 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?
Isit possible to detect if we are 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ì, 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 ... 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?
>
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
