Hi All,
Have anyone have some pointers om some performance tuning on out-of-the-box ITSM v7 (SM to be specific). A client is complaining that his vanilla ITSM is extremely slow. I have not yet been at the site, nor did we implement it. I know ITSM 7 is about 30% more resource intensive than ITSM 6, (Which they were on previously) do you think they met the resource threshold or is there some performance tweaks that you are aware of that could speed up Vanilla ITSM 7? For example, Fast & List Threads, API Threads, etc etc... I must admit, I have not been on site yet but the client wants to downgrade due to bad performance and this is a small market here currently and bad rumors spread fast.

Hi,

I need more information if you want a good response...

How many concurent users?
How many records created/modified per seconds?
How many searches per seconds?
How many records in your forms?
How many CI in your CMDB?
What's your thread configuration?
Do you use private threads for ITSM 7 plugins?
Do you use private threads for reconciliation engine?
Do you use a specific box for reconciliation jobs?
What's your architecture?
What's your hardware sizing for each part (box) of your architecture?
etc...

And what are your performance goals?

Ok, I actually knew you guys are going to ask for more :-D, so let me get back to you. I thought their might be a quick Tweak trick to help them first time.

I know there is no FIXED rule for setting Fast/List Servers, but is there at least a rule of thumb? And also for API Threads (Like when using Web Services)

Thanks for the prompt response :-)

The general quick rule for threads usage is :
* Fast & List min : # of CPUs
* Fast max (no more than) : 3 * # of CPUs
* List max (no more than) : 5 * # of CPUs

No more than 2 of difference between min and max.

Private queues threads configuration depends upon their utilization.

A general rule can be the following : Create private queues for :
* DSO
* Reconciliation Engine
* CAI Plugin
* Webservices
* Specific API programs
* Large imports

Look at ITSM 7 whitepapers. There is a performance whitepaper.

Ok Thanks,

Quick update

