| T.R | Title | User | Personal
Name | Date | Lines |
|---|
| 1049.1 | 4.8 million page faults to integrate | UKVMS3::PJACKSON | Oracle UK Rdb Support | Thu Feb 13 1997 10:14 | 11 |
| It finished.
SQL>
UKAA19::Peter Jackson 15:01:04 SQL$61 CPU=00:38:16.70 PF=4832274
IO=99240 MEM=1545
I suspect that some data structure that is being sequentially sacnned
has grown too big for memory.
Peter
|
| 1049.2 | | UKVMS3::PJACKSON | Oracle UK Rdb Support | Thu Feb 13 1997 12:03 | 13 |
| It looks like it maybe a Rdb data structure. I tried a commit after the
integrate. It is still running.
UKAA19::Peter Jackson 15:01:04 SQL$61 CPU=00:38:16.70 PF=4832274
IO=99240 MEM=1545
SQL> commit;
UKAA19::Peter Jackson 15:32:01 SQL$61 CPU=00:39:09.50 PF=5187014
IO=105331 MEM=2048
UKAA19::Peter Jackson 17:01:49 SQL$61 CPU=00:44:34.99 PF=7428302
IO=146541 MEM=2048
Peter
|
| 1049.3 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Thu Feb 13 1997 12:56 | 3 |
| Try defining CDD$WAIT as "EXCLUSIVE" before the INTEGRATE.
Ian
|
| 1049.4 | | UKVMS3::PJACKSON | Oracle UK Rdb Support | Fri Feb 14 1997 05:19 | 15 |
| Much better with cdd$wait = exclusive
SQL> integrate database filename device_db
cont> create pathname [.cdd]device_db;
UKAA19::Peter Jackson 08:58:55 SQL$61 CPU=00:00:01.94 PF=2087 IO=790
MEM=712
UKAA19::Peter Jackson 09:44:02 SQL$61 CPU=00:23:29.11 PF=179107
IO=59147 MEM=2048
SQL>
UKAA19::Peter Jackson 10:16:35 SQL$61 CPU=00:34:52.38 PF=404807
IO=86985 MEM=42
But that does not explain why the ECO slowed it down so much.
Peter
|
| 1049.5 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Fri Feb 14 1997 06:56 | 15 |
| ~ But that does not explain why the ECO slowed it down so much.
It might be something to do with the access strategy to the various databases.
This would require a comparison of the STRATEGY output before and after
installing the ECO.
The page faulting is due to keeping DBKEY lists for ALG. When you use
CDD$WAIT EXCLUSIVE it means all the tables updated in the CDD/Repository are
reserved for exclusive access so these lists do not need to be maintained.
Thus less memory is used.
It might be that more rows are read (sequential scan? less selective index
usage?) and so the accumulated DBKEY list is larger.
Ian
|
| 1049.6 | | UKVMS3::PJACKSON | Oracle UK Rdb Support | Fri Feb 14 1997 08:15 | 6 |
| The customer is using a VAX 3100. I was using an Alphaserver 1000. Even
with cdd$wait = exclusive it may be too slow for her.
I will try on V7 to see if that is better.
Peter
|
| 1049.7 | v7 | UKVMS3::PJACKSON | Oracle UK Rdb Support | Fri Feb 14 1997 10:09 | 57 |
| On a VAX 6000-330 with cdd$wait = exclusive and double the working set
I was using on the Alpha, using Rdb 6.1A and CDD 7.0, it still seems to
be doing an excessive number of page faults. I had rdms$debug_flags =
tps.
UKVMS3> define cdd$wait exclusive
UKVMS3> define rdms$debug_flags tps
UKVMS3> sql
SQL>
UKVMS3::_RTA6: 14:27:57 SQL$61 CPU=00:00:15.87 PF=6106 IO=629
MEM=2104
SQL> integrate database filename device_db
cont> create pathname [.cdd]device_db;
.
.
.
Get Retrieval sequentially of relation RDB$CONSTRAINTS
Get Retrieval by index of relation RDB$RELATION_CONSTRAINTS
Index name RDB$RLC_CONSTRAINT_NAME_NDX [1:1] Direct lookup
UKVMS3::_RTA6: 14:37:34 SQL$61 CPU=00:08:41.11 PF=137948 IO=2356
MEM=65500
UKVMS3::_RTA6: 14:40:56 SQL$61 CPU=00:10:31.06 PF=163604 IO=2378
MEM=65500
UKVMS3::_RTA6: 14:50:04 SQL$61 CPU=00:17:04.17 PF=229466 IO=2380
MEM=65500
Conjunct
Match
Outer loop
Get Retrieval by index of relation RDB$RELATIONS
Index name RDB$REL_REL_NAME_NDX [0:0]
Inner loop (zig-zag)
Get Retrieval by index of relation RDB$INDICES
Index name RDB$NDX_REL_NAME_NDX [0:0]
Leaf#01 FFirst RDB$INDEX_SEGMENTS Card=32
BgrNdx1 RDB$NDX_SEG_NAM_FLD_POS_NDX [1:1] Fan=8
%CDD-I-MBLRSYNINFO, unsupported entity - marked Incomplete at mblr
offset 50
.
.
.
%CDD-I-MBLRSYNINFO, unsupported entity - marked Incomplete at mblr
offset 52
Get Retrieval by DBK of relation CDD$$O_RECFLD_REL_S
.
.
.
Conjunct Get Retrieval by index of relation
CDD$$O_FILE_REL_S
Index name CDD$$X_FILE_REL_S_2 [2:2]
Leaf#01 FFirst CDD$$O_4GL_REL Card=0
BgrNdx1 CDD$$X_4GL_REL_2 [2:2] Fan=49
UKVMS3::_RTA6: 15:04:51 SQL$61 CPU=00:26:14.16 PF=258989 IO=3411
MEM=65500
UKVMS3::_RTA6: 15:08:07 SQL$61 CPU=00:29:17.01 PF=259486 IO=3770
MEM=65500
Peter
|
| 1049.8 | | UKVMS3::PJACKSON | Oracle UK Rdb Support | Mon Feb 17 1997 04:28 | 11 |
| Apart from one stage the page faulting was reasonable, though it still
took a long time (VAX 6000, CDD V7, WS 65500).
Conjunct Firstn Get Retrieval by DBK of relation
CDD$$O_RELATION
SQL>
UKVMS3::_RTA6: 09:04:03 SQL$61 CPU=05:18:02.02 PF=329129 IO=113083
MEM=65500
Peter
|
| 1049.9 | Commit is slow, but no constraints | UKVMS3::PJACKSON | Oracle UK Rdb Support | Mon Feb 17 1997 05:55 | 33 |
| What is the commit doing? I thought it might be checking deffered
constraints, but show table (constaints) * shows that there are no
constraints in the CDD database.
SQL> commit;
Conjunct Firstn Get Retrieval by DBK of relation
CDD$$O_DATABASE
UKVMS3::_RTA6: 09:26:58 SQL$61 CPU=05:18:03.05 PF=331107 IO=113104
MEM=65500
Commit_transaction on db: X00000001
Prepare_transaction on db: X00000001
UKVMS3::_RTA6: 09:27:19 SQL$61 CPU=05:18:06.06 PF=331622 IO=113107
MEM=65500
Conjunct Firstn Get
Retrieval by DBK of relation CDD$$O_RECFLD_REL_S
Conjunct Firstn Get
Retrieval by DBK of relation CDD$$O_DATABASE_REL
UKVMS3::_RTA6: 09:28:32 SQL$61 CPU=05:18:34.10 PF=338385 IO=113251
MEM=65500
Conjunct Firstn Get Retrieval by DBK of relation
CDD$$O_RECFLD
UKVMS3::_RTA6: 09:29:48 SQL$61 CPU=05:18:48.43 PF=343837 IO=113502
MEM=65500
UKVMS3::_RTA6: 09:31:46 SQL$61 CPU=05:19:47.03 PF=345601 IO=114493
MEM=65500
UKVMS3::_RTA6: 09:41:01 SQL$61 CPU=05:26:05.91 PF=363230 IO=119841
MEM=65500
UKVMS3::_RTA6: 10:27:19 SQL$61 CPU=06:03:15.81 PF=418197 IO=151048
MEM=65500
UKVMS3::_RTA6: 10:51:55 SQL$61 CPU=06:20:51.32 PF=443284 IO=167169
MEM=65499
Peter
|
| 1049.10 | | UKVMS3::PJACKSON | Oracle UK Rdb Support | Tue Feb 18 1997 10:29 | 36 |
| A test on an Alpha 3000-500 with CDD 6.1-03 and a WSextent of 65536,
gave these results.
UKAA34> define cdd$wait exclusive
UKAA34> sql
SQL>
UKAA34::PJACKSON 11:57:35 SQL$61 CPU=00:00:03.63 PF=2469 IO=1148
MEM=455
SQL> integrate database filename device_db
cont> create pathname [.cdd]device_db;
%CDD-I-MBLRSYNINFO, unsupported entity - marked Incomplete at mblr
offset 50
.
.
.
%CDD-I-MBLRSYNINFO, unsupported entity - marked Incomplete at mblr
offset 52
SQL>
UKAA34::PJACKSON 13:02:24 SQL$61 CPU=00:45:53.03 PF=51931 IO=150914
MEM=4096
SQL> commit;
SQL>
UKAA34::PJACKSON 14:22:09 SQL$61 CPU=01:05:11.04 PF=444570 IO=366285
MEM=4096
SQL>
This was with a repository create special for this integrate.
It looks like it is not going to be feasable for the customer to
integrate her databases on her 32Mbyte VAX 3100.
Can anyone suggest a way to speed this up without using extra
resources?
Peter
|
| 1049.11 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Tue Feb 18 1997 11:42 | 7 |
| You must make sure that WSQUOTA and WSEXTENT remain close in value to avoid
SORT page faulting.
Also have they thought about using the INTEGRATE DOMAIN and INTEGRATE TABLE
commands to do just a few objects at a time?
Ian
|
| 1049.12 | | UKVMS3::PJACKSON | Oracle UK Rdb Support | Tue Feb 18 1997 12:29 | 22 |
| >You must make sure that WSQUOTA and WSEXTENT remain close in value to avoid
>SORT page faulting.
Doubling WSEXTENT whilst leaving WSQUOTA alone reduced the page
faulting by a factor of around 3 so that does not seem to be the
problem here. My WSEXTENT for the latest test equalled the total memory
on her system, but she has not be receptive to suggestions that her
system needs upgrading.
>Also have they thought about using the INTEGRATE DOMAIN and INTEGRATE TABLE
>commands to do just a few objects at a time?
According to the syntax diagram in HELP and the manual those options
can't be used with CREATE PATHNAME.
The integrate is done as part of installing the customer's product.
They restore the database, create a repository and integrate into the
repository. So this is not a one off operation for them. In fact the
installation integrates several databases. Device_db is the slowest to
integrate.
Peter
|
| 1049.13 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Tue Feb 18 1997 13:14 | 4 |
| Did you give any thought to my suggestion in .5? Maybe you can create query
outlines to improve the queries on the CDD tables.
Ian
|
| 1049.14 | | UKVMS3::PJACKSON | Oracle UK Rdb Support | Wed Feb 19 1997 05:18 | 16 |
| >Did you give any thought to my suggestion in .5? Maybe you can create query
>outlines to improve the queries on the CDD tables.
Looking at RMU/SHOW STAT during the pauses in the debug flag output,
it seemed that a lot of the time little Rdb work is being done, though a
lot of CPU was being used and pagefaults were occurring. It seems that
much of the time is spent on actual CDD activity.
The customer is a bit unclear as to which version of CDD it last worked
on. I tried it on 6.1-02 and it fell over with the error we supplied
ECO3 to fix (NOLINK).
The strategies that appear just before the long pauses are retrievals
via DBKEY on single tables, so I don't think they can be improved.
Peter
|
| 1049.15 | | UKVMS3::PJACKSON | Oracle UK Rdb Support | Wed Feb 19 1997 06:40 | 27 |
| I am running the integrate again. This time with Rdb7 and CDD 6.1-03.
The strategies just before pauses in the output are:
Get Retrieval sequentially of relation RDB$CONSTRAINTS
Get Retrieval by index of relation RDB$RELATION_CONSTRAINTS
Index name RDB$RLC_CONSTRAINT_NAME_NDX [1:1] Direct lookup
Conjunct Get Retrieval by index of relation CDD$$O_FILE_REL_S
Index name CDD$$X_FILE_REL_S_2 [2:2]
Leaf#01 FFirst CDD$$O_4GL_REL Card=0
BgrNdx1 CDD$$X_4GL_REL_2 [2:2] Fan=49
Conjunct Firstn Get
Retrieval by DBK of relation CDD$$O_RECFLD_REL_S
Conjunct Firstn Get Retrieval by DBK of relation CDD$$O_RECFLD
Conjunct Firstn Get Retrieval by DBK of relation CDD$$O_RECFLD
Conjunct Firstn Get Retrieval by DBK of relation CDD$$O_RECFLD
Conjunct Firstn Get
Retrieval by DBK of relation CDD$$O_DATABASE_REL
They look too simple for a query outline to have a chance of improving
them.
Peter
|
| 1049.16 | Could the constraints be the problem | UKVMS3::PJACKSON | Oracle UK Rdb Support | Wed Feb 19 1997 10:30 | 13 |
| Rdb V& did not make much difference.
RDB$RELATIONS has 75 rows, RDB$FIELDS has 837 rows, RDB$CONSTRAINTS has
1301 rows.
This seems to be a high number of constraints to me.
Most are of the form
CHECK( F_A_CHANGE_GIVING_COUNT BETWEEN 0 AND 255 )
CHECK( F_COIN_VALUE_DENOM_A BETWEEN 0 AND 65535 )
Peter
|
| 1049.17 | | UKVMS3::PJACKSON | Oracle UK Rdb Support | Wed Feb 19 1997 11:34 | 23 |
| I dropped all the constraints and tried again.
SQL>
UKAA34::_RTA4: 16:25:56 SQL$ CPU=00:00:01.85 PF=1285 IO=644
MEM=435
SQL> integrate database filename device_db
cont> create pathname [.cdd]device_db;
UKAA34::_RTA4: 16:26:33 SQL$ CPU=00:00:02.34 PF=1566 IO=815
MEM=790
SQL>
UKAA34::_RTA4: 16:27:30 SQL$ CPU=00:00:45.86 PF=3664 IO=3268
MEM=2770
SQL> commit;
SQL>
UKAA34::_RTA4: 16:27:49 SQL$ CPU=00:00:46.28 PF=3716 IO=3337
MEM=2770
So it is the constraints that are causing the problem.
I don't think the customer is interested in having the constraints
integrated. She is doing it for DATATRIEVE.
Is it worth asking for an option to ignore constraints on an integrate?
Peter
|
| 1049.18 | | UKVMS3::PJACKSON | Oracle UK Rdb Support | Thu Feb 20 1997 04:13 | 22 |
| I restored the database and dropped just the constraints with a blank
rdb$field_name in rdb$relation_constraints (759 of them, approx 2/3 of
the total). I'll show the strategies for the relation tables.
SQL> integrate database filename device_db
cont> create pathname [.cdd]device_db;
UKAA34::_RTA4: 17:15:16 SQL$ CPU=00:00:01.99 PF=1297 IO=627
MEM=588
Get Retrieval sequentially of relation RDB$CONSTRAINTS
Get Retrieval by index of relation RDB$RELATION_CONSTRAINTS
Index name RDB$RLC_CONSTRAINT_NAME_NDX [1:1] Direct lookup
UKAA34::_RTA4: 17:16:00 SQL$ CPU=00:00:38.00 PF=4607 IO=3009
MEM=2923
SQL>
UKAA34::_RTA4: 17:30:48 SQL$ CPU=00:11:11.00 PF=15195 IO=29235
MEM=4096
SQL> commit;
SQL>
UKAA34::_RTA4: 08:59:33 SQL$ CPU=00:15:26.21 PF=19678 IO=72016
MEM=4096
Peter
|
| 1049.19 | BugNo:455963 | UKVMS3::PJACKSON | Oracle UK Rdb Support | Fri Feb 21 1997 03:04 | 1 |
|
|
| 1049.20 | | M5::LWILCOX | Chocolate in January!! | Fri Feb 21 1997 09:29 | 8 |
| <<< Note 1049.16 by UKVMS3::PJACKSON "Oracle UK Rdb Support" >>>
-< Could the constraints be the problem >-
>> Rdb V& did not make much difference.
Must be some "special" version.
:-).
|
| 1049.21 | | UKVMS3::PJACKSON | Oracle UK Rdb Support | Thu Apr 17 1997 05:21 | 18 |
| We are still trying to find a workaround that the customer will accept.
We have suggested two so far.
1) Drop the constraints. Do the integrate, Add the constraints back in.
This is faster, but still too slow.
2) Restore and integrate a copy of the database with no constraints.
Delete it without letting CDD know. Restore the full database without
integrating.
The customer considers this too complicated for her customer.
They only use CDD for Datatrieve. They only other workaround I can
think off is to get rid of CDD.
Any other ideas would be welcomed.
Peter
|