{"id":13,"date":"2018-10-08T14:50:00","date_gmt":"2018-10-08T14:50:00","guid":{"rendered":""},"modified":"2018-12-10T14:29:52","modified_gmt":"2018-12-10T14:29:52","slug":"hammerdb-best-practice-for-postgresql-performance-and-scalability","status":"publish","type":"post","link":"https:\/\/www.hammerdb.com\/blog\/uncategorized\/hammerdb-best-practice-for-postgresql-performance-and-scalability\/","title":{"rendered":"HammerDB Best Practice for PostgreSQL Performance and Scalability"},"content":{"rendered":"
This post gives a HOWTO guide on system configuration for achieving top levels of performance with the HammerDB PostgreSQL TPC-C test.\u00a0\u00a0<\/span>As an Intel employee (#IAMINTEL) the examples are taken from a PostgreSQL on Linux on Intel system, the approach is the same for whatever system you are testing although some of the settings you see may be different.<\/span><\/div>\n
<\/div>\n
Firstly for system choice a 2 socket system is optimal for PostgreSQL OLTP performance at the time of writing. This limitation is at the database level rather than the hardware level, nevertheless with up to date hardware (from mid-2018) PostgreSQL on a 2 socket system can be expected to deliver more than 2M PostgreSQL TPM and 1M NOPM with the HammerDB TPC-C test.\u00a0<\/span>
\n<\/span><\/p>\n
I\/O<\/span><\/h6>\n

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:\u00a0<\/span><\/p>\n

\/dev\/nvme0n1p1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/mnt\/ssd\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 xfs\u00a0\u00a0\u00a0\u00a0 noatime<\/span><\/pre>\n<\/div>\n
\n
The OS\u00a0\u00a0username you are using it can be anything but for people familair with Oracle this becomes the equivalent of the system username. <\/span>\u00a0Many people use\u00a0<\/span>postgres.<\/span><\/span><\/p>\n
CPU Configuration<\/span><\/h6>\n
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\u00a0<\/span>\/usr\/lib\/linux-tools directory under the specific kernel name.\u00a0<\/span><\/p>\n<\/div>\n
\n
\r\n# ls\r\nacpidbg\u00a0 cpupower\u00a0 perf\u00a0 turbostat\u00a0 usbip\u00a0 usbipd\u00a0 x86_energy_perf_policy<\/span><\/pre>\n
With these installed set the cpufreq governor to performance as follows:<\/span><\/div>\n
# .\/cpupower frequency-set --governor=performance<\/span>\r\nSetting cpu: 0<\/span>\r\nSetting cpu: 1<\/span>\r\nSetting cpu: 2<\/span><\/span><\/pre>\n
Check that the settings have been applied and the frequency settings as expected. From the following output key things to check are that the driver is shown as intel_pstate (for Intel CPUs), the governor shows as performance, the frequency range goes to the maximum frequency for the CPU and boost state is supported (if your CPU supports turbo boost).\u00a0<\/span><\/div>\n
# .\/cpupower frequency-info<\/span>\r\nanalyzing CPU 0:<\/span>\r\n\u00a0 driver: intel_pstate<\/b><\/span>\r\n\u00a0 CPUs which run at the same hardware frequency: 0<\/span>\r\n\u00a0 CPUs which need to have their frequency coordinated by software: 0<\/span>\r\n\u00a0 maximum transition latency:\u00a0 Cannot determine or is not supported.<\/span>\r\n\u00a0 hardware limits: 1000 MHz - 3.80 GHz<\/span>\r\n\u00a0 available cpufreq governors: performance powersave<\/span>\r\n\u00a0 current policy: frequency should be within 1000 MHz and 3.80 GHz<\/b>.<\/span>\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 The governor \"performance\"<\/b> may decide which speed to use<\/span>\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 within this range.<\/span>\r\n\u00a0 current CPU frequency: Unable to call hardware<\/span>\r\n\u00a0 current CPU frequency: 1.99 GHz (asserted by call to kernel)<\/span>\r\n\u00a0 boost state support:<\/b><\/span>\r\n\u00a0 \u00a0 Supported: yes<\/b><\/span>\r\n\u00a0 \u00a0 Active: yes<\/b><\/span><\/span><\/pre>\n
\n
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. D<\/span>isabling 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.\u00a0<\/span><\/p>\n<\/div>\n
# .\/cpupower idle-set --enable-all<\/span>\r\n# .\/cpupower idle-info<\/span>\r\nCPUidle driver: intel_idle<\/b><\/span>\r\nCPUidle governor: menu<\/span>\r\nanalyzing CPU 0:<\/span>\r\nNumber of idle states: 4<\/span>\r\nAvailable idle states: POLL C1 C1E C6<\/span>\r\nPOLL:<\/span>\r\nFlags\/Description: CPUIDLE CORE POLL IDLE<\/span>\r\nLatency: 0<\/span>\r\nUsage: 10736<\/span>\r\nDuration: 29182086<\/span><\/span><\/pre>\n
You are not quite done. There is another tool in the directory called x86_energy_perf_policy that determines how the boost states are used. By default this is set to normal so you will want to set it to performance.\u00a0<\/span>
\n<\/span><\/div>\n
\n
# .\/x86_energy_perf_policy -rcpu0: EPB 6<\/b><\/span>\r\ncpu0: HWP_REQ: min 38 max 38 des 0 epp 0 window 0x0 (0*10^0us) use_pkg 0<\/span>\r\ncpu0: HWP_CAP: low 7 eff 10 guar 25 high 38<\/span>\r\ncpu1: EPB 6<\/span>\r\n# .\/x86_energy_perf_policy performance<\/span><\/span><\/pre>\n<\/div>\n
# .\/x86_energy_perf_policy -rcpu0: EPB 0<\/b><\/span>\r\ncpu0: HWP_REQ: min 38 max 38 des 0 epp 0 window 0x0 (0*10^0us) use_pkg 0<\/span>\r\ncpu0: HWP_CAP: low 7 eff 10 guar 25 high 38<\/span>\r\ncpu1: EPB 0<\/span><\/span><\/pre>\n
As the name indicates the turbostat tool can be used to monitor the CPU turbo frequencies.\u00a0<\/span><\/div>\n
<\/div>\n
\n
Huge Page Memory Configuration<\/span><\/h6>\n<\/div>\n<\/div>\n
For the memory we need to set Huge Pages \u2013 so as root add the following line to <\/span>\/etc\/sysctl.conf we are going to create 64GB of buffers by adding the vm.nr_hugepages line (Huge Pages are 2MB in size).\u00a0<\/span><\/span><\/p>\n
vm.swappiness = 0<\/span>\r\nkernel.sem = 250 32000 100 128<\/span>\r\nfs.file-max = 6815744<\/span>\r\nnet.ipv4.ip_local_port_range = 9000 65500<\/span>\r\nnet.core.rmem_default = 262144<\/span>\r\nnet.core.rmem_max = 4194304<\/span>\r\nnet.core.wmem_default = 262144<\/span>\r\nnet.core.wmem_max = 1048576<\/span>\r\nfs.aio-max-nr = 1048576<\/span>\r\nvm.nr_hugepages = 35000<\/b><\/span><\/span><\/pre>\n
 \u00a0<\/span>Also edit \/etc\/security\/limits.conf and add the following: <\/span><\/div>\n
postgres soft memlock 100000000<\/span>\r\npostgres hard memlock 100000000<\/span><\/span><\/pre>\n
Now run the command \u201c<\/span>sysctl \u2013p<\/span>\u201d as root, when the database is running you will see the memory allocated from huge pages.\u00a0<\/span><\/span><\/div>\n
cat \/proc\/meminfo<\/span>\r\nHugePages_Total:\u00a0\u00a0 35000<\/span>\r\nHugePages_Free:\u00a0\u00a0\u00a0\u00a0 6588<\/span>\r\nHugePages_Rsvd:\u00a0\u00a0\u00a0\u00a0 4572<\/span><\/span><\/pre>\n
\n
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.<\/span><\/span><\/p>\n
Install PostgreSQL from Source<\/span><\/h6>\n<\/div>\n
Make sure that you have the necessary software to compile PostgreSQL from source<\/span><\/a>, download the postgresql source and extract it to your PostgreSQL file system configured above. Although 9.6.5 has been used here, feel free to download and use the latest release.\u00a0<\/span><\/div>\n
postgres:\/mnt\/ssd\/postgresqlsrc$ ls<\/span>\r\npgsql\u00a0 postgresql-9.6.5\u00a0 postgresql-9.6.5.tar<\/span><\/span><\/pre>\n
 \u00a0<\/span>Find and change change the file pg_config.h (after having run configure first)<\/span><\/div>\n
find . -name pg_config.h -print\r\n<\/span>.\/src\/include\/pg_config.h<\/span> <\/span><\/pre>\n
 \u00a0<\/span>So it looks like below, this will set the WAL file size to be 1GB instead of the default 16MB.\u00a0<\/span><\/span>
\n<\/span><\/p>\n
\/* XLOG_SEG_SIZE is the size of a single WAL file. This must be a power of 2<\/span>\r\n\u00a0\u00a0 and larger than XLOG_BLCKSZ (preferably, a great deal larger than<\/span>\r\n\u00a0\u00a0 XLOG_BLCKSZ). Changing XLOG_SEG_SIZE requires an initdb. *\/<\/span>\r\n#define XLOG_SEG_SIZE (1024 * 1024 * 1024)<\/span><\/pre>\n
The default location for the install is in \/usr\/local\/pgsql so the easiest way to configure is to set a symbolic link to a directory from your ssd to this location using the \u201cln \u2013s\u201d command<\/span><\/div>\n
 \u00a0<\/span><\/div>\n
pgsql -> \/mnt\/ssd\/postgresqlsrc\/pgsql<\/span>\r\npostgres:\/mnt\/ssd\/postgresqlsrc$ ls -ltr \/usr\/local<\/span>\r\ntotal 32<\/span>\r\nlrwxrwxrwx 1 root root\u00a0\u00a0 28 Oct 11 03:41 pgsql -> \/mnt\/ssd\/postgresqlsrc\/pgsql<\/span><\/span><\/pre>\n
 \u00a0<\/span><\/div>\n
Now follow the steps previously to compile PostgreSQL from source. When you do \u201c<\/span>make install\u201d it will create the binaries in this location.\u00a0 You also need to create a new directory called \u201c<\/span>data\u201d and if all went well you should now have a directory that looks like this: <\/span><\/span><\/div>\n
postgres:\/usr\/local\/pgsql$ ls<\/span>\r\nbin\u00a0 data\u00a0 include\u00a0 lib\u00a0 share<\/span>\r\n<\/span><\/pre>\n
\n
Configure PostgreSQL<\/span><\/h6>\n<\/div>\n
Run initdb from the bin directory specifying the data directory<\/span>
\n<\/span><\/div>\n
.\/bin\/initdb -D .\/data<\/span><\/pre>\n
This creates your database with the username of the OS user you are using as the superuser.\u00a0Now edit 2 configuration files\u00a0<\/span>postgresql.conf and <\/span>pg_hba.conf.\u00a0<\/span><\/div>\n
<\/div>\n
For Postgresql.conf you have a number of options. It is your test so you can set the options as you see fit. Remember to set huge_pages=on and note that whereas wal_level minimal=minimal and synchronous_commit=off will give you the best WAL performance for a test it may not be what you would want in a production environment. (and you can run further tests to quantify the impact of these options).\u00a0<\/span><\/div>\n
 \u00a0<\/span><\/div>\n
postgres:\/mnt\/ssd\/postgresqlsrc\/pgsql\/data$ more postgresql.conf<\/span>\r\nlisten_addresses ='192.168.1.1'<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # what IP address(es) to listen on;<\/span>\r\nport = 5432\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # (change requires restart)<\/span>\r\nmax_connections = 256\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # (change requires restart)<\/span>\r\nshared_buffers = 64000MB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # min 128kB<\/span>\r\nhuge_pages = on\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # on, off, or try<\/span>\r\ntemp_buffers = 4000MB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # min 800kB<\/span>\r\nwork_mem = 4000MB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # min 64kB<\/span>\r\nmaintenance_work_mem = 512MB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # min 1MB<\/span>\r\nautovacuum_work_mem = -1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # min 1MB, or -1 to use maintenance_work_mem<\/span>\r\nmax_stack_depth = 7MB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # min 100kB<\/span>\r\ndynamic_shared_memory_type = posix\u00a0\u00a0\u00a0\u00a0\u00a0 # the default is the first option<\/span>\r\nmax_files_per_process = 4000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # min 25<\/span>\r\neffective_io_concurrency = 32\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # 1-1000; 0 disables prefetching<\/span>\r\nwal_level = minimal\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # minimal, archive, hot_standby, or logical<\/span>\r\nsynchronous_commit = off\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # synchronization level;<\/span>\r\nwal_buffers = 512MB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # min 32kB, -1 sets based on shared_buffers<\/span>\r\n#checkpoint_segments = 256\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # in logfile segments, min 1, 16MB each<\/span>\r\ncheckpoint_timeout = 1h\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # range 30s-1h<\/span>\r\ncheckpoint_completion_target = 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # checkpoint target duration, 0.0 - 1.0<\/span>\r\ncheckpoint_warning = 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # 0 disables<\/span>\r\nlog_min_messages = error\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # values in order of decreasing detail:<\/span>\r\nlog_min_error_statement = error # values in order of decreasing detail:<\/span>\r\nlog_timezone = 'GB'<\/span>\r\nautovacuum = off\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # Enable autovacuum subprocess?\u00a0 'on'<\/span>\r\ndatestyle = 'iso, dmy'<\/span>\r\ntimezone = 'GB'<\/span>\r\nlc_messages = 'en_GB.UTF-8'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # locale for system error message<\/span>\r\nlc_monetary = 'en_GB.UTF-8'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # locale for monetary formatting<\/span>\r\nlc_numeric = 'en_GB.UTF-8'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # locale for number formatting<\/span>\r\nlc_time = 'en_GB.UTF-8'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # locale for time formatting<\/span>\r\ndefault_text_search_config = 'pg_catalog.english'<\/span>\r\nmax_locks_per_transaction = 64\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # min 10<\/span>\r\nmax_pred_locks_per_transaction = 64\u00a0\u00a0\u00a0\u00a0 # min 10<\/span><\/span><\/pre>\n
Also change the pg_hba.conf and add the ip addresses for your test server and load testing client running HammerDB. <\/span><\/div>\n
# TYPE\u00a0 DATABASE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 USER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ADDRESS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 METHOD<\/span>\r\n# \"local\" is for Unix domain socket connections only<\/span>\r\nlocal\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 trust<\/span>\r\n# IPv4 local connections:<\/span>\r\nhost\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0127.0.0.1\/32\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 trust<\/span>\r\n# IPv6 local connections:<\/span>\r\nhost\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ::1\/128\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 trust<\/span>\r\nhost\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 192.168.1.1<\/span>\/32\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 trust<\/span>\r\nhost\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 192.168.1.2<\/span>\/32\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 trust<\/span>\r\n# Allow replication connections from localhost, by a user with the<\/span>\r\n# replication privilege.<\/span>\r\n#local\u00a0\u00a0 replication\u00a0 \u00a0 \u00a0postgres\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 trust<\/span>\r\n#host\u00a0\u00a0\u00a0 replication\u00a0 \u00a0 \u00a0postgres\u00a0 \u00a0 \u00a0 \u00a0 127.0.0.1\/32\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 trust<\/span>\r\n#host\u00a0\u00a0\u00a0 replication\u00a0 \u00a0 \u00a0postgres\u00a0 \u00a0 \u00a0 \u00a0::1\/128\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 trust<\/span><\/span><\/pre>\n
Start the PostgreSQL Database<\/span><\/h6>\n
\n
Start the database as follows:<\/span>
\n<\/span><\/p>\n<\/div>\n
.\/bin\/pg_ctl start -D .\/data<\/span><\/pre>\n

\n<\/span>And check it is running (remember to check the huge page allocation as well).<\/span><\/div>\n
postgres:\/usr\/local\/pgsql$ ps -ef | grep post<\/span>\r\npostgres\u00a0 \u00a0 201539\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0 0 Sep19 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:00:00 \/mnt\/ssd\/postgresqlsrc\/pgsql\/bin\/postgres -D .\/data<\/span>\r\npostgres\u00a0 \u00a0 201541 201539\u00a0 0 Sep19 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:00:57 postgres: checkpointer process<\/span>\r\npostgres\u00a0 \u00a0 201542 201539\u00a0 0 Sep19 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:00:02 postgres: writer process<\/span>\r\npostgres\u00a0 \u00a0 201543 201539\u00a0 0 Sep19 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:03:18 postgres: wal writer process<\/span>\r\npostgres\u00a0 \u00a0201544 201539\u00a0 0 Sep19 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:00:04 postgres: stats collector process<\/span>\r\n<\/span><\/pre>\n
You can stop the database as follows: <\/span>
\n<\/span><\/div>\n
.\/bin\/pg_ctl stop -D .\/data<\/span><\/pre>\n
 \u00a0<\/span>Now login set the password for the \u201csuperuser\u201d note how this takes the name from the OS user so in this case postgres. <\/span><\/div>\n
postgres:\/usr\/local\/pgsql$ .\/bin\/psql -U postgres -d postgres<\/span>\r\npsql (9.6.5)<\/span>\r\nType \"help\" for help.<\/span>\r\npostgres=# alter role postgres password 'postgres';<\/span>\r\nALTER ROLE<\/span>\r\npostgres=#<\/span><\/span><\/pre>\n
Use \u2013W to test the password<\/span><\/div>\n
postgres:\/usr\/local\/pgsql$ .\/bin\/psql -U postgres -d postgres -W<\/span>\r\nPassword for user postgres:<\/span>\r\npsql (9.6.5)<\/span>\r\nType \"help\" for help.<\/span>\r\npostgres=#<\/span><\/span><\/pre>\n
\n
Configure the HammerDB Client<\/span><\/h6>\n
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 \u2013 you don\u2019t need to create a database or set the config files. Then add the library to the library path:<\/span><\/p>\n<\/div>\n
postgres:~$ export LD_LIBRARY_PATH=\/usr\/local\/pgsql\/lib:$LD_LIBRARY_PATH<\/span><\/pre>\n
\n
and check that it loads with a librarycheck test. <\/span><\/p>\n
$ .\/hammerdbcli<\/span>\r\nHammerDB CLI v3.1<\/span>\r\nCopyright (C) 2003-2018 Steve Shaw<\/span>\r\nType \"help\" for a list of commands<\/span>\r\nThe xml is well-formed, applying configuration<\/span>\r\nhammerdb>librarycheck<\/span>\r\nChecking database library for PostgreSQL<\/span>\r\nSuccess ... loaded library Pgtcl for PostgreSQL<\/span><\/span><\/pre>\n
Create the Schema and Run the Test<\/span><\/h6>\n<\/div>\n
You can now start HammerDB on the client and from the Database options choose PostgreSQL. Follow the HammerDB documentation<\/span><\/a> to build the schema and run the test.\u00a0<\/span>Do not select the EnterpriseDB Oracle compatible schema as you installed the software from source. Cr<\/span>eating an 800 Warehouse schema is a good starting point, the data should load quickly but note that creating indexes can take up to 20 mins or so this is to be expected. If you have a system with an up to date CPU (as of mid-2018), enough memory and everything installed on a fast SSD then more than 2M PostgreSQL TPM and more than 1M NOPM should be achievable.<\/span><\/div>\n