Orion v2 benchmarking results

[Lezek123]

The first round of benchmarks has been completed.

• Both Orion v1 and Orion v2 were hosted on 8 GB Dedicated instances on Linode (https://www.linode.com/pricing/#compute-dedicated), in London data center.
• Orion v1 was hosted together with the local Query Node on the same instance.
• Both Orion v2 and Orion v1's Query Node were processing the current mainnet chain.

Scripts used for performing the benchmarks can be found here: https://github.com/Lezek123/orion/tree/orion-v2/src/tests/benchmarks

Results

Detailed results including all queries that were tested and the execution times of each individual attempt can be found here: https://github.com/Lezek123/orion/tree/orion-v2/benchmarking_results/21-02-2023

For each tested query there were 5 executions performed against both Orion v1 and Orion v2. The 5 resulting execution times were then aggregated using both avg (mean) and median function.

The formula to measure the improvement in given query execution time was:

(aggregated_orion_v1_execution_time - aggregated_orion_v2_execution_time) / min(aggregated_orion_v1_execution_time, aggregated_orion_v2_execution_time) * 100%

Note that this means the "improvement" can be negative.
Generally 100% improvement means the query is 2x faster in Orion v2 and -100% means it's 2x slower.

The queries were then divided into 5 categories:

• All queries (0ms - Infinity range)
• Queries of average exeuction time of 0 - 100 ms in Orion v1
• Queries of average execution time of 101 - 500 ms in Orion v1
• Queries of average execution time of 501 - 2000 ms in Orion v1
• Queries of average execution time of > 2001 ms in Orion v1

For each of those categories, the improvements were aggregated again using both median and avg (mean), which resulted in 4 calculated values per category:

• avg(avg_improvements)
• median(avg_improvements)
• avg(median_improvements)
• median(median_improvements)

All queries:

Details: https://github.com/Lezek123/orion/blob/orion-v2/benchmarking_results/21-02-2023/benchmark_result_with_events

All queries excluding notifications.graphql:

Details: https://github.com/Lezek123/orion/blob/orion-v2/benchmarking_results/21-02-2023/benchmark_result_without_events

Just queries from notifications.graphql:

Details: https://github.com/Lezek123/orion/blob/orion-v2/benchmarking_results/21-02-2023/benchmark_result_events_only

Conclusions

1. Generally the improvement is decent, for all queries tested the average observed improvement was 40%, which means queries in Orion v2 were found to execute ~1.4x faster on average.
2. Queries involving events, however, were found to perform much worse in Orion v2. The possible causes of this include:
   • The fact that all events are now stored in one table, which makes it harder to perform efficient queries when the total number events grows,
   • The fact that some redundant relationships inside events were removed, which simplifies the schema, but forces deeper filtering which involves more tables and can reduce query performance,
   • The fact that event data is now stored as jsonb field which may have negative impact on the performance as compared to separate table for each specific event.
     Further investigation is advised.
3. There has been a decrease in perfromance for smaller queries (0-100ms), but this seems to be mostly caused by a very bad performance of GetMetaprotocolTransactionStatusEvents query (~ -750%), which is another query that involves the events table and is in this case substantially skewing the average.
4. In general the improvements are much more pronounced for larger queries, which average improvement for queries in 500 - 2000 ms range being 100% (2x) and a stunning 389% (almost 5x faster execution time) for queries in > 2000 ms range.

[Lezek123]

Round 2

After making several optimalizations:

• 87f3ccb
• fb413ac
• 646d3d4

A second round of benchmarking was performed.

Results (all queries)

Details: https://github.com/Lezek123/orion/blob/orion-v2/benchmarking_results/22-02-2023/benchmark_result

Conclusion

As mentioned in #79, the event queries are now faster in Orion v2 than in Orion v1 (after the applied optimalizations). The only remaining minor issue is w/ queries between 0 and 100ms which are potentially slightly slower in Orion v2, but the difference seems now negligible (especially taking into account that Orion v2 does additional content filtering by default, which was not possible in Orion v1).

The average overall improvement is now over 100%, making Orion v2 on average a 2x faster backend than Orion v1 and significantly more faster (up to 5-10x) for bigger queries.