This document describes how to tune the columnar engine for an AlloyDB instance. This document assumes that you are familiar with PostgreSQL.
Adjust the CPU resources for maintenance tasks
The following example shows you how to configure the columnar engine to use 50% of the CPU cores:
google_job_scheduler.maintenance_cpu_percentage = 50
Columnar engine uses up to 20% of CPU cores for background maintenance jobs, such as population, refresh, and recommendation. To adjust the CPU resources to allow for maintenance tasks, set the google_job_scheduler.maintenance_cpu_percentage flag to a value from 20 to 100. The default value is 20. The lowest CPU utilization for background maintenance tasks is 1 vCPU.
Add workers to accelerate columnar maintenance
The following example shows you how to set four worker processes:
google_job_scheduler.max_parallel_workers_per_job = 4
The columnar engine uses up to two worker processes for population and refresh background jobs. To increase the speed of these operations, set the following flag to increase the number of worker processes for background jobs. The default setting is 2 worker processes. These processes run on limited CPU resources as specified in the google_job_scheduler.maintenance_cpu_percentage flag. This flag doesn't require a database restart.
Change the auto-columnarization schedule
The following example shows you how to set the auto-columnarization schedule to six hours:
google_columnar_engine.auto_columnarization_schedule = 'EVERY 6 HOURS'
The auto-columnarization schedule can be set in the google_columnar_engine.auto_columnarization_schedule flag based upon anticipated scan-heavy workloads. Specify one or more hours for this flag. Changing the value of this flag doesn't require a database restart.
Manually populate the columnar engine
The following example shows you how to manually populate the columnar engine:
google_columnar_engine.enable_auto_columnarization = OFF
If you prefer to only manually populate the columnar engine, set the google_columnar_engine.enable_auto_columnarization flag to OFF. The default value of this flag is ON. Changing the value of this flag doesn't require a database restart.
See Manage Content Manually for more details.
Tuning the columnar engine population heap size
The following example shows you how to configure the google_columnar_engine.population_heap_size_in_mb flag:
population_heap_size_in_mb = 250
To convert data to columnar format, the columnar engine allocates the amount of memory in megabytes specified by the google_columnar_engine.population_heap_size_in_mb flag. Once the conversion is complete, this memory is immediately deallocated, typically within seconds.
The allowed values for the population_heap_size_in_mb setting range from a minimum of 200 MB to a maximum of 1000 MB.
The default value of 250 MB for the population_heap_size_in_mb flag works well for most typical database usage. However, you might face situations where you need to increase this value.