Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 92F5664FD42 for ; Wed, 12 Nov 2008 12:48:04 -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 53797-10 for ; Wed, 12 Nov 2008 12:47:56 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gransy.com (unknown [82.208.29.208]) by mail.postgresql.org (Postfix) with ESMTP id B745064FD2A for ; Wed, 12 Nov 2008 12:47:55 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by mail.gransy.com (Postfix) with ESMTP id 03DFA7AC019 for ; Wed, 12 Nov 2008 17:47:52 +0100 (CET) Received: from mail.gransy.com ([127.0.0.1]) by localhost (nathalia.gransy.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 0qOB23RGkbJd for ; Wed, 12 Nov 2008 17:47:51 +0100 (CET) Received: from anastacia.gransy.com (anastacia.gransy.com [89.187.130.100]) by mail.gransy.com (Postfix) with ESMTP id C36277AC008 for ; Wed, 12 Nov 2008 17:47:51 +0100 (CET) Received: from sq.gransy.com (localhost [127.0.0.1]) by anastacia.gransy.com (Postfix) with ESMTP id EC3B6282BF9 for ; Wed, 12 Nov 2008 17:47:58 +0100 (CET) Received: from 89.102.139.23 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Wed, 12 Nov 2008 17:47:59 +0100 (CET) Message-ID: <63509.89.102.139.23.1226508479.squirrel@sq.gransy.com> In-Reply-To: <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> <200811121725.30238@centrum.cz> Date: Wed, 12 Nov 2008 17:47:59 +0100 (CET) Subject: Re: slow full table update From: tv@fuzzy.cz To: pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.10a MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-2 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.1 tagged_above=0 required=5 tests=RDNS_NONE=0.1 X-Spam-Level: X-Archive-Number: 200811/92 X-Sequence-Number: 31823 Hi, so the table occupies about 50 MB, i.e. each row has about 1 kB, right? Updating 1000 rows should means about 1MB of data to be updated. There might be a problem with execution plan of the updates - I guess the 100 rows update uses index scan and the 1000 rows update might use seq scan. Anyway the table is not too big, so I wouldn't expect such I/O bottleneck on a properly tuned system. Have you checked the postgresql.conf settings? What are the values for 1) shared_buffers - 8kB pages used as a buffer (try to increase this a little, for example to 1000, i.e. 8MB, or even more) 2) checkpoint_segments - number of 16MB checkpoint segments, aka transaction logs, this usually improves the write / update performance a lot, so try to increase the default value (3) to at least 8 3) wal_buffers - 8kB pages used to store WAL (minimal effect usually, but try to increase it to 16 - 64, just to be sure) There is a nicely annotated config, with recommendations on how to set the values based on usage etc. See this: http://www.powerpostgresql.com/Downloads/annotated_conf_80.html http://www.powerpostgresql.com/PerfList regards Tomas > 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 >> > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >