Configuring Full-Text Indexes

Key Recommendations

• Set the number of partitions in a database based on query-parallelism needs, not by ingest throughput concerns.

• Use the full-text index threadpool to maintain ingest throughput at lower partition counts.

Full-Text Index Ingestion and Creation Process

Full-text indexes are built using the following process. When rows are inserted into a table with a version 2 full-text index:

1. The rows are inserted into the table's rowstore segments.

2. The rowstore segments are flushed to disk to create columnstore segments.

3. Full-text indexes are created when the segments are flushed. The flushes may occur due to:

   • Background flushing.

   • Explicit calls to OPTIMIZE TABLE.

   • Large inserts that write straight to disk.

   • Inserts helping with flushing (on small inserts).

Version 2 full-text indexes are built for each column with a full-text index defined on it when the associated row segment is flushed to disk and the columnstore segment is created.

Configure Index Build

Full-text index build is affected by several mechanisms including the full-text index threadpool, the full-text index merger, the flush threads, and the background merger.

• Full-Text Index Threadpool: When the full-text index threadpool is enabled with fts2_use_index_threadpool, each indexing request is decomposed into smaller tasks that are run by a threadpool; otherwise a single thread handles an entire segment’s indexing.

• Full-text index merger: The full-text index merger combines per-segment full-text indexes to create cross-segment full-text indexes. The merged indexes can improve the performance of full-text search queries by reducing the number of indexes that must be examined for a query. The full-text index merger is enabled with enable_fts2_merger.

• Concurrent Flush Threads: Multiple flush threads can send concurrent indexing requests to JLucene (SingleStore's Lucene‑based full-text service). The concurrency of these requests is bounded by fts2_max_connections. From the engine’s perspective, each indexing request (sent by a flush thread) is synchronous.

• Background Merger: The background merger re‑indexes full-text data when it merges columnstore segments. This background merging uses compute resources for full-text indexing.

  • If flushing is too frequent, which may be caused by overly frequent calls to OPTIMIZE TABLE … FLUSH, or low settings of columnstore_flush_bytes or columnstore_segment_rows, the engine creates many small segments, which increases background re‑indexing work.

Refer to Tune Ingest for information on setting these variables.

Key Engine Variables

This table describes the function of key engine variables that impact full-text index creation.

Tune Ingest

1. Start with fewer partitions aligned to query performance (e.g., >1 core/partition), then turn on the full-text index threadpool. Verify ingest uniformity and determine whether throttling delays ingest as described in the Monitor Ingest section.

2. If ingest still falls behind, schedule periodic calls to OPTIMIZE TABLE FLUSH during ingest to allocate more resources to flushing. Tune the frequency to avoid producing many small segments.

3. Increase columnstore_flush_bytes to form larger segments and reduce flush overhead.

4. Calibrate fts2_max_connections so multiple flush threads can index concurrently without CPU oversubscription. Watch the Java (JLucene) CPU to ensure indexing is active and healthy.

5. Keep the fts2_index_threadpool_size fixed while testing different partition counts to determine whether the bottleneck is in flushing or indexing. If higher partitions help while the threadpool is turned off, the system is flushing‑bound, and indexing is not throttling ingest. SingleStore recommends increasing flush resources (by increasing the OPTIMIZE TABLE ... FLUSH cadence) instead of increasing the partition to node ratio, which permanently fractures data.

   1. SingleStore recommends starting with fts2_index_threadpool_size set to the number of cores on a leaf node.

6. Once ingest is stable, iterate towards fewer partitions by relying on the indexing threadpool and a measured OPTIMIZE TABLE ... FLUSH schedule to preserve query performance while sustaining ingest throughput.

Monitor Ingest and Obtain Diagnostics

• Track pending flush pressure by observing columnstore ingest management metrics such as columnstore_ingest_management_estimated_segments_to_flush. A sharp rise in the value of this variable towards a threshold (e.g., around cores × 10) typically precedes throttling.

• Compare full-text index sizes and counts per segment using the following query.

  SQL

  Copy

  SELECT database_name, table_name, node_id, size
  FROM information_schema.MV_COLUMNSTORE_FILES
  WHERE file_type = 'FULLTEXT';

• Watch the CPU usage of the Java process (JLucene) during flush; high utilization indicates indexing is active and can explain differences across runs.

Monitor Index Build Status

For large tables, there are often multiple segments per partition. The progress of index building can be monitored with the SHOW FULLTEXT INDEX STATUS command or by querying the MV_FULLTEXT_INDEX_STATUS information schema view.

SHOW FULLTEXT INDEX STATUS FOR <table_name>;

SELECT *
FROM information_schema.MV_FULLTEXT_INDEX_STATUS
WHERE DATABASE_NAME = "<database_name>"
  AND TABLE_NAME = "<table_name>";

Note: While full-text indexes are initially created per-segment, those indexes may later be merged.

Broken Indexes

In rare situations, an index creation may fail due to issues such as insufficient disk space or memory.

If an index creation failure occurs, the index will be flagged as "broken" and an error message is returned when a user searches against the broken index.

To identify broken indexes, use the SHOW FULLTEXT INDEX STATUS command or query the MV_FULLTEXT_INDEX_STATUS information schema view.

SHOW FULLTEXT INDEX STATUS FOR <table_name>;

SELECT *
FROM information_schema.MV_FULLTEXT_INDEX_STATUS
WHERE DATABASE_NAME = "<database_name>"
   AND TABLE_NAME = "<table_name>"
AND BROKEN_PERCENT > 0;

To attempt to fix a broken index, run the OPTIMIZE TABLE … FIX_FULLTEXT command.

OPTIMIZE TABLE <table_name> FIX_FULLTEXT;

However, full-text indexes cannot be repaired by the system in all cases, including:

• There is an invalid path for the full-text JLucene service.

• The Java runtime used by the JLucene service runs out of memory. Consider increasing the value of the fts2_max_memory_mb global variable if this occurs.

• The size of the data being exchanged between the engine and the JLucene process is too large. In this case, consider increasing the value of the fts2_max_service_buffer_mb global variable.

• A term to be indexed is longer than the maximum length of 32766. This is a limitation of JLucene and may be addressed by truncating or breaking up tokens in the data itself.

Debug Full-Text Index Tokens

To debug full-text search query behavior, it can be helpful to understand how the input documents are broken into tokens.

The tokens that will be generated for a full-text index can be viewed using the ANALYZE FULLTEXT command. Use the specification from the analyzer key for INDEX_OPTIONS from the relevant index definition as the OPTIONS for the ANALYZE FULLTEXT command.

Memory Usage

The Alloc_fts2_svc status variable is the number of allocated bytes (out of the total max_memory) that is attributable to the next-generation (VERSION 2) full-text subprocess.

View the value of Alloc_fts2_svc on the leaf nodes with the SHOW LEAF STATUS EXTENDED command.

SHOW LEAF STATUS EXTENDED LIKE '%fts%';

The value of Alloc_fts2_svc on the aggregator nodes can be viewed with the SHOW STATUS EXTENDED command. The Alloc_fts2_svc variable will have a value on the aggregator nodes if a search has been run using the BM25_GLOBAL search function. Thus, the following command will return results only if a BM25_GLOBAL search has been used.

SHOW STATUS EXTENDED LIKE '%fts%';

Full-Text Index Merger

When a full-text index is built, a table-level index is created for the text column. This table-level index is composed of smaller pieces; one per-segment index per columnstore segment.

The cost of full-text search is driven by the number of indexes searched. To improve performance, the full-text index merger combines per-segment full-text indexes to create cross-segment full-text indexes. These cross-segment indexes improve the performance of full-text search queries by reducing the number of indexes that must be examined.

The full-text index merger:

• Is enabled with the enable_fts2_merger engine variable.

  SQL

  Copy

  SET GLOBAL enable_fts2_merger = ON;

• Can merge any number of smaller indexes.

• Has an upper limit on the size of an index that can be merged, controlled by fts2_merge_soft_limit_mb. This limit is not strictly enforced and may be exceeded in some cases.

• Is used when the query is able to take advantage of the ORDER BY LIMIT optimization.

SingleStore recommends using the largest possible fts2_merge_soft_limit_mb and nodes big enough to handle that size of index. The full-text index merger removes the need to specify larger segments. With the full-text index merger, you can use smaller segments, which improve segment elimination, while maintaining the benefits of larger segments for full-text search.

The full-text index merger automatically runs in the background to continuously improve search performance. In addition, the full-text index merger is also run when the OPTIMIZE TABLE ... FULL command is run.

OPTIMIZE TABLE <table_name> FULL;

Full-Text Index Threadpool

The full-text index threadpool uses multiple threads to build full-text indexes. The threadpool parallelizes index building within a single segment flush request. When using the threadpool, a full-text index (for one segment) is built using multiple threads. Thus using the threadpool can reduce index build time and reduce the impact of index builds on data ingest.

Two engine variables are available to configure the threadpool. The goal is to configure the threadpool so that the rate of flushing segments to disk matches the rate of ingest. That is, set the thread pool parallelism high enough so that the index builds match the rate of ingest.

SingleStore recommends setting fts2_index_threadpool_size to the number of cores on a leaf node.

Query Optimization

SELECT
    ucid, company_name, fc.company_description,
    MATCH (TABLE fc)
    AGAINST ('company_technologies:("font awesome" OR "open graph")') AS _match
FROM Company c
JOIN FTS_Company_new fc USING (ucid)
JOIN Person p USING(ucid)
ANTI_JOIN GlobalExclude_global ge ON ge.PersonId = p.upid
WHERE _match
GROUP BY ucid
ORDER BY _match DESC
LIMIT 25 OFFSET 0;

WITH _fts AS (
    SELECT
        ucid, company_description,
        MATCH (TABLE fc)
        AGAINST ('company_technologies:("font awesome" OR "open graph")')
            AS _match
    FROM FTS_Company_new fc
    WHERE _match ORDER BY _match desc limit 25
)
SELECT ucid, company_name, fc.company_description, fc._match
FROM Company c
JOIN _fts fc USING (ucid)
JOIN Person p USING(ucid)
ANTI_JOIN GlobalExclude_global ge ON ge.PersonId = p.upid
WHERE fc._match
GROUP BY ucid
ORDER BY fc._match DESC
LIMIT 25 OFFSET 0;

Caching

SET GLOBAL fts2_cache_enable = ON;

SET GLOBAL fts2_stats_cache_enable = TRUE;

SELECT COUNT(*)
FROM table_name
WHERE MATCH(TABLE table_name) AGAINST ('valid_column:random_term');

Resource Management

Segment Elimination

Segment elimination is the process whereby the system avoids scanning segments that do not meet the filter in the query. With highly selective filters, segment elimination will result in much less data being scanned and can significantly improve performance. In this case, smaller segments mean less data is scanned (small segments are more likely to be able to be 'eliminated' and a smaller number of rows are read from the segments that pass segment elimination). Thus, smaller segments are good for performance for queries with highly selective filters that are not for full-text search. An example of such a filter is a date range filter in the WHERE clause of a query.

That is, queries with highly selective predicates may benefit from an increased number of partitions and segments. The increased number of smaller partitions will enable more segment elimination.

Refer to Choosing a Columnstore Key for information on segment elimination.

Flexible Parallelism

Flexible parallelism allows multiple threads to scan a single partition, increasing the parallelism (number of cores) used for a query. Flexible parallelism allows all cores to work on a particular query even if there are more cores than partitions. This decreases query latency. More specifically, flexible parallelism allows each core to scan a sub-partition, so multiple threads per query per leaf. This is great for certain workloads, but not for all workloads.

If you have multiple queries running concurrently, parallelism may not help. Multiple queries can use multiple cores, so there is less need for parallelism. However, if you have fewer queries running, parallelism can help improve performance. At a high level, if you have multiple concurrent queries, turn down the parallelism, but if you have few large queries running, turn up the parallelism. Flexible parallelism is managed with the engine variable query_parallelism_per_leaf_core.

Turning down parallelism by decreasing query_parallelism_per_leaf_core helps throughput by reducing overhead for starting and synchronizing threads. More importantly, turning down parallelism can also reduce total work by avoiding having multiple threads process the same segment, potentially each doing an index search independently, or doing ORDER BY ... LIMIT queries on more threads which could increase the total number of rows decoded and transmitted. This extra decoding and transmission can drive up the total CPU cycles needed to run a query.

Flexible Parallelism Configuration

When doing a parallel (non-indexed) scan:

• If (flexible) parallelism is set to one (query_parallelism_per_leaf_core = 1), then there will be one thread per CPU (vCPU) core and each thread will scan different sub-partitions in parallel.

• If (flexible) parallelism is set lower (query_parallelism_per_leaf_core < 1), then fewer threads will be used and each thread will scan multiple sub-partitions. Setting query_parallelism_per_leaf_core = .01 will give one thread per leaf node, which is ideal for high-throughput search workloads.

Configure for Large Data

For huge data, you may want more than one thread per leaf node to balance response time versus throughput. If throughput is still your top concern, you can still limit the threads per leaf node by setting query_parallelism_per_leaf_core = 0.01.

Configure for Response Time

To obtain the best response times for individual queries, use multiple threads per query. Set query_parallelism_per_leaf_core at its default value of 1.0 or some relatively high value such as 0.5 or 0.25. Increasing query parallelism increases parallelism at the cost of thread overhead. Thus, there is a tradeoff between increased parallelism and thread overhead which can be managed by setting query parallelism.