Thread: Postgres crashed when adding a sequence column
I have a largish table (71 million rows) to which I needed to add a new
integer column containing a unique identifier - a simple sequence seemed
to be good enough. I discovered the CREATE SEQUENCE command which looked
as if it would do the job, and did the following:
ALTER TABLE intwfs ADD COLUMN id int ;
CREATE SEQUENCE myseq;
UPDATE intwfs SET id = nextval('myseq');
I expected it to take under an hour, but the process was still running
after several hours, taking ~15% cpu and a modest amount of memory.
Later on other users reported the machine was almost unusable and I found
that postmaster was hogging over 99% of cpu and all of memory. I was
about to stop the process, but before I could do that the postmaster
crashed.
Obviously I'm doing something that Postgres doesn't support, but I'm not
quite clear what. Any suggestions on how to achieve the same objective
more easily?
--
Clive Page
Clive Page wrote:
> I have a largish table (71 million rows) to which I needed to add a new
> integer column containing a unique identifier - a simple sequence seemed
> to be good enough. I discovered the CREATE SEQUENCE command which looked
> as if it would do the job, and did the following:
>
> ALTER TABLE intwfs ADD COLUMN id int ;
> CREATE SEQUENCE myseq;
> UPDATE intwfs SET id = nextval('myseq');
Nowt wrong with that.
> I expected it to take under an hour, but the process was still running
> after several hours, taking ~15% cpu and a modest amount of memory.
> Later on other users reported the machine was almost unusable and I found
> that postmaster was hogging over 99% of cpu and all of memory. I was
> about to stop the process, but before I could do that the postmaster
> crashed.
>
> Obviously I'm doing something that Postgres doesn't support, but I'm not
> quite clear what. Any suggestions on how to achieve the same objective
> more easily?
Nothing wrong with what you're doing, however, you are running a
transaction that touches 142 million rows (expiring the old rows and
adding new ones). Still, unless you are particularly short of memory, or
haven't tuned PostgreSQL it should be fine.
Some questions:
1. Is the table particularly wide (i.e. number/size of columns)?
2. Do you have any foreign keys/triggers on the table?
I suspect point 2, but that's just me guessing. What I'm guessing is
that there is a load of pending/deferred triggers working their way through.
--
Richard Huxton
Archonet Ltd
On Wed, Jan 19, 2005 at 09:35:15AM +0000, Clive Page wrote:
> I have a largish table (71 million rows) to which I needed to add a new
> integer column containing a unique identifier - a simple sequence seemed
> to be good enough. I discovered the CREATE SEQUENCE command which looked
> as if it would do the job, and did the following:
>
> ALTER TABLE intwfs ADD COLUMN id int ;
> CREATE SEQUENCE myseq;
> UPDATE intwfs SET id = nextval('myseq');
You're not doing anything unusual there. Do you have any deferred
constrints or foreign keys. I can't think of anything else that might
take memory during the transaction.
The pg_dump output of the schema of that table would clear this up
probably.
Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
Attachment
On Wed, 19 Jan 2005, Richard Huxton wrote: > Nothing wrong with what you're doing, however, you are running a > transaction that touches 142 million rows (expiring the old rows and > adding new ones). Still, unless you are particularly short of memory, or > haven't tuned PostgreSQL it should be fine. > > Some questions: > 1. Is the table particularly wide (i.e. number/size of columns)? Well 28 columns, one varchar, one box, rest 4-byte real/integer. > 2. Do you have any foreign keys/triggers on the table? No triggers, but there's one R-tree on the box, and one B-tree on an integer column. I guess I should have dropped those and re-created afterwards, but the recreation takes some time, so I hoped to avoid the need to do that. I've now managed to restart the server, which took ~1hr to clean itself up, and am doing a VACUUM FULL ANALYZE on the table. Looks as if the data are ok, but the new column is still empty of course. Thanks very much for your help, Richard. Will try again without indices, keeping an eye on the cpu/memory consumption. -- Clive Page Dept of Physics & Astronomy, University of Leicester, Leicester, LE1 7RH, U.K.
Clive Page wrote: > On Wed, 19 Jan 2005, Richard Huxton wrote: > > >>Nothing wrong with what you're doing, however, you are running a >>transaction that touches 142 million rows (expiring the old rows and >>adding new ones). Still, unless you are particularly short of memory, or >>haven't tuned PostgreSQL it should be fine. >> >>Some questions: >>1. Is the table particularly wide (i.e. number/size of columns)? > > Well 28 columns, one varchar, one box, rest 4-byte real/integer. Nothing terribly dramatic. >>2. Do you have any foreign keys/triggers on the table? > > > No triggers, but there's one R-tree on the box, and one B-tree on an > integer column. I guess I should have dropped those and re-created > afterwards, but the recreation takes some time, so I hoped to avoid the > need to do that. Hmm - wonder if there might be some memory leak in updates to the R-tree - it gets used less than B-tree, so it could be. If you reply to this, make sure you mention your version of PG - one of the developers might know more. Probably also worth looking in the documentation for 8.0 and check the release notes section to see if any changes look applicable to your problem. Perhaps a quick search of the -bugs mailing list archives too. -- Richard Huxton Archonet Ltd
On Wed, 19 Jan 2005, Richard Huxton wrote: > Hmm - wonder if there might be some memory leak in updates to the R-tree > - it gets used less than B-tree, so it could be. If you reply to this, > make sure you mention your version of PG - one of the developers might > know more. Probably also worth looking in the documentation for 8.0 and > check the release notes section to see if any changes look applicable to > your problem. That's a possibility. I've now dropped all indices, and am trying again. I forgot to post the version in use, it was 7.4.1 - will try to upgrade to v8.0 soon, as it clearly solves another problem I encountered recently. Thanks for all the help. -- Clive Page Dept of Physics & Astronomy, University of Leicester, Tel +44 116 252 3551 Leicester, LE1 7RH, U.K. Fax +44 116 252 3311
This is just to report success: I dropped all indices and repeated:
UPDATE intwfs SET id = nextval('myseq');
and it worked fine - took 3681 secs (my estimate of an hour wasn't far
out). Now doing a VACUUM FULL to remove the old tuples.
--
Clive Page
Dept of Physics & Astronomy,
University of Leicester, Tel +44 116 252 3551
Leicester, LE1 7RH, U.K. Fax +44 116 252 3311
Richard Huxton <dev@archonet.com> writes:
> Hmm - wonder if there might be some memory leak in updates to the R-tree
Yup, found one. The attached patch is against 7.4.
regards, tom lane
Index: rtree.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/access/rtree/rtree.c,v
retrieving revision 1.80
diff -c -r1.80 rtree.c
*** rtree.c 25 Sep 2003 06:57:57 -0000 1.80
--- rtree.c 24 Jan 2005 02:34:19 -0000
***************
*** 594,599 ****
--- 594,601 ----
rtup = (IndexTuple) index_formtuple(tupDesc,
&(v.spl_rdatum), isnull);
pfree(isnull);
+ pfree(DatumGetPointer(v.spl_ldatum));
+ pfree(DatumGetPointer(v.spl_rdatum));
/* set pointers to new child pages in the internal index tuples */
ItemPointerSet(&(ltup->t_tid), lbknum, 1);