Understanding Greenplum Database Performance Factors

Post date: Jan 23, 2014 6:33:45 PM

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.