Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Explore usefulness of indexes idx_t_transactions_node_account and idx_t_transactions_payer_id #548

Closed
apeksharma opened this issue Feb 20, 2020 · 3 comments
Closed

Explore usefulness of indexes idx_t_transactions_node_account and idx_t_transactions_payer_id #548

apeksharma opened this issue Feb 20, 2020 · 3 comments
Labels
database Area: Database P3 performance

Comments

@apeksharma
Copy link
Contributor

apeksharma commented Feb 20, 2020 •
edited
Loading

In PR#546 (https://github.com/hashgraph/hedera-mirror-node/pull/546#pullrequestreview-361816742), there is a good suggestion to explore usefulness of indexes idx_t_transactions_node_account and idx_t_transactions_payer_id.
We should measure how does it affect (or not) the rest-api queries, and drop them if they are not needed to get some more write performance.

Update: idx_t_transactions_node_account was removed in #817
@apeksharma apeksharma self-assigned this Feb 20, 2020
@apeksharma apeksharma removed their assignment May 21, 2020
@apeksharma
Copy link
Contributor Author

Usage of these two indexes ~4 months ago (from here)

 schemaname |       tablename       |                        indexname                        |  num_rows   | table_size | index_size | unique | number_of_scans | tuples_read  | tuples_fetched
------------+-----------------------+---------------------------------------------------------+-------------+------------+------------+--------+-----------------+--------------+----------------
 public     | t_transactions        | idx_t_transactions_node_account                         |  3.7633e+07 | 5380 MB    | 1554 MB    | N      |          664265 |       664264 |         664264
 public     | t_transactions        | idx_t_transactions_payer_id                             |  3.7633e+07 | 5380 MB    | 1553 MB    | N      |        53098367 |     53098370 |       53098366

Usage of these two indexes today:

 schemaname |       tablename       |                        indexname                        |  num_rows   | table_size | index_size | unique | number_of_scans | tuples_read  | tuples_fetched
------------+-----------------------+---------------------------------------------------------+-------------+------------+------------+--------+-----------------+--------------+----------------
 public     | t_transactions        | idx_t_transactions_node_account                         | 1.43182e+08 | 23 GB      | 4615 MB    | N      |          664265 |       664264 |         664264
 public     | t_transactions        | idx_t_transactions_payer_id                             | 1.43182e+08 | 23 GB      | 4613 MB    | N      |        95993726 |     95993729 |       95993725

idx_t_transactions_node_account can be dropped without second thought. Need to think a bit about idx_t_transactions_payer_id....

@apeksharma
Copy link
Contributor Author

Additionally, on failover mainnet:

 public     | t_transactions | idx_t_transactions_node_account     | 2.13758e+08 | 39 GB      | 6182 MB    | N      |               0 |           0 |              0

and on dev

public     | t_transactions | idx_t_transactions_node_account     | 2.13758e+08 | 39 GB      | 6182 MB    | N      |               0 |           0 |              0

@apeksharma apeksharma mentioned this issue Jun 10, 2020
Merged
2 tasks
apeksharma added a commit that referenced this issue Jun 11, 2020
@apeksharma
Remove foreign keys on entity id (#817)
620cc9a 
- Replace foreign keys columns - cud entity, payer account, node account - with encoded entity ids
- Use encoded ids in REST to remove joins with t_entities table
- Migration testing: Migrating db with ~2.93m transactions took ~20sec. Extrapolating, mainnet with ~150m txns should take less than 30min
- Migration is done by creating new transactions table. Index idx_t_transactions_node_account is not created for the new table (#548)
- Change DataGenerator to use EntityId rather than just a long for entity num
- Remove dead functions from EntityRepository
- Now auto renew accounts and proxy accounts are inserted in batch (with other PreparedStatements) rather than synchronously during transaction processing
- There is one remaining entity lookup, will fix that later. It won't affect performance of crypto transfer heavy workloads

Signed-off-by: Apekshit Sharma <[email protected]>
@steven-sheehy
Copy link
Contributor

These indexes no longer exist

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
database Area: Database P3 performance
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@apeksharma @steven-sheehy