Performance Guide

This guide provides detailed performance benchmarks, optimization tips, and best practices for MygramDB.

Benchmark Results

The benchmark tables, charts, environment, and measured memory use are rendered from the same result snapshot as the benchmark page. The snapshot uses verify_text: all with the MygramDB query cache disabled.

Benchmark Environment

1,100,000 Wikipedia articles, MySQL 8.4.10 FULLTEXT (ngram parser), MygramDB v1.9.0, verify_text: all, and the query cache disabled. Latencies are p50 over 10 iterations.

Search Latency (SORT id LIMIT 100)

Search Latency (SORT id LIMIT 100) (p50, log scale)

Query Type	Matches	MySQL	MygramDB	Speedup
Multi-word ("quantum physics")	104	2928.44ms	15.19ms	193x
Medium-freq ("quantum")	1,961	2072.03ms	62.75ms	33x
Low-freq ("algorithm")	2,498	375.14ms	12.78ms	29x
Rare term ("fibonacci")	84	1172.29ms	53.66ms	22x

CJK Search Latency (SORT id LIMIT 100)

CJK Search Latency (SORT id LIMIT 100) (p50, log scale)

Query	Matches	MySQL	MygramDB	Speedup
日本	32,282	917.03ms	20.64ms	44x
東京	6,989	201.51ms	4.39ms	46x
科学	1,551	3.35ms	2.49ms	1x

COUNT Performance

COUNT Performance (p50, log scale)

Query Type	Count	MySQL	MygramDB	Speedup
Medium-freq ("quantum")	1,961	2000.08ms	82.76ms	24x
Low-freq ("algorithm")	2,498	469.95ms	14.06ms	33x

Result Consistency

Query	MySQL	MygramDB	Match
quantum	1,961	1,961	exact
algorithm	2,498	2,498	exact
日本	32,282	32,282	exact
科学	1,551	1,551	exact

Concurrent Throughput

Concurrent Throughput — QPS

Query: "algorithm", 10 seconds per connection level.

Connections	MySQL QPS	MygramDB QPS	MySQL p50	MygramDB p50
1	2.51	83	399.71ms	11.86ms
4	7.09	245	569.23ms	16.45ms

Memory Usage

Documents	Index	Documents + Text	Total RSS	Per 1M docs
1,100,000	152MB	1.78GB	3.41GB	~3.1GB

Docker Desktop was allocated 32 GiB; 31.29 GiB was visible to the containers.

Performance Analysis

Why MySQL is Slow

1. Disk-based B-tree: FULLTEXT index requires disk I/O for each query
2. No compression: Posting lists are not compressed, requiring more disk reads
3. ORDER BY overhead: Sorting requires additional processing and I/O
4. High-frequency terms: Short, common terms result in large posting list scans
5. Concurrency bottleneck: Under concurrent load, disk I/O serialization causes request queuing

Why MygramDB is Fast

1. In-memory index: Zero disk I/O, all data in RAM
2. Compressed posting lists: Hybrid Delta encoding + Roaring bitmaps
3. Optimized intersections: SIMD-accelerated bitmap operations
4. Sorting: SORT validates the primary key and configured filter columns before ordering results
5. verify_text: Post-filter eliminates false positives when exactness matters
6. Targeted cache invalidation: A row change visits only cache entries whose indexed dependencies can be affected

Performance Characteristics

Query Time Complexity

Operation	MySQL FULLTEXT	MygramDB
Single term search	O(n log n) with disk I/O	O(n) in memory
AND intersection	O(n * m) with disk I/O	O(n + m) with SIMD
Sort by id	Comparison sort	Comparison sort (partial or full, depending on result count)
COUNT	Full scan	Bitmap cardinality

Scalability

MygramDB scales linearly with:

• Number of search terms (efficient bitmap intersection)
• Result set size (compressed bitmaps)
• Concurrent queries (thread pool architecture)

MygramDB does NOT scale with:

• Dataset size beyond available RAM (in-memory only)

Current Execution Paths

Several details keep large queries and busy caches from turning into a broad scan or a long lock hold:

• Candidate filtering walks a posting list and the sorted candidate list once, so its work is proportional to the candidates and postings it visits.
• Candidate text is materialized in bounded chunks. The DocumentStore lock is released before BM25 scoring, term-frequency counting, and text post-filters.
• A deduplicated term-information lookup is reused across boolean evaluation, NOT filtering, synonym expansion, and verification. Expressions without NOT skip the full document-ID scan.
• Filter column names are interned once per store, and per-document values use compact vectors keyed by the interned IDs.
• Cache invalidation has separate reverse indexes for n-gram, filter, and text-sensitive dependencies. LRU maintenance resumes from a bounded slice on each tick instead of sweeping the full cache under one exclusive lock.
• Per-table statistics are aggregated into a bounded snapshot for INFO, /metrics, and Prometheus, rather than locking every table while formatting each scrape.

Optimization Tips

1. Choose Appropriate ngram_size

tables:
  - name: "articles"
    ngram_size: 2          # ASCII/alphanumeric: bigram (recommended)
    kanji_ngram_size: 1    # CJK characters: unigram (recommended)

Recommendations:

• Bigram (2) for ASCII/English: Good balance of precision and index size
• Unigram (1) for CJK: Each character is meaningful
• Trigram (3): More precise but larger index and slower queries

2. Enable verify_text

memory:
  verify_text: "all"     # Eliminate n-gram false positives

With verify_text=all, MygramDB verifies every candidate against the original text. The benchmark snapshot records matching counts with MySQL FULLTEXT; see the rendered results above for the measured latency.

3. Memory Configuration

