feat: convert processing, block and message tables to hypertables #111
Conversation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
iand
force-pushed
the
iand/processing-partitioning
branch
2 times, most recently
from
2277493 to
168bdac
Compare
iand
force-pushed
the
iand/processing-partitioning
branch
2 times, most recently
from
8ab23e8 to
af1bfdb
Compare
iand
changed the title
iand
added a commit
that referenced
this pull request
Oct 21, 2020
…g stats table The processing indexes are very slow to create against populated databases. Remove them and add them back in as part of a later conversion of the processing tables to hypertables (see #111) While we are amending this migration, also change the structure of the visor_processing_stats table. Fixes #120
iand
force-pushed
the
iand/processing-partitioning
branch
8 times, most recently
from
2a7bffb to
bf26b5b
Compare
|
Note that github hides the migration file by default since it is large |
willscott
approved these changes
Oct 21, 2020
lanzafame
suggested changes
Oct 22, 2020
iand
added a commit
that referenced
this pull request
Oct 22, 2020
…g stats table (#130) The processing indexes are very slow to create against populated databases. Remove them and add them back in as part of a later conversion of the processing tables to hypertables (see #111) While we are amending this migration, also change the structure of the visor_processing_stats table. Fixes #120
iand
force-pushed
the
iand/processing-partitioning
branch
2 times, most recently
from
8521593 to
8c74514
Compare
This is a major rework of the database schema and may only be applied to an empty database. It is not reversible without droping the schema and restating. This change adds a height column to processing, message and actor tables as part of the primary key and uses it to partition the table as a timescale hypertable. Rough calculations of expected number of rows per epoch and row widths are included to estimate the potential chunk sizes of each hypertable. Timescale recommend that chunks are small enough that the most recent can fit into 25% of main memory, summed over all tables. Additionally the indexes on the visor_processing tables have been analysed to give the best performance for the work selection queries used by visor. The tipsets and message processing table indexes fully cover the query so only an index scan is performed. The actors processing table requires two indexes to cover the cases where all codes are specified vs zero codes. Also fixes the sector_id columns for miner_pre_commit_infos, miner_sector_infos and miner_deal_sectors which were created as autoincrement serials by the go-pg orm package.
iand
force-pushed
the
iand/processing-partitioning
branch
from
8c74514 to
3d4befa
Compare
This was referenced Oct 26, 2020
Closed
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
This is a major rework of the database schema and may only be applied
to an empty database. It is not reversible without dropping the schema
and restating.
This change adds a height column to processing, message and actor
tables as part of the primary key and uses it
to partition the table as a timescale hypertable. Rough calculations of
expected number of rows per epoch and row widths are included to estimate
the potential chunk sizes of each hypertable. Timescale recommend that chunks
are small enough that the most recent can fit into 25% of main memory, summed
over all tables.
Additionally the indexes on the visor_processing tables have been analysed to
give the best performance for the work selection queries used by visor. The
tipsets and message processing table indexes fully cover the query so only an
index scan is performed. The actors processing table requires two indexes to
cover the cases where all codes are specified vs zero codes.
Also fixes the sector_id columns for miner_pre_commit_infos, miner_sector_infos and
miner_deal_sectors which were created as autoincrement serials by the go-pg
orm package.