Enhancing the query performance through caching
You can configure different layers of caching in watsonx.data to optimize the query performance.
watsonx.data on Red Hat® OpenShift®
About this task
This procedure helps you to add (enable), edit, and delete different layers of caching that watsonx.data supports.
- Data cache: It reduces the need to repeatedly fetch the same data from the storage layer for running similar queries and the overall query processing time.
- Fragment result cache: It stores intermediate results of queries to speed up the repeated or similar queries.
- Metastore versioned cache: It stores the metadata about tables, like structure and schema, to reduce the need for repeated metadata lookups.
- File list cache: It keeps a list of files for a specified table and partition to avoid querying the storage system repeatedly for this information.
- File and stripe footer cache: It stores the footers of files and stripes in ORC/Parquet files, reducing the I/O for reading file metadata.
Metastore versioned cache and File and stripe footer cache are enabled by default with an option to update any of the properties listed in the following procedure:
Procedure
-
Set up the caching on a
wxdengine
instance.- Set the namespace in the console.
oc project ${PROJECT_CPD_INST_OPERANDS}
- Determine which Presto engine you want to update.
oc get wxdengine -o custom-columns='DISPLAY NAME:spec.engineDisplayName,ENGINE ID:metadata.labels.engineName'
Example:oc get wxdengine -o custom-columns='DISPLAY NAME:spec.engineDisplayName,ENGINE ID:metadata.labels.engineName' DISPLAY NAME ENGINE ID Presto presto750 presto-01 presto-01
- Delete all the
statefulsets
belonging to that Presto engine by using itsENGINE ID
from the previous command. This allows each Prestostatefulset
to be re-created with new volume mounts.
Example:oc delete sts -l engineName=<ENGINE_ID>
oc delete sts -l engineName=presto750 statefulset.apps "ibm-lh-lakehouse-presto750-coordinator-blue" deleted statefulset.apps "ibm-lh-lakehouse-presto750-prestissimo-worker" deleted statefulset.apps "ibm-lh-lakehouse-presto750-presto-worker" deleted statefulset.apps "ibm-lh-lakehouse-presto750-single-blue" deleted
- Add the cache configuration under the
spec
section of engine configuration.
Example:oc patch wxdengine/<engine-name> \ --type=merge \ -n ${PROJECT_CPD_INST_OPERANDS} \ -p '{ "spec": { "<property1>": "<value1>", "<property2>": "<value2>" } }'
oc patch wxdengine/lakehouse-presto-01 \ --type=merge \ -n ${PROJECT_CPD_INST_OPERANDS} \ -p '{ "spec": { "hive_metastore_cache_partition_versioning_enabled": "true", "hive_metastore_cache_scope": "PARTITION", "hive_metastore_cache_ttl": "3d", "hive_metastore_cache_refresh_interval": 4d, "hive_metastore_cache_maximum_size": "20000000" } }'
Note: Add all the configurations that you want to change injson
format.Customizable configurations for different levels of cache
Data cachecacheStorageClass: <nfs-client> cacheStorageSize: <10Gi> cache_alluxio_max_cache_size: <8GB>
cacheStorageSize
is the name of theStorageClass
.cacheStorageSize
is the size of storage that is required for the cache mount.cache_alluxio_max_cache_size
is the maximum size of the cache that is allowed for data cache. This must be less than or equal tocacheStorageSize
.
Fragment result cachefragment_result_cache_enabled: true fragment_result_cache_max_cached_entries: 1000000 fragment_result_cache_ttl: 36h fragment_result_cache_partition_statistics_based_optimization_enabled: true fragmentCacheStorageClass: <your storage class> fragmentCacheStorageSize: <10Gi>
- The properties
fragment_result_cache_max_cached_entries
,fragment_result_cache_ttl
, andfragment_result_cache_partition_statistics_based_optimization_enabled
are optional. - The default value for
fragment_result_cache_max_cached_entries
is 1000000. - The default value for
fragment_result_cache_ttl
is 36 h. - The default value for
fragment_result_cache_partition_statistics_based_optimization_enabled
is true.
Metastore versioned cache
You can add/update the following properties into the engine configuration by using theoc patch
command.hive_metastore_cache_partition_versioning_enabled: false hive_metastore_cache_scope: ALL hive_metastore_cache_ttl: 10m hive_metastore_cache_maximum_size: 10000000 hive_metastore_cache_timeout: 3m hive_metastore_cache_refresh_interval: 2m
Note: The propertyhive_metastore_cache_timeout
is not applicable from version 1.1.4 and later of watsonx.data.- The property
hive_metastore_cache_partition_versioning_enabled
is disabled by default. All the other properties excepthive_metastore_cache_maximum_size
are defaulted with the values listed here.
File list cachefile_status_cache_expire_time: 24h file_status_cache_tables: "*" file_status_cache_size: 100000000
- If you add any of the three properties, file list caching is added with the default values of the other two properties that are specified in previous steps.
File and stripe footer cache- For ORC or DWRF:
The default values are:
You can add/update the following properties into the engine configuration by using thefile_metadata_orc_file_tail_cache_enabled: true file_metadata_orc_file_tail_cache_size: Calculated based on jvm.Xmx file_metadata_orc_file_tail_cache_ttl_since_last_access: 6h file_metadata_orc_stripe_metadata_cache_enabled: true file_metadata_orc_stripe_footer_cache_size: Calculated based on jvm.Xmx file_metadata_orc_stripe_footer_cache_ttl_since_last_access: 6h file_metadata_orc_stripe_stream_cache_size: Calculated based on jvm.Xmx file_metadata_orc_stripe_stream_cache_ttl_since_last_access: 6h file_metadata_orc_use_column_names: true
oc patch
command. - The properties
file_metadata_orc_file_tail_cache_enabled
andfile_metadata_orc_stripe_metadata_cache_enabled
are set astrue
by default. The default values for the other properties are listed under the default values. - For Parquet:
The default values are:
You can add/update the following properties into the engine configuration by using thefile_metadata_parquet_metadata_cache_enabled: true file_metadata_parquet_metadata_cache_size: Calculated based on jvm.Xmx file_metadata_parquet_metadata_cache_ttl_since_last_access: 6h file_metadata_parquet_metadata_batch_read_optimization_enabled: true file_metadata_parquet_metadata_use_column_names: true
oc patch
command.The property
file_metadata_parquet_metadata_cache_enabled
is set astrue
by default. The default values for the other properties are listed under the default values.
If you want to update the properties for Metastore versioned cache, File list cache, and File and stripe footer cache without deleting the stateful sets, then update thecr.yaml
file and run the following command to track the status of operator reconcile and the config maps.
Thewatch --color "oc get wxdEngine -n ${PROJECT_CPD_INST_OPERANDS} -o custom-columns='NAME:metadata.name,DISPLAY NAME:spec.engineDisplayName,RECONCILE:status.engineStatus,STATUS:status.middleEndStatus'"
RECONCILE
status first appears asRUNNING
and then changes toCOMPLETED
. Restart all Presto pods to apply the new configuration. - Set the namespace in the console.
- Optional: Set up Persistent Volume (PV).
This step is for setting storage classes for data cache and fragment result cache. You need two separate PVs and storage classes for data cache and fragment cache. Use the
localStorageProvisioner
, and complete the following steps:Note: This step is optional.- Create a
yaml
file.vi pv1.yaml
- Copy the content to
pv1.yaml
file and save it.Note: The values forname
,storage
,storageClassName
,path
, andnodeAffinity
are based on the client requirement.For example, in name: presto-cache-pv1 in the following output, presto-cache-pv1 is customizable.
apiVersion: v1 kind: PersistentVolume metadata: name: presto-cache-pv1 spec: capacity: storage: 1Gi volumeMode: Filesystem accessModes: - ReadWriteOnce persistentVolumeReclaimPolicy: Delete storageClassName: cache-storage local: path: /dev/cache nodeAffinity: required: nodeSelectorTerms: - matchExpressions: - key: kubernetes.io/hostname operator: In values: - worker0.bicorn.cp.fyre.ibm.com - worker1.bicorn.cp.fyre.ibm.com
- To get the values field under
nodeAffinity
, runoc get nodes
and use the name of the worker nodes that have the disk space available to mount the cache of Presto pods. - Access the debugging session on the selected worker node and create the necessary
directory structure.
oc debug node/<name of node> -- chroot /host mkdir -p <path used under local, /dev/cache>
Note: Repeat steps c and d for all the selected nodes. - Run the following command to apply the PV configurations.
oc apply -f pv1.yaml
- Provision more PVs based on the t-shirt sizing. If you have three Presto pods, create
pv2.yaml
, andpv3.yaml
. Forpv2
, use name aspresto-cache-pv2
and path aspath /dev/cache/pv2
. Forpv3
, use namepresto-cache-pv3
and path aspath /dev/cache/pv3
. - Create two or more PVs based on the requirements.
oc apply -f pv2.yaml pv3.yaml
- Create a
- Delete the caching.
- Determine the Presto instance from which you want to remove the
cache.
Example:oc get wxdengine -o custom-columns='DISPLAY NAME:spec.engineDisplayName,ENGINE ID:metadata.labels.engineName'
oc get wxdengine -o custom-columns='DISPLAY NAME:spec.engineDisplayName,ENGINE ID:metadata.labels.engineName' DISPLAY NAME ENGINE ID Presto presto750 presto-01 presto-01
- Delete all the
statefulsets
belonging to that Presto engine by using itsENGINE ID
from the previous command. This allows each Prestostatefulset
to be re-created with new volume mounts.
Example:oc delete sts -l engineName=<ENGINE_ID>
oc delete sts -l engineName=presto750 statefulset.apps "ibm-lh-lakehouse-presto750-coordinator-blue" deleted statefulset.apps "ibm-lh-lakehouse-presto750-prestissimo-worker" deleted statefulset.apps "ibm-lh-lakehouse-presto750-presto-worker" deleted statefulset.apps "ibm-lh-lakehouse-presto750-single-blue" deleted
- Remove the configurations of the cache that you want to delete.
oc patch wxdengine/<engine-name> -n ${PROJECT_CPD_INST_OPERANDS} --type='json' -p='[{"op": "remove", "path": "/spec/property1"}, {"op": "remove", "path": "/spec/property2"}]'
- Delete the Persistent Volume Claims (PVC) of the cache.
- For data cache
-
- List the PVCs of the data
cache.
$ oc get pvc | grep ibm-lh-cache-mount
- Delete the
PVCs.
$ oc delete pvc <PVC's name>
- List the PVCs of the data
cache.
- For fragment-result cache
-
- List the PVCs of the fragment-result
cache.
$ oc get pvc | grep ibm-lh-fragment-cache
- Delete the
PVCs.
$ oc delete pvc <PVC's name>
- List the PVCs of the fragment-result
cache.
- Delete the PVs if you have completed step
2.
oc delete pv <pv name>
Data cachecacheStorageClass
cacheStorageSize
cache_alluxio_max_cache_size
Fragment result cachefragment_result_cache_enabled
fragment_result_cache_max_cached_entries
fragment_result_cache_ttl
fragment_result_cache_partition_statistics_based_optimization_enabled
fragmentCacheStorageClass and fragmentCacheStorageSize
Metastore versioned cachehive_metastore_cache_partition_versioning_enabled
hive_metastore_cache_scope
hive_metastore_cache_ttl
hive_metastore_cache_refresh_interval
hive_metastore_cache_maximum_size
hive_metastore_cache_timeout
Note: The propertyhive_metastore_cache_timeout
is not applicable from version 1.1.4 and later of watsonx.data.
File list cachefile_status_cache_expire_time
file_status_cache_tables
file_status_cache_size
File and stripe footer cachefile_metadata_parquet_metadata_cache_enabled
file_metadata_parquet_metadata_cache_size
file_metadata_parquet_metadata_cache_ttl_since_last_access
file_metadata_parquet_metadata_batch_read_optimization_enabled
file_metadata_parquet_metadata_use_column_names
file_metadata_orc_file_tail_cache_enabled
file_metadata_orc_file_tail_cache_size
file_metadata_orc_file_tail_cache_ttl_since_last_access
file_metadata_orc_stripe_metadata_cache_enabled
file_metadata_orc_stripe_footer_cache_size
file_metadata_orc_stripe_footer_cache_ttl_since_last_access
file_metadata_orc_stripe_stream_cache_size
file_metadata_orc_stripe_stream_cache_ttl_since_last_access
file_metadata_orc_use_column_names
- Determine the Presto instance from which you want to remove the
cache.