Informix Users: Security Domain Performance Tuning

security Supervisor (4.x - 6.x)
1

Hello!

Maybe you have figured this out already, but we’ve been using BO for 4 years and just figured this out. We have 1500 users and growing and it take 1 user 10 seconds to login to the security domain. Wouldn’t you think the Security Domain would be created with the appropriate indexes for optimal performance? Think again because its do-it-yourself performance tuning on the security domain. We were able to make the following modifications and cut our login time in half. So, here they are:

OBJ_M_DOCUMENTS, Add indexes on M_DOC_N_TYPE, M_DOC_N_REPOID, M_DOC_N_PERSISTENT, M_DOC_N_USERID

TABLE OBJ_M_RESLINK, Add indexes on M_RES_N_LAT, M_RES_N_RESTYPE, M_RES_N_ACTLINKID

TABLE OBJ_M_UNIVDBCST, Add indexes on M_UNID_N_LAT

TABLE OBJ_M_CONNECTION, Add indexes on M_CNTN_N_TYPE

TABLE OBJ_M_GENPAR, Add Indexes on M_GENPAR_N_USERID

TABLE OBJ_M_CONNECTDATA, Add indexes on M_CNTA_N_CNTNID

Also update statistics high on tables, indexes and composite indexes (especially in the OBJ_M_CONNECTDATA). This change improves the performance of Business Objects logins which take 10 seconds, 73 sequential scans and 914,249 locks currently. With the changes, the login time is 5 seconds with 16 sequential scans and 143,000 locks.

I still have a case logged with Business Objects to tell us what to index on the Connection and Connection Data tables as they appear to be the remaining offenders. Hope this helps someone…

Jodie

Listserv Archives (BOB member since 2002-06-25)

2

Well I’m ready to run index my repository database but…does that void our service agreement if we don’t do it with their cooperation/blessing?

Listserv Archives (BOB member since 2002-06-25)

3

Uhhh… yes, unfortunately. They will not “officially” support you if you make any type of change to the repository database. On the other hand, when I was there, we had a couple of customers that told us about the things they did, and we thought they were good things, so we pass them on to Paris. Jodie, you might consider doing that, if you’re so inclined.

Paul

Listserv Archives (BOB member since 2002-06-25)

4

Hi jodie,
We used Quest’s analyzer tool, found a few things we could tweak… It did seem to help (we are in an oracle world over here)…

I know, goes against the rules a bit, but I have also been known to cross a street RIGHT in the middle

Brent

Hello!

Maybe you have figured this out already, but we’ve been using BO for 4 years
and just figured this out. We have 1500 users and growing and it take 1
user 10 seconds to login to the security domain. Wouldn’t you think the
Security Domain would be created with the appropriate indexes for optimal
performance? Think again because its do-it-yourself performance tuning on
the security domain. We were able to make the following modifications and
cut our login time in half. So, here they are:

OBJ_M_DOCUMENTS, Add indexes on M_DOC_N_TYPE, M_DOC_N_REPOID, M_DOC_N_PERSISTENT, M_DOC_N_USERID

TABLE OBJ_M_RESLINK, Add indexes on M_RES_N_LAT, M_RES_N_RESTYPE, M_RES_N_ACTLINKID

TABLE OBJ_M_UNIVDBCST, Add indexes on M_UNID_N_LAT

TABLE OBJ_M_CONNECTION, Add indexes on M_CNTN_N_TYPE

TABLE OBJ_M_GENPAR, Add Indexes on M_GENPAR_N_USERID

TABLE OBJ_M_CONNECTDATA, Add indexes on M_CNTA_N_CNTNID

Also update statistics high on tables, indexes and composite indexes (especially in the OBJ_M_CONNECTDATA). This change improves the performance
of Business Objects logins which take 10 seconds, 73 sequential scans and
914,249 locks currently. With the changes, the login time is 5 seconds with
16 sequential scans and 143,000 locks.

I still have a case logged with Business Objects to tell us what to index on
the Connection and Connection Data tables as they appear to be the remaining
offenders. Hope this helps someone…

Jodie

Listserv Archives (BOB member since 2002-06-25)

5

Thanks for the responses everyone!

I would definitely recommend using some kind of performance utility. Unfortunately, we don’t have anything robust enough here to use with Informix.

