This page describes how to configure memory for vector indexes, and create,tune, monitor, and drop vector indexes.

Before you begin

Before you create a vector index, you must load data into the base table withvector embedding values. Your base table must have at least 1,000 rows. If youhave more data points available, you can get better partitioning and training ofthe index.

Configure memory allocation for vector indexes

Thecloudsql_vector_max_mem_size database flag controls how much memory yourCloud SQL instance dedicates to vector indexes. This is a static flagthat requires a restart of your instance. This memory serves two main purposes:

1. Storing the vector index structure: the non-leaf portion of the vectorindex (theTREE_MEMORY) resides in this memory. The approximate size ofthis tree depends on the number of leaf nodes (num_leaves) and thedimensions of your vectors:

    Approximate TREE_MEMORY = num_leaves * vector dimensions * 4 * 2

   For example, an index with 1000 leaves and 768 dimensions would have anapproximateTREE_MEMORY of 1000 * 768 * 4 * 2 or 6144000 bytes. You canalso check the actualTREE_MEMORY using theinformation_schema.innodb_vector_indexes table. Cloud SQLmanages that memory. You don't need to allocate space for all vector indexessimultaneously, as inactive indexes are unloaded to make room for otherrequests.

2. Memory for index creation (training data): during vector index creation,memory is needed to process a sample of the data from your base table tobuild the index. This memory is used only during the index creation processand is freed afterward. The approximate size of the memory needed fortraining is:

    approximate_training_memory = num_rows in base table * 0.1 * 4 * vector dimensions

   For example, with a table of 1,000,000 rows and 768 dimensions, thetraining_memory would be 1000000 * 0.1 * 768 * 4 or 307,200,000 bytes.Only 10% of the base table data is sampled to compute the centroids for thetree.

   When you enable thecloudsql_vector flag, Cloud SQLautomatically sets a defaultcloudsql_vector_max_mem_size based on your VMsize. This default usually suffices for typical workloads.Cloud SQL reduces theinnodb_buffer_pool_size flag toallocate this memory. The default maximum value forcloudsql_vector_max_mem_size is 16GB. If you need to tune your memorysize, you can dynamically adjustcloudsql_vector_max_mem_size based onyour vector index usage.

   Important: If you increasecloudsql_vector_max_mem_size, you mustcorrespondingly decreaseinnodb_buffer_pool_size to avoid memory issues.

cloudsql_vector_max_mem_size values

The range of vector index memory allocated is the following:

• 128MB minimum
• 10% of the buffer pool
• 16GB maximum

You can adjust the memory later, as needed. For more information, seeEnable the database flag for vector embeddings.

For information about monitoring the size of your vector index, seeMonitor vector indexes.

To update the memory allocated for vector indexes on the instance, use thefollowing command:

gcloud sql instances patchINSTANCE_NAME \ --database-flags= cloudsql_vector_max_mem_size=NEW_MEMORY_VALUE;

Replace the following:

• INSTANCE_NAME: the name of the instance on which you are changing thememory allocation.
• NEW_MEMORY_VALUE: the updated memory allocation, in bytes, for your vectorindexes.

This change takes effect immediately after a database restart.

Create a vector index

There are two ways to create a vector index:

• CREATE VECTOR INDEXstatement,a Cloud SQL extensionto standard MySQL syntax.
• ALTER TABLE statementwith the Cloud SQLADD VECTOR INDEX clause extension. Youcan't run this statement simultaneously with other DDL statements on thetable.

CREATEVECTORINDEXINDEX_NAMEONTABLE_NAME(COLUMN_NAME)USINGSCANN[QUANTIZER=SQ8]DISTANCE_MEASURE=L2_SQUARED|COSINE|DOT_PRODUCT[NUM_LEAVES=INT_VALUE{'</var>'}}];

CREATEVECTORINDEXvectorIndexONdbname.books(embeddings)DISTANCE_MEASURE=L2_SQUARED;

ALTERTABLEtbl_nameADDVECTORINDEXindex_name(key_part)[index_option];

ALTERTABLEt1ADDVECTORINDEXindex1(j)USINGSCANNQUANTIZER=SQ8DISTANCE_MEASURE=l2_squaredNUM_LEAVES=10;

DROPINDEXindex_nameONbooks;ALTERTABLEtable_nameDROPINDEXindex_name;

For more detailed information, see theInformation schema.

To view information in theinnodb_vector_indexes table, run the following command:

SELECT*FROMinformation_schema.innodb_vector_indexes\G;

The output looks similar to the following:

INDEX_NAME:t1_vec_indexTABLE_NAME:test.t1INDEX_TYPE:TREE_SQDIMENSION:3DIST_MEASURE:COSINESTATUS:ReadySTATE:INDEX_READY_TO_USENUM_LEAVES:10NUM_LEAVES_TO_SEARCH:10QUERIES:1MUTATIONS:1TREE_MEMORY:443

What's next

• Read theoverview about vector search on Cloud SQL.
• Learn how toenable and disable vector embeddings on your instance.
• Learn how togenerate vector embeddings.
• Learn how toperform searches on vector embeddings.