Best Practices for System Performance
<aside> 💡 To download a PDF copy of this TRIRIGAFEEDIA Best Practices Chapter 5: Database Server Tuning & Maintenance, click the attachment below.
</aside>
TRIRIGAFEEDIA_3.x.x_Best_Practices_05.0_Database_Server_Tuning_&_Maintenance_20210414.pdf
The database is central to the functionality of TRIRIGA. This database stores all data that is collected and calculated by the applications. The database also stores metadata for configuring and maintaining the environment. The database server processes all transactions from the applications. The integrated reporting function accesses the data in the database to generate documents. Reporting also generates resource-intensive management reports.
You should apply standard database-tuning techniques to TRIRIGA and periodically monitor production databases during peak load. You can use standard monitoring tools such as those native to the database platform. If necessary, adjust parameters to resolve the bottlenecks as suggested by the monitoring tools.
Indexing a database requires a good understanding of the data, the user functions, and how databases use indexes. Indexes use key parts of data from a table in a binary structure to enhance searching capability. Each record of data in the table must have associated data in the index. Opportunities for indexing should be evaluated on a regular basis by analyzing the TRIRIGA logs using the Performance Analyzer for long running queries and/or using database tools such as Oracle AWR Reports and DB2 snapshots to identify long running queries.
When long running SQL queries are identified, you can use query tuning utilities native to the database platform to identify potential indexes to improve the query response time. Oracle SQL Developer, IBM Data Studio, and Microsoft SQL Server Management Studio each provide SQL tuning utilities. In general, each of the above utilities operate similarly in that the SQL query to be tuned is input into the tool, and the option to tune the query is selected. The utility will then list any potential indexes that can be created to improve query performance. For more information, see the documentation for your database platform.
Indexing can greatly increase search speeds. However, a drawback of indexes is that for each insert, update, or delete, the index must also be updated. Database administrators often apply many indexes to a table to enhance searching, and then sometimes find that other activities have slowed. Review and test all potential indexes to ensure that you have the right balance for searching and for updating tables.
TRIRIGA includes several built-in indexes against IBS_SPEC_ASSIGNMENTS and these should remain. Some additional IBS_SPEC_ASSIGNMENTS indexes are also recommended and these should be added, but analyzed to ensure that the indexes are proven to help. Any additional indexes added to IBS_SPEC_ASSIGNMENTS ****are generally not recommended and should be avoided, because they are particularly prone to cause issues, due to the volatile nature of such a large table in TRIRIGA. However, if an analysis proves that an additional index helps, then it can be added.