Unexpected slowdown of indexing speed with Bytes as IDs and Immutable entities

[steegecs]

I would like to report what I believe to be a bug with the recent updated that claimed performance improvements with the addition on bytes as IDs and immutable entities.

I expected both of these updates to improve the performance, but I have gotten strange results. With the addition or Bytes as IDs and immutable entities separately and both together, they all see a decrease in performance from the baseline implementation (without either update).

The odd thing about these decreases is that the addition of Bytes as ID's only decreased the indexing speed by about 23%, adding immutable entities only decreased the speed by around 6.8%, and when I added both, the decrease was only about 1.2%.

The details of how the test was conducted and an explanation of the results can be found in the Messari repo here:
https://github.com/messari/subgraphs/blob/master/docs/Perf.md#:~:text=Bytes%20and%20Immutables%20Test,the%20desired%20performance%20improvements.

The two instances of my subgraph implementation can be found here:
https://github.com/steegecs/subgraphs/tree/steegecs/uniswap-forks-IDs
https://github.com/steegecs/subgraphs/tree/steegecs/uniswap-forks-bytes

The former was used to test the regular implementation and the implementation with the addition of immutable entities.
The latter was used to test the addition of Bytes as IDs and both Bytes as IDs and the addition of immutable entities.

If you have any questions, please let me know. I am happy to help explain any further.

[lutter]

@steegecs Thanks for the detailed writeup! Did you do these experiments in the hosted service? If so, let me know the subgraph names/IPFS hashes, and I can dig up details about where indexing spent time from our internal metrics. In general, there is so much stuff going on in the hosted service that I am not sure that comparing data between subgraphs will be super useful. We have an internal setup that I can use to run a subgraph in isolation; it would be easiest for me if you gave me dead simple instructions of how/what to deploy for the different scenarios.

[steegecs]

No problem! Yes - I did these experiments in the hosted service.

Here you can find the deployments:
Not Bytes IDs or Immutables:
URL - https://thegraph.com/hosted-service/subgraph/steegecs/uniswap-v2
IPFS - QmXW3qvxV7zXnwRntpj7yoK8HZVtaraZ67uMqaLRvXDXhA
Fork - uniswap-forks-IDs
Code - https://github.com/steegecs/subgraphs/tree/steegecs/uniswap-forks-IDs/subgraphs/uniswap-forks

With Immutable entities:
URL - https://thegraph.com/hosted-service/subgraph/steegecs/uniswap-v2-tes
IPFS - QmPmZ5XQKU5XHNKMjuju8GSWFUQvWc52pJmAwj8nDBKz7x
Fork - uniswap-forks-IDs-Immutables
Code - https://github.com/steegecs/subgraphs/tree/steegecs/uniswap-forks-IDs-Immutables/subgraphs/uniswap-forks

Bytes IDs:
URL - https://thegraph.com/hosted-service/subgraph/steegecs/uniswap-v2-bytes
IPFS - Qmb4si7uJdJM5iSfs8Sp7hwxbGdvdhmwSyfbPZbfVtBEAE
Fork - uniswap-forks-bytes
Code - https://github.com/steegecs/subgraphs/tree/steegecs/uniswap-forks-bytes/subgraphs/uniswap-forks

Both Bytes IDs and Immutables:
URL - https://thegraph.com/hosted-service/subgraph/steegecs/uniswap-v2-tes2
IPFS - QmUqhGZV6tbo3PzheJ6YwX6yvpmG2e1Gp8J6qQGKuYWfjz
Fork - uniswap-forks-bytes-Immutables
Code - https://github.com/steegecs/subgraphs/tree/steegecs/uniswap-forks-bytes-Immutables/subgraphs/uniswap-forks

From my repository, you can just run the uniswap-forks code from the four separate branches. They should be set to go.
Here is the head of the repository - https://github.com/steegecs/subgraphs

Basically - the only difference between the branches should be the presence or lack of immutable entities or the necessary adjustments in the code to account for the use of bytes as the IDs with the necessary updates in the schema. There should be six immutable entities that have them when they are being tested - RewardToken, Deposit, Withdraw, Swap, Account, ActiveAccount.

The subgraph you will want to test is the Uniswap V2 subgraph. There is a script that prepares and deploys them. So from the uniswap-forks folders, you can just run "yarn deploy-uniswap-v2-mainnet" once you update the deployment location.

This is the specific script in the package.json file:
"deploy-uniswap-v2-mainnet": "yarn prepare:uniswap-v2-mainnet && yarn codegen && yarn build && graph deploy --product hosted-service steegecs/uniswap-v2"

[github-actions]

Looks like this issue has been open for 6 months with no activity. Is it still relevant? If not, please remember to close it.

[0xdavinchee]

any updates on this? @lutter

[lutter]

@0xdavinchee The long and short of it is that I don't fully trust these numbers because they were taken against the hosted service; I haven't been able to run similar tests in a more controlled environment. The hosted service is not a good place for taking such numbers since it uses multiple databases, with varying amounts of load, and there's no outside control over the database (or the index node) to which a subgraph gets deployed.

I'll try and get some numbers from a more controlled environment, but am not sure how quickly I can get to that.

[lutter]

Thanks to @steegecs for creating subgraphs that can be used to compare the effect of Bytes as IDs and immutable entities. After fixing an issue with the initial subgraphs and testing this in a controlled environment, the results are very encouraging:

| Variant   | ID                                             | block avg | speedup | block max | sync time |
|-----------+------------------------------------------------+-----------+---------+-----------+-----------|
| base      | Qme678y1MsTDA41p3wWvNY8dTCRQm3UtgTm5DNUunsAxfW |       268 |       - |       616 | > 331 hrs |
| immutable | QmX2LdzGo3Ubi11hBmxM7tnbuX6rYEtfuGabCJJyWvQ2je |       217 |     19% |       485 | 292 hrs   |
| bytes     | QmYGqqxVMGvMfQkJnZffiwPgg3LEfzHgLi1KaygNRC3aLX |       225 |     16% |       551 | 299 hrs   |
| both      | QmWvCSfdjuJGcUHHAwuYT4PTvDDELThnvq4CWjQdPXbDMU |       194 |     28% |       542 | 259 hrs   |

The speedup with using both bytes as ID and immutable entities is definitely better than what I would have expected, but this shows that using them can be very beneficial for indexing speed.

Closing this issue since there is no slowdown, and in fact, a pretty good speedup.