iidbpriv table problem?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

William - 30 Aug 2006 15:37 GMT

Hi,
When a connection is made to a database in my Ingres 2 installation I
see hundreds of shared page locks being taken on the iidbdb iidbpriv
catalog. Connections are also quite slow taking between 0.5 and 2 seconds
to make.

1> help table iidbpriv

Name: iidbpriv
Owner: $ingres
Created: 03-sep-1997 13:27:22
Location: ii_database
Type: system catalog
Version: OI2.0
Page size: 2048
Cache priority: 0
Alter table version: 0
Alter table totwidth: 140
Row width: 140
Number of rows: 22993
Storage structure: btree with unique keys
Compression: none
Duplicate Rows: not allowed
Number of pages: 4133
Overflow data pages: 0
Journaling: enabled
Base table for view: yes
Optimizer statistics: yes; see avg count below, more info in the iistats
catalog

Column Information:
Key Avg
Count
Column Name Type Length Nulls Defaults Seq Per
Value
dbname char 32 no yes 3
295.9
grantee char 32 no yes 1
65.0
gtype integer 2 no yes 2
11835.5
dbflags integer 2 no yes
23671.0
control integer 4 no yes
11835.5
flags integer 4 no yes
7890.3
qrow_limit integer 4 no yes
23671.0
qdio_limit integer 4 no yes
23671.0
qcpu_limit integer 4 no yes
23671.0
qpage_limit integer 4 no yes
23671.0
qcost_limit integer 4 no yes
23671.0
idle_time_limit integer 4 no yes
23671.0
connect_time_limit integer 4 no yes
23671.0
priority_limit integer 4 no yes
23671.0
reserve char 32 no yes
23671.0

Secondary indexes: none

I have approx 460 users each granted to 50 databases.
Running sysmod and optimizedb hasn't seemed to help the 'problem'.
Should I be concerned about this?
Connections on similar hardware but with less ingres users configured are
much quicker to execute.

Regards.

Will

Reply to this Message

Karl & Betty Schendel - 30 Aug 2006 18:26 GMT

>Hi,
> When a connection is made to a database in my Ingres 2 installation I
[quoted text clipped - 5 lines]
>Running sysmod and optimizedb hasn't seemed to help the 'problem'.
>Should I be concerned about this?

Ingres does a bunch of reads out of iidbpriv; a read for a
(user, grant-type, database) key and another with a blank
database name, for "user" = the real user, the group specified
if any, public, and the role specified if any. It also does
another, similar pair of reads for every role in the system,
I think. (That's in case of "set role" in the session.)
So it will always do at least 4 reads and possibly many more.

All of these are fully keyed unique record reads, though, and
none should do table scans, so the total size of iidbpriv should
not matter all that much. I'm guessing that either your
2K buffer cache is under-configured, or Ingres is being too
quick to throw away cached pages when iidbdb is closed.

Try this: open a terminal monitor session to iidbdb and let it
just sit there. Does that make connections go any faster?
If so, that might be the simplest workaround.

Karl

Reply to this Message

Karl & Betty Schendel - 30 Aug 2006 18:26 GMT

Reply to this Message

William - 30 Aug 2006 21:55 GMT

> Ingres does a bunch of reads out of iidbpriv; a read for a
> (user, grant-type, database) key and another with a blank
[quoted text clipped - 3 lines]
> I think. (That's in case of "set role" in the session.)
> So it will always do at least 4 reads and possibly many more.

I have305 roles defined so maybe that's part of the problem.

> Try this: open a terminal monitor session to iidbdb and let it
> just sit there. Does that make connections go any faster?
> If so, that might be the simplest workaround.

Running a test with sql connecting to iidbdb in the backround speeds up
database connections by 39% :-)

Reply to this Message