pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedslow table updates
5+ messages / 3 participants
[nested] [flat]
* slow table updates
@ 2003-07-23 00:40 Reece Hart <rkh@gene.COM>
2003-07-23 08:49 ` Re: slow table updates Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 5+ messages in thread
From: Reece Hart @ 2003-07-23 00:40 UTC (permalink / raw)
To: pgsql-performance; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>; SF PostgreSQL <sfpug@postgresql.org>
I'm trying to update a table but it's taking a very long time. I would
appreciate any tips folks may have about ways to speed it up.
The table is paprospect2, as below:
\d paprospect2
Column | Type | Modifiers
-------------+---------+-------------------------------------------------------------------
pfeature_id | integer | not null default nextval('unison.pfeature_pfeature_id_seq'::text)
pseq_id | integer | not null
pftype_id | integer | not null
start | integer |
stop | integer |
confidence | real |
run_id | integer | not null
[snip 13 integer and real columns]
run_id_new | integer |
Indexes: paprospect2_redundant_alignment unique btree (pseq_id, "start", stop, run_id, pmodel_id),
p2thread_p2params_id btree (run_id),
p2thread_pmodel_id btree (pmodel_id)
Foreign Key constraints: pftype_id_exists FOREIGN KEY (pftype_id) REFERENCES pftype(pftype_id) ON UPDATE CASCADE ON DELETE CASCADE,
p2thread_pmodel_id_exists FOREIGN KEY (pmodel_id) REFERENCES pmprospect2(pmodel_id) ON UPDATE CASCADE ON DELETE CASCADE,
pseq_id_exists FOREIGN KEY (pseq_id) REFERENCES pseq(pseq_id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers: p2thread_i_trigger
The columns pfeature_id..confidence and run_id_new (in red) are from an
inherited table. Although the inheritance itself is probably not
relevant here (correction welcome), I suspect it may be relevant that
all existing rows were written before the table definition included
run_id_new. p2thread_i_trigger is defined fires on insert only (not
update).
paprospect2 contains ~40M rows. The goal now is to migrate the data to
the supertable-inherited column with
update paprospect2 set run_id_new=run_id;
The update's been running for 5 hours (unloaded dual 2.4 GHz Xeon w/2GB
RAM, SCSI160 10K drive). There are no other jobs running. Load is ~1.2
and the update's using ~3-5% of the CPU.
$ ps -ostime,time,pcpu,cmd 28701
STIME TIME %CPU CMD
12:18 00:07:19 2.3 postgres: admin csb 128.137.116.213 UPDATE
This suggests that the update is I/O bound (duh) and vmstat supports
this:
$ vmstat 1
procs memory swap io system cpu
r b w swpd free buff cache si so bi bo in cs us sy id
0 1 0 0 11288 94632 3558960 0 0 14 6 12 21 1 0 6
0 1 0 0 12044 94632 3558956 0 0 0 972 332 16 0 1 99
0 1 0 0 11092 94632 3558932 0 0 16 4420 309 25 0 2 97
0 1 0 0 11456 94636 3558928 0 0 0 980 326 23 0 1 99
1 0 0 0 12340 94636 3558924 0 0 16 532 329 14 0 0 100
0 1 0 0 12300 94636 3558916 0 0 0 1376 324 16 1 0 99
0 1 0 0 12252 94636 3558904 0 0 16 1888 325 18 0 0 99
0 1 0 0 11452 94636 3558888 0 0 16 2864 324 23 1 1 98
0 1 0 0 12172 94636 3558884 0 0 0 940 320 12 0 1 99
0 1 0 0 12180 94636 3558872 0 0 16 1840 318 22 0 1 99
0 1 0 0 11588 94636 3558856 0 0 0 2752 312 16 1 2 97
Presumably the large number of blocks written (bo) versus blocks read
(bi) reflects an enormous amount of bookkeeping that has to be done for
MVCC, logging, perhaps rewriting a row for the new definition (a guess
-- I don't know how this is handled), indicies, etc. There's no swapping
and no processes are waiting. In short, it seems that this is ENTIRELY
an I/O issue. Obviously, faster drives will help (but probably only by
small factor).
Any ideas how I might speed this up? Presumably this is all getting
wrapped in a transaction -- does that hurt me for such a large update?
Thanks,
Reece
Bonus diversionary topic: In case it's not obvious, the motivation for
this is that the subtable (paprospect2) contains a column (run_id) whose
definition I would like to migrate to the inherited table (i.e., the
'super-table'). Although postgresql permits adding a column to a
supertable with the same name as an extant column in a subtable, it
appears that such "merged definition" columns do not have the same
properties as a typical inherited column. In particular, dropping the
column from the supertable does not drop it from the subtable (but
renaming it does change both names). Hmm.
--
Reece Hart, Ph.D. rkh@gene.com, http://www.gene.com/
Genentech, Inc. 650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93 http://www.in-machina.com/~reece/
South San Francisco, CA 94080-4990 reece@in-machina.com, GPG: 0x25EC91A0
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: slow table updates
2003-07-23 00:40 slow table updates Reece Hart <rkh@gene.COM>
@ 2003-07-23 08:49 ` Richard Huxton <dev@archonet.com>
2003-07-23 17:44 ` Re: [PERFORM] slow table updates Reece Hart <rkh@gene.COM>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2003-07-23 08:49 UTC (permalink / raw)
To: Reece Hart <rkh@gene.COM>; pgsql-performance; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>; SF PostgreSQL <sfpug@postgresql.org>
On Wednesday 23 July 2003 01:40, Reece Hart wrote:
> I'm trying to update a table but it's taking a very long time. I would
> appreciate any tips folks may have about ways to speed it up.
[snip]
> paprospect2 contains ~40M rows. The goal now is to migrate the data to
> the supertable-inherited column with
>
> update paprospect2 set run_id_new=run_id;
>
>
> The update's been running for 5 hours (unloaded dual 2.4 GHz Xeon w/2GB
> RAM, SCSI160 10K drive). There are no other jobs running. Load is ~1.2
> and the update's using ~3-5% of the CPU.
[snip]
> This suggests that the update is I/O bound (duh) and vmstat supports
> this:
[snip]
> Presumably the large number of blocks written (bo) versus blocks read
> (bi) reflects an enormous amount of bookkeeping that has to be done for
> MVCC, logging, perhaps rewriting a row for the new definition (a guess
> -- I don't know how this is handled), indicies, etc. There's no swapping
> and no processes are waiting. In short, it seems that this is ENTIRELY
> an I/O issue. Obviously, faster drives will help (but probably only by
> small factor).
>
> Any ideas how I might speed this up? Presumably this is all getting
> wrapped in a transaction -- does that hurt me for such a large update?
Well, it needs to keep enought bookkeeping to be able to rollback the whole
transaction if it encounters a problem, or 40M rows in your case. Looks like
you're right and it's an I/O issue. I must admit, I'm a bit puzzled that your
CPU is quite so low, but I suppose you've got two fast CPUs so it shouldn't
be high.
[note the following is more speculation than experience]
What might be happening is that the drive is spending all its time seeking
between the WAL, index and table as it updates. I would also tend to be
suspicious of the foreign keys - PG might be re-checking these, and obviously
that would take time too.
What you might want to try in future:
1. begin transaction
2. drop indexes, foreign keys
3. update table
4. vacuum it
5. recreate indexes, foreign keys etc
6. commit
Now that's just moving the index updating/fk stuff to the end of the task, but
it does seem to help sometimes.
HTH
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [PERFORM] slow table updates
2003-07-23 00:40 slow table updates Reece Hart <rkh@gene.COM>
2003-07-23 08:49 ` Re: slow table updates Richard Huxton <dev@archonet.com>
@ 2003-07-23 17:44 ` Reece Hart <rkh@gene.COM>
0 siblings, 0 replies; 5+ messages in thread
From: Reece Hart @ 2003-07-23 17:44 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>; SF PostgreSQL <sfpug@postgresql.org>
Richard-
Thanks for the suggestions. I too had thought about the FK checks, even
though the columns aren't getting updated.
I'm flabbergasted that the update is still running (~22 hours elapsed).
By comparison, the database takes only 4 hours to recreate from backup!
Something funny is happening here. I just interrupted the update and
will find another way.
But hang on, what's this:
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
30164 compbio 25 0 6056 6056 3568 R 98.8 0.1 29:55 postgres: admin csb [local] COPY
I am the only user and I'm not doing a copy... this must be part of the
update process. Does anyone out there know whether updates do a table
copy instead of in-table udpating (perhaps as a special case for
whole-table updates)?
Of course, I can't help but wonder whether I just killed it when it was
nearly done...
Thanks,
Reece
--
Reece Hart, Ph.D. rkh@gene.com, http://www.gene.com/
Genentech, Inc. 650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93 http://www.in-machina.com/~reece/
South San Francisco, CA 94080-4990 reece@in-machina.com, GPG: 0x25EC91A0
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [PERFORM] slow table updates
@ 2003-07-23 18:07 Reece Hart <rkh@gene.COM>
0 siblings, 0 replies; 5+ messages in thread
From: Reece Hart @ 2003-07-23 18:07 UTC (permalink / raw)
To: Guthrie, Jeremy <jeremy.guthrie@berbee.com>; +Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>; pgsql-performance; SF PostgreSQL <sfpug@postgresql.org>
On Wed, 2003-07-23 at 10:47, Guthrie, Jeremy wrote:
> Have you checked the sizes of your indexes? You may need to rebuild them...
>
> Multiply the relpages colum by 8192.
So, what does this tell me? I'm guessing that you're implying that I
should expect 8192 keys per page, and that this therefore indicates the
sparseness of the key pages. Guessing that, I did:
rkh@csb=> SELECT c2.relname, c2.relpages, c2.relpages*8192 as "*8192",
43413476::real/(c2.relpages*8192) FROM pg_class c, pg_class c2, pg_index i
where c.oid = i.indrelid AND c2.oid = i.indexrelid and c2.relname~'^p2th|^papro'
ORDER BY c2.relname;
relname | relpages | *8192 | ?column?
---------------------------------+----------+------------+--------------------
p2thread_p2params_id | 122912 | 1006895104 | 0.0431161854174633
p2thread_pmodel_id | 123243 | 1009606656 | 0.0430003860830331
paprospect2_redundant_alignment | 229934 | 1883619328 | 0.0230479032332376
What do you make of 'em apples?
Thanks,
Reece
--
Reece Hart, Ph.D. rkh@gene.com, http://www.gene.com/
Genentech, Inc. 650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93 http://www.in-machina.com/~reece/
South San Francisco, CA 94080-4990 reece@in-machina.com, GPG: 0x25EC91A0
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: slow table updates
@ 2003-07-23 18:38 Guthrie, Jeremy <jeremy.guthrie@berbee.com>
0 siblings, 0 replies; 5+ messages in thread
From: Guthrie, Jeremy @ 2003-07-23 18:38 UTC (permalink / raw)
To: Reece Hart <rkh@gene.COM>; +Cc: pgsql-admin@postgresql.org; pgsql-performance; SF PostgreSQL <sfpug@postgresql.org>
Look at it like this(this is how this affected me):
I had a table that use to be the primary home for my data(6 gigs worth). I copied out and copied to another table. I purged and then I 'vacuum full''d the database. After a day things really started going to hell. SLOOOW.. like 30 minutes to run my software versus the 1-5 seconds it normally takes.
The old table is still used but I use it to queue up data. After the data is processed, it is deleted. Mind you that the repurposed 'queue' table usually has no more than 3000-10000 entries in it. Guess what the index size was..... all told I had 7 gigs of indexes. Why? Because vacuum doesn't reoptimize the indexes. If postgresql can't use a deleted row's index entry, it creates a new one. The docs make it sound that if the difference between the values of the deleted rows vs the new row aren't close, it can't use the old index space. Look in the docs about reindexing to see their explanation. So back to my example, my table should maybe be 100K w/ indexes but it was more like 7 gigs. I re-indexed and BAM! My times were sub-second.
Based on the information you have below, you have 3 gigs worth of indexes. Do you have that much data(in terms of rows)?
-----Original Message-----
From: Reece Hart [mailto:rkh@gene.COM]
Sent: Wed 7/23/2003 1:07 PM
To: Guthrie, Jeremy
Cc: pgsql-admin@postgresql.org; pgsql-performance@postgresql.org; SF PostgreSQL
Subject: RE: [PERFORM] slow table updates
On Wed, 2003-07-23 at 10:47, Guthrie, Jeremy wrote:
> Have you checked the sizes of your indexes? You may need to rebuild them...
>
> Multiply the relpages colum by 8192.
So, what does this tell me? I'm guessing that you're implying that I
should expect 8192 keys per page, and that this therefore indicates the
sparseness of the key pages. Guessing that, I did:
rkh@csb=> SELECT c2.relname, c2.relpages, c2.relpages*8192 as "*8192",
43413476::real/(c2.relpages*8192) FROM pg_class c, pg_class c2, pg_index i
where c.oid = i.indrelid AND c2.oid = i.indexrelid and c2.relname~'^p2th|^papro'
ORDER BY c2.relname;
relname | relpages | *8192 | ?column?
---------------------------------+----------+------------+--------------------
p2thread_p2params_id | 122912 | 1006895104 | 0.0431161854174633
p2thread_pmodel_id | 123243 | 1009606656 | 0.0430003860830331
paprospect2_redundant_alignment | 229934 | 1883619328 | 0.0230479032332376
What do you make of 'em apples?
Thanks,
Reece
--
Reece Hart, Ph.D. rkh@gene.com, http://www.gene.com/
Genentech, Inc. 650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93 http://www.in-machina.com/~reece/
South San Francisco, CA 94080-4990 reece@in-machina.com, GPG: 0x25EC91A0
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-07-23 18:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-23 00:40 slow table updates Reece Hart <rkh@gene.COM>
2003-07-23 08:49 ` Richard Huxton <dev@archonet.com>
2003-07-23 17:44 ` Reece Hart <rkh@gene.COM>
2003-07-23 18:07 Re: [PERFORM] slow table updates Reece Hart <rkh@gene.COM>
2003-07-23 18:38 Re: slow table updates Guthrie, Jeremy <jeremy.guthrie@berbee.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