Mechanics of foreign key constraint addition
User migrating large database to Linux was concerned about slow foreign key constraint addition (70% of migration time). Contributors confirmed that when FK columns already have indexes, the main work is validating existing data against parent table constraints, not index creation. User noted Informix lacks Oracle's NOVALIDATE option to skip validation for pre-validated data. No technical resolution provided; PMR was opened.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.50FC9 on x86 Linux I'm migrating a large database to a Linux platform, under a very tight downtime deadline. This is being done by migrating all the data via unload/load for the small tables and HPL for the largest ones; then add the indexes; then re-add all the foreign key constraints. This last part - adding the fks - takes about 70% of the time, with some on the largest foreign keys taking over 2 hours each. I'm trying to understand this better. From the Manual: "When the ALTER TABLE ADD CONSTRAINT statement places a foreign key constraint on a column or on a set of columns that reference a child table, and no referential constraint or user-defined index already exists on that column or on that set of columns, the database server creates an internal B-tree index on the specified column or set of columns. If a user-created index already exists on that column or set of columns, the constraint shares the existing index." All my fks ARE predicated upon a column which is already an index in the parent table. Therefore I infer that the *only* work the fk build is doing is validating the existing data and, when that validation has finished, making a trivial update to the systables. Does that sound a 100% accurate assesmment of what's going on? Thanks Neil
On Sun, Jun 24, 2012 at 5:35 PM, NEIL TRUBY <neil.truby@ardenta.com> wrote: > IDS 11.50FC9 on x86 Linux > > I'm migrating a large database to a Linux platform, under a very tight > downtime deadline. This is being done by migrating all the data via > unload/load for the small tables and HPL for the largest ones; then add the > indexes; then re-add all the foreign key constraints. > > This last part - adding the fks - takes about 70% of the time, with some on > the largest foreign keys taking over 2 hours each. > > I'm trying to understand this better. From the Manual: > "When the ALTER TABLE ADD CONSTRAINT statement places a foreign key > constraint > on a column or on a set of columns that reference a child table, and no > referential constraint or user-defined index already exists on that column > or > on that set of columns, the database server creates an internal B-tree > index > on the specified column or set of columns. If a user-created index already > exists on that column or set of columns, the constraint shares the existing > index." > > All my fks ARE predicated upon a column which is already an index in the > parent table. Therefore I infer that the *only* work the fk build is doing > is > validating the existing data and, when that validation has finished, > making a > trivial update to the systables. > > Does that sound a 100% accurate assesmment of what's going on? > > Thanks > Neil > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > The use of "parent table" confuses me... Usually I refer to "parent table" as the table who has the primary key... the table being referenced. I suppose you're calling "parent table" the table where you're creating the foreign key, and you're saying it already has an index with the column(s) included in the other table's primary key. So, assuming the above, I'd say you're correct. What are you seing while it's running? I/O wait? You could try the BATCHEDREAD* parameters. 11.70 has some features that may be helpful... (just for reference) Regards -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --00248c70fddd508c1c04c33efe9e
Yes, most of the time is being taken up with validating the rows in the dependent table against the parent table's primary key. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Jun 24, 2012 at 12:35 PM, NEIL TRUBY <neil.truby@ardenta.com> wrote: > IDS 11.50FC9 on x86 Linux > > I'm migrating a large database to a Linux platform, under a very tight > downtime deadline. This is being done by migrating all the data via > unload/load for the small tables and HPL for the largest ones; then add the > indexes; then re-add all the foreign key constraints. > > This last part - adding the fks - takes about 70% of the time, with some on > the largest foreign keys taking over 2 hours each. > > I'm trying to understand this better. From the Manual: > "When the ALTER TABLE ADD CONSTRAINT statement places a foreign key > constraint > on a column or on a set of columns that reference a child table, and no > referential constraint or user-defined index already exists on that column > or > on that set of columns, the database server creates an internal B-tree > index > on the specified column or set of columns. If a user-created index already > exists on that column or set of columns, the constraint shares the existing > index." > > All my fks ARE predicated upon a column which is already an index in the > parent table. Therefore I infer that the *only* work the fk build is doing > is > validating the existing data and, when that validation has finished, > making a > trivial update to the systables. > > Does that sound a 100% accurate assesmment of what's going on? > > Thanks > Neil > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340e1b9e8e0804c33fb6aa
>> The use of "parent table" confuses me... Usually I refer to "parent table" as the table who has the primary key... the table being referenced. I suppose you're calling "parent table" the table where you're creating the foreign key, and you're saying it already has an index with the column(s) included in the other table's primary key. Yes, you're right, I had my terminology wrong and your summary above is exactly right. So if Informix had a NOVALIDATE option as Oracle has, and *existing* rows did not have to be validated (as I know if the data is migrated faithfully that they obey the constraint) when the fk constraint was added, I could cut my migration time by around 60%.
True, but AFAIK we don't have any "let the user shoot himself in the feet" features. But I find it a bit strange that the validation itself takes so much time... Did you "debug" it? What is it doing? I/O? Have you setup the batched_* parameters? Tried Read Ahead tuning? Regards On Mon, Jun 25, 2012 at 1:18 AM, NEIL TRUBY <neil.truby@ardenta.com> wrote: > >> The use of "parent table" confuses me... Usually I refer to "parent > table" > as the table who has the primary key... the table being referenced. > I suppose you're calling "parent table" the table where you're creating the > foreign key, and you're saying it already has an index with the column(s) > included in the other table's primary key. > > Yes, you're right, I had my terminology wrong and your summary above is > exactly right. > > So if Informix had a NOVALIDATE option as Oracle has, and *existing* rows > did > not have to be validated (as I know if the data is migrated faithfully that > they obey the constraint) when the fk constraint was added, I could cut my > migration time by around 60%. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf303b40ef8b3be804c3413913
Seemingly endless I/O, buffer waits, mutex waits on the parent .... I actually have a PMR open, 53612 019 866.