Re: [PERFORM] COPY TO and VACUUM
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 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 are facing problem on IO or Linux systemItself? Many thank in advance for all your help. Regards, Roberto - Messaggio originale - Da: Jeff Janes jeff.ja...@gmail.com A: Roberto Grandi roberto.gra...@trovaprezzi.it Cc: pgsql-performance@postgresql.org, Kevin Grittner kgri...@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.gra...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] How to optimization database for heavy I/O from updates (software and hardware)
I'm in the process of taking the next leap in performance optimization of our database, I just need some good advice on my journey. I posted the full question with images here on stackexchange if someone would be interested in commenting / answering it would be great! Regards Niels Kristian http://dba.stackexchange.com/questions/49984/how-to-optimization-database-for-heavy-i-o-from-updates-software-and-hardware -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] stable and immutable functions in GROUP BY clauses.
Hello, Stable and immutable functions do not improve performance when used within the GROUP BY clause. Here, the function will be called for each row. To avoid it, I can replace the funtion by its arguments within GROUP BY. Shame on me ! This is of course bullsh... It has nothing to do with immutability and can only applies to few cases e.g: it's fine for select x+1 ... group by x, but not forselect x^2 ... group by x Marc Mamin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] function execute on v.9.2 slow down
On Mon, Aug 12, 2013 at 8:21 AM, Александр Белинский avinf...@gmail.com wrote: Hi! I can't explain why function is slow down on same data. Postgresql.conf the same, hardware is more powerful. Diffrents is postgresql version Hmm. PostgreSQL 9.2 will sometimes replan queries a number of times where older releases, looking to see whether the choice of bind variables affects the optimal plan choice, where older versions would create a generic plan on first execution and use it forever. I'm not sure whether that behavior applies in this situation, though. If you run it say 15 times does it eventually start running faster? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
