继上一篇 数据库性能测试:Mysql、Sqlite、Duckdb 之后,做了一下内存数据库的性能测试,这里主要比较的是 Sqlite、Duckdb以及我这边使用容器实现的一个简单内存数据库,因为Sqlite和Duckdb都支持内存数据库模式,于是打算对比一下。Redis由于是要通过网络通信交互,没有列入比较。
Duckdb使用内存模式仅需要在创建数据库时,传入的数据库名称指定null即可,Sqlie这边需要改用 sqlite3_open_v2 来创建数据库,并且需要指定 flags 参数包含 SQLITE_OPEN_MEMORY,具体创建语句如下:
sqlite3* db;
auto ret = sqlite3_open_v2(nullptr, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |SQLITE_OPEN_MEMORY, nullptr);
单主键下duckdb与sqlite写入耗时对比:
20240829-08:52:03.618 10560 INFO WriteToDuckdb:9246 ms, RecordCount:6779139 ---DuckdbTest.cpp:133[WriteToDuckdb]
20240829-08:53:07.506 10560 INFO WriteToSqlitebatchCost:61116 ms, RecordCount:6779139 ---SqliteTest.cpp:131[WriteToSqliteBatch]
对比前面在文件模式(参考这篇文章中的数据:数据库性能测试:Mysql、Sqlite、Duckdb )下的写入速度还是提升不少的。
单主键下duckdb与sqlite查询耗时对比:
20240829-08:52:03.624 10560 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:2 ms, RecordCount:9340 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.632 10560 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:3 ms, RecordCount:11831 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.636 10560 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:0 ms, RecordCount:2799 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.643 10560 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:3 ms, RecordCount:15019 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.654 10560 INFO ReadFromDuckdbIndex QueryCost:5 ms, ParseCost:5 ms, RecordCount:21515 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.659 10560 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:1 ms, RecordCount:5115 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.665 10560 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:2 ms, RecordCount:9451 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.672 10560 INFO ReadFromDuckdbIndex QueryCost:4 ms, ParseCost:2 ms, RecordCount:9171 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.676 10560 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:1 ms, RecordCount:6869 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.682 10560 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:2 ms, RecordCount:8711 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:06.062 10560 INFO ReadFromDuckdb QueryCost:356 ms, ParseCost:2023 ms, RecordCount:6779139 ---DuckdbTest.cpp:197[ReadFromDuckdb]
20240829-08:53:07.514 10560 INFO ReadFromSqliteIndex QueryCost:7 ms, RecordCount:9340 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.520 10560 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:11831 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.522 10560 INFO ReadFromSqliteIndex QueryCost:1 ms, RecordCount:2799 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.530 10560 INFO ReadFromSqliteIndex QueryCost:7 ms, RecordCount:15019 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.541 10560 INFO ReadFromSqliteIndex QueryCost:10 ms, RecordCount:21515 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.545 10560 INFO ReadFromSqliteIndex QueryCost:2 ms, RecordCount:5115 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.550 10560 INFO ReadFromSqliteIndex QueryCost:4 ms, RecordCount:9451 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.555 10560 INFO ReadFromSqliteIndex QueryCost:4 ms, RecordCount:9171 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.559 10560 INFO ReadFromSqliteIndex QueryCost:3 ms, RecordCount:6869 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.563 10560 INFO ReadFromSqliteIndex QueryCost:4 ms, RecordCount:8711 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:10.498 10560 INFO ReadFromSqlite QueryCost:0 ms, ParseCost:2934 ms, RecordCount:6779139 ---SqliteTest.cpp:180[ReadFromSqlite]
查询性能对比文件模式下,相差也不大,duckdb的内存模式下跟文件模式下查询耗时基本持平,sqlite内存模式比文件模式每次大约快1ms(约10%-25%)的样子。
因为本次测试引入了我自己的一套内存数据库,相较于上一次的测试,本次对所有数据库增加了一个合约代码的索引,增加索引后,Duckdb的写入性能下降比较严重,而查询性能几乎没变。sqlite 在增加索引后,写入与查询性能都跟之前相差不大。
windows下写入耗时对比:
20240829-11:15:11.642 21688 INFO WriteToDuckdb:115186 ms, RecordCount:6779139 ---DuckdbTest.cpp:133[WriteToDuckdb]
20240829-11:16:19.737 21688 INFO WriteToSqlitebatchCost:65104 ms, RecordCount:6779139 ---SqliteTest.cpp:131[WriteToSqliteBatch]
20240829-11:16:30.411 21688 INFO WriteToMdb:7247 ms, RecordCount:6779139 ---MdbTest.cpp:60[WriteToMdb]
linux下写入耗时对比:
20240829-11:06:07.588 472875264 INFO WriteToDuckdb:108009 ms, RecordCount:6779139 ---DuckdbTest.cpp:133[WriteToDuckdb]
20240829-11:07:35.848 472875264 INFO WriteToSqlitebatchCost:80476 ms, RecordCount:6779139 ---SqliteTest.cpp:131[WriteToSqliteBatch]
20240829-11:07:48.812 472875264 INFO WriteToMdb:8784 ms, RecordCount:6779139 ---MdbTest.cpp:60[WriteToMdb]
windows下查询耗时对比:
20240829-11:15:11.669 21688 INFO ReadFromDuckdbIndex QueryCost:23 ms, ParseCost:2 ms, RecordCount:9340 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.677 21688 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:2 ms, RecordCount:11831 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.681 21688 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:0 ms, RecordCount:2799 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.689 21688 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:3 ms, RecordCount:15019 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.700 21688 INFO ReadFromDuckdbIndex QueryCost:5 ms, ParseCost:5 ms, RecordCount:21515 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.706 21688 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:1 ms, RecordCount:5115 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.712 21688 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:2 ms, RecordCount:9451 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.718 21688 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:2 ms, RecordCount:9171 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.722 21688 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:1 ms, RecordCount:6869 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.728 21688 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:1 ms, RecordCount:8711 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:14.253 21688 INFO ReadFromDuckdb QueryCost:508 ms, ParseCost:2017 ms, RecordCount:6779139 ---DuckdbTest.cpp:197[ReadFromDuckdb]
20240829-11:16:19.743 21688 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:9340 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.749 21688 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:11831 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.751 21688 INFO ReadFromSqliteIndex QueryCost:1 ms, RecordCount:2799 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.759 21688 INFO ReadFromSqliteIndex QueryCost:7 ms, RecordCount:15019 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.771 21688 INFO ReadFromSqliteIndex QueryCost:10 ms, RecordCount:21515 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.774 21688 INFO ReadFromSqliteIndex QueryCost:2 ms, RecordCount:5115 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.780 21688 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:9451 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.786 21688 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:9171 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.789 21688 INFO ReadFromSqliteIndex QueryCost:3 ms, RecordCount:6869 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.794 21688 INFO ReadFromSqliteIndex QueryCost:4 ms, RecordCount:8711 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:22.733 21688 INFO ReadFromSqlite QueryCost:0 ms, ParseCost:2939 ms, RecordCount:6779139 ---SqliteTest.cpp:180[ReadFromSqlite]
20240829-11:16:30.411 21688 INFO ReadFromMdb:230 us, RecordCount:9340 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.412 21688 INFO ReadFromMdb:170 us, RecordCount:11831 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.412 21688 INFO ReadFromMdb:35 us, RecordCount:2799 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.412 21688 INFO ReadFromMdb:226 us, RecordCount:15019 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.412 21688 INFO ReadFromMdb:337 us, RecordCount:21515 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.412 21688 INFO ReadFromMdb:189 us, RecordCount:5115 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.413 21688 INFO ReadFromMdb:192 us, RecordCount:9451 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.413 21688 INFO ReadFromMdb:120 us, RecordCount:9171 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.413 21688 INFO ReadFromMdb:193 us, RecordCount:6869 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.414 21688 INFO ReadFromMdb:88 us, RecordCount:8711 ---MdbTest.cpp:92[ReadFromMdbIndex]
linux下查询耗时对比:
20240829-11:06:07.872 472875264 INFO ReadFromDuckdbIndex QueryCost:279 ms, ParseCost:4 ms, RecordCount:9340 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.879 472875264 INFO ReadFromDuckdbIndex QueryCost:4 ms, ParseCost:2 ms, RecordCount:11831 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.885 472875264 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:0 ms, RecordCount:2799 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.894 472875264 INFO ReadFromDuckdbIndex QueryCost:6 ms, ParseCost:3 ms, RecordCount:15019 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.909 472875264 INFO ReadFromDuckdbIndex QueryCost:9 ms, ParseCost:4 ms, RecordCount:21515 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.917 472875264 INFO ReadFromDuckdbIndex QueryCost:5 ms, ParseCost:1 ms, RecordCount:5115 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.924 472875264 INFO ReadFromDuckdbIndex QueryCost:5 ms, ParseCost:1 ms, RecordCount:9451 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.929 472875264 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:1 ms, RecordCount:9171 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.935 472875264 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:1 ms, RecordCount:6869 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.940 472875264 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:1 ms, RecordCount:8711 ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:14.760 472875264 INFO ReadFromDuckdb QueryCost:1899 ms, ParseCost:4920 ms, RecordCount:6779139 ---DuckdbTest.cpp:197[ReadFromDuckdb]
20240829-11:07:35.860 472875264 INFO ReadFromSqliteIndex QueryCost:10 ms, RecordCount:9340 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.873 472875264 INFO ReadFromSqliteIndex QueryCost:12 ms, RecordCount:11831 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.876 472875264 INFO ReadFromSqliteIndex QueryCost:2 ms, RecordCount:2799 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.885 472875264 INFO ReadFromSqliteIndex QueryCost:8 ms, RecordCount:15019 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.898 472875264 INFO ReadFromSqliteIndex QueryCost:12 ms, RecordCount:21515 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.902 472875264 INFO ReadFromSqliteIndex QueryCost:2 ms, RecordCount:5115 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.910 472875264 INFO ReadFromSqliteIndex QueryCost:7 ms, RecordCount:9451 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.916 472875264 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:9171 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.920 472875264 INFO ReadFromSqliteIndex QueryCost:3 ms, RecordCount:6869 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.927 472875264 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:8711 ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:39.712 472875264 INFO ReadFromSqlite QueryCost:0 ms, ParseCost:3784 ms, RecordCount:6779139 ---SqliteTest.cpp:180[ReadFromSqlite]
20240829-11:07:48.813 472875264 INFO ReadFromMdb:248 us, RecordCount:9340 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.813 472875264 INFO ReadFromMdb:136 us, RecordCount:11831 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.813 472875264 INFO ReadFromMdb:41 us, RecordCount:2799 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.813 472875264 INFO ReadFromMdb:179 us, RecordCount:15019 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.813 472875264 INFO ReadFromMdb:298 us, RecordCount:21515 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.813 472875264 INFO ReadFromMdb:58 us, RecordCount:5115 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.813 472875264 INFO ReadFromMdb:93 us, RecordCount:9451 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.814 472875264 INFO ReadFromMdb:96 us, RecordCount:9171 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.814 472875264 INFO ReadFromMdb:65 us, RecordCount:6869 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.814 472875264 INFO ReadFromMdb:90 us, RecordCount:8711 ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:49.315 472875264 INFO ReadFromMdb:498109 us, RecordCount:6779139 ---MdbTest.cpp:75[ReadFromMdb]
因为内存数据库在查询时响应时间均小于1ms,所以这里改用了微秒(ms)。
结论:
在内存模式下(相较与文件模式),duckdb与sqlite的写入性能都有较大的提升,而查询性能提升不大,可能是因为文件模式下,两者都会有缓存的原因。我这边自己实现的简单内存数据库由于没有SQL的一些包袱,也仅支持一些简单的增删改查操作,性能还是要高不少(1-2个数量级)。
duckdb在增加索引的情况下,写入性能直线(指数)下降,而其对查询提升不大,建议在使用时少用索引。