Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id C614364FEAB for ; Wed, 12 Nov 2008 12:26:02 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 51589-02 for ; Wed, 12 Nov 2008 12:25:59 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail1011.centrum.cz (mail1011.centrum.cz [90.183.38.141]) by mail.postgresql.org (Postfix) with ESMTP id 15E9264FEE7 for ; Wed, 12 Nov 2008 12:25:57 -0400 (AST) Received: by mail1011.centrum.cz id S537232957AbYKLQZt (ORCPT ); Wed, 12 Nov 2008 17:25:49 +0100 Received: from 147.32.124.245 (X-Forwarded-For: 147.32.124.245) by mail1011.centrum.cz (Centrum Mail) with HTTP Date: Wed, 12 Nov 2008 17:25:49 +0100 From: To: Cc: X-Mailer: Centrum Mail 5.0 MIME-Version: 1.0 X-Priority: 3 Message-ID: <200811121725.30238@centrum.cz> References: <200811101728.3048@centrum.cz> <200811101729.14977@centrum.cz> <200811101730.11057@centrum.cz> <200811101730.15902@centrum.cz> <61593.89.102.139.23.1226335300.squirrel@sq.gransy.com> In-Reply-To: <61593.89.102.139.23.1226335300.squirrel@sq.gransy.com> Subject: Re: slow full table update Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=UNPARSEABLE_RELAY=0.001 X-Spam-Level: X-Archive-Number: 200811/90 X-Sequence-Number: 31821 Hi, thank you for your reply. Here is some aditional information: the problem is on every tables with small and large rows too. autovacuum is running. relpages reltuples 6213 54743 tables are almost write-only Munin Graphs shows that problems is with I/O bottleneck. I found out that Update 100 rows takes 0.3s but update 1000 rows takes 50s Is this better information? Thanks for any help. best regards Marek Fiala ______________________________________________________________ > Od: tv@fuzzy.cz > Komu: firerox@centrum.cz > CC: pgsql-performance@postgresql.org > Datum: 10.11.2008 17:42 > Předmět: Re: [PERFORM] slow full table update > >Sorry, but you have to provide much more information about the table. The >information you've provided is really not sufficient - the rows might be >large or small. I guess it's the second option, with a lots of dead rows. > >Try this: > >ANALYZE table; >SELECT relpages, reltuples FROM pg_class WHERE relname = 'table'; > >Anyway, is the autovacuum running? What are the parameters? Try to execute > >VACUUM table; > >and then run the two commands above. That might 'clean' the table and >improve the update performance. Don't forget each such UPDATE will >actually create a copy of all the modified rows (that's how PostgreSQL >works), so if you don't run VACUUM periodically or autovacuum demon, then >the table will bloat (occupy much more disk space than it should). > >If it does not help, try do determine if the UPDATE is CPU or disk bound. >I'd guess there are problems with I/O bottleneck (due to the bloating). > >regards >Tomas > >> Hi, >> >> I have table with cca 60.000 rows and >> when I run query as: >> Update table SET column=0; >> after 10 minutes i must stop query, but it still running :( >> >> I've Postgres 8.1 with all default settings in postgres.conf >> >> Where is the problem? >> >> Thak you for any tips. >> >> best regards. >> Marek Fiala >> >> >> >> >> >> >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> > > > >-- >Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-performance >