Pivotal Knowledge Base

Follow

Overview of Memory Tuning Best Practices for Greenplum Database

Environment

Pivotal Greenplum Database (GPDB) running on Red Hat Enterprise Linux (RHEL) 6 or 7

Introduction

The purpose of this guide is to provide an overview of memory tuneables which are required or recommended to adjust on a Greenplum Database cluster. 

Please engage with your system administrator team to discuss best practices specific to your environment.  

Description

Required Tuning

As discussed in our documentation, it is required to adjust two tuneables related to virtual memory overcommitting.

vm.overcommit_memory = 2 (default is 0)
vm.overcommit_ratio = 95 (default is 50)

Additionally, it is required to disable transparent huge pages by adding,  transparent_hugepage=never to the kernel command line.

Recommended Tuning

The following additional memory tuning is recommended:

vm.swappiness = 10 (default is 30)
vm.dirty_expire_centisecs = 500 (default is 3000)
vm.dirty_writeback_centisecs = 100 (default is 500)

For systems with 64GB of memory or less, the following tuning is recommended:

vm.dirty_background_ratio = 3 (default is 10)
vm.dirty_ratio = 10 (default is 30)

And, for systems with more than 64GB of memory, the following tuning is recommended:

vm.dirty_background_bytes = 1610612736 (1.5 GB - Default is 0, as dirty_background_ratio is applied instead)
vm.dirty_bytes = 4294967296 (4GB - Default is 0, as dirty_ratio is applied instead)
vm.dirty_background_ratio=0 (as the background bytes will take effect)
vm.dirty_ratio=0

Applying Tuneables

Please apply all tuneables by adding the relevant line to "/etc/sysctl.conf"You can then apply the changes with the command "# sysctl -p" or by restarting the system. It is not recommended to apply these tuneables while the database is running. Please stop the database, apply the tuneables, and then restart the database.

Further discussion

Overcommit Tuneables

For an overview of the overcommit memory tuneables, please see the guide,

Linux Overcommit strategies and Pivotal Greenplum(GPDB)/Pivotal HDB(HDB)

You can find a calculator which recommends memory tuning settings for the overcommit_ratio value, as well as the gp_vmem_protect_limit in the Greenplum memory calculator.

Swappiness Tuneable

The swappiness tuneable is adjusted so that the system avoids swapping to disk, as this is resource intensive and should be avoided. An algorithm determines the tendency of the operating system to swap at any given memory pressure - setting the tuneable to 10 should be sufficient to avoid swapping in all but the most extreme memory pressure scenarios. 

Dirty Pages Tuneables

The goal of the additional tuneables (dirty writeback and dirty ratio/bytes), is to encourage more active memory management which pushes dirty pages to disk more frequently and in smaller, more manageable increments. 

Because Greenplum Database does not use direct IO, the Linux Kernel will manage writing data to the disk. When the database determines that a page should be written to the disk, the memory address will be marked "dirty". The tuneables related to dirty writeback centisecs determine how often a memory management thread is awoken to write data to disk. Additionally, the dirty ratio/bytes tuneables determine the amount of memory must be marked "dirty" before more extensive action is taken to flush dirty pages. Please see the documentation on kernel.org for more detailed discussion on these tuneables.

For an additional overview, please read the following kernel document pages on kernel.org:

Overcommit Accounting

Sysctl-vm (an overview of all tuneables in /proc/sys/vm)

Comments

Powered by Zendesk