GUC Parameters

This session introduces PG-Strom's configuration parameters.

Enables/disables a particular feature

pg_strom.enabled [type: bool / default: on]
Enables/disables entire PG-Strom features at once
pg_strom.enable_gpuscan [type: bool / default: on]
Enables/disables GpuScan
pg_strom.enable_gpuhashjoin [type: bool / default: on]
Enables/disables JOIN by GpuHashJoin
pg_strom.enable_gpunestloop [type: bool / default: on]
Enables/disables JOIN by GpuNestLoop
pg_strom.enable_gpupreagg [type: bool / default: on]
Enables/disables GpuPreAgg
pg_strom.enable_brin [type: bool / default: on]
Enables/disables BRIN index support on tables scan
pg_strom.enable_gpucache [type: bool / default: on]
Controls whether GPU Cache is referenced, instead of PostgreSQL tables, if any
Note that GPU Cache trigger functions continue to update the REDO Log buffer, even if this parameter is turned off.
pg_strom.enable_partitionwise_gpujoin [type: bool / default: on]
Enables/disables whether GpuJoin is pushed down to the partition children.
pg_strom.enable_partitionwise_gpupreagg [type: bool / default: on]
Enables/disables whether GpuPreAgg is pushed down to the partition children.
pg_strom.pullup_outer_scan [type: bool / default: on]
Enables/disables to pull up full-table scan if it is just below GpuPreAgg/GpuJoin, to reduce data transfer between CPU/RAM and GPU.
pg_strom.pullup_outer_join [type: bool / default: on]
Enables/disables to pull up tables-join if GpuJoin is just below GpuPreAgg, to reduce data transfer between CPU/RAM and GPU.
pg_strom.enable_numeric_aggfuncs [type: bool / default: on]
Enables/disables support of aggregate function that takes numeric data type.
Note that aggregated function at GPU mapps numeric data type to double precision floating point values. So, if you are sensitive to calculation errors, you can turn off this configuration to suppress the calculation errors by the operations on CPU.
pg_strom.cpu_fallback [type: bool / default: off]
Controls whether it actually run CPU fallback operations, if GPU program returned "CPU ReCheck Error"
pg_strom.regression_test_mode [type: bool / default: off]
It disables some EXPLAIN command output that depends on software execution platform, like GPU model name. It avoid "false-positive" on the regression test, so use usually don't tough this configuration.

Optimizer Configuration

pg_strom.chunk_size [type: int / default: 65534kB]
Size of the data blocks processed by a single GPU kernel invocation. It was configurable, but makes less sense, so fixed to about 64MB in the current version.
pg_strom.gpu_setup_cost [type: real / default: 4000]
Cost value for initialization of GPU device
pg_strom.gpu_dma_cost [type: real / default: 10]
Cost value for DMA transfer over PCIe bus per data-chunk (pg_strom.chunk_size = 64MB)
pg_strom.gpu_operator_cost [type: real / default: 0.00015]
Cost value to process an expression formula on GPU. If larger value than cpu_operator_cost is configured, no chance to choose PG-Strom towards any size of tables

Executor Configuration

pg_strom.max_async_tasks [type: int / default: 5]
Max number of asynchronous taks PG-Strom can throw into GPU's execution queue per process.
If CPU parallel is used in combination, this limitation shall be applied for each background worker. So, more than pg_strom.max_async_tasks asynchronous tasks are executed in parallel on the entire batch job.
pg_strom.reuse_cuda_context [type: bool / default: off]
If on, it tries to reuse CUDA context on the next query execution, already constructed according to the previous query execution.
Usually, construction of CUDA context takes 100-200ms, it may improve queries response time, on the other hands, it continue to occupy a part of GPU device memory on the down-side. So, we don't recommend to enable this parameter expect for benchmarking and so on.
Also, this configuration makes no sense if query uses CPU parallel execution, because the worker processes shall always construct new CUDA context for each.

GPUDirect SQL Configuration

