Monday, February 10, 2025

Vector indexes, MariaDB & pgvector, large server, dbpedia-openai dataset

This post has results from ann-benchmarks to compare MariaDB and Postgres with a larger dataset, dbpedia-openai at 100k, 500k and 1M rows. It has 1536 dimensions and uses angular (cosine) as the distance metric. By larger I mean by the standards of what is in ann-benchmarks. This work was done by Small Datum LLC and sponsored by the MariaDB Corporation.

tl;dr

  • Index create time was much less for MariaDB in all cases except the result for recall >= 0.95
  • For a given recall, MariaDB gets between 2.1X and 2.7X more QPS than Postgres
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. Output from the benchmark is here.

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 lines to run the benchmark using my helper scripts are:
    bash rall.batch.sh v1 dbpedia-openai-100k-angular c32r128
    bash rall.batch.sh v1 dbpedia-openai-500k-angular c32r128
    bash rall.batch.sh v1 dbpedia-openai-1000k-angular c32r128

Results: QPS vs recall

These charts show the best QPS for a given recall. MariaDB gets about 2X more QPS than Postgres for a specific recall level 

With 100k rows

With 500k rows

With 1M rows

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
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
Table sizes:
* Postgres is 7734M
* MariaDB is 7856M

                -- pgvector --          -- pgevector --
                -- float32 --           -- halfvec   --
M       cons    secs    size(MB)        secs    size(MB)
 8       32       458   7734             402    3867
16       32       720   7734             655    3867
 8       64       699   7734             627    3867
16       64      1144   7734            1029    3867
32       64      2033   7734            1880    3867
 8       96       934   7734             843    3867
16       96      1537   7734            1382    3867
32       96      2730   7734            2482    3867
48       96      4039   7734            3725    3867
 8      192      1606   7734            1409    3867
16      192      2778   7734            2435    3867
32      192      4683   7734            4154    3867
48      192      6830   7734            6106    3867
64      192      8601   7734            7831    3958
 8      256      2028   7734            1764    3867
16      256      3609   7734            3151    3867
32      256      5838   7734            5056    3867
48      256      8224   7734            7283    3867
64      256     11031   7734            9931    3957

mariadb
M       secs    secs(MB)
 4        318   3976
 5        372   3976
 6        465   3976
 8        717   3976
12       1550   3976
16       2887   3976
24       7248   3976
32      14120   3976
48      36697   3980

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 much less for MariaDB in all cases except the result for recall >= 0.95
  • For a given recall target, MariaDB gets between 2.1X and 2.7X 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, pgvector did not reach the recall target
recall  QPS     isecs
1.000    20     36697   MariaDB(m=48, ef_search=40)

Best QPS with recall >= 0.99, MariaDB gets >= 2.2X more QPS than Postgres
recall  QPS     isecs
0.990   287     8224    PGVector(m=48, ef_cons=256, ef_search=40)
0.990   321     7283    PGVector_halfvec(m=48, ef_cons=256, ef_search=40)
0.992   731     7248    MariaDB(m=24, ef_search=10)

Best QPS with recall >= 0.98, MariaDB gets >= 2.7X more QPS than Postgres
recall  QPS     isecs
0.984    375    4683    PGVector(m=32, ef_cons=192, ef_search=40)
0.984    418    4154    PGVector_halfvec(m=32, ef_cons=192, ef_search=40)
0.981   1130    2887    MariaDB(m=16, ef_search=10)

Best QPS with recall >= 0.97, MariaDB gets >= 2.3X more QPS than Postgres
recall  QPS     isecs
0.974    440    6830    PGVector(m=48, ef_cons=192, ef_search=20)
0.973    483    6106    PGVector_halfvec(m=48, ef_cons=192, ef_search=20)
0.981   1130    2887    MariaDB(m=16, ef_search=10)

Best QPS with recall >= 0.96, MariaDB gets >= 2.2X more QPS than Postgres
recall  QPS     isecs
0.962    568    4683    PGVector(m=32, ef_cons=192, ef_search=20)
0.961    635    4154    PGVector_halfvec(m=32, ef_cons=192, ef_search=20)
0.965   1433    1550    MariaDB(m=12, ef_search=10)

Best QPS with recall >= 0.95, MariaDB gets >= 2.1X more QPS
recall  QPS     isecs
0.953    588    2730    PGVector(m=32, ef_cons=96, ef_search=20)
0.957    662    1382    PGVector_halfvec(m=16, ef_cons=96, ef_search=40)
0.965   1433    1550    MariaDB(m=12, ef_search=10)

Vector indexes, MariaDB & pgvector, large server, dbpedia-openai dataset

This post has results from  ann-benchmarks  to compare MariaDB and Postgres with a larger dataset, dbpedia-openai at 100k, 500k and 1M rows....