On the 2 socket system you will need I/O that is able to keep up with writing to the WAL – An example is an NVME PCIe SSD formatted as an XFS file system and mounted as below:
/dev/nvme0n1p1 /mnt/ssd xfs noatime
The OS username you are using it can be anything but for people familair with Oracle this becomes the equivalent of the system username. Many people use postgres.
Make sure that your CPU is configured for optimal performance. To do this make sure that the Linux tools package is installed. If so there are a number of tools under the /usr/lib/linux-tools directory under the specific kernel name.
# ls acpidbg cpupower perf turbostat usbip usbipd x86_energy_perf_policy
# ./cpupower frequency-set --governor=performance Setting cpu: 0 Setting cpu: 1 Setting cpu: 2
# ./cpupower frequency-info analyzing CPU 0: driver: intel_pstate CPUs which run at the same hardware frequency: 0 CPUs which need to have their frequency coordinated by software: 0 maximum transition latency: Cannot determine or is not supported. hardware limits: 1000 MHz - 3.80 GHz available cpufreq governors: performance powersave current policy: frequency should be within 1000 MHz and 3.80 GHz. The governor "performance" may decide which speed to use within this range. current CPU frequency: Unable to call hardware current CPU frequency: 1.99 GHz (asserted by call to kernel) boost state support: Supported: yes Active: yes
Also check that the idle settings are enabled and (for Intel CPUs) that the intel_idle driver is used. Note that the pstate and cstate drivers work together to provide the most efficient use of turbo boost. Initially it is best leave all of these states enabled. Disabling all C-states or Idle states is likely to reduce overall performance by reducing the available turbo frequency. You can experiment with the C-states between ‘all or nothing’ to find the optimal settings.
# ./cpupower idle-set --enable-all # ./cpupower idle-info CPUidle driver: intel_idle CPUidle governor: menu analyzing CPU 0: Number of idle states: 4 Available idle states: POLL C1 C1E C6 POLL: Flags/Description: CPUIDLE CORE POLL IDLE Latency: 0 Usage: 10736 Duration: 29182086
# ./x86_energy_perf_policy -rcpu0: EPB 6 cpu0: HWP_REQ: min 38 max 38 des 0 epp 0 window 0x0 (0*10^0us) use_pkg 0 cpu0: HWP_CAP: low 7 eff 10 guar 25 high 38 cpu1: EPB 6 # ./x86_energy_perf_policy performance
# ./x86_energy_perf_policy -rcpu0: EPB 0 cpu0: HWP_REQ: min 38 max 38 des 0 epp 0 window 0x0 (0*10^0us) use_pkg 0 cpu0: HWP_CAP: low 7 eff 10 guar 25 high 38 cpu1: EPB 0
Huge Page Memory Configuration
For the memory we need to set Huge Pages – so as root add the following line to /etc/sysctl.conf we are going to create 64GB of buffers by adding the vm.nr_hugepages line (Huge Pages are 2MB in size).
vm.swappiness = 0 kernel.sem = 250 32000 100 128 fs.file-max = 6815744 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 fs.aio-max-nr = 1048576 vm.nr_hugepages = 35000
postgres soft memlock 100000000 postgres hard memlock 100000000
cat /proc/meminfo HugePages_Total: 35000 HugePages_Free: 6588 HugePages_Rsvd: 4572
To reiterate this will only be when you have reached the stage of starting the database, so firstly you will need to install the software.
Install PostgreSQL from Source
postgres:/mnt/ssd/postgresqlsrc$ ls pgsql postgresql-9.6.5 postgresql-9.6.5.tar
find . -name pg_config.h -print ./src/include/pg_config.h
So it looks like below, this will set the WAL file size to be 1GB instead of the default 16MB.
/* XLOG_SEG_SIZE is the size of a single WAL file. This must be a power of 2 and larger than XLOG_BLCKSZ (preferably, a great deal larger than XLOG_BLCKSZ). Changing XLOG_SEG_SIZE requires an initdb. */ #define XLOG_SEG_SIZE (1024 * 1024 * 1024)
pgsql -> /mnt/ssd/postgresqlsrc/pgsql postgres:/mnt/ssd/postgresqlsrc$ ls -ltr /usr/local total 32 lrwxrwxrwx 1 root root 28 Oct 11 03:41 pgsql -> /mnt/ssd/postgresqlsrc/pgsql
postgres:/usr/local/pgsql$ ls bin data include lib share
./bin/initdb -D ./data
postgres:/mnt/ssd/postgresqlsrc/pgsql/data$ more postgresql.conf listen_addresses ='192.168.1.1' # what IP address(es) to listen on; port = 5432 # (change requires restart) max_connections = 256 # (change requires restart) shared_buffers = 64000MB # min 128kB huge_pages = on # on, off, or try temp_buffers = 4000MB # min 800kB work_mem = 4000MB # min 64kB maintenance_work_mem = 512MB # min 1MB autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem max_stack_depth = 7MB # min 100kB dynamic_shared_memory_type = posix # the default is the first option max_files_per_process = 4000 # min 25 effective_io_concurrency = 32 # 1-1000; 0 disables prefetching wal_level = minimal # minimal, archive, hot_standby, or logical synchronous_commit = off # synchronization level; wal_buffers = 512MB # min 32kB, -1 sets based on shared_buffers #checkpoint_segments = 256 # in logfile segments, min 1, 16MB each checkpoint_timeout = 1h # range 30s-1h checkpoint_completion_target = 1 # checkpoint target duration, 0.0 - 1.0 checkpoint_warning = 0 # 0 disables log_min_messages = error # values in order of decreasing detail: log_min_error_statement = error # values in order of decreasing detail: log_timezone = 'GB' autovacuum = off # Enable autovacuum subprocess? 'on' datestyle = 'iso, dmy' timezone = 'GB' lc_messages = 'en_GB.UTF-8' # locale for system error message lc_monetary = 'en_GB.UTF-8' # locale for monetary formatting lc_numeric = 'en_GB.UTF-8' # locale for number formatting lc_time = 'en_GB.UTF-8' # locale for time formatting default_text_search_config = 'pg_catalog.english' max_locks_per_transaction = 64 # min 10 max_pred_locks_per_transaction = 64 # min 10
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust host all all 192.168.1.1/32 trust host all all 192.168.1.2/32 trust # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres trust #host replication postgres 127.0.0.1/32 trust #host replication postgres ::1/128 trust
Start the PostgreSQL Database
Start the database as follows:
./bin/pg_ctl start -D ./data
And check it is running (remember to check the huge page allocation as well).
postgres:/usr/local/pgsql$ ps -ef | grep post postgres 201539 1 0 Sep19 ? 00:00:00 /mnt/ssd/postgresqlsrc/pgsql/bin/postgres -D ./data postgres 201541 201539 0 Sep19 ? 00:00:57 postgres: checkpointer process postgres 201542 201539 0 Sep19 ? 00:00:02 postgres: writer process postgres 201543 201539 0 Sep19 ? 00:03:18 postgres: wal writer process postgres 201544 201539 0 Sep19 ? 00:00:04 postgres: stats collector process
./bin/pg_ctl stop -D ./data
postgres:/usr/local/pgsql$ ./bin/psql -U postgres -d postgres psql (9.6.5) Type "help" for help. postgres=# alter role postgres password 'postgres'; ALTER ROLE postgres=#
postgres:/usr/local/pgsql$ ./bin/psql -U postgres -d postgres -W Password for user postgres: psql (9.6.5) Type "help" for help. postgres=#
Configure the HammerDB Client
Download and install HammerDB on a test client system, another 2 socket server is ideal. You need the client libraries so you can either copy the postgres lib directory you have just compiled or compile from source again on this host – you don’t need to create a database or set the config files. Then add the library to the library path:
postgres:~$ export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH
and check that it loads with a librarycheck test.
$ ./hammerdbcli HammerDB CLI v3.1 Copyright (C) 2003-2018 Steve Shaw Type "help" for a list of commands The xml is well-formed, applying configuration hammerdb>librarycheck Checking database library for PostgreSQL Success ... loaded library Pgtcl for PostgreSQL