Database performance tweaks

[frol]

I want to collect the knowledge we gain over running Indexer for Explorer in production here.

[frol]

We hit hight IO wait on our database instance that we only use to WRITE indexed data, so one of the entry-points was this post: https://serverfault.com/a/284824

There I learned about https://wiki.postgresql.org/wiki/Performance_Optimization and https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Here are the discussed settings:

max_connections - we don't need to tweak that since we maintain 15 connections per Indexer for Explorer instance and the upper bound is currently 500 connections

shared_buffers - it seems that GCP has auto-tweak for this as it is already set to 5GB

effective_cache_size - it seems that GCP has auto-tweak for this as it is already set to 6GB

checkpoint_segments - deprecated, so we should look into min_wal_size and max_wal_size tweaking instead

checkpoint_completion_target - already set to 0.9

default_statistics_target - we currently have the default 100 and it is not clear if we need to tweak it at this point

work_mem - we already set it to 64MB and I recently bumped it to 128MB without any noticeable outcomes

maintenance_work_mem - we don't seem to need to tweak it now

wal_sync_method, wal_buffers - I have not made any decision about those yet

constraint_exclusion, max_prepared_transactions - already has good defaults

synchronous_commit - this was on, and I am going to try to switch it off

random_page_cost - the default is 4 and it is suggested to lower it to 2, so I will try it

UPD: I have not seen any improvement from synchronous_commit and random_page_cost tweaks, so we can revert them next time we need to upgrade anything.

[frol]

I bumped the hardware of the main instance (and all the replicas) to 8 vCPU and 52GB RAM

UPD: That helped a bit, but not enough to resolve the lag we have

[frol]

I have disabled storing NFT and account_changes while keeping transaction and action-receipt actions on the beta nodes (started from block 57526420), and that helped a bit (it feels that it can keep up with the network, but still won't be able to catch up)

[frol]

UPD: the SELECT query should be completely removed; I am addressing it now.

It revealed unnecessary code in indexer-for-explorer, so there is no need to optimize it

Currently I see the following stats:

The top dominating queries are:

SELECT
  "execution_outcome_receipts"."produced_receipt_id",
  "receipts"."originated_from_transaction_hash"
FROM ("execution_outcome_receipts"
  INNER JOIN
    "receipts"
  ON
    "execution_outcome_receipts"."executed_receipt_id" = "receipts"."receipt_id")
WHERE
  "execution_outcome_receipts"."produced_receipt_id" = ANY($1)

INSERT INTO
  "execution_outcomes" ("receipt_id",
    "executed_in_block_hash",
    "executed_in_block_timestamp",
    "index_in_chunk",
    "gas_burnt",
    "tokens_burnt",
    "executor_account_id",
    "status",
    "shard_id")
VALUES
  ($1, $2, $3, $4, $5, $6, $7, $8, $9),
  ($10, $11, $12, $13, $14, $15, $16, $17, $18),
  ($19, $20, $21, $22, $23, $24, $25, $26, $27),
  ($28, $29, $30, $31, $32, $33, $34, $35, $36),
  ($37, $38, $39, $40, $41, $42, $43, $44, $45),
  ($46, $47, $48, $49, $50, $51, $52, $53, $54),
  ($55, $56, $57, $58, $59, $60, $61, $62, $63),
  ($64, $65, $66, $67, $68, $69, $70, $71, $72),
  ($73, $74, $75, $76, $77, $78, $79, $80, $81),
  ($82, $83, $84, $85, $86, $87, $88, $89, $90),
  ($91, $92, $93, $94, $95, $96, $97, $98, $99),
  ($100, $101, $102, $103, $104, $105, $106, $107, $108),
  ($109, $110, $111, $112, $113, $114, $115, $116, $117),
  ($118, $119, $120, $121, $122, $123, $124, $125, $126),
  ($127, $128, $129, $130, $131, $132, $133, $134, $135),
  ($136, $137, $138, $139, $140, $141, $142, $143, $144),
  ($145, $146, $147, $148, $149, ...

@telezhnaya ~Could you dive into the performance of those? I think it is easier to start with the SELECT query.~

[frol]

@telezhnaya ~I have seen various posts regarding IN vs ANY(ARRAY[]) vs ANY(VALUES()), e.g. https://www.datadoghq.com/blog/100x-faster-postgres-performance-by-changing-1-line/. Yet, it seems that there is no "correct" answer to the problem, and we should consider if we can do any better~

We don't need to deal with that query at all, as it should be removed.