IBM Tivoli Monitoring > Version 6.3 Fix Pack 2 > Installation Guides > Installation Guide > Performance tuning > Optimizing queries
IBM Tivoli Monitoring, Version 6.3 Fix Pack 2
Define custom queries
Custom queries reduce network traffic, processing at the agent and Tivoli Enterprise Monitoring Server, and memory usage at the Tivoli Enterprise Portal Server and Tivoli Enterprise Portal client. Custom queries accomplish this by limiting the number of rows and columns passed from the Tivoli Enterprise Monitoring Agent to the Tivoli Enterprise Monitoring Server.
Most of the predefined, predefined queries request all columns and all rows of an attribute group, of which only a few might be of interest to you. Removing the unwanted columns (attributes) reduces the amount of data transferred from monitoring agent to Tivoli Enterprise Monitoring client via the portal server, hub monitoring server, and remote monitoring server. Additionally, in the case of OMEGAMON Monitoring Agents located on z/OS, you also reduce the amount of EBCDIC/ASCII conversion required when data is passed between mainframe and distributed platforms.
It is recommended to tune any queries servicing workspaces that are frequently executed or return large quantities of data. Query execution always requires resources and intermittent large reports causes a spike in memory requirements and network resources.
- Restricting the number of rows
- Most predefined queries return all rows and columns. You can create a custom query to filter out the irrelevant or uninteresting metrics. Not only does this make it easier to read the report, but it saves Tivoli Enterprise Portal Server memory, client memory, and CPU because there are fewer rows to translate, sort, and transmit.
You might be using view filters inappropriately. View filters work only on the current page returned from the query. For example, if page size is 100 lines and the filter reduces it to five lines on page 1 and similarly on subsequent pages, the row set cannot be seen on one page. Do not increase the workspace page size to see everything on one page. Increased page size actually increases portal client memory requirements.
Instead, avoid this condition by creating a custom query that filters the data at query execution time.
- Restricting the number of columns
- Most predefined queries return all columns (or attributes). A particular attribute group might contain 50 columns, yet all you need is five. Creating a custom query to retrieve only the required five attributes reduces portal server and client CPU and memory.
- Use the same query in a workspace
- If you have multiple views in a workspace requiring data from different attribute groups, you will need a different query for each group. However, if the views have data from the same attribute group, then use a single query to accommodate both, even if the attributes (columns) that each view requires are different. Two unique queries will each drive data collection at the agent and increase resource overhead. For each workspace, have one query that can be shared by all views using that attribute group. Remember that the entire results set for each query is stored on the portal server, this technique avoids duplicate result sets being stored.
- Collect agent data less frequently
- A good practice is to avoid the use of workspace automatic refresh when possible. The Navigator view and event views (message log, event console, and graphic console) refreshes automatically. This provides you with instantaneous alerts, and you can navigate to their event workspaces with actual data. The graphic view, the graphical equivalent of the Navigator, which shows alerts but no data, affects client memory but not the portal server.
- Reduce the distribution of queries
- A query can be assigned to a particular managed system or to a group of systems by defining a managed system group. The default group usually includes all known instances for that application or operating system.
It might be that you want this query to be applied to only certain managed systems and so distribution to all managed systems is an unnecessary waste of resources. Modify MSLs to reduce the distribution of the query. Also remove the MSLs for queries that are of no interest to the user. Even if you are not viewing the results of the query, there might be a use of system resources that can be avoided by restricting the distribution of the unneeded queries.
Parent topic:
Optimizing queries