Basic performance parameter setting for BigAnimal cluster

The article will offer an overview of how to adjust the parameter to maximize BigAnimal cluster performance.

What is Performance parameter Tuning?

Performance tuning is the process of the parameters tuning of a BigAnima cluster to increase the throughput of a database system. Using best practices of BigAnimal to find and solve problems, increase query performance and increase database response time. In performance tests, requirements should be identified and tested. Typical parameters include processing speed, data transfer rates, network bandwidth and throughput, workload efficiency and reliability.

Why Performance parameter Tuning?

1. An application’s speed is directly related to how fast the database can handle and serve queries. With a high-performance database, query execution time is significantly reduced, allowing the application to process more requests at once and deliver data more quickly.
2. The database must grow to accommodate an application's increased need to handle user loads and data volumes. An efficient PostgreSQL database can handle growing workloads and keep the application responsive and operating smoothly even with high usage.
3. You can optimize your PostgreSQL database while preserving functionality by enabling the efficient use of system resources including CPU, RAM, and disk I/O. This reduces the amount of hardware needed, which saves money, and it may also lower cloud computing costs.

By modifying configurations and putting best practices into effect, PostgreSQL performance optimization seeks to increase the effectiveness of a PostgreSQL database system by locating and eliminating bottlenecks, speeding up queries, and maximizing database throughput and responsiveness.

BigAnimal cluster basic performance parameters

You will need to set following parameters through BigAnimal portal

• work_mem - The work_mem value defaults to 4MB in Postgres, and that's likely a bit low. This means that per Postgres activity (each join, some sorts, etc.) can consume 4MB before it starts spilling to disk. When Postgres starts writing temp files to disk, obviously things will be much slower than in memory. You can find out if you're spilling to disk by searching for temporary file within your PostgreSQL logs when you have log_temp_files enabled. If you see temporary file, it can be worth increasing your work_mem.
• max_wal_size - The max_wal_size parameter tells PostgreSQL what the approximate maximum total size of the stored WAL segments should be. Once the allowed space is exhausted, a checkpoint is requested so that the space can be recycled.
• wal_buffers - The amount of shared memory used for WAL data that has not yet been written to disk. The default setting of -1 selects a size equal to 1/32nd (about 3%) of shared_buffers, but not less than 64kB nor more than the size of one WAL segment, typically 16MB. If this value is specified without units, it is taken as WAL blocks, typically 8KB. The contents of the WAL buffers are written out to disk at every transaction commit, so extremely large values are unlikely to provide a significant benefit. However, setting this value to at least a few megabytes can improve write performance by reducing the frequency of WAL writes on a busy server. If you have limited memory available, you may want to keep the wal_buffer size lower to avoid excessive memory usage.
• checkpoint_timeout - Is a configuration parameter defining the maximum time between automatic WAL checkpoints.
• checkpoint_segments - Just a number of how many WAL segment can be filled before checkpoint will be forced.
• checkpoint_warning - If checkpoint happens more often than this (which is the same as timeout) warn about it in logs. This is to see situation when we have too few checkpoint_segments.
• Checkpoint_completion_target - This parameter aims to distribute checkpoint writing activities to decrease their impact on the overall performance of the database(Mostly I/O related). It’s generally advantageous to spread out checkpoints as much as possible to prevent I/O spikes.

Checkpoints

Checkpoints are fairly expensive. Checkpoints are run periodically (the frequency of requested checkpoints is defined by checkpoint_timeout), and when they complete in time and the next one naturally begins it’s considered a timed checkpoint. If checkpoints are calculating too slow or out of tune, they will be requested checkpoints.

Requested checkpoints are almost always bad, and should only occur occasionally with very high load. You can see information about how many of each there are with this query:

bdrdb=> SELECT checkpoints_timed, checkpoints_req, stats_reset 
 FROM pg_stat_bgwriter;
 checkpoints_timed | checkpoints_req |          stats_reset          
-------------------+-----------------+-------------------------------
               549 |               7 | 2024-01-24 11:57:03.876955+00

Performance test

We have run the performance test by using the HammerDB with the below parameter setting and instance type

AWS r5b.2xlarge, 8vCPU, 64GB RAM