pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
slow 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