Recommended post-installation configuration

Once you’ve installed SQream DB, you can and should tune your system for better performance and stability.

This page provides recommendations for production deployments of SQream DB.

Use a dedicated SQream DB administration account

Create a user for SQream DB, and optionally assign it to the wheel group for sudo access.

$ useradd -m -u 1132 -U sqream
$ passwd sqream
$ usermod -aG wheel sqream

Note

  • The UID (1132 in the example above) is set to ensure all shared files are accessible by all workers.

Configure the OS locale and timezone

  1. Set your OS to use UTF-8, which SQream DB uses for non-English language support.

    $ sudo localectl set-locale LANG=en_US.UTF-8
    
  2. Set the correct timezone for your server. Refer to the list of available timezones to find a timezone that matches your location.

    $ sudo timedatectl set-timezone America/New_York
    

Configure NTP for clock synchronization

SQream DB clusters rely on clock synchronization to function correctly.

$ sudo yum install -y ntp ntpdate
$ sudo systemctl enable ntpd
$ sudo systemctl start ntpd

If your organization has an NTP server, configure it by adding records to /etc/ntpd.conf, reloading the service, and checking that synchronization is enabled:

$ echo -e "\nserver <your NTP server address>\n" | sudo tee -a /etc/ntp.conf
$ sudo systemctl restart ntpd
$ sudo timedatectl
         Local time: Sat 2019-10-12 17:26:13 EDT
     Universal time: Sat 2019-10-12 21:26:13 UTC
           RTC time: Sat 2019-10-12 21:26:13
          Time zone: America/New_York (EDT, -0400)
        NTP enabled: yes
   NTP synchronized: yes
    RTC in local TZ: no
         DST active: yes
    Last DST change: DST began at
                     Sun 2019-03-10 01:59:59 EST
                     Sun 2019-03-10 03:00:00 EDT
    Next DST change: DST ends (the clock jumps one hour backwards) at
                     Sun 2019-11-03 01:59:59 EDT
                     Sun 2019-11-03 01:00:00 EST

Tuning OS parameters for performance and stability

SQream DB requires certain OS parameters to be set on all hosts in your cluster.

These settings affect:

  • Shared memory - Most OS installations may try to limit high throughput software like SQream DB.
  • Network - On high throughput operations like ingest, optimizing network connection parameters can boost performance
  • User limits - SQream DB may open a large amount of files. The default OS settings may cause some statements to fail if the system runs out of file descriptors.
  • Core dump creation rules
  1. Create a directory for core dumps

    In this step, you will create a directory for writing core dumps - which you will configure in the next step.

    $ sudo mkdir /tmp/core_dumps
    

    Note

    Core dumps can be large - up to the size of the system memory (i.e. for a machine with 512GB of RAM, the size of the core dump will be 512GB).

    Make sure the directory has enough space for writing a core dump.

  2. Set sysctl overrides to tune system performance

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    $ sudo tee /etc/sysctl.d/sqreamdb.conf > /dev/null <<EOT
    kernel.sysrq = 1
    kernel.core_uses_pid = 1
    kernel.core_pattern = /tmp/core_dumps/%f-core-%e-%s-%u-%g-%p-%t
    kernel.pid_max = 524288
    vm.max_map_count = 2042292
    vm.dirty_background_ratio = 5
    vm.dirty_ratio = 3
    vm.swappiness = 1
    vm.vfs_cache_pressure = 200
    vm.zone_reclaim_mode = 0
    fs.suid_dumpable = 2
    fs.file-max = 2097152
    EOT
    

    Note

    The settings above include provisioning for core dumps. Core dumps can be a valuable source of information in some scenarios, where stack traces and error logs are not enough.

    By default, the kernel writes core dump files in the current working directory of the process. SQream recommends overriding this setting and write the core dump files to a fixed directory.

    The setting on line 4 uses the directory you created in the previous step (/tmp/core_dumps).

  3. Increase the limit of open files and processes

    $ sudo tee -a /etc/security/limits.conf > /dev/null <<EOT
    * soft nproc 524288
    * hard nproc 524288
    * soft nofile 524288
    * hard nofile 524288
    * soft core unlimited
    * hard core unlimited
    EOT
    
  4. Verify mount options for drives

    SQream recommends XFS for local data storage. The recommended XFS mount options are:

    rw,nodev,noatime,nobarrier,inode64

Note

Reboot your system for the above settings to take effect.

Disable SELinux

SELinux may interfere with NVIDIA driver installation and some SQream DB operations. Unless absolutely necessary, we recommend disabling it.

  1. Check if SELinux is enabled

    $ sudo sestatus
    SELinux status:                 disabled
    
  2. You can disable SELinux by changing the value of SELINUX parameter to disabled in /etc/selinux/config and rebooting.

Secure the server with a firewall

Opening up ports in the firewall

The example below shows how to open up all ports required by SQream DB and related management interfaces. The example also takes into account up to 4 workers on the host.

$ sudo systemctl start firewalld
$ sudo systemctl enable firewalld
$ for p in {2812,3000,3001,3105,3108,5000-5003,5100-5103}; do sudo firewall-cmd --zone=public --permanent --add-port=${p}/tcp; done
$ sudo firewalld --reloadi

Disabling the built in firewall

If not required, you can disable the server’s firewall. This will reduce connectivity issues, but should only be done inside your internal network.

$ sudo systemctl disable firewalld
$ sudo systemctl stop firewalld