memory:
  hard_limit_mb: 16384      # Reserved / not yet enforced
  soft_target_mb: 8192      # Reserved / not yet enforced
  roaring_threshold: 0.18   # Delta→Roaring conversion threshold

Recommendations:

• Treat hard_limit_mb and soft_target_mb as reserved compatibility fields; they do not enforce process memory limits today

Enforce memory limits at the OS level

hard_limit_mb is not a hard process memory cap today. When running under Docker, systemd, or Kubernetes, set the container or service memory limit as well, and monitor it.

• Leave roaring_threshold at default (0.18) unless memory is tight

4. Use Filters for Selective Queries

tables:
  - name: "articles"
    filters:
      - name: "status"
        type: "int"
      - name: "category_id"
        type: "int"

Filter early to reduce result set:

SEARCH articles tech FILTER status=1 FILTER category_id=5 LIMIT 100

5. Optimize Query Patterns

Fast queries:

• SEARCH table term SORT id LIMIT 100 - Sorts by the primary key
• COUNT table term - Bitmap cardinality operation
• SEARCH table term1 AND term2 - Efficient bitmap intersection

Slower queries:

• SEARCH table term LIMIT 100 without SORT - Still fast, but may scan more
• Very high LIMIT values (>1000) - More IDs to return

6. Use OPTIMIZE Command

Run periodically to optimize posting list storage:

OPTIMIZE

This converts Delta-encoded lists to Roaring bitmaps based on density, reducing memory usage by 10-30%.

Production Deployment Recommendations

1. Memory Sizing

Rule of thumb: Plan for 1-2GB RAM per million documents

Example sizing:

• 1M documents: 2GB RAM minimum, 4GB recommended
• 10M documents: 16GB RAM minimum, 32GB recommended
• 100M documents: Consider sharding across multiple instances

2. High Availability Setup

Deploy multiple MygramDB instances behind a load balancer:

3. Monitoring

Monitor these metrics via INFO command:

INFO

Key metrics:

• doc_count: Number of indexed documents
• index_size: Memory used by index
• total_requests: Total queries processed
• connections: Current active connections
• uptime: Server uptime in seconds

4. Backup Strategy

Use DUMP SAVE command to create snapshots:

DUMP SAVE /path/to/snapshot.dmp

Schedule regular snapshots:

# Daily snapshot
0 2 * * * dump_date=$(date +\%Y\%m\%d); printf 'AUTH \%s\nDUMP SAVE /backup/mygramdb-\%s.dmp\n' "$MYGRAM_API_ADMIN_TOKEN" "$dump_date" | mygram-cli

This cron example keeps AUTH and DUMP SAVE on one connection. AUTH is unnecessary only when api.admin_token is empty; configure a token for non-loopback deployments.

Troubleshooting

Query is Slower Than Expected

1. Check if index is optimized:

   OPTIMIZE

2. Verify memory usage:

   INFO

   Look at index_size and process RSS; hard_limit_mb is reserved and does not enforce a process memory limit today.

3. Enable debug mode:

   DEBUG ON
   SEARCH table term LIMIT 100

   Review query_time, index_time, and optimization fields.

High Memory Usage

1. Run OPTIMIZE:

   OPTIMIZE

   Converts dense posting lists to Roaring bitmaps (10-30% reduction).

2. Adjust roaring_threshold:

   memory:
     roaring_threshold: 0.15  # Lower = more aggressive compression

3. Consider sharding: Split data across multiple MygramDB instances.

Comparison with Alternatives

vs MySQL FULLTEXT

MygramDB advantages:

• Cache-off measurements are maintained in the benchmark snapshot above
• Exact result consistency with verify_text (zero false positives)
• Consistent performance regardless of cache state
• The published concurrent measurements cover 1 and 4 connections

MySQL advantages:

• No separate infrastructure
• Works with existing MySQL data
• Lower memory requirements

vs Elasticsearch

MygramDB advantages:

• Simpler deployment (single binary)
• Lower operational complexity
• Direct MySQL replication (no ETL)
• Lower latency for simple queries

Elasticsearch advantages:

• Distributed search across nodes
• Advanced analytics and aggregations
• Full-text features (highlighting, fuzzy search)
• Not limited by single-node RAM

Benchmarking Your Own Data

The benchmark suite is reproducible. To run the same benchmark on your hardware:

# Run the included benchmark (requires Docker)
make bench-up    # Start MySQL with Wikipedia dataset
make bench-run   # Execute benchmark suite

To benchmark with your own data, start the server:

./mygramdb -c config.yaml

With automatic snapshots disabled, use the same authenticated CLI connection to start the sync, check its status, enable debugging, and run the test queries:

$ mygram-cli
127.0.0.1:11016> AUTH <token>
OK AUTHENTICATED
127.0.0.1:11016> SYNC table
127.0.0.1:11016> SYNC STATUS
127.0.0.1:11016> DEBUG ON
127.0.0.1:11016> SEARCH table common_term LIMIT 100
127.0.0.1:11016> COUNT table common_term

Compare the results with MySQL:

mysql -e "SELECT COUNT(*) FROM table WHERE MATCH(column) AGAINST('common_term')"
mysql -e "SELECT id FROM table WHERE MATCH(column) AGAINST('common_term') ORDER BY id LIMIT 100"

Conclusion

The measured difference depends on the query, data, hardware, and cache configuration. The cache-off snapshot above is the canonical source for its latency, memory, and concurrent-throughput values.

With verify_text: all, MygramDB removes n-gram false positives and returned matching counts with MySQL FULLTEXT in this run. CJK queries with few matches can narrow the gap because MySQL may finish quickly.

For read-heavy workloads with millions of documents, evaluate the included benchmark on your own data with make bench-up && make bench-run.