pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql update op is very very slow
12+ messages / 8 participants
[nested] [flat]
* Postgresql update op is very very slow
@ 2008-06-25 03:12 jay <jackem.mojx@alibaba-inc.com>
0 siblings, 3 replies; 12+ messages in thread
From: jay @ 2008-06-25 03:12 UTC (permalink / raw)
To: pgsql-performance
I've a table with about 34601755 rows ,when I execute 'update msg_table set
type=0;' is very very slow, cost several hours, but still not complete?
Why postgresql is so slowly? Is the PG MVCC problem?
But I try it on Mysql, the same table and rows, it only cost about 340
seconds.
Any idea for the problem?
My machine config:
Memory 8G, 8 piece 15K disk , 2CPU(Quad-Core) AMD
OS: Red Hat AS4
My postgres.conf main parameter is following:
shared_buffers = 5GB # min 128kB or max_connections*16kB
# (change requires restart)
temp_buffers = 512MB # min 800kB
work_mem = 400MB # min 64kB
maintenance_work_mem = 600MB # min 1MB
max_fsm_pages = 262144 # 2G min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 2000 # min 100, ~70 bytes each
bgwriter_delay = 20ms # 10-10000ms between rounds
bgwriter_lru_maxpages = 500 # 0-1000 max buffers written/round
bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgresql update op is very very slow
@ 2008-06-25 05:02 Rusty Conover <rconover@infogears.com>
parent: jay <jackem.mojx@alibaba-inc.com>
2 siblings, 0 replies; 12+ messages in thread
From: Rusty Conover @ 2008-06-25 05:02 UTC (permalink / raw)
To: jay <jackem.mojx@alibaba-inc.com>; +Cc: pgsql-performance
On Jun 24, 2008, at 9:12 PM, jay wrote:
>
> I've a table with about 34601755 rows ,when I execute 'update
> msg_table set
> type=0;' is very very slow, cost several hours, but still not
> complete?
>
> Why postgresql is so slowly? Is the PG MVCC problem?
>
> But I try it on Mysql, the same table and rows, it only cost about 340
> seconds.
>
> Any idea for the problem?
>
>
> My machine config:
> Memory 8G, 8 piece 15K disk , 2CPU(Quad-Core) AMD
> OS: Red Hat AS4
>
> My postgres.conf main parameter is following:
>
>
Hi Jay,
Is the "type" used in an index? Have you properly increased your
number of checkpoint segments? Any warnings in in your log file about
excessive checkpointing?
Cheers,
Rusty
--
Rusty Conover
InfoGears Inc.
http://www.infogears.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgresql update op is very very slow
@ 2008-06-25 10:11 Heikki Linnakangas <heikki@enterprisedb.com>
parent: jay <jackem.mojx@alibaba-inc.com>
2 siblings, 1 reply; 12+ messages in thread
From: Heikki Linnakangas @ 2008-06-25 10:11 UTC (permalink / raw)
To: jay <jackem.mojx@alibaba-inc.com>; +Cc: pgsql-performance
jay wrote:
> I've a table with about 34601755 rows ,when I execute 'update msg_table set
> type=0;' is very very slow, cost several hours, but still not complete?
>
> Why postgresql is so slowly? Is the PG MVCC problem?
Possibly. Because of MVCC, a full-table update will actually create a
new version of each row.
I presume that's a one-off query, or a seldom-run batch operation, and
not something your application needs to do often. In that case, you
could drop all indexes, and recreate them after the update, which should
help a lot:
BEGIN;
DROP INDEX <index name>, <index name 2>, ...; -- for each index
UPDATE msg_table SET type = 0;
CREATE INDEX ... -- Recreate indexes
COMMIT;
Or even better, instead of using UPDATE, do a SELECT INTO a new table,
drop the old one, and rename the new one in its place. That has the
advantage that the new table doesn't contain the old row version, so you
don't need to vacuum right away to reclaim the space.
Actually, there's an even more clever trick to do roughly the same thing:
ALTER TABLE msg_table ALTER COLUMN type TYPE int4 USING 0;
(assuming type is int4, replace with the actual data type if necessary)
This will rewrite the table, similar to a DROP + CREATE, and rebuild all
indexes. But all in one command.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgresql update op is very very slow
@ 2008-06-25 13:50 Greg Smith <gsmith@gregsmith.com>
parent: jay <jackem.mojx@alibaba-inc.com>
2 siblings, 0 replies; 12+ messages in thread
From: Greg Smith @ 2008-06-25 13:50 UTC (permalink / raw)
To: jay <jackem.mojx@alibaba-inc.com>; +Cc: pgsql-performance
On Wed, 25 Jun 2008, jay wrote:
> Why postgresql is so slowly? Is the PG MVCC problem?
Update is extremely intensive not just because of MVCC, but because a
new version of all the rows are being written out. This forces both lots
of database commits and lots of complicated disk I/O to accomplish.
Couple of suggestions:
-Increase checkpoint_segments a lot; start with a 10X increase to 30.
-If you can afford some potential for data loss in case of a crash,
consider using async commit:
http://www.postgresql.org/docs/8.3/static/wal-async-commit.html
> Memory 8G, 8 piece 15K disk , 2CPU(Quad-Core) AMD
Is there any sort of write cache on the controller driving those disks?
If not, or if you've turned it off, that would explain your problem right
there, because you'd be limited by how fast you can sync to disk after
each update. Async commit is the only good way around that. If you have
a good write cache, that feature won't buy you as much improvement.
> bgwriter_delay = 20ms # 10-10000ms between rounds
> bgwriter_lru_maxpages = 500 # 0-1000 max buffers written/round
> bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers
This a bit much and the background writer can get in the way in this
situation. You might turn it off (bgwriter_lru_maxpages = 0) until you've
sorted through everything else, then increase that parameter again. The
combination of 20ms and 500 pages is far faster than your disk system can
possibly handle anyway; 100ms/500 or 20ms/100 (those two are approximately
the same) would be as aggressive as I'd even consider with an 8-disk
array, and something lower is probably more appropriate for you.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ??: Postgresql update op is very very slow
@ 2008-06-26 12:19 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 1 reply; 12+ messages in thread
From: Heikki Linnakangas @ 2008-06-26 12:19 UTC (permalink / raw)
To: jay <jackem.mojx@alibaba-inc.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ??: Postgresql update op is very very slow
@ 2008-06-26 12:40 Holger Hoffstaette <holger@wizards.de>
parent: Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 4 replies; 12+ messages in thread
From: Holger Hoffstaette @ 2008-06-26 12:40 UTC (permalink / raw)
To: pgsql-performance
Hi -
I have been following this thread and find some of the recommendations
really surprising. I understand that MVCC necessarily creates overhead,
in-place updates would not be safe against crashes etc. but have a hard
time believing that this is such a huge problem for RDBMS in 2008. How do
large databases treat mass updates? AFAIK both DB2 and Oracle use MVCC
(maybe a different kind?) as well, but I cannot believe that large updates
still pose such big problems.
Are there no options (algorithms) for adaptively choosing different
update strategies that do not incur the full MVCC overhead?
Holger
(Disclaimer: I'm not a professional DBA, just a curious developer).
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ??: Postgresql update op is very very slow
@ 2008-06-26 13:16 Craig Ringer <craig@postnewspapers.com.au>
parent: Holger Hoffstaette <holger@wizards.de>
3 siblings, 2 replies; 12+ messages in thread
From: Craig Ringer @ 2008-06-26 13:16 UTC (permalink / raw)
To: Holger Hoffstaette <holger@wizards.de>; +Cc: pgsql-performance
Holger Hoffstaette wrote:
> Hi -
>
> I have been following this thread and find some of the recommendations
> really surprising. I understand that MVCC necessarily creates overhead,
> in-place updates would not be safe against crashes etc. but have a hard
> time believing that this is such a huge problem for RDBMS in 2008. How do
> large databases treat mass updates? AFAIK both DB2 and Oracle use MVCC
> (maybe a different kind?) as well, but I cannot believe that large updates
> still pose such big problems.
> Are there no options (algorithms) for adaptively choosing different
> update strategies that do not incur the full MVCC overhead?
I think Pg already does in place updates, or close, if the tuples being
replaced aren't referenced by any in-flight transaction. I noticed a
while ago that if I'm doing bulk load/update work, if there aren't any
other transactions no MVCC bloat seems to occur and updates are faster.
I'd be interested to have this confirmed, as I don't think I've seen it
documented anywhere. Is it a side-effect/benefit of HOT somehow?
--
Craig Ringer
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ??: Postgresql update op is very very slow
@ 2008-06-26 13:53 Andrew Sullivan <ajs@commandprompt.com>
parent: Holger Hoffstaette <holger@wizards.de>
3 siblings, 0 replies; 12+ messages in thread
From: Andrew Sullivan @ 2008-06-26 13:53 UTC (permalink / raw)
To: pgsql-performance
On Thu, Jun 26, 2008 at 02:40:59PM +0200, Holger Hoffstaette wrote:
> large databases treat mass updates? AFAIK both DB2 and Oracle use MVCC
> (maybe a different kind?) as well, but I cannot believe that large updates
> still pose such big problems.
DB2 does not use MVCC. This is why lock escalation is such a big
problem for them.
Oracle uses a kind of MVCC based on rollback segments: your work goes
into the rollback segment, so that it can be undone, and the update
happens in place. This causes a different kind of pain: you can run
out of rollback segments (part way through a long-running transaction,
even) and then have to undo everything in order to do any work at
all. Every system involves trade-offs, and different systems make
different ones. The bulk update problem is PostgreSQL's weak spot,
and for that cost one gets huge other benefits.
> Are there no options (algorithms) for adaptively choosing different
> update strategies that do not incur the full MVCC overhead?
How would you pick? But one thing you could do is create the table
with a non-standard fill factor, which might allow HOT to work its magic.
A
--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ??: Postgresql update op is very very slow
@ 2008-06-26 13:55 Andrew Sullivan <ajs@commandprompt.com>
parent: Craig Ringer <craig@postnewspapers.com.au>
1 sibling, 0 replies; 12+ messages in thread
From: Andrew Sullivan @ 2008-06-26 13:55 UTC (permalink / raw)
To: pgsql-performance
On Thu, Jun 26, 2008 at 09:16:25PM +0800, Craig Ringer wrote:
> I think Pg already does in place updates, or close, if the tuples being
> replaced aren't referenced by any in-flight transaction. I noticed a while
> ago that if I'm doing bulk load/update work, if there aren't any other
> transactions no MVCC bloat seems to occur and updates are faster.
Are you on 8.3? That may be HOT working for you. MVCC doesn't get
turned off if there are no other transactions (it can't: what if
another transaction starts part way through yours?).
A
--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ??: Postgresql update op is very very slow
@ 2008-06-26 14:53 Mark Mielke <mark@mark.mielke.cc>
parent: Holger Hoffstaette <holger@wizards.de>
3 siblings, 0 replies; 12+ messages in thread
From: Mark Mielke @ 2008-06-26 14:53 UTC (permalink / raw)
To: Holger Hoffstaette <holger@wizards.de>; +Cc: pgsql-performance
Holger Hoffstaette wrote:
> Hi -
>
> I have been following this thread and find some of the recommendations
> really surprising. I understand that MVCC necessarily creates overhead,
> in-place updates would not be safe against crashes etc. but have a hard
> time believing that this is such a huge problem for RDBMS in 2008. How do
> large databases treat mass updates? AFAIK both DB2 and Oracle use MVCC
> (maybe a different kind?) as well, but I cannot believe that large updates
> still pose such big problems.
> Are there no options (algorithms) for adaptively choosing different
> update strategies that do not incur the full MVCC overhead?
>
My opinion:
Any system that provides cheap UPDATE operations is either not ACID
compliant, or is not designed for highly concurrent access, possibly
both. By ACID compliant I mean that there both the OLD and NEW need to
take space on the hard disk in order to guarantee that if a failure
occurs in the middle of the transaction, one can select only the OLD
versions for future transactions, or if it fails after the end fo the
transaction, one can select only the NEW versions for future
transactions. If both must be on disk, it follows that updates are
expensive. Even with Oracle rollback segments - the rollback segments
need to be written. Perhaps they will be more sequential, and able to be
written more efficiently, but the data still needs to be written. The
other option is to make sure that only one person is doing updates at a
time, and in this case it becomes possible (although not necessarily
safe unless one implements the ACID compliant behaviour described in the
previous point) for one operation to complete before the next begins.
The HOT changes introduced recently into PostgreSQL should reduce the
cost of updates in many cases (but not all - I imagine that updating ALL
rows is still expensive).
There is a third system I can think of, but I think it's more
theoretical than practical. That is, remember the list of changes to
each row/column and "replay" them on query. The database isn't ever
stored in a built state, but is only kept as pointers that allow any
part of the table to be re-built on access. The UPDATE statement could
be recorded cheaply, but queries against the UPDATE statement might be
very expensive. :-)
Cheers,
mark
--
Mark Mielke <mark@mielke.cc>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ??: Postgresql update op is very very slow
@ 2008-06-26 22:15 Greg Smith <gsmith@gregsmith.com>
parent: Holger Hoffstaette <holger@wizards.de>
3 siblings, 0 replies; 12+ messages in thread
From: Greg Smith @ 2008-06-26 22:15 UTC (permalink / raw)
To: Holger Hoffstaette <holger@wizards.de>; +Cc: pgsql-performance
On Thu, 26 Jun 2008, Holger Hoffstaette wrote:
> How do large databases treat mass updates? AFAIK both DB2 and Oracle use
> MVCC (maybe a different kind?) as well
An intro to the other approaches used by Oracle and DB2 (not MVCC) is at
http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_...
(a URL which I really need to shorten one day).
> Are there no options (algorithms) for adaptively choosing different
> update strategies that do not incur the full MVCC overhead?
If you stare at the big picture of PostgreSQL's design, you might notice
that it usually aims to do things one way and get that implementation
right for the database's intended audience. That intended audience cares
about data integrity and correctness and is willing to suffer the overhead
that goes along with operating that way. There's few "I don't care about
reliability here so long as it's fast" switches you can flip, and not
having duplicate code paths to support them helps keep the code simpler
and therefore more reliable.
This whole area is one of those good/fast/cheap trios. If you want good
transaction guarantees on updates, you either get the hardware and
settings right to handle that (!cheap), or it's slow. The idea of
providing a !good/fast/cheap option for updates might have some
theoretical value, but I think you'd find it hard to get enough support
for that idea to get work done on it compared to the other things
developer time is being spent on right now.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ??: Postgresql update op is very very slow
@ 2008-06-26 22:20 Greg Smith <gsmith@gregsmith.com>
parent: Craig Ringer <craig@postnewspapers.com.au>
1 sibling, 0 replies; 12+ messages in thread
From: Greg Smith @ 2008-06-26 22:20 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: pgsql-performance
On Thu, 26 Jun 2008, Craig Ringer wrote:
> I'd be interested to have this confirmed, as I don't think I've seen it
> documented anywhere. Is it a side-effect/benefit of HOT somehow?
The documentation is in README.HOT, for example:
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/heap/README.HOT?rev=1.3;content-ty...
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2008-06-26 22:20 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-25 03:12 Postgresql update op is very very slow jay <jackem.mojx@alibaba-inc.com>
2008-06-25 05:02 ` Rusty Conover <rconover@infogears.com>
2008-06-25 10:11 ` Heikki Linnakangas <heikki@enterprisedb.com>
2008-06-26 12:19 ` Re: ??: Postgresql update op is very very slow Heikki Linnakangas <heikki@enterprisedb.com>
2008-06-26 12:40 ` Re: ??: Postgresql update op is very very slow Holger Hoffstaette <holger@wizards.de>
2008-06-26 13:16 ` Re: ??: Postgresql update op is very very slow Craig Ringer <craig@postnewspapers.com.au>
2008-06-26 13:55 ` Re: ??: Postgresql update op is very very slow Andrew Sullivan <ajs@commandprompt.com>
2008-06-26 22:20 ` Re: ??: Postgresql update op is very very slow Greg Smith <gsmith@gregsmith.com>
2008-06-26 13:53 ` Re: ??: Postgresql update op is very very slow Andrew Sullivan <ajs@commandprompt.com>
2008-06-26 14:53 ` Re: ??: Postgresql update op is very very slow Mark Mielke <mark@mark.mielke.cc>
2008-06-26 22:15 ` Re: ??: Postgresql update op is very very slow Greg Smith <gsmith@gregsmith.com>
2008-06-25 13:50 ` Greg Smith <gsmith@gregsmith.com>
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