It is true that BO will not support changes to the repository, but we’re talking about indexes here. The way the security domain is set up by BO currently, you’re going to see degrading performance as you scale…just as we did. Ten seconds for only one user to login and consuming 1 CPU on a unix box is rediculous and unnecessary if the indexes are set up appropriately.

My personal opinion…for what it’s worth…is that when I create a security domain using Business Objects it should be created with all the necessary indexes for optimal performance. It should not be our job and responsibility to analyze and resolve poor performance due to a lack of indexing in a database created by the tool. For Business Objects to come back and say they don’t support it is a no-win situation for both sides. I cannot say that I am too concerned about that…I have no choice if I want happy users and to continue to scale our deployment.

I’m actually surprised that they haven’t done something about it. How hard can it be to create those indexes when the domain is created?

Jodie

Listserv Archives (BOB member since 2002-06-25)

6

dws.disney.com>

At 03:27 PM 3/24/2000 -0500, Jodie Schott wrote:

My personal opinion...for what it's worth...is that when I create a security domain using Business Objects it should be created with all the necessary indexes for optimal performance. It should not be our job and responsibility to analyze and resolve poor performance due to a lack of indexing in a database created by the tool.

I’m actually surprised that they haven’t done something about it. How hard can it be to create those indexes when the domain is created?

I’m with Jodie. If indexes are needed in a large-scale deploymentment for reasonable response times, the tool should define and set up those indexes!

I further agree that the addition of “missing” indexes should not be construed as being a modification of the repository. It is simply a performance enhancer. It does not modify content.

Anita Craig
Stanford University

Listserv Archives (BOB member since 2002-06-25)

7

Some comments from support team members from Business Objects on this listserv will be greatly appreciated
on this. I know there are a few on this list as some time we get some replies from them here.

Hoping,
Durgesh

At 03:27 PM 3/24/2000 -0500, Jodie Schott wrote:

My personal opinion...for what it's worth...is that when I create a security domain using Business Objects it should be created with all the necessary indexes for optimal performance. It should not be our job and responsibility to analyze and resolve poor performance due to a lack of indexing in a database created by the tool.

I’m actually surprised that they haven’t done something about it. How hard can it be to create those indexes when the domain is created?

I’m with Jodie. If indexes are needed in a large-scale deploymentment for reasonable response times, the tool should define and set up those indexes!

I further agree that the addition of “missing” indexes should not be construed as being a modification of the repository. It is simply a performance enhancer. It does not modify content.

Anita Craig
Stanford University

Listserv Archives (BOB member since 2002-06-25)

8

Going by our Login time as well , where we have several groups existing in our repository we too
face the Delay in Login.

And as expressed by the other Listers also, the absence of indexes would really need
an attention by the Business Objects.

Hope BO would give their Inputs on this

In Anticipation of favorable response
Heston

durgesh_chandra@jpmorgan.com on 03/24/2000 04:01:44 PM

Please respond to BUSOB-L@listserv.aol.com

cc: (bcc: Heston S Pitta)

Some comments from support team members from Business Objects on this listserv will be greatly appreciated
on this. I know there are a few on this list as some time we get some replies from them here.

Hoping,
Durgesh

At 03:27 PM 3/24/2000 -0500, Jodie Schott wrote:

My personal opinion...for what it's worth...is that when I create a security domain using Business Objects it should be created with all the necessary indexes for optimal performance. It should not be our job and responsibility to analyze and resolve poor performance due to a lack of indexing in a database created by the tool.

I’m actually surprised that they haven’t done something about it. How hard can it be to create those indexes when the domain is created?

I’m with Jodie. If indexes are needed in a large-scale deploymentment for reasonable response times, the tool should define and set up those indexes!

I further agree that the addition of “missing” indexes should not be construed as being a modification of the repository. It is simply a performance enhancer. It does not modify content.

Anita Craig
Stanford University

Listserv Archives (BOB member since 2002-06-25)

9

Well, there are Business Objects employees participating in this list I believe. How about following up on Jodie’s suggestions and writing up something suggesting additional appropriate indexes on the repository tables. Our login process takes 30 - 45 seconds.

Also, what about additional indexes to improve universe and report export performance. I opened a case recently on a universe that takes 1.5 hours to export. Admittedly we may have additional issues with this particular universe but indexes certainly couldn’t hurt.

Do we all need to open individual cases or could something standard be available?

