When approaching a performance tuning initiative, it is important to know your system’s expected level of performance and to define measurable performance requirements. Without setting an acceptable threshold for database performance, you will end up chasing a carrot always out of reach. Consider the following when setting performance goals:

1. Baseline Hardware Performance
2. Performance Benchmarks

1. Baseline Hardware Performance
The majority of database performance problems are caused not by the database itself, but by the underlying systems on which the database is running. I/O bottlenecks, memory problems, and network problems can significantly degrade database performance. Therefore, it is important to know the baseline capabilities of your hardware and operating system (OS). This will help you to identify and troubleshoot hardware-related problems before undertaking database-level or query-level tuning initiatives.

2. Performance Benchmarks
In order to maintain good performance or improve upon performance issues, you need to know the capabilities of your DBMS on a defined workload. A benchmark is a predefined workload that produces a known result set, which can then be used for comparison purposes. Periodically running the same benchmark tests can help identify system-related performance degradation over time. Benchmarks can also be used as a comparison to other workloads in an effort to identify queries or applications in need of optimization.

There are many third-party organizations which provide benchmark tools for the database industry, one of those being the Transaction Processing Performance Council (TPC). TPC-H is the ad-hoc, decision support benchmark. This benchmark illustrates decision support systems that examine large volumes of data, execute queries with a high degree of complexity, and give answers to critical business questions.

Understanding Greenplum Database Performance Factors

There are some key performance factors which influence database performance.

1. System Resources
2. Workload
3. Throughput
4. Contention
5. Optimization

Understanding these factors helps identify performance opportunities and avoid problems.

System Resources: Database performance relies heavily on
1. disk I/O
2.memory usage.

To accurately set performance expectations, you need to know the baseline performance of the hardware on which your DBMS is deployed. Performance of hardware components such as CPUs, hard disks, disk controllers, RAM, and network interfaces will significantly affect how fast your database performs.

Workload: The total workload is a combination of user queries, applications, batch jobs, transactions, and system commands directed through the DBMS at any given time.
For example: It can increase when month-end reports are run or decrease on weekends when most users are out of the office.

Workload strongly influences database performance. Knowing your workload and peak demand times helps you plan for the most efficient use of your system resources and enables processing the largest possible workload.

The workload equals the total demand from the DBMS, and it varies over time.

Throughput: A system’s throughput defines its overall capability to process data. DBMS throughput is measured in queries per second, transactions per second, or average response times.

DBMS throughput is closely related to the processing capacity of the underlying systems (disk I/O, CPU speed, memory bandwidth, and so on), so it is important to know the throughput capacity of your hardware when setting DBMS throughput goals.

Contention: Contention is the condition in which two or more components of the workload attempt to use the system in a conflicting way . For example, multiple queries that try to update the same piece of data at the same time or multiple large workloads that compete for system resources.  As contention increases, throughput decreases.

Optimization: DBMS optimizations can affect the overall system performance.

SQL formulation, database configuration parameters, table design, data distribution, and so on enable the database query planner and optimizer to create the most efficient access plans.Checking Query Disk Spill Space Usage

Greenplum Database creates work files on disk if it does not have sufficient memory to execute the query in memory. This information can be used for troubleshooting and tuning queries. The gp_workfile_* views show information about all the queries that are currently using disk spill space. The information in the views can also be used to specify the values for the Greenplum Database configuration parameters gp_workfile_limit_per_query and gp_workfile_limit_per_segment. Let look into details about these views.

1. gp_workfile_entries
2. gp_workfile_usage_per_query
3. gp_workfile_usage_per_segment

1. gp_workfile_entries
This view contains one row for each operator using disk space for workfiles on a segment at the current time. The view is accessible to all users, however non-superusers only to see information for the databases that they have permission to access.

2. gp_workfile_usage_per_query
This view contains one row for each query using disk space for workfiles on a segment at the current time. The view is accessible to all users, however non-superusers only to see information for the databases that they have permission to access.

3. gp_workfile_usage_per_segment
This view contains one row for each segment. Each row displays the total amount of disk space used for workfiles on the segment at the current time. The view is accessible to all users, however non-superusers only to see information for the databases that they have permission to access.