samvera
Look into using Sequel for Postgres adapter
https://hmistry.github.io/software/2017/12/11/activerecord-vs-sequel.html
ROM-rb uses Sequel and it may get us past the issue where right now a Valkyrie application can only talk to one database.
Working implementation here: https://github.com/samvera-labs/valkyrie-sequel
Small resource:
Loading a resource(valkyrie-sequel) 35.513k (± 5.2%) i/s - 176.823k in 4.997674s
Loading a resource(valkyrie) 23.299k (± 4.9%) i/s - 116.112k in 4.997953s
Large resource (many many metadata fields)
Loading a resource(valkyrie-sequel) 13.176k (± 7.5%) i/s - 65.376k in 4.999297s
Loading a resource(valkyrie) 8.113k (± 6.2%) i/s - 40.208k in 5.000543s
Creating a small resource
Saving a small resource(valkyrie-sequel) 11.212k (± 5.7%) i/s - 55.803k in 4.999551s
Saving a small resource (valkyrie) 5.352k (± 4.4%) i/s - 26.703k in 5.002222s
Extensive benchmarks using @ojlyytinen's excellent valkyrie-benchmark utility:
| Adapter | iterations/s | std.dev. | iterations | time (s) |
|---|---|---|---|---|
| basic_metadata_tests create_stub | ||||
| sequel_postgres | 1093.359 | (± 8.5%) | 5474 | 5.007 |
| postgres | 632.964 | (± 7.7%) | 3190 | 5.040 |
| basic_metadata_tests create_with_text | ||||
| sequel_postgres | 990.559 | (± 7.9%) | 5035 | 5.083 |
| postgres | 534.192 | (± 6.2%) | 2695 | 5.045 |
| basic_metadata_tests create_with_many_fields | ||||
| sequel_postgres | 816.170 | (±13.1%) | 4116 | 5.043 |
| postgres | 478.432 | (± 6.7%) | 2400 | 5.016 |
| basic_metadata_tests update_stub | ||||
| sequel_postgres | 856.220 | (± 8.9%) | 4300 | 5.022 |
| postgres | 571.965 | (±11.5%) | 2880 | 5.035 |
| basic_metadata_tests update_with_text | ||||
| sequel_postgres | 795.114 | (± 6.9%) | 4015 | 5.050 |
| postgres | 482.145 | (± 8.0%) | 2450 | 5.081 |
| basic_metadata_tests update_with_many_fields | ||||
| sequel_postgres | 503.969 | (±19.2%) | 2546 | 5.052 |
| postgres | 393.030 | (±13.6%) | 1989 | 5.061 |
| basic_metadata_tests delete_stubs | ||||
| Warning, ran out of stubs to delete. Extrapolating. | ||||
| sequel_postgres | 1479.763 | (± 4.5%) | 7780 | 5.258 |
| Warning, ran out of stubs to delete. Extrapolating. | ||||
| postgres | 663.848 | (±51.8%) | 3325 | 5.009 |
| basic_metadata_tests delete_with_many_fields | ||||
| Warning, ran out of big items to delete. Extrapolating. | ||||
| sequel_postgres | 2559.037 | (± 1.8%) | 13079 | 5.111 |
| Warning, ran out of big items to delete. Extrapolating. | ||||
| postgres | 501.137 | (±62.1%) | 2508 | 5.005 |
| member_tests create_parent | ||||
| sequel_postgres | 368.357 | (±15.5%) | 1862 | 5.055 |
| postgres | 134.309 | (±15.9%) | 714 | 5.316 |
| member_tests reload_parent | ||||
| sequel_postgres | 1050.062 | (± 3.9%) | 5355 | 5.100 |
| postgres | 864.855 | (± 2.4%) | 4361 | 5.042 |
| member_tests update_child | ||||
| sequel_postgres | 934.457 | (± 4.9%) | 4692 | 5.021 |
| postgres | 637.122 | (± 2.5%) | 3213 | 5.043 |
| member_tests update_parent | ||||
| sequel_postgres | 362.266 | (±10.1%) | 1813 | 5.005 |
| postgres | 136.678 | (± 9.4%) | 686 | 5.019 |
| member_tests one_more_page | ||||
| sequel_postgres | 340.676 | (±12.6%) | 1728 | 5.072 |
| postgres | 140.262 | (± 7.1%) | 714 | 5.090 |
| member_tests remove_page | ||||
| sequel_postgres | 779.612 | (± 4.4%) | 3952 | 5.069 |
| postgres | 459.082 | (± 5.9%) | 2296 | 5.001 |
| member_tests find_parents | ||||
| sequel_postgres | 2034.769 | (± 8.3%) | 10230 | 5.028 |
| postgres | 1245.298 | (±23.3%) | 6298 | 5.057 |
| member_tests find_members | ||||
| sequel_postgres | 71.984 | (± 2.8%) | 364 | 5.057 |
| postgres | 49.044 | (± 4.1%) | 250 | 5.098 |
| alternate_id_tests create | ||||
| sequel_postgres | 1027.375 | (± 4.8%) | 5184 | 5.046 |
| postgres | 544.055 | (± 6.0%) | 2756 | 5.066 |
| alternate_id_tests find | ||||
| sequel_postgres | 985.589 | (± 1.9%) | 4950 | 5.022 |
| postgres | 1087.837 | (± 3.6%) | 5500 | 5.056 |
I'm getting vastly different numbers, both for sequel_postgres and postgres. For the first few basic tests where you have between 500 and 1000 iterations/s, I get around 90 iterations/s for both adapters. I'm guessing it's probably due to some Postgres server settings. I've tried with Postgres 9.5 and 10 and running them on default settings as they come in Ubuntu. I'm just curious what kind of server tuning you've done or what might explain the massive difference in the numbers.
@ojlyytinen This is running on my desktop, which is a mac pro with 8 cores, 64 GB of ram, and a SSD. So maybe it's just hardware related?
Edit: I would expect your results if ruby was capping your CPU or something.
Might be hardware related yes. I'm doing this on a fairly basic desktop. SSD might make a big difference too. For me sequel_postgres was only slightly, if at all, faster than normal postgres. Just shows that people should probably do their own benchmarking on the hardware they're going to use before making any critical decisions.
We're using this in Figgy in production now with no problems, and are showing somewhere between 10-30% speed-ups according to metrics for operations with queries that return many objects.
I just put in a couple patches that should speed up valkyrie-sequel significantly too - curious if https://github.com/samvera-labs/valkyrie-sequel/pull/5 specifically helps your metrics @ojlyytinen ?
Below is what I'm getting now. This version also cleans the database between every test and then recreates some objects for each test separately (--clean parameter). While most of the tests should be comparable to ones done before, in some cases this could have a sizeable effect.
Not sure how big a change that one pull request made but I think around 10-30% improvement compared to plain postgres seems about right. In some cases it's much better and in a few sequel is slightly worse.
My numbers in general are quite a bit lower and standard deviation is much bigger. I suspect me running this on normal hard drive as opposed to SSD might have a fairly big effect and then how hard drive caching might explain the big standard deviation in my numbers.
| Adapter | iterations/s | std.dev. | iterations | time (s) |
|---|---|---|---|---|
| basic_metadata_tests create_stub | ||||
| postgres | 99.098 | (±41.1%) | 497 | 5.015 |
| sequel_postgres | 83.257 | (±38.6%) | 417 | 5.009 |
| active_record_postgres | 95.565 | (±44.0%) | 478 | 5.002 |
| basic_metadata_tests create_with_text | ||||
| postgres | 82.997 | (±21.1%) | 415 | 5.000 |
| sequel_postgres | 88.971 | (±45.1%) | 445 | 5.002 |
| active_record_postgres | 78.449 | (±45.7%) | 393 | 5.010 |
| basic_metadata_tests create_with_many_fields | ||||
| postgres | 73.376 | (±30.2%) | 376 | 5.124 |
| sequel_postgres | 83.065 | (±46.9%) | 416 | 5.008 |
| active_record_postgres | 79.656 | (±42.9%) | 399 | 5.009 |
| basic_metadata_tests update_stub | ||||
| postgres | 86.080 | (±31.9%) | 431 | 5.007 |
| sequel_postgres | 77.399 | (±52.5%) | 388 | 5.013 |
| active_record_postgres | 77.999 | (±43.2%) | 390 | 5.000 |
| basic_metadata_tests update_with_text | ||||
| postgres | 85.704 | (±44.0%) | 429 | 5.006 |
| sequel_postgres | 79.396 | (±36.7%) | 397 | 5.000 |
| active_record_postgres | 83.589 | (±19.0%) | 418 | 5.001 |
| basic_metadata_tests update_with_many_fields | ||||
| postgres | 78.350 | (±42.2%) | 392 | 5.003 |
| sequel_postgres | 85.603 | (±37.7%) | 428 | 5.000 |
| active_record_postgres | 82.261 | (±34.0%) | 412 | 5.008 |
| basic_metadata_tests delete_stubs | ||||
| postgres | 85.132 | (±56.5%) | 426 | 5.004 |
| sequel_postgres | 158.273 | (±27.2%) | 791 | 4.998 |
| active_record_postgres | 84.887 | (±21.2%) | 425 | 5.007 |
| basic_metadata_tests delete_with_many_fields | ||||
| postgres | 85.406 | (±25.1%) | 427 | 5.000 |
| sequel_postgres | 93.513 | (±25.6%) | 468 | 5.005 |
| active_record_postgres | 92.803 | (±25.7%) | 464 | 5.000 |
| member_tests create_parent | ||||
| postgres | 71.150 | (±22.6%) | 356 | 5.003 |
| sequel_postgres | 89.688 | (±25.9%) | 449 | 5.006 |
| active_record_postgres | 16.520 | (±11.8%) | 83 | 5.024 |
| member_tests reload_parent | ||||
| postgres | 1302.078 | (± 9.8%) | 6504 | 4.995 |
| sequel_postgres | 1368.765 | (± 9.6%) | 6837 | 4.995 |
| active_record_postgres | 1358.207 | (±10.1%) | 6784 | 4.995 |
| member_tests update_child | ||||
| postgres | 87.495 | (±33.5%) | 438 | 5.006 |
| sequel_postgres | 71.157 | (±53.0%) | 356 | 5.003 |
| active_record_postgres | 86.937 | (±35.9%) | 435 | 5.004 |
| member_tests update_parent | ||||
| postgres | 81.864 | (±12.6%) | 410 | 5.008 |
| sequel_postgres | 79.486 | (±29.7%) | 398 | 5.007 |
| active_record_postgres | 35.585 | (±24.5%) | 178 | 5.002 |
| member_tests find_parents | ||||
| postgres | 607.747 | (±41.5%) | 3038 | 4.999 |
| sequel_postgres | 537.198 | (± 5.4%) | 2685 | 4.998 |
| active_record_postgres | 401.582 | (± 8.0%) | 2008 | 5.000 |
| member_tests find_members | ||||
| postgres | 71.537 | (± 7.0%) | 358 | 5.004 |
| sequel_postgres | 120.759 | (± 8.2%) | 604 | 5.002 |
| active_record_postgres | 35.008 | (± 5.7%) | 176 | 5.027 |
| member_tests one_more_page | ||||
| postgres | 80.705 | (±12.6%) | 404 | 5.006 |
| sequel_postgres | 81.765 | (±17.0%) | 410 | 5.014 |
| active_record_postgres | 36.153 | (±22.1%) | 181 | 5.007 |
| member_tests remove_page | ||||
| postgres | 85.815 | (±33.9%) | 430 | 5.011 |
| sequel_postgres | 83.038 | (±42.3%) | 416 | 5.010 |
| active_record_postgres | 37.639 | (±19.4%) | 189 | 5.021 |
| alternate_id_tests create | ||||
| postgres | 83.670 | (±37.6%) | 419 | 5.008 |
| sequel_postgres | 86.672 | (±45.8%) | 434 | 5.007 |
| active_record_postgres | 78.941 | (±19.4%) | 395 | 5.004 |
| alternate_id_tests find | ||||
| postgres | 1658.876 | (± 6.3%) | 8282 | 4.993 |
| sequel_postgres | 2710.105 | (± 6.8%) | 13530 | 4.992 |
| active_record_postgres | 1011.226 | (± 7.0%) | 5052 | 4.996 |