Investigating and Preventing Performance issues in Greenplum
Post date: Oct 09, 2012 6:15:13 PM
Every database statement executes on several levels:
1. Hardware level (hardware performance)
2. OS level (processes, resource utilization, configuration)
3. Database level (statement, objects involved, execution plans, etc.)
When you note a performance problem in statement execution, verify performance at each level.
Verifying Performance at the Hardware Level
Disk I/O and network performance must be optimal to achieve the best performance. Verify that the hardware components perform according to specifications. This is critical for disk input/output activity and for network activity. Ensure that disk I/O meets the hardware specifications for each segment node. Because Greenplum Database is distributed, network performance is critical to the overall database performance. To help you evaluate your hardware performance, Greenplum provides the gpcheckperf tool. gpcheckperf runs tests on all segments to determine the performance of the disk and network subsystems.
gpcheckperf must be run on a quiet system. This ensures that the results are consistent across runs and with the hardware specifications, and avoids other processes 'stealing' resources, causing a false impression of sub-optimal performance.
Verifying Performance at the OS Level
The operating system manages processes and platform resources. Check the following OS-level items:
1. CPU load: Are Postgres processes CPU-starved by other processes?
2. Memory: Are database processes swapping?
3. Network: Are NICs performing properly and without errors or dropping packets?
OS tools such as vmstat, iostat, top, and netstat -i provide the best picture of OS-level performance. Another good source is the OS log files, including /var/log/messages and dmesg (the kernel ring buffer).
Verifying Performance at the Database Level
Database-level performance problems are generally related to the following:
1. The query execution plan prepared by the Greenplum query optimizer
2. Regular maintenance of the database catalog and user tables (Vacuum)
3. Minimizing data skew
Verify the Query Execution Plan
Performance at the database level is mostly associated with the execution plan that the Greenplum optimizer prepares and executes for a statement. The first step in analyzing query performance problems is to run Explain Analyze on the slow statement and analyze the output. Explain Analyze provides information such as the plan tree, estimated row counts, actual row
counts, and time spent in each plan node. If the statement does not finish, you can collect the Explain output, but the output contains only the plan tree and estimated row counts, no execution statistics. If the estimated row counts and actual row counts in Explain Analyze do not match for table scan nodes, this usually means that the tables are not analyzed. Accurate table statistics are necessary for proper plan generation and proper optimization. If after Explain Analyze the table statistics are not correct, consider increasing the target statistics level for the specific column. See Alter Table in the Greenplum Database Administration Guide for details.
Perform Regular Maintenance on the Catalog and User Tables
Make sure that regular maintenance (Vacuum) is performed on the catalog and user tables. Not performing regular Vacuum operations can result in table „bloat‟ and poor performance. Bloat affects tables that had sizeable numbers of rows deleted or updated, and severely impacts the catalog when many table manipulation operations are performed, such as create, drop,
alter, and so on. This can significantly affect performance on user queries; they require data from the catalog tables for execution.
Check Data Skew
Data skew means that data is not uniformly distributed on all segments. The result is that one segment spends more time working than others, and the entire query finishes slowly; queries finish only when all segments finish their processing. See the Greenplum Database Administration Guide for information about checking for data skew.
Configure and maintain your Greenplum Database cluster properly and it will run fast and keep your users happy!