search

tonbo-io / tonbo

A portable embedded database using Arrow.
https://tonbo.io
Apache License 2.0
798 stars 46 forks source link

add write benchmark #198

Closed crwen closed 1 month ago

crwen commented 1 month ago

Add write test for python binding

Here are the results of the writing tests in my machine:

write 10000 records:

DuckDB took 20.72643 seconds to insert 10000 records
SQLite took 0.02475 seconds to insert 10000 records
SQLite with autocommit took 5.43819 seconds to insert 10000 records
Tonbo took 0.89315 seconds to insert 10000 records
Tonbo without transaction took 1.68304 seconds to insert 10000 records
Tonbo without transaction write batch took 0.75287 seconds to insert 10000 records

benchmark for 10000 inserts(compare mode):

BENCH=true pytest tests/test_write_benchmark.py --benchmark-compare

------------------------------------------------------------------------------- benchmark 'autocommit': 3 tests -------------------------------------------------------------------------------
Name (time in s)                     Min                Max               Mean            StdDev             Median               IQR            Outliers     OPS            Rounds  Iterations
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_tonbo_no_txn[True]           2.7148 (1.0)       2.8385 (1.0)       2.7607 (1.0)      0.0466 (1.06)      2.7492 (1.0)      0.0452 (1.0)           1;0  0.3622 (1.0)           5           1
test_sqlite_autocommit[True]      5.5469 (2.04)      7.0150 (2.47)      6.0955 (2.21)     0.5977 (13.58)     6.0006 (2.18)     0.8815 (19.48)         1;0  0.1641 (0.45)          5           1
test_duckdb_autocommit[True]     17.6864 (6.51)     17.7931 (6.27)     17.7252 (6.42)     0.0440 (1.0)      17.7087 (6.44)     0.0647 (1.43)          1;0  0.0564 (0.16)          5           1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------- benchmark 'txn': 3 tests ---------------------------------------------------------------------------------------
Name (time in ms)              Min                    Max                   Mean             StdDev                 Median                 IQR            Outliers     OPS            Rounds  Iterations
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_sqlite[False]        209.4384 (1.0)         216.4901 (1.0)         213.1646 (1.0)       2.5988 (1.0)         213.5285 (1.0)        3.2399 (1.0)           2;0  4.6912 (1.0)           5           1
test_tonbo[False]       1,185.6245 (5.66)      1,205.6479 (5.57)      1,196.3573 (5.61)      7.5267 (2.90)      1,196.6798 (5.60)      10.2414 (3.16)          2;0  0.8359 (0.18)          5           1
test_duckdb[False]     17,305.8920 (82.63)    17,542.0705 (81.03)    17,375.8318 (81.51)    97.2371 (37.42)    17,348.8095 (81.25)    108.3791 (33.45)         1;0  0.0576 (0.01)          5           1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Legend:
  Outliers: 1 Standard Deviation from Mean; 1.5 IQR (InterQuartile Range) from 1st Quartile and 3rd Quartile.
  OPS: Operations Per Second, computed as 1 / Mean

benchmark for async(normal mode)

BENCH=true pytest tests/test_write_async_benchmark.py
------------------------------------------------------------------------------------------ benchmark '1 async task': 4 tests ------------------------------------------------------------------------------------------
Name (time in ms)                              Min                    Max                   Mean             StdDev                 Median                IQR            Outliers     OPS            Rounds  Iterations
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_sqlite_one_task[1]                   182.0578 (1.0)         217.4159 (1.0)         191.4590 (1.0)      13.1612 (4.59)        186.0564 (1.0)       7.5507 (1.76)          1;1  5.2231 (1.0)           6           1
test_tonbo_disable_wal_one_task[1]        796.8536 (4.38)        804.0866 (3.70)        799.9534 (4.18)      2.8689 (1.0)         799.0795 (4.29)      4.2852 (1.0)           2;0  1.2501 (0.24)          5           1
test_tonbo_one_task[1]                  1,227.5543 (6.74)      1,261.9408 (5.80)      1,247.1690 (6.51)     12.3587 (4.31)      1,248.7901 (6.71)      9.0597 (2.11)          2;1  0.8018 (0.15)          5           1
test_duckdb_one_task[1]                16,310.7241 (89.59)    16,485.9345 (75.83)    16,387.5184 (85.59)    64.1768 (22.37)    16,369.9616 (87.98)    69.2377 (16.16)         2;0  0.0610 (0.01)          5           1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------- benchmark '4 async task': 4 tests -------------------------------------------------------------------------------------------
Name (time in ms)                               Min                    Max                   Mean             StdDev                 Median                 IQR            Outliers     OPS            Rounds  Iterations
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_sqlite_four_task[4]                   197.8323 (1.0)         248.1988 (1.0)         218.8520 (1.0)      22.2879 (6.28)        216.3441 (1.0)       35.7736 (11.12)         1;0  4.5693 (1.0)           6           1
test_tonbo_disable_wal_four_task[4]        717.8889 (3.63)        726.9563 (2.93)        723.9934 (3.31)      3.5478 (1.0)         724.6637 (3.35)       3.2160 (1.0)           1;1  1.3812 (0.30)          5           1
test_tonbo_four_task[4]                    828.1453 (4.19)        851.2807 (3.43)        835.4349 (3.82)      9.4134 (2.65)        834.1583 (3.86)      10.7695 (3.35)          1;0  1.1970 (0.26)          5           1
test_duckdb_four_task[4]                16,860.2662 (85.23)    17,039.4894 (68.65)    16,924.7830 (77.33)    72.5923 (20.46)    16,908.2101 (78.15)    102.1100 (31.75)         1;0  0.0591 (0.01)          5           1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------- benchmark '8 async task': 4 tests -------------------------------------------------------------------------------------------
Name (time in ms)                                Min                    Max                   Mean             StdDev                 Median                IQR            Outliers     OPS            Rounds  Iterations
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_sqlite_eight_task[8]                   223.5105 (1.0)         371.0482 (1.0)         273.6408 (1.0)      63.4304 (20.28)       235.5098 (1.0)      90.9326 (16.06)         1;0  3.6544 (1.0)           5           1
test_tonbo_disable_wal_eight_task[8]        715.9489 (3.20)        722.9558 (1.95)        719.4189 (2.63)      3.1279 (1.0)         718.5793 (3.05)      5.6605 (1.0)           2;0  1.3900 (0.38)          5           1
test_tonbo_eight_task[8]                    771.7515 (3.45)        792.1555 (2.13)        779.6061 (2.85)      7.6349 (2.44)        777.4425 (3.30)      7.7205 (1.36)          2;0  1.2827 (0.35)          5           1
test_duckdb_eight_task[8]                17,291.9178 (77.37)    17,409.6344 (46.92)    17,339.4274 (63.37)    47.1919 (15.09)    17,319.2801 (73.54)    67.8616 (11.99)         2;0  0.0577 (0.02)          5           1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Legend:
  Outliers: 1 Standard Deviation from Mean; 1.5 IQR (InterQuartile Range) from 1st Quartile and 3rd Quartile.
  OPS: Operations Per Second, computed as 1 / Mean