Client has ITSM 7 (SM, AM & CM) (Clean Install, phase 2 is to migrate old data to new version)
Hardware:
Solaris 9, Oracle 10g r2
1 x E25K Board
Everything is on one server
Only using SM (Incident Mainly) currently
Max of 13 concurrent users (for now)
Everything is fast except if you submit or modify. Menu's is fast, Window's open up quickly etc etc. Even the Admin tool is instant (except listing 22'000 active links)
As soon as you submit records (all of the record have attachments, which seems to be the culprit) it takes more than a minute to submit.
If I modify (and delete) attachment, it is instant aswell, but if I modify and add, same issue

How does the LOB and CLOB work and as what should it be set etc etc?

Regards

Is it big attachments?

Try to log API and SQL in same log and see if it's a network problem (API and SQL response time are quite fast) or if it's a database problem (SQL response time is quite long)...

Did you create attachment fields on main forms? Why not using the workinfo for attachments? This has been design to prevent attachments on main forms and performance problems...

between 100KB and 400KB (I used to attach 2MB files MUCH faster than this)
Could this be that the Large Object (LOB) is not set up properly, I think it is a definite DB issue, Selects are fast but update and insert is slow (when Large Objects are involved)
They even got a ARS timeout when adding a 800KB attachment
Like I said everything else is fast, Even making changes in the admin is fast. I've created a test form (with attachment) to create a record is less than 1 second, to create an attachment with < 100KB, it takes more than 1 minute.
Where can I read up on the LOB and CLOB (in-row CLOB tickbox in ARAdmin is not ticked).
I'm currently on the go so I don't have all my resources (PDF's and tools) with me which makes it even worse :-) I'm not an Oracle expert so if anyone can help me with this, I would appreciate it dearly.

Thanking you in advance

See with you DBA to improve that. The attachments are stored in BxxxCyyy tables.

See the configuring guide, there are some specific configuration parameters for Attachment tuning on Oracle database. Check these parameters with your DBA...

Check in the arstruct.h file to see if there are no specific undocumented configuration parameters on this point and ask support for the usage of these parameters...

I don't see it?
Only get FTS and B,BC,(T & H) table SQL queries related to attachment fields?

B<schemaid_of_your_form>C<fieldid_of_your_attacment_field>

For example B1234C5678900001 table store the attachment files for the attachment field 5678900001 of the form which have the 1234 schemaID

I Understand the whole DB data storing (T B H Tables)
In the PDF I don't find the settings you were referring to
I only find SQL Scripts on how to update the Request ID, How to Index for FTS etc. Nothing about how to setup LOB.
But never mind that, the DBA gave me some stats and unfortunately I don't understand Greek, (or is that Oracle.. ??):
Can anybody tell me what this means... In English if possible :-p

Individual database segments responsible for significant user I/O wait were
found.

RECOMMENDATION 1: Segment Tuning, 4.3% benefit (1096 seconds)
ACTION: Run "Segment Advisor" on LOB "ARADMIN.SYS_LOB0000054992C00015$$"
with object id 55018.
RELEVANT OBJECT: database object with id 55018
ACTION: Investigate application logic involving I/O on LOB
"ARADMIN.SYS_LOB0000054992C00015$$" with object id 55018.
RELEVANT OBJECT: database object with id 55018
RATIONALE: The I/O usage statistics for the object are: 0 full object
scans, 5629 physical reads, 770 physical writes and 5629 direct
reads.
RATIONALE: The SQL statement with SQL_ID "22qwvwfmk04bm" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 22qwvwfmk04bm


RECOMMENDATION 2: Segment Tuning, 3.7% benefit (933 seconds)
ACTION: Run "Segment Advisor" on LOB "ARADMIN.SYS_LOB0000089156C00012$$"
with object id 89182.
RELEVANT OBJECT: database object with id 89182
ACTION: Investigate application logic involving I/O on LOB
"ARADMIN.SYS_LOB0000089156C00012$$" with object id 89182.
RELEVANT OBJECT: database object with id 89182
RATIONALE: The I/O usage statistics for the object are: 0 full object
scans, 4668 physical reads, 1933 physical writes and 4668 direct
reads.
RATIONALE: The SQL statement with SQL_ID "f4345rw1a1jdu" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID f4345rw1a1jdu

RATIONALE: The SQL statement with SQL_ID "c1sv7qyttcvdn" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID c1sv7qyttcvdn


RECOMMENDATION 3: Segment Tuning, 2.1% benefit (529 seconds)
ACTION: Run "Segment Advisor" on LOB "ARADMIN.SYS_LOB0000054992C00016$$"
with object id 55020.
RELEVANT OBJECT: database object with id 55020
ACTION: Investigate application logic involving I/O on LOB
"ARADMIN.SYS_LOB0000054992C00016$$" with object id 55020.
RELEVANT OBJECT: database object with id 55020
RATIONALE: The I/O usage statistics for the object are: 0 full object
scans, 5627 physical reads, 770 physical writes and 5627 direct
reads.
RATIONALE: The SQL statement with SQL_ID "22qwvwfmk04bm" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 22qwvwfmk04bm


SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(23% impact [5807 seconds])

Some more Greek (Sound like RAID required, Bad Performance on disk, or disks to slow):

The throughput of the I/O subsystem was significantly lower than expected.

RECOMMENDATION 1: Host Configuration, 21% benefit (5446 seconds)
ACTION: Consider increasing the throughput of the I/O subsystem.
Oracle's recommended solution is to stripe all data file using the
SAME methodology. You might also need to increase the number of disks
for better performance. Alternatively, consider using Oracle's
Automatic Storage Management solution.
RATIONALE: During the analysis period, the average data files' I/O
throughput was 12 K per second for reads and 10 K per second for
writes. The average response time for single block reads was 254
milliseconds.

SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(23% impact [5807 seconds])

Ask your DBA for the SQL statments which consume a lot of I/O. He already gives you the SDL_ID of these statments. So you can check which form and request is behind these statments.

The folloging SQL statment can help you and you DBA to find consuming SQL.

I've got the AWR Report already (Don't want to paste it here though) but here is a snippet:

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN prtsm_growth_monitor; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

AND

INSERT INTO TBL_SPACE_CHECK_INFO SELECT TABLESPACE_NAME , SUM(BYTES) / (1024 * 1024 ) , TRUNC(SYSDATE) , DB.NAME FROM DBA_EXTENTS , V$DATABASE DB GROUP BY TABLESPACE_NAME , TRUNC(SYSDATE) , DB.NAME

Both Of these queries takes
7291.80 Elap per Exec (s)
more than 1,233,344,249.00 Gets per Exec
506,334.00 Reads per Exec

They stand out like sore thumbs !!!

Are these queries executed by ARADMIN?

Are your oracle statistics up to date?

I think it's executed by the OracleAdmin user

AWR Report Start/End Dates:
Start Date: 17-Apr-07 00:00:18
End Date: 17-Apr-07 17:00:44

My personal opinion is that they are having Disk I/O problems with the Database

Are the disks configured in asynchronous mode?
Do you have different tablespaces?
Are your tablespaces correctly stripped onto your disks?

Tablespaces?
I thought Remedy only uses one Tablespace namely 'ARSYSTEM'?
Or am I misunderstanding the question? :-p

By default, there are at minimum 3 tablespaces which are very often used by AR System :
* ARSYSTEM
* TEMP
* UNDO

You should ensure you that there are on seperate disks and the TEMP tablespace is on the fastest disks.

You can also have multiple datafile per tablespace which are splitted onto different disks....

You have the possibility to add more tablespaces for AR System data and indexes. For example :
* ARSYS_DATA1
* ARSYS_IDX1
* ARSYS_DATA_ARCH1
* ARSYS_IDX_ARCH1

It's just an example... For some AR System application I had developped we have 16 tablespaces for AR System data and indexes...

Then you can move tables and indexes (using my Advanced Admin Tool which is available in the download section ) or use ardb.conf file to create new objects on the good tablespace or do both : Move objects with the AAT and generate the ardb.conf file with perl script which is available on cummunity site.

Cool, Didn't know you could do that.
Ok so TEMP Tablespace is always used by Remedy? I thought it is only used for installation?

But in the case of putting the Service Desk on a different Tablespace, shouldn't you create the ardb.conf prior to installing the Service Desk?

I assume this wont work:
Form: B123C53680001
Clause:TABLESPACE SectionA
Field {
id:53680001
Clause: NOT FOR REPLICATION
}

No, TEMP tablespace is used by AR System and very often. This is why you need in general a big TEMP tablespace specially if you have a lot of join forms and/or unqualified searches.

Look at the Optimizing and Troubleshooting guide for more complete information.

For ITSM 7 a good value for very small databases (<30GB) can be 1GB...

The form names in ardb.conf should exist within arschema right?

It's impossible for the first installation because you don't know all forms, fields and indexes before installing it...

This is why I had developped the AAT and this tool is in production on many of my customer's site...

If you want the last version (it's stable but not yet fully documented so not yet on the download section ) I'll mail it to you... And I have no time for the moment to do this job

The last version includes :
* Some Windows / SQL Server bug correction. (I finally tested it in this case )
* Reports are available for all table fields

I've got v3.3 :-)