Monthly Archives: December 2013

Advanced Memory Management

Overview
In the Installation Guide, it states (for RHEL 6) that the sysctl.conf file needs the following values:

xfs_mount_options = rw,noatime,inode64,allocsize=16m 
kernel.shmmax = 500000000
kernel.shmmni = 4096
kernel.shmall = 4000000000
kernel.sem = 250 512000 100 2048
kernel.sysrq = 1
kernel.core_uses_pid = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.msgmni = 2048
net.ipv4.tcp_syncookies = 1 
net.ipv4.ip_forward = 0 
net.ipv4.conf.default.accept_source_route = 0 
net.ipv4.tcp_tw_recycle = 1 
net.ipv4.tcp_max_syn_backlog = 4096 
net.ipv4.conf.all.arp_filter = 1 
net.ipv4.ip_local_port_range = 1025 65535 
net.core.netdev_max_backlog = 10000 
vm.overcommit_memory = 2

The kernal.shmmax, kernal.shmall, and vm.overcommit_memory settings are basically allowing the operating system cache management memory. Quoting Luke Lonergan (one of the founders of Greenplum), “The automatic IO cache in Linux will cache the first IO to tables that fit and subsequent queries will use the cache provided the tables aren’t ejected.” This caching is automatic too. So this is very different from Oracle for example, where you manage the “SGA” rather than letting the OS handle the caching.
+1 for Greenplum

This also means it is significantly easier to manage memory. You won’t be running to the init.ora file to manage the SGA but rather, you’ll let the OS handle the caching.
+1 for Greenplum

Eager Free Memory Policy
The “gp_resqueue_memory_policy” setting is in the postgresql.conf file and it set on the master only. You can also set it in your session which may be helpful for large transformation statements.

The default is “eager_free” and was introduced in Greenplum 4.2. It exploits the fact that all slices won’t be executing at the same time. It divides the query plan into stages and memory is distributed to each stage knowing that subsequent slices won’t execute until the blocking operators have completed. Leaving this the default value is typically the best idea.

statement_mem
If you have very large queries that need more memory, you can change statement_mem to use more memory rather than spilling to disk. You can see a query wanting more memory by looking at the explain plan of a query by using “explain analyze”. For example:

EXPLAIN ANALYZE SELECT * FROM .....;

The output will show the plan used but a key item to look for is “bytes wanted”. When you see this, it means that Greenplum had to spill to disk because there wasn’t enough memory available. The best approach is likely to rewrite the query. Alternatively, you can increase the amount of memory available.

The maximum value you can set for statement_mem is determined by “max_statement_mem”. The default max_statement_mem is 2MB.

On the Master, execute the following to increase the statement_mem:

gpconfig -c max_statement_mem -v 16GB
gpstop -u

Now, you can change the memory setting in your session. You can also do this with gpconfig to make the setting for all sessions.

set statement_mem = '8GB';

Re-run your query and see if it executes faster and if it still has “bytes wanted” in the query plan.

Compressed Work Files
If you know you are spilling to disk when executing queries because EXPLAIN ANALYZE showed that more bytes were wanted than available, you can trade CPU for IO by compressing the work files. This is is done with “gp_workfile_compress_algorithm”. The default value is “none” but you can change this to “zlib”. It can be done at the session or with gpconfig to make it system wide.

Temporary Tables
Another way to deal with very large queries that spill to disk is to use temporary tables that are compressed. This is ideal when you use a subquery that is then joined to other tables. If you know it is spilling to disk (again from EXPLAIN ANALYZE showing more bytes wanted than available), you can populate a compressed temporary table instead. For example:

CREATE TEMPORARY TABLE foo
(myid int, bar text)
WITH (APPENDONLY=true, COMPRESSTYPE=quicklz) ON COMMIT DROP 
DISTRIBUTED BY (myid);

Summary
Greenplum is pretty easy to manage memory because it has been designed to leverage the OS caching. The default Eager Free Memory Policy and default statement_mem works very well for most all queries in the database. However, if you do see queries still need more memory than is available, you can increase the statement_mem. If you are still spilling to disk because your statement needs more memory, you can have Greenplum automatically compress work files and/or use compressed temporary tables.