The basic method of issue analysis is to identify performance bottlenecks. To identify performance bottlenecks, you need to identify the resource type that ranks the first in terms of the consumption amount and find the reason for the high resource consumption. Then, you can perform a more detailed analysis of the issue.
- System resources: CPU, I/O, network, memory, and disk resources.
- Database resources: locks and caches. The locks include lightweight locks (lwlocks) and other locks. The caches include the cache stored in buffer pools and the Simple Least Recently Used (SLRU) cache.
The common optimization strategy is to analyze the wait events of a database and identify the insufficient resources that cause bottlenecks. Then, identify the issue causes based on the analysis on the resources.