Vector indexes, MariaDB & pgvector, large server, large dataset: part 1

This post has results from ann-benchmarks to compare MariaDB and Postgres with a larger dataset, gist-960-euclidean.  Previous posts (here and here) used fashion-mnist-784-euclidean which is a small dataset. By larger I mean by the standards of what is in ann-benchmarks. This dataset has 1M rows and 960 dimensions. The fashion-mnist-784-euclidean dataset has 60,000 rows and 784 dimensions. Both use Euclidean distance. This work was done by Small Datum LLC and sponsored by the MariaDB Corporation.

tl;dr

• MariaDB gets between 2.5X and 3.9X more QPS than Postgres for recall >= 0.95

Benchmark

This post has much more detail about my approach in general. I ran the benchmark for 1 session. I use ann-benchmarks via my fork of a fork of a fork at this commit.  The ann-benchmarks config files are here for MariaDB and for Postgres.

I used a larger server (Hetzner ax162-s) with 48 cores, 128G of RAM, Ubuntu 22.04 and HW RAID 10 using 2 NVMe devices. The database configuration files are here for MariaDB and for Postgres.

I had ps and vmstat running during the benchmark and confirmed there weren't storage reads as the table and index were cached by MariaDB and Postgres.

The command line to run the benchmark using my helper scripts is:
    bash rall.batch.sh v1 gist-960-euclidean c32r128

Results: QPS vs recall

This chart shows the best QPS for a given recall. MariaDB gets ~1.5X more QPS than pgvector at low recall and between 2X and 4X more QPS at high recall.

Results: create index

The database configs for Postgres and MariaDB are shared above, and parallel index create is disabled by the config for Postgres and not supported yet by MariaDB. The summary is:

• index sizes are similar between MariaDB and pgvector with halfvec
• time to create the index varies a lot and it is better to consider this in the context of recall which is done in next section

Table size is 3906 MB for Postgres and 5292 MB for MariaDB.

Legend

• M - value for M when creating the index
• cons - value for ef_construction when creating the index
• secs - time in seconds to create the index
• size(MB) - index size in MB

                -- pgvector --          -- pgvector --

                -- float32  --          -- halfvec  --

M       cons    secs    size(MB)        secs    size(MB)

 8       32      323    3870             292    2568

16       32      610    7811             551    2603

 8       64      512    3865             466    2565

16       64      964    7684             869    2561

32       64     1958    7812            1773    2604

 8       96      717    3863             646    2564

16       96     1330    7681            1187    2560

32       96     2640    7679            2368    2559

48       96     3990    7812            3606    2606

 8      192     1265    3861            1142    2562

16      192     2295    7679            2036    2559

32      192     4361    7678            3880    2559

48      192     6281    7678            5581    2562

64      192     8589    7678            7612    3839

 8      256     1607    3861            1448    2562

16      256     2882    7678            2560    2559

32      256     5260    7678            4611    2559

48      256     7678    7678            6713    2561

64      256     9962    7678            8851    3839

mariadb

M       secs    size(MB)

 4        243   2316

 5        313   2320

 6        439   2316

 8        775   2316

12       1878   2316

16       3547   2348

24       8690   2696

32      16172   2696

48      38732   2756

Results: best QPS for a given recall

Many benchmark results are marketed via peak performance (max throughput or min response time) but these are usually constrained optimization problems -- determine peak performance that satisfies some SLA. And the SLA might be response time or efficiency (cost).

With ann-benchmarks the constraint is recall. Below I share the best QPS for a given recall target along with the configuration parameters (M, ef_construction, ef_search) at which that occurs for each of the algorithms (MariaDB, pgvector with float32, pgvector with float16/halfvec).

Summary

• Postgres does not get recall=1.0 for the values of M, ef_construction and ef_search I used
• Index create time was less for MariaDB in all cases except the result for recall >= 0.96. However, if you care more about index size than peak QPS then it might be better to look at more results per recall level, as in the best 3 results per DBMS rather than the best as I do here.
• For a given recall target, MariaDB gets between 2.5X and 3.9X more QPS than Postgres

Legend:

• recall, QPS - best QPS at that recall
• isecs - time to create the index in seconds
• m= - value for M when creating the index
• ef_cons= - value for ef_construction when creating the index
• ef_search= - value for ef_search when running queries

Best QPS with recall >= 1.000, Postgres did not reach the recall target

recall  QPS     isecs

1.000    87.1  38732   MariaDB(m=48, ef_search=10)

Best QPS with recall >= 0.99, MariaDB gets 3.7X more QPS than Postgres

recall  QPS     isecs

0.990    81.1   9962    PGVector(m=64, ef_cons=256, ef_search=120)

0.990    85.4   8851    PGVector_halfvec(m=64, ef_cons=256, ef_search=120)

0.991   311.8   8690    MariaDB(m=24, ef_search=10)

Best QPS with recall >= 0.98,MariaDB gets 3.5X more QPS than Postgres

recall  QPS     isecs

0.984   101.1   6281    PGVector(m=48, ef_cons=192, ef_search=120)

0.984   109.7   5581    PGVector_halfvec(m=48, ef_cons=192, ef_search=120)

0.985   384.9   3547    MariaDB(m=16, ef_search=20)

Best QPS with recall >= 0.97, MariaDB gets 2.5X more QPS than Postgres

recall  QPS     isecs

0.973   138.0   5260    PGVector(m=32, ef_cons=256, ef_search=120)

0.971   152.7   3880    PGVector_halfvec(m=32, ef_cons=192, ef_search=120)

0.985   384.9   3547    MariaDB(m=16, ef_search=20)

Best QPS with recall >= 0.96, MariaDB gets 3.9X more QPS than Postgres

recall  QPS     isecs

0.966   139.8   6281    PGVector(m=48, ef_cons=192, ef_search=80)

0.964   155.4   2368    PGVector_halfvec(m=32, ef_cons=96, ef_search=120)

0.960   610.1   3547    MariaDB(m=16, ef_search=10)

Best QPS with recall >= 0.95, MariaDB gets 2.9X more QPS than Postgres

recall  QPS     isecs

0.951   190.8   5260    PGVector(m=32, ef_cons=256, ef_search=80)

0.951   208.7   4611    PGVector_halfvec(m=32, ef_cons=256, ef_search=80)

0.960   610.1   3547    MariaDB(m=16, ef_search=10)

Vector indexes, MariaDB & pgvector, large server, small dataset: part 2

This post has results for vector index support in MariaDB and Postgres. This work was done by Small Datum LLC and sponsored by the MariaDB Corporation. This is part 2 in a series that compares QPS and recall for the fashion-mnist-784-euclidean dataset using from 1 to 48 concurrent sessions on a large server. This is part 2 and part 1 is here. 

The purpose of this post is to explain the results I shared in part 1 where MariaDB does much better than pgvector at low and high concurrency but the performance gap isn't as large at medium concurrency where low means <= 4 concurrent sessions, medium means 8 to 20 and high means >= 24.

tl;dr

• QPS is ~1.4X larger for MariaDB than for pgvector at 2 and 48 concurrent sessions
• pgvector uses more CPU/query than MariaDB
• MariaDB does more context switches /query than pgvector
• MariaDB appears to use less CPU to compute euclidean distance

Benchmark

This post has much more detail about my approach in general and part 1 has more detail on this particular setup. I repeated the benchmark for 2 to 48 concurrent sessions because my test server has 48 cores. I use ann-benchmarks via my fork of a fork of a fork at this commit. 

For more on euclidean distance (L2) see here.

For MariaDB:

• queries use ORDER by vec_distance_euclidean
• create index uses DISTANCE=euclidean

For Postgres

• queries use ORDER BY embedding::halfvec(...) <-> $name::halfvec(...)
• create index uses USING hnsw ((embedding::halfvec(...)) halfvec_l2_ops

Results: QPS by concurrency

The charts in this section show QPS by concurrency level as the benchmark was repeated for 1 to 48 concurrent sessions (X concurrent sessions means X concurrent queries).

The charts come from this spreadsheet. All of the data from the benchmark is here and the data I scraped to make these charts is here. I used configurations that provide a recall of ~0.96.

• MariaDB - ef_search=10, M=6
• Postgres - ef_search=10, M=16, ef_construction=32

This chart has absolute QPS for each of the systems tested.

This chart has relative QPS which is: (QPS for Postgres / QPS for MariaDB).

• The MariaDB advantage is larger at low and high concurrency.
• The MariaDB advantage isn't as large between 8 and 20 concurrent sessions

And this table also has relative QPS.

The MariaDB advantage is larger at low and high concurrency.

The MariaDB advantage isn't as large between 8 and 20 concurrent sessions

	pgvector.halfvec	pgvector
1	0.60	0.57
2	0.71	0.69
4	0.78	0.72
8	0.89	0.83
12	0.82	0.79
16	0.98	0.86
20	0.96	0.85
24	0.82	0.81
28	0.85	0.79
32	0.90	0.86
36	0.71	0.76
40	0.84	0.75
44	0.70	0.65
48	0.73	0.64

This table has (QPS / concurrency). For all systems tested the QPS per session decreases as the concurrency increases. I suspect the benchmark client is part of the problem but I am just speculating

• multiprocessing.pool is used by both MariaDB and pgvector, which is good, less GIL. See here for MariaDB and for pgvector.
• the benchmark client includes all of the time to process queries, including
• creating & start multiprocessing.pool - perhaps the pool can be cached & reused across runs
• creating a database connection
• gathering results from the concurrent sessions - some of this is done in the main thread
• AFAIK, the total number of queries per run is fixed, so the number of queries per session is less when there are more concurrent sessions and setup overhead (create database connection, create multiprocessing.pool, process results) becomes more significant as the concurrency level increases.

	MariaDB	pgvector.halfvec	pgvector
1	4639.6	2803.5	2661.3
2	3376.4	2405.6	2327.3
4	2869.2	2226.8	2076.0
8	2149.6	1922.2	1784.0
12	1858.1	1522.2	1471.8
16	1527.5	1498.3	1310.5
20	1353.1	1303.1	1156.3
24	1261.8	1039.8	1023.5
28	1062.7	906.6	839.5
32	909.0	814.8	784.9
36	918.5	649.0	702.5
40	783.2	659.8	589.9
44	745.8	518.9	482.5
48	680.1	495.9	435.3

Performance debugging

The benchmark client does a lot of work (like checking results for recall) which means there is a brief burst of CPU overhead when queries run followed by longer periods where the benchmark client is processing the results. So I modified the benchmark client to only run queries in a loop and avoid other overheads like checking the results for recall. This makes it easier to collect performance data like CPU profiles (perf), PMP stacks and vmstat samples.

Performance debugging: MariaDB

From a test with 2 concurrent sessions the perf profile shows that much CPU is used to compute the dot product which is used to determine the distance between vectors:

# Overhead  Command          Shared Object        Symbol
    16.89%  one_connection   mariadbd.orig        [.] FVector::dot_product
     4.71%  one_connection   mariadbd.orig        [.] escape_string_for_mysql
     3.42%  one_connection   mariadbd.orig        [.] search_layer
     2.99%  one_connection   mariadbd.orig        [.] buf_page_get_gen
     2.31%  one_connection   mariadbd.orig        [.] my_charlen_utf8mb4
     2.16%  one_connection   mariadbd.orig        [.] MYSQLparse
     2.03%  one_connection   libc.so.6            [.] __memmove_avx512_unaligned_erms
     1.74%  one_connection   mariadbd.orig        [.] PatternedSimdBloomFilter<FVectorNode>::Query
     1.58%  one_connection   libm.so.6            [.] __roundf
     1.49%  one_connection   mariadbd.orig        [.] mtr_memo_slot_t::release
     1.40%  one_connection   mariadbd.orig        [.] mhnsw_read_first
     1.32%  one_connection   mariadbd.orig        [.] page_cur_search_with_match
     1.09%  one_connection   libc.so.6            [.] __memcmp_evex_movbe
     1.06%  one_connection   mariadbd.orig        [.] FVectorNode::distance_to
     1.03%  one_connection   mariadbd.orig        [.] row_search_mvcc
     0.98%  one_connection   mariadbd.orig        [.] rec_get_offsets_func
     0.93%  one_connection   mariadbd.orig        [.] cmp_data
     0.93%  one_connection   mariadbd.orig        [.] alloc_root
     0.75%  one_connection   mariadbd.orig        [.] Visited::cmp

And then the result with 48 concurrent sessions

• the percentage of time in dot_product was ~17% above, but only ~11.5% here
• more time is spent in InnoDB functions like buf_page_get_gen, mtr_memo_slot_t::release, page_cur_search_with_match, btr_cur_t_::search_leaf, ssux_lock::psd_read_lock, rec_get_offsets_func and buf_page_make_young_if_needed. Some of that might be expected but that can also be a sign of too much mutex contention in InnoDB.
• I don't see signs of mutex contention in PMP output

# Overhead  Command          Shared Object        Symbol
    11.49%  one_connection   mariadbd.orig        [.] FVector::dot_product
     7.17%  one_connection   mariadbd.orig        [.] buf_page_get_gen
     4.44%  one_connection   mariadbd.orig        [.] search_layer
     4.00%  one_connection   mariadbd.orig        [.] escape_string_for_mysql
     2.49%  one_connection   mariadbd.orig        [.] mtr_memo_slot_t::release
     2.23%  one_connection   mariadbd.orig        [.] page_cur_search_with_match
     1.86%  one_connection   mariadbd.orig        [.] MYSQLparse
     1.85%  one_connection   mariadbd.orig        [.] my_charlen_utf8mb4
     1.75%  one_connection   libc.so.6            [.] __memmove_avx512_unaligned_erms
     1.60%  one_connection   mariadbd.orig        [.] btr_cur_t::search_leaf
     1.38%  one_connection   mariadbd.orig        [.] ssux_lock::psi_rd_lock
     1.37%  one_connection   mariadbd.orig        [.] mhnsw_read_first
     1.32%  one_connection   mariadbd.orig        [.] FVectorNode::distance_to
     1.23%  one_connection   mariadbd.orig        [.] rec_get_offsets_func
     1.19%  one_connection   mariadbd.orig        [.] PatternedSimdBloomFilter<FVectorNode>::Query
     1.02%  one_connection   mariadbd.orig        [.] FVectorNode::load
     0.97%  one_connection   libm.so.6            [.] __roundf
     0.88%  one_connection   mariadbd.orig        [.] buf_page_make_young_if_needed
     0.83%  one_connection   mariadbd.orig        [.] cmp_dtuple_rec_with_match_low

From vmstat with 2 concurrent sessions

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st

 2  0  96784 89703408 1982284 34588692    0    0     0    70 1401 56743  3  1 95  0  0

 2  0  96784 89703408 1982284 34588696    0    0     0     9 1356 56588  4  1 96  0  0

 2  0  96784 89703408 1982284 34588696    0    0     0     0 1434 56755  3  1 95  0  0

 2  0  96784 89703408 1982284 34588696    0    0     0     0 1340 56629  4  1 96  0  0

 2  0  96784 89702672 1982288 34588700    0    0     0    86 1810 56874  4  1 95  0  0

From vmstat with 48 concurrent sessions

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st

58  0  96784 89428544 1988284 34733232    0    0     0   290 27883 637320 80 20  0  0  0

56  0  96784 89430560 1988284 34733236    0    0     0     0 28393 639018 81 19  0  0  0

52  0  96784 89430560 1988284 34733236    0    0     0     0 28240 638914 80 20  0  0  0

59  0  96784 89430560 1988284 34733236    0    0     0     0 33141 642055 80 20  1  0  0

56  0  96784 89430560 1988284 34733236    0    0     0     0 29520 639747 81 19  0  0  0

Comparing the vmstat results for 2 vs 48 sessions

• CPU/query is (vmstat.us + vmstat.sy) / QPS
• For 2 sessions it is ((4+5+4+5+5) / 5) / 6752.7 = .000681
• For 48 sessions it is ((100+100+100+100+100)/5) / 32645.5 = .003063
• CPU/query is ~4.5X larger at 48 sessions
• Context switches /query is vmstat.cs / QPS
• For 2 sessions it is 56743 / 6752.7 = 8.40
• For 48 sessions it is 637320 / 32645.5 = 19.52
• Context switches /query is ~2.3X larger at 48 sessions

Performance debugging: pgvector with halfvec

From a test with 2 concurrent sessions the perf profile shows

• computing L2 distance accounts for the most time, here it is 25.98% while above for MariaDB at 2 concurrent sessions it was 16.89%. Perhaps MariaDB is faster at computing L2 distance, perhaps MariaDB has more overhead elsewhere to reduce the fraction of time in computing L2 distance. But I suspect that the former is true.
• Postgres here appears to have more CPU overhead than MariaDB in accessing the data (PinBuffer, LWLock, etc)

# Overhead  Command   Shared Object      Symbol
    25.98%  postgres  vector.so          [.] HalfvecL2SquaredDistanceF16c
     9.68%  postgres  postgres           [.] PinBuffer
     6.46%  postgres  postgres           [.] hash_search_with_hash_value
     5.39%  postgres  postgres           [.] LWLockRelease
     4.25%  postgres  postgres           [.] pg_detoast_datum
     3.95%  postgres  vector.so          [.] vector_to_halfvec
     3.06%  postgres  postgres           [.] LWLockAttemptLock
     2.97%  postgres  postgres           [.] LWLockAcquire
     2.89%  postgres  vector.so          [.] HnswLoadUnvisitedFromDisk
     2.44%  postgres  postgres           [.] StartReadBuffer
     1.82%  postgres  postgres           [.] GetPrivateRefCountEntry
     1.70%  postgres  vector.so          [.] tidhash_insert_hash_internal
     1.65%  postgres  postgres           [.] LockBuffer
     1.59%  postgres  vector.so          [.] HnswLoadElementImpl
     0.88%  postgres  libc.so.6          [.] __memcmp_evex_movbe
     0.80%  postgres  postgres           [.] ItemPointerEquals
     0.79%  postgres  postgres           [.] ResourceOwnerForget
     0.71%  postgres  vector.so          [.] HnswSearchLayer
     0.64%  postgres  postgres           [.] pq_getmsgfloat4

And then from a test with 48 concurrent sessions

• The fraction of time computing L2 distance here is less than it is for 2 sessions above. This is similar to the results for MariaDB.
• From PMP I don't see signs of mutex contention, but I only took 3 samples

# Overhead  Command   Shared Object      Symbol
    19.86%  postgres  vector.so          [.] HalfvecL2SquaredDistanceF16c
    10.89%  postgres  postgres           [.] PinBuffer
     6.78%  postgres  postgres           [.] hash_search_with_hash_value
     5.44%  postgres  postgres           [.] LWLockRelease
     5.25%  postgres  postgres           [.] LWLockAttemptLock
     4.97%  postgres  postgres           [.] pg_detoast_datum
     3.30%  postgres  vector.so          [.] vector_to_halfvec
     2.62%  postgres  vector.so          [.] HnswLoadUnvisitedFromDisk
     2.61%  postgres  postgres           [.] StartReadBuffer
     2.03%  postgres  postgres           [.] GetPrivateRefCountEntry
     1.70%  postgres  postgres           [.] LockBuffer
     1.69%  postgres  vector.so          [.] HnswLoadElementImpl
     1.49%  postgres  postgres           [.] LWLockAcquire
     1.28%  postgres  vector.so          [.] tidhash_insert_hash_internal
     0.79%  postgres  postgres           [.] ReadBufferExtended
     0.78%  postgres  postgres           [.] AllocSetAlloc
     0.76%  postgres  libc.so.6          [.] __memcmp_evex_movbe
     0.65%  postgres  postgres           [.] ResourceOwnerForget
     0.63%  postgres  vector.so          [.] HnswSearchLayer

From vmstat with 2 concurrent sessions

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----

 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st

 2  0  96784 90724048 1997176 36391152    0    0     0    80 4188 24830  4  0 96  0  0

 2  0  96784 90724304 1997176 36391152    0    0     0     9 4079 24931  4  0 96  0  0

 2  0  96784 90724304 1997176 36391152    0    0     0   210 4117 24857  4  0 96  0  0

 2  0  96784 90724048 1997176 36391152    0    0     0    42 4160 24946  4  0 96  0  0

 2  0  96784 90723576 1997176 36391152    0    0     0    51 4511 24971  4  1 96  0  0

From vmstat with 48 concurrent sessions

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
88  0  96784 90323440 2001256 36414792    0    0     0     0 13129 224133 92  8  0  0  0
86  0  96784 90323440 2001256 36414796    0    0     0   197 13011 223926 92  8  0  0  0
85  0  96784 90322936 2001256 36414796    0    0     0    48 13118 224239 92  8  0  0  0
83  0  96784 90322688 2001264 36414800    0    0     0    56 13084 223987 92  8  0  0  0
86  0  96784 90324952 2001264 36414800    0    0     0    17 13361 224189 92  8  0  0  0

Comparing the vmstat results for 2 vs 48 sessions

• QPS is ~1.4X larger for MariaDB at 2 and 48 sessions
• CPU/query is (vmstat.us + vmstat.sy) / QPS
• For 2 sessions it is ((4+4+4+4+5)/5) / 4811.2 = .000872
• For 48 sessions it is ((100+100+100+100+100)/5) / 23802.6 = .004201
• CPU/query is ~4.8X larger at 48 sessions than at 2
• CPU/query for Postgres is ~1.3X larger than MariaDB at 2 sessions and ~1.4X larger at 48
• Context switches is vmstat.cs / QPS
• For 2 sessions it is 24830 / 4811.2 = 5.16
• For 48 sessions it is 224133 / 23802.6 = 9.41
• Context switches /query is ~1.8X larger at 48 sessions
• Context switches /query for MariaDB is ~1.6X larger than Postgres at 2 sessions and ~2.1X larger at 48

Vector indexes, MariaDB & pgvector, large server, small dataset: part 1

This post has results for vector index support in MariaDB and Postgres. This work was done by Small Datum LLC and sponsored by the MariaDB Corporation. 

My previous posts (here and here) used a server with 8 cores and 32G of RAM. While that was OK for one of the smaller datasets from ann-benchmarks it wasn't enough for larger datasets and the problem was the amount of memory used by the benchmark client. I have some changes to the benchmark client to reduce the transient spikes in memory usage I wasn't able to fully solve the problem, so I moved to a larger server with 48 cores and 128G of RAM. 

This post has results for the fashion-mnist-784-euclidean dataset using from 1 to 48 concurrent sessions. This is part 1. There will be parts 2, 3 and 4 to explain the results and then I move on to a larger dataset.

I compare MariaDB with pgvector because I respect the work that the Postgres community has done to support vector search workloads. And I am happy to report that MariaDB has also done a great job on this. While I don't know the full story of the development effort, this feature came from the MariaDB Foundation and the community and it is wonderful to see that collaboration.

tl;dr

• At low and high concurrency levels MariaDB gets much more QPS for a given recall target. Here low means <= 4 concurrent sessions and high means >= 24. 
• At middle concurrency levels (8 through 20 concurrent sessions) MariaDB still does better but the gap isn't as large. I try to explain this in future posts.

Benchmark

This post has much more detail. However I switched to a larger server (Hetzner ax162-s) with 48 cores, 128G of RAM, Ubuntu 22.04 and HW RAID 10 using 2 NVMe devices.

I use ann-benchmarks via my fork of a fork of a fork at this commit. Note that parallel index create was disabled for Postgres by my configuration and isn't (yet) supported by MariaDB.

I ran tests for fashion-mnist-784-euclidean at 1, 2, 4, 8, 12, 16, 20, 24, 28, 32, 36, 40, 44 and 44 concurrent sessions. The command lines were the following using pgvector, pgvector_halfvec and mariadb as the value of $alg. When --batch is used the concurrency level (between 2 and 48 concurrent sessions) is set by an environment variable (POSTGRES_BATCH_CONCURRENCY or MARIADB_BATCH_CONCURRENCY)
    python3 run.py --algorithm $alg  --dataset fashion-mnist-784-euclidean --timeout -1 --local --force \

        --respect_config_order --runs 3
    python3 run.py --algorithm $alg  --dataset fashion-mnist-784-euclidean --timeout -1 --local --force \

        --respect_config_order --runs 3 --batch

I filed MDEV-35897 for MariaDB because it allocates and then deallocates too much memory when ef_search is large, and large for me was >= 300. The overhead from this hurts query response times. Fortunately the fix should be easy. For now I changed config.yml for MariaDB to not use ef_search values larger than 200 (see query_args here).

Files:

• The config.yml files are here for MariaDB and for pgvector.
• Files related to these tests are archived here.
• The database configuration files are here for MariaDB and for Postgres.

Results: QPS vs recall graphs

The recall vs QPS graph is created by running plot.py from ann-benchmarks. The line colors are red for MariaDB, dark blue for pgvector with halfvec (float16) and light blue for pgvector.

1 session (no concurrency)

2 sessions

4 sessions

8 sessions

12 sessions

16 sessions

20 sessions

24 sessions

28 sessions

32 sessions

36 sessions

40 sessions

44 sessions

48 sessions

Evaluating vector indexes in MariaDB and pgvector: part 2

This post has results from the ann-benchmarks with the fashion-mnist-784-euclidean dataset for MariaDB and Postgres (pgvector) with concurrent queries (--batch). My previous post has results when not using concurrent queries. This work was done by Small Datum LLC and sponsored by the MariaDB Corporation.

I compare MariaDB with pgvector because I respect the work that the Postgres community has done to support vector search. And I am happy to report that MariaDB has also done a great job on this. While I don't know the full story of the development effort, this feature came from the MariaDB Foundation and the community and it is wonderful to see that collaboration.

Performance for MariaDB is excellent

• peak QPS for a given recall target is much better than pgvector
• time to create the index to achieve a given recall target is almost always much better than pgvector

Benchmark

Part 1 has all of the details.

I am using the ann-benchmarks via my fork of a fork of a fork at this commit. I added --batch to the command lines listed in part 1 to repeat the benchmark using concurrent queries. Note that parallel index create was disabled for Postgres and isn't (yet) supported by MariaDB.

With --batch there is one concurrent query per CPU core and my server has 8 cores.

Files related to these tests are archived here.

Results: QPS vs recall graphs

The recall vs QPS graph is created by: python3 plot.py --dataset fashion-mnist-784-euclidean --batch. This chart is very similar to the chart in part 1. One difference is that the peak QPS for MariaDB and Postgres are ~6000/s and ~4000/s there vs ~22000/s and ~15000/s here.

The results below for MariaDB are excellent. It gets more QPS than pgvector at a given recall target.

Results: create index

I am still trying to figure out how to present this data. All of the numbers are here for the time to create an index and the size of the index. The database configs for Postgres and MariaDB are shared above, and parallel index create is disabled by the config for Postgres (and not supported yet by MariaDB). The summary is:

• Indexes have a similar size with MariaDB and Postgres with halfvec. The Postgres index without halfvec is about 2X larger.
• Time to create the index for Postgres is similar with and without halfvec
• Time to create the index for MariaDB is less than for pgvector. Perhaps the best way to compare this is the time to create the index for a similar point on the QPS/recall graph (see the last section of this blog post)

Results: best QPS for a given recall

Many benchmark results are marketed via peak performance (max throughput or min response time) but these are usually constrained optimization problems -- determine peak performance that satisfies some SLA. And the SLA might be response time or efficiency (cost).

With ann-benchmarks the constraint might be recall where you seek the best QPS that satisfies a recall target. Below I share the best QPS for a given recall target along with the configuration parameters (M, ef_construction, ef_search) at which that occurs for each of the algorithms (MariaDB, pgvector with float32, pgvector with float16).

For all cases below except the first (recall = 1.000) the time to create the index is less with MariaDB.

For all cases below the best QPS at a given recall target is much better, sometimes 3X better, with MariaDB than with pgvector.

Legend

* recall & QPS - results from the benchmark

* isecs - number of seconds to create the index for M and ef_cons (ef_cons)

Best QPS with recall = 1.000

recall  QPS     isecs   algorithm

1.000    4727   115.9   PGVector(m=16, ef_cons=256, ef_search=120)

1.000    5479    98.6   PGVector_halfvec(m=16, ef_cons=192, ef_search=120)

1.000   13828   108.5   MariaDB(m=32, ef_search=10)

Best QPS with recall >= 0.99

recall  QPS     isecs   algorithm

0.990   10704    71.2   PGVector(m=16, ef_cons=96, ef_search=20)

0.991   11377    90.1   PGVector_halfvec(m=16, ef_cons=256, ef_search=20)

0.995   17642    24.5   MariaDB(m=12, ef_search=10)

Best QPS with recall >= 0.98

recall  QPS     isecs   algorithm

0.985   10843    51.0   PGVector(m=16, ef_cons=32, ef_search=20)

0.984   11749    44.5   PGVector_halfvec(m=16, ef_cons=32, ef_search=20)

0.995   17642    24.5   MariaDB(m=12, ef_search=10)

Best QPS with recall >= 0.97

recall  QPS     isecs   algorithm

0.970   12836    35.1   PGVector(m=8, ef_cons=64, ef_search=20)

0.973   13742    33.8   PGVector_halfvec(m=8, ef_cons=96, ef_search=20)

0.995   17642    24.5   MariaDB(m=12, ef_search=10)

Best QPS with recall >= 0.96

recall  QPS     isecs   algorithm

0.970   12836    35.1   PGVector(m=8, ef_cons=64, ef_search=20)

0.973   13742    33.8   PGVector_halfvec(m=8, ef_cons=96, ef_search=20)

0.995   17642    24.5   MariaDB(m=12, ef_search=10)

Best QPS with recall >= 0.95

recall  QPS     isecs   algorithm

0.970   12836    35.1   PGVector(m=8, ef_cons=64, ef_search=20)

0.973   13742    33.8   PGVector_halfvec(m=8, ef_cons=96, ef_search=20)

0.995   17642    24.5   MariaDB(m=12, ef_search=10)

Evaluating vector indexes in MariaDB and pgvector: part 1

This post has results for vector index support in MariaDB and Postgres. I am new to vector indexes so I will start small and over time add more results.  This work was done by Small Datum LLC and sponsored by the MariaDB Corporation.

I compare MariaDB with pgvector because I respect the work that the Postgres community has done to support vector search workloads. And I am happy to report that MariaDB has also done a great job on this. While I don't know the full story of the development effort, this feature came from the MariaDB Foundation and the community and it is wonderful to see that collaboration.

tl;dr

• Performance for MariaDB is excellent
• peak QPS for a given recall target is much better than pgvector
• time to create the index to achieve a given recall target is almost always much better than pgvector
• MariaDB is easier to tune than pgvector
• MariaDB needs more documentation

The good and the less good

The good for MariaDB starts with performance. The results I get are great and match the results in this blog post from upstream. Another good thing about MariaDB is that it is easier to tune. With pgvector I need to set M and ef_construction while creating an index and then ef_search while running a query, With MariaDB there is no option to set ef_construction. And evaluations are easier when there are fewer options to tune. Note that the M with pgvector isn't the same thing as the M with MariaDB, but they are similar (at least in spirit).

The less good for MariaDB is the documentation. We need more, but that is easy to fix. I have been using:

• this blog post on benchmarks
• this overview
• this syntax description

Things that need more documentation

• the algorithm is described as a variation of HNSW (MHNSW) and the community will benefit from more details on what has been changed. For example, I can't set ef_construction and it always uses float16 in the index. But with pgvector I can use either float32 (default) or float16 (via halfvec).
• What transaction isolation level is supported? I get that workloads will be read-heavy but I assume that some won't be read-only so I won't to know whether repeatable read and read committed are provided.
• What concurrent operations are supported? Obviously, reads can be concurrent with other reads. But can writes be concurrent with reads or with other writes on the same table?

Hardware

The hardware is a Beelink SER7 7840HS with a Ryzen 7 7840HS CPU, 32G of RAM and Ubuntu 22.04.

While I already installed some dependencies on this server long ago, to run this benchmark I did:

sudo apt install libmariadb3 libmariadb-dev

pip3 install mariadb

pip3 install pgvector psycopg

Database software

For Postgres I compiled version 17.2 and pgvector 0.8.0 from source. Files that I used include:

• build script
• configuration file
• initialization script

For MariaDB I compiled version 11.7.1 from source. Files that I used include:

• build script
• configuration file
• initialization script

Benchmark

I am using the ann-benchmarks via my fork of a fork of a fork at this commit. These forks have changes to run the benchmark for MariaDB and pgvector without using Docker containers. A request I have for anyone writing a benchmark client is to limit dependencies, or at least make them optional. I just want to point the benchmark client at an existing installation.

The ann-benchmarks configuration files are here for MariaDB and for pgvector. I am open to feedback that I should try different parameters. I added support to use float16 (halfvec) for the pgvector index (but only the index, not for the vector stored in the table).

In this post I use the fashion-mnist-784-euclidean dataset in non-batch mode where non-batch mode doesn't run concurrent queries and batch mode does. Note that I don't set --count when running the benchmark which means that all of the queries use LIMIT 10.

Files related to these tests are archived here.

The command lines for non-batch mode are:

POSTGRES_CONN_ARGS=root:pw:127.0.0.1:5432 POSTGRES_DB_NAME=ib \
    python3 -u run.py  --algorithm pgvector --dataset fashion-mnist-784-euclidean --local

POSTGRES_CONN_ARGS=root:pw:127.0.0.1:5432 POSTGRES_DB_NAME=ib \
    python3 -u run.py  --algorithm pgvector_halfvec --dataset fashion-mnist-784-euclidean --local

MARIADB_CONN_ARGS=root:pw:127.0.0.1:3306 MARIADB_DB_NAME=test \
    python3 -u run.py  --algorithm mariadb --dataset fashion-mnist-784-euclidean --local

Results: QPS vs recall graphs

The recall vs QPS graph is created by: python3 plot.py --dataset fashion-mnist-784-euclidean

The results below for MariaDB are excellent. It gets more QPS than pgvector at a given recall target.

Results: create index

I am still trying to figure out how to present this data. All of the numbers are here for the time to create an index and the size of the index. The database configs for Postgres and MariaDB are shared above, and parallel index create is disabled by the config for Postgres (and not supported yet by MariaDB). The summary is:

• Indexes have a similar size with MariaDB and Postgres with halfvec. The Postgres index without halfvec is about 2X larger.
• Time to create the index for Postgres is similar with and without halfvec
• Time to create the index for MariaDB is less than for pgvector. Perhaps the best way to compare this is the time to create the index for a similar point on the QPS/recall graph (see the last section of this blog post)

Results: best QPS for a given recall

Many benchmark results are marketed via peak performance (max throughput or min response time) but these are usually constrained optimization problems -- determine peak performance that satisfies some SLA. And the SLA might be response time or efficiency (cost).

With ann-benchmarks the constraint might be recall where you seek the best QPS that satisfies a recall target. Below I share the best QPS for a given recall target along with the configuration parameters (M, ef_construction, ef_search) at which that occurs for each of the algorithms (MariaDB, pgvector with float32, pgvector with float16).

For all cases below except the first (recall = 1.000) the time to create the index is about 2X or more longer with pgvector than with MariaDB.

For all cases below the best QPS at a given recall target is much better, sometimes 3X better, with MariaDB than with pgvector.

Legend

* recall & QPS - results from the benchmark

* isecs - number of seconds to create the index for M and ef_construction (ef_cons)

Best QPS with recall >= 1.000

recall  QPS  isecs  algorithm

1.000   920  97.4   PGVector(m=16, ef_cons=192, ef_search=120)

1.000   991  89.8   PGVector_halfvec(m=16, ef_cons=256, ef_search=120)

1.000  3226 111.8   MariaDB(m=32, ef_search=10)

Best QPS with recall >= 0.99

recall  QPS  isecs  algorithm

0.990  2337  70.7   PGVector(m=16, ef_cons=96, ef_search=20)

0.991  2558  78.3   PGVector_halfvec(m=16, ef_cons=192, ef_search=20)

0.995  4745  27.4   MariaDB(m=12, ef_search=10)

Best QPS with recall >= 0.98

recall  QPS  isecs  algorithm

0.985  2383  50.4   PGVector(m=16, ef_cons=32, ef_search=20)

0.988  2608  52.8   PGVector_halfvec(m=16, ef_cons=64, ef_search=20)

0.984  5351  18.1   MariaDB(m=8, ef_search=10)

Best QPS with recall >= 0.97

recall  QPS  isecs  algorithm

0.972  3033  42.3   PGVector(m=8, ef_cons=96, ef_search=20)

0.973  3185  89.8   PGVector_halfvec(m=16, ef_cons=256, ef_search=10)

0.984  5351  18.1   MariaDB(m=8, ef_search=10)

Best QPS with recall >= 0.96

recall  QPS  isecs  algorithm

0.961  3044   50.4  PGVector(m=16, ef_cons=32, ef_search=10)

0.967  3250   52.8  PGVector_halfvec(m=16, ef_cons=64, ef_search=10)

0.961  5691   14.8  MariaDB(m=6, ef_search=10)

Best QPS with recall >= 0.95

recall  QPS  isecs  algorithm

0.961  3044  50.4   PGVector(m=16, ef_cons=32, ef_search=10)

0.967  3250  52.8   PGVector_halfvec(m=16, ef_cons=64, ef_search=10)

0.961  5691  14.8   MariaDB(m=6, ef_search=10)