This document describes the best practices for properly tuning and configurig a SQream DB installation, to support data warehouse workloads.

Ignore settings where not applicable

1. BIOS/RAID

  • (For Dell PowerEdge servers) Enable Memory Map I/O Over 4GB

  • Set power profile to maximum performance

  • Set power regulator to high performance mode

  • Enable Intel Turbo Boost and Hyperthreading

  • Disable Intel Virtualization Technology

  • Disable Intel VT-d

  • Disable processor C-States (Minimum processor idle power core state)

  • Set Energy/Performance bias to maximum performance

  • Disable dynamic power capping

  • Set DIMM voltage to Optimized for Performance

  • Set memory power savings mode to Maximum performance

  • Enable ACPI SLIT

  • Set QPI Snoop configuration to Home-Snoop or Early-Snoop

2. OS settings

  • Set number of open files to 500,000:

echo -e "*      	soft 	nproc      	500000\n*      	hard 	nproc   	500000\n*      	soft 	nofile     	500000\n*      	hard 	nofile     	500000"  >> /etc/security/limits.conf
  • Tune kernel by adding lines to /etc/sysctl.conf:

echo -e " fs.file-max=2097152\n vm.dirty_background_ratio = 5 \n vm.dirty_ratio = 10 \n vm.swappiness = 10 \n vm.zone_reclaim_mode = 7 \n vm.vfs_cache_pressure = 200 \n"  >> /etc/sysctl.conf
  • Disable transparent hugepages

echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled
  • Tune NVIDIA Tesla series cards by placing the following lines in /etc/rc.local:

nvidia-persistenced
nvidia-smi -pm 1
nvidia-smi -acp 0
nvidia-smi --auto-boost-permission=0
nvidia-smi --auto-boost-default=0
# Assuming all GPUs are of the same type
nvidia-smi -ac $(SC=`nvidia-smi --query-supported-clocks=mem,gr --format=csv,noheader | head -n1`; echo $SC | awk 'BEGIN { FS=" " } ; { print $1 "," $3 }')