pg_strom.gpudirect_driver [type: text]
It shows the driver software name of GPUDirect SQL (read-only).
Either nvidia cufile or heterodb nvme-strom
pg_strom.gpudirect_enabled [type: bool / default: on]
Enables/disables GPUDirect SQL feature.
pg_strom.gpudirect_threshold [type: int / default: auto]
Controls the table-size threshold to invoke GPUDirect SQL feature.
The default is auto configuration; a threshold calculated by the system physical memory size and shared_buffers configuration.
pg_strom.cufile_io_unitsz [type: int / default: 16MB]
Unit size of read-i/o when PG-Strom uses cuFile API. No need to change from the default setting for most cases.
It is only available when nvidia cufile driver is used.
pg_strom.nvme_distance_map [type: text / default: null]
It manually configures the closest GPU for the particular storage devices, like NVME devices or NFS volumes.
Its format string is <gpuX>=(<nvmeX>|<sfdvX>|</path/to/nfsmount>)[;<gpuY>=...]; semi-colon separated list of pairs of GPU and storage like NVME devices.
(example: gpu0=nvme1; gpu1=/mnt/nfs_volume)
  • <gpuX> means a GPU with device identifier X.
  • <nvmeX> means a local NVME-SSD or a remote NVME-oF device.
  • <sfdvX> means a special device of CSD drives by ScaleFlux,Inc.
  • /path/to/nfsmount means a mount point by NFS volume with NFS-over-RDMA.
Automatic configuration is often sufficient for local NVME-SSD drives, however, you should manually configure the closest GPU for NVME-oF or NFS-over-RDMA volumes.

Arrow_Fdw Configuration

arrow_fdw.enabled [type: bool / default: on]
By adjustment of estimated cost value, it turns on/off Arrow_Fdw. Note that only Foreign Scan (Arrow_Fdw) can scan on Arrow files, if GpuScan is not capable to run on.
arrow_fdw.metadata_cache_size [type: int / default: 128MB]
Size of shared memory to cache metadata of Arrow files.
Once consumption of the shared memory exceeds this value, the older metadata shall be released based on LRU.
arrow_fdw.record_batch_size [type: int / default: 256MB]
Threshold of RecordBatch when Arrow_Fdw foreign table is written. When total amount of the buffer size exceeds this configuration, Arrow_Fdw writes out the buffer to Apache Arrow file, even if INSERT command is not completed yet.

GPU Cache configuration

pg_strom.enable_gpucache [type: bool / default: on]
Controls whether search/analytic query tries to use GPU Cache.
Note that this parameter does not affect to any writes on the REDO Log buffer by the trigger.
pg_strom.gpucache_auto_preload [type: text / default: null]
It specifies the table names to be loaded onto GPU Cache just after PostgreSQL startup.
Its format is DATABASE_NAME.SCHEMA_NAME.TABLE_NAME, and separated by comma if multiple tables are preloaded.
Initial-loading of GPU Cache usually takes a lot of time. So, preloading enables to avoid delay of response time of search/analytic queries on the first time.
If this parameter is '*', PG-Strom tries to load all the configured tables onto GPU Cache sequentially.

Configuration of GPU code generation and build

pg_strom.program_cache_size [type: int / default: 256MB]
Amount of the shared memory size to cache GPU programs already built. It needs restart to update the parameter.
pg_strom.num_program_builders [type: int / default: 2]
Number of background workers to build GPU programs asynchronously. It needs restart to update the parameter.
pg_strom.debug_jit_compile_options [type: bool / default: off]
Controls to include debug option (line-numbers and symbol information) on JIT compile of GPU programs.
It is valuable for complicated bug analysis using GPU core dump, however, should not be enabled on daily use because of performance degradation.
pg_strom.extra_kernel_stack_size [type: int / default: 0]
Extra size of stack, in bytes, for each GPU kernel thread to be allocated on execution. Usually, no need to change from the default value.

GPU Device Configuration

pg_strom.cuda_visible_devices [type: text / default: null]
List of GPU device numbers in comma separated, if you want to recognize particular GPUs on PostgreSQL startup.
It is equivalent to the environment variable CUDAVISIBLE_DEVICES
pg_strom.gpu_memory_segment_size [type: int / default: 512MB]
Specifies the amount of device memory to be allocated per CUDA API call.
Larger configuration will reduce the overhead of API calls, but not efficient usage of device memory.

PG-Strom shared memory configuration

shmbuf.segment_size [type: int / default: 256MB]
It configures the unit length of the shared memory segment that has portable virtual addresses.
Usually, it does not need to change the default value, except for the case when GPU Cache uses REDO Log buffer larger than 256MB. In this case, you need to enlarge this parameter also.
This parameter allows only power of 2.
shmbuf.num_logical_segments [type: int / default: auto]
It configures the number of the shared memory segment that has portable virtual addresses.
On the system startup, PG-Strom reserves (shmbuf.segment_size x shmbuf.num_logical_segments) bytes of virtual address space using mmap(2) with PROT_NONE, then, signal handler allocates physical memory on the demand.
The default configuration is auto; that is almost twice of the physical memory size installed on the system.