Listserv Archives (BOB member since 2002-06-25)

10

One thing about the Universe imports.
Indexing may help there but keep in mind that for a Universe import there is an expansion factor of about 30 for when you export and import Universes. For example a 30KB Universe will be stored in the data base as 900KB of meta-data. The 900KB of data has to be imported first before the 30KB binary file is created. That does not even include your lists of values that may or may not have been purged before exporting.
Simon

Listserv Archives (BOB member since 2002-06-25)

11

Everyone,

Just wanted to give you an update on this highly discussed topic. I just spoke with Business Objects Technical Support regarding the case I opened on this issue. They are in the process of investigating the addition of these indexes and I am awaiting a recommendation from them. I think everyone wanted to know what Business Objects had to say about it and I believe they’ll respond when they get the opportunity to research and recommend a solution. I expect they will do so in a timely manner if not for the pure talk time it received in the listserv which they did notice…perhaps sometime this week (I am an optimist).

Jodie

Listserv Archives (BOB member since 2002-06-25)

12

Hi List,

Date: Mon, 27 Mar 2000 08:46:29 -0500

From: “Clayton, Cindy, HRSVC” cindyclayton@ATT.COM

Well, there are Business Objects employees participating in this list I believe. How about following up on Jodie’s suggestions and writing up something suggesting additional appropriate indexes on the repository tables. Our login process takes 30 - 45 seconds.

Our logins were taking 1 minute plus on WAN links, so we tried to take the login process apart.

It appears that the major factor governing how much work to be done is how many GROUPS the user appears in. E.g. if the user is nested 4 groups down:

COMP
|___UNIT
|____DEPT
|______USER RECORD

The login procedure will execute 4 statements against each of the security elements, such as Universe access, docment access, row level restrictions, repository access etc.etc.

In some cases, because we had used ‘special’ groups for enabling certain access, we had users n as many as 6 groups, leading to over 50 SQL statements in the login.

A flatter, easier structure (we found many unnecessary groups) reduced login times by half!

All the same, Business Objects, it would be nice to finally get some advice on optimising repo perfromance!

Cheers,
Phil Morris

********************************************************************** Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message, and please notify us immediately. Please advise immediately if you or your employer does not consent to Internet email for messages of this kind. Opinions, conclusions and other information expressed in this message are not given or endorsed by my firm or employer unless otherwise indicated by an authorised representative independent of this message.

Listserv Archives (BOB member since 2002-06-25)

13

This is quite a usefull advice. However, our DBA department is not a BusinessObjects Competence Center yet, so they do not have sufficient knowledge to create those indexes with the information in this mail.

Can someone provide more details on how to create those indexes? Or does someone have those index creation scripts already available? Are those individual indexes or combined indexes? In what order should the column be? Etc…

Best regards,
Eric

Date: 3/24/00 13:23

[…] We were able to make the following modifications and cut our login time in half. So, here they are:

OBJ_M_DOCUMENTS, Add indexes on M_DOC_N_TYPE, M_DOC_N_REPOID, M_DOC_N_PERSISTENT, M_DOC_N_USERID

TABLE OBJ_M_RESLINK, Add indexes on M_RES_N_LAT, M_RES_N_RESTYPE, M_RES_N_ACTLINKID

TABLE OBJ_M_UNIVDBCST, Add indexes on M_UNID_N_LAT

TABLE OBJ_M_CONNECTION, Add indexes on M_CNTN_N_TYPE

TABLE OBJ_M_GENPAR, Add Indexes on M_GENPAR_N_USERID

TABLE OBJ_M_CONNECTDATA, Add indexes on M_CNTA_N_CNTNID

Also update statistics high on tables, indexes and composite indexes (especially in the OBJ_M_CONNECTDATA). This change improves the performance of Business Objects logins which take 10 seconds, 73 sequential scans and 914,249 locks currently. With the changes, the login time is 5 seconds with 16 sequential scans and 143,000 locks.

PLEASE NOTE:
The information contained in this electronic mail message is privileged and confidential, and is intended only for use of the addressee. If you are not the intended recipient, you are hereby notified that any disclosure, reproduction, distribution or other use of this communication is strictly prohibited. If you have received this communication in error, please notify the sender by reply transmission and delete the message without copying or disclosing it.

Listserv Archives (BOB member since 2002-06-25)