abnormally long elapsed time

[AlstonChan]

I have been developing my application with Shovel indexing data from local blockchain (anvil) without any issue for a few months, but I notice that some integration is seriously lacking behind when backfilling compared to other integration.

My current setup has a total of 6 integration. Four of it indexes event from a single contract address, while the other two integration usesfilter_ref to reference one of the integration table's Ethereum address column to source data of the address.

• The four integration that source data from only a single contract from base mainnet use around 1-2 seconds.
• The fifth integration uses around 18 seconds
• The sixth integration uses around 1-3 minute

My integration is quite simple:

{
  "dashboard": {},
  "pg_url": "postgresql://postgres:secret@localhost:5432/shovel-test",
  "eth_sources": [
    {
      "name": "base",
      "chain_id": 8453,
      "url": "...",
      "urls": [
        "..."
      ],
      "poll_duration": "2s",
      "batch_size": 1,
      "concurrency": 1
    }
  ],
  "integrations": [
    { ...integration 1 },
    { ...integration 2 },
    { ...integration 3 },
    {
      ...
      ],
      "table": {
        "name": "index_pool",
        "columns": [...]
      },
      "block": [
         ...,
        {
          "name": "log_addr",
          "column": "src_addr",
          "filter_op": "contains",
          "filter_arg": [
            "$ADDRESS"
          ]
        }
      ],
      "event": {...}
    },
    {
      "enabled": true,
      "name": "uniswapPool_Swap",
      "sources": [
        {
          "name": "base",
          "start": "27836948"
        }
      ],
      "table": {... },
      "block": [
        ...,
        {
          "name": "log_addr",
          "column": "src_addr",
          "filter_op": "contains",
          "filter_ref": {
            "integration": "Pool",
            "column": "new_pool"
          }
        }
      ],
      "event": {
        "name": "Swap",
        "type": "event",
        "inputs": [...]
      }
    },
    {
      "enabled": true,
      "name": "Transfer",
      "sources": [
        {
          "name": "base",
          "start": "27836948"
        }
      ],
      "table": {
        "name": "index_transfers",
        "columns": [...]
      },
      "block": [
        ...
        {
          "name": "log_addr",
          "column": "src_addr",
          "filter_op": "contains",
          "filter_ref": {
            "integration": "Creation",
            "column": "token_address"
          }
        }
      ],
      "event": {
        "name": "Transfer",
        "type": "event",
        "inputs": [...]
      }
    }
  ]
}

I have also check with my postgres database to make sure that it isn't the database overloaded causing the issue. The query performance related to shovel table all have a mean time of a 100ms +-, so I think it isn't a db issue.

• Shovel version: af07
• Postgres version: 15.8.1.044

Outcome

This causes the indexed data to have a very large gap. While all 6 of my integrations are indexing the same blockchain, the actual block they have indexed currently has a distance of 15,000 blocks and still counting.

**Edit

I run it again with verbose logging, and found that the long elapsed time is from insert. Hope this helps.

• insert elapsed=14m58.7278259s
• converge elapsed=15m0.838574582s
• http-get-headers elapsed=1.361358186s
• http-get-logs elapsed=414.755637ms
• jrpc2-get elapsed=1.776220168s
• load elapsed=1.77627844s

[ryandotsmith]

It's likely due to filter_ref doing N queries instead of batching a block of logs into 1 query.

This function likely needs to be rewritten to batch its queries.

I don't have time to get to this anytime soon but patches are welcome.

[AlstonChan]

Got it. I am not familiar with golang, but I wanted to help.

Could you enlighten me on the filter_ref doing N queries? What I find currently for the reason of the prolong processing is due to Insert func processing every single log. Filter.Accept func takes about ~200ms to complete, so if a tx happens to contain 50 index, it would take 50 x 200ms = 10s just for a transaction.

[ryandotsmith]

Just to make sure we are solving the correct problem, if you remove your filters, do you see better performance?