Product Master side checks

Any task, whether it is a request to display an item or whether it is running an import job, will trigger some type of workload such as:

  • item details (attribute-, spec-, category-, security- and so on type details) need to be retrieved from the local cache, or
  • when details are not available, they need to be retrieved from the database server by using SQL, and
  • retrieved information needs to be processed, this includes running container scripts, attribute value rules or other custom scripts, which can be quite resource intensive, and
  • data packets need to be sent to the client.
There are different factors to consider which can influence the performance on Product Master side:
  • Are cache settings sufficiently high so that SQL statements that are to retrieve information from the database can be avoided? See Tools for troubleshooting.
  • What is the SQL footprint when facing performance issues? Use the Sumall utility, see Sumall utility to collect SQL workload and performance statistics, to analyze the db.log files collected in debug mode to see whether there are many long running SQLs accounting for most of the runtime or whether it is rather the accumulated runtime of fast performing SQLs impacting the overall performance. Too many SQLs of the same type might be an indicator for customization issues (complex custom scripts). SQL footprint might be problematic if for a defined use case the amount of total SQL runtime for the respective Product Master service accounts for a high portion of the monitored time period.
  • Are there any CPU or memory bottlenecks on the server, which throttle overall performance? You would need to use tools like nmon, vmstat, top, topas to track the physical resource usage.
  • Is the respective Product Master service JVM started with the right options to allow optimal performance (for example, memory settings and garbage collection policy)? See System tuning for more information.
  • What is the complexity of involved custom scripts (for example, container scripts, attribute value rules, import script logic, and so on). This can result in both retrieval of large amounts of data from the database and high CPU usage of the respective Product Master service JVM. The following two methods can be used to diagnose such problems:
    • monitor the SQL workload using Sumall utility
    • profile the respective services JVM to see which functions contribute most to the overall workload
    See Monitoring Java virtual machine (JVM) for more information.
    See JVM monitoring section in the Troubleshooting IBM Product Master.