You can tune the AlloyDB columnar engine to speed up background column population and refresh jobs in the column store and to control when auto-columnarization runs. The settings cover maintenance CPU and workers, the auto-columnarization schedule, and the column population heap size.
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
The columnar engine uses up to 20% of CPU cores for background maintenance jobs, such as population, refresh, and recommendation. To adjust the CPU resources for maintenance tasks, set the google_job_scheduler.maintenance_cpu_percentage flag to a value from 0 to 100. The default value is 20. Setting this flag to 0 disables background maintenance jobs. For non-zero values, 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 column store content manually for more information.
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:
google_columnar_engine.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 recommended values for the google_columnar_engine.population_heap_size_in_mb setting range from a minimum of 200 MB to a maximum of 1000 MB. However, the allowed flag values are from 0 to 2147483647 MB.
The default value of 250 MB for the google_columnar_engine.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.