From: Heikki Linnakangas <heikki@enterprisedb.com>
To: jay <jackem.mojx@alibaba-inc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: ??: Postgresql update op is very very slow
Date: Thu, 26 Jun 2008 15:19:05 +0300
Message-ID: <48638939.5080500@enterprisedb.com> (raw)
In-Reply-To: <005101c8d773$ff81b2c0$0644000a@hz.ali.com>
References: <473587.97248.qm@web32701.mail.mud.yahoo.com>
<op.uc83c7m2cigqcu@apollo13.peufeu.com>
<004b01c8d671$3dec1630$0544000a@hz.ali.com>
<486219B6.7050003@enterprisedb.com>
<005101c8d773$ff81b2c0$0644000a@hz.ali.com>
jay wrote:
> I know the problem, because there are about 35 million rows , which
> cost about 12G disk space and checkpoint segments use 64, but update
> operation is in one transaction which lead fast fill up the checkpoint
> segments and lead do checkpoints frequently, but checkpoints will cost lots
> resources, so update operation become slowly and slowly and bgwrite won't
> write because it's not commit yet.
> Create a new table maybe a quick solution, but it's not appropriated in some
> cases.
> If we can do commit very 1000 row per round, it may resolve the
> problem.
Committing more frequently won't help you with checkpoints. The updates
will generate just as much WAL regardless of how often you commit, so
you will have to checkpoint just as often. And commits have no effect on
bgwriter either; bgwriter will write just as much regardless of how
often you commit.
One idea would be to partition the table vertically, that is, split the
table into two tables, so that the columns that you need to update like
that are in one table, together with the primary key, and the rest of
the columns are in another table. That way the update won't need to scan
or write the columns that are not changed. You can create a view on top
of the two tables to make them look like the original table to the
application.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki@enterprisedb.com, jackem.mojx@alibaba-inc.com
Subject: Re: ??: Postgresql update op is very very slow
In-Reply-To: <48638939.5080500@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox