Technical Blog Post
Abstract
Improving Start Center performance in Maximo
Body
Have you experienced some cases where Start Center portlets show spinning disks and everything is so slow in that screen?
Well, you may be facing some performance issues within Start Center and there are some troubleshooting recommendations that I would like to give you.
Logging into Maximo can take a while to load the Start Center. Going to the Start Center from the application takes a while to load. A 'busy' Start Center with many portlets that access the database (for example, Result Set, KPI Graph, KPI Chart, Inbox, etc.) can be slow to load. Every portlet runs queries against database tables and displays the result set in each portlet.
"Is there a query that can tell me which portlets/queries are being used on a user's Start Center?" , I should tell you that there is no easy way to view that info, but you can try this :
SELECT * FROM SCCONFIG WHERE USERID = 'MAXADMIN'
SELECT * FROM SCTEMPLATE WHERE SCTEMPLATEID IN ('X','Y','Z')
* I considered user MAXADMIN and the X Y and Z are the templates associated to him, as per results from the first query.
As promised, here are my recommendations and suggestions for this kind of issue when working with Start Centers. I have compiled my recommendations for an optimal performance of Maximo in regards to the Start Center, and I would like you to consider implementing all of them.
You must follow all the recommendations below in your system, as a guideline or as a starting point to improve your Start Center performance.
Here are some tips for you to get the most from your Start Center experience. Please work with the Start Centers the following way :
1- Maintain records in portlet tables. Determine what users are inactive and delete their start center records. If indexes are not present, put indexes on portlet tables. There should be an index on LAYOUTID for all start center portlet configuration tables.
2- When a user record is deleted or a user is made inactive, we do not delete that user's start center data. Make sure you watch when users are made inactive and delete them from corresponding tables.
3- A high number of queries are fired when a start center is loaded. Start center layout and configuration of a portlet is cached when a user first logs in. There are a number of things that can invalidate the cache:
- Editing a portlet
- Editing the portlet layout
- An user choosing a different start center
- Administrators creating a new template
Make sure a user is not given the same template(s) multiple times. Although the user would not see multiple instances of a template on a start center, following this practice reduces some checking during log in. For administrators, creating a new template invalidates the cache.
4- Do NOT work with many result set portlets in your Start Center. Administrators should make start center templates lean by making sure few templates are not packed with many portlets. It is recommended that only 1-2 result set portlets are configured in any template. Queries being used by the result set portlets should be reviewed.
A large number of portlets on the Start Center will contribute to a slow performance. Use the Start Center tabs so that a limited number of portlets are displayed on any given tab. Some customers have suggested using default Start Centers without any portlets to help alleviate this issue.
5- To help on the Start Center load times, you can also create multiple Security Groups with different Start Centers assigned. Add the users with the 'busy' Start Centers to these groups. When the users log in they will now see tabs on the Start Center. The first tabbed Start Center should not have a lot of portlets. This will allow for faster login times and faster Start Center loads from applications. Configure the other tabs with the other portlets those users need.
6- Periodically monitor a production database during peak load. You can use any appropriate monitoring tools or techniques. Database maintenance consists of tasks such as reorganizing tables and indexes, and archiving and deleting historical data. Database server memory and instance memory must also be monitored.
7- Ensure query tuning on your Maximo environment. Most of the WHERE clauses in queries are generated by individual users. Queries with order by clauses can be expensive, especially list panel queries since these are heavily used. Saved queries are stored in a table named QUERY. You can periodically review these saved queries for inefficient conditions and use of columns that are not indexed, and to remove order by clauses.
In your portlets, regarding Start Center result set and pre-defined queries, it is important to ensure that any start center result set queries are well tuned to avoid performance issues. In general, all pre-defined queries must be well tuned for optimal performance across the system.
You want to prevent users from performing queries that retrieve hundreds of thousands of records. These types of queries adversely affect database performance, causing slower user response times. The Start Center is a heavily used component of Maximo products. As such, it is important to ensure that any start center result set queries are well tuned to avoid performance issues. In general, all pre-defined queries must be well tuned for optimal performance.
For tables with so many records that are site-level, we recommend you to use SITEID in your queries.
For example :
If your query on a Start Center portlet is against the WORKORDER table, then I recommend you to ensure that the query has a WHERE clause with SITEID = 'XXXX'
(replacing XXXX with a valid SITEID for your environment.)
The reason for that is because the WORKORDER table contains records that are site-level records. This way, you ensure the query time is faster than without having SITEID criteria.
8- If you have Live KPIs on Start Centers, you can choose a method of retrieving information. You can run an immediate query to get the information, or you can retrieve the information from a table that is updated by a cron task. The immediate query runs every time the Start Center opens. This approach can cause a long delay in opening the Start Center, and it puts a load on the database. Make sure you periodically review queries that you write for KPIs.
9- Check for SQL efficiency and index usage. The reason for that is because indexing can greatly increase search speeds. Each record of data in the table being searched by a query must have associated data in the index. (DBAs often apply many indexes to a table to enhance searching, and then sometimes find that other activities have slowed. Review the indexes to ensure that you have the right balance for searching and for updating tables.)
10- Another good recommendation not only for Start Center performance, but for general performance is to apply the latest available patch, fix pack, or hot fix for your release of Maximo product. Patches (FixPacks) and hot fixes (IFIX packages) contain fixes for application issues and often contain features that can improve system performance. Do you plan to upgrade your system, or install the latest IFIX/Fixpacks available for your current version?
We always recommend the clients to be at the latest FixPack or IFIX level of Maximo versions they use, because the more up-to-date you are with its version, the more efficient your system will work, due to the fixes that are included.
Upgrading your Maximo system to the latest FixPack or IFIX of it available, avoid many issues that you might be experiencing at the moment or some other issue not discovered by the client because each IFIX package or FixPack release fixes issues from all Maximo applications, and the combination of multiple fixes help to prevent other issues that were not seen yet.
11- Users can be a member of multiple cumulative security groups.
However, users shouldn’t be members of multiple Independent of Other Groups because the login time will be increased since each independent group must be rendered and permissions from each group executed.
If a user is a member of multiple Independent of Other Groups, try to limit that to no more than 2 groups assigned as “Independent of Other Groups” to limit the login time.
Determine why groups should or should not be Independent of Other Groups based on business need.
Create groups based on job function, organization, sites, and business need.
12- In System Properties app, there is a property that can help you. Set the mxe.db.UseSiteListInQuery system property to a value of 1, which improves the start center performance.
By default, it is currently set to 0 in new Maximo environments.
13- There have been a couple of issues found with having special characters in the title of portlets in the startcenter; namely, ( ) % or '.
You can check those portlet titles. Also, many pie charts and bar charts in just one Start Center for users, or specific Start Center templates having issues due to corrupted data in the database.
I'm pretty sure that by implementing all these recommendations together will improve your Start Center experience and allow you to get the most from it !!!
Thanks all for now, folks...
See ya
UID
ibm11112673