Comments (14)
roadscape
commented on September 28, 2024
DEFERRABLE
NOT DEFERRABLE
This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable.
BEGIN; SET CONSTRAINTS ALL DEFERRED;
...
COMMIT
from hivemind.
roadscape
commented on September 28, 2024
https://www.postgresql.org/docs/9.4/static/runtime-config-resource.html
from hivemind.
roadscape
commented on September 28, 2024
(This comment has moved to its own issue: "payout sweep performance degrades quickly" #76)
from hivemind.
roadscape
commented on September 28, 2024
- shared_buffers = 128MB
+ shared_buffers = 4096MB
effective_cache_size = 4GB
- work_mem = 4MB
+ work_mem = 128MB
- maintenance_work_mem = 64MB
+ maintenance_work_mem = 1024MB
- synchronous_commit = on
+ synchronous_commit = off
- autovacuum_vacuum_scale_factor = 0.2
+ autovacuum_vacuum_scale_factor = 0.01
- autovacuum_analyze_scale_factor = 0.1
+ autovacuum_analyze_scale_factor = 0.005
- checkpoint_completion_target = 0.5
+ checkpoint_completion_target = 0.9
checkpoint_segments
from hivemind.
roadscape
commented on September 28, 2024
- effective_cache_size = 4GB
+ effective_cache_size = 12GB
- work_mem = 128MB
+ work_mem = 512MB
- maintenance_work_mem = 1024MB
+ maintenance_work_mem = 2GB
- random_page_cost = 4.0
+ random_page_cost = 1.0
from hivemind.
roadscape
commented on September 28, 2024
-- defaults
ALTER TABLE hive_accounts SET (autovacuum_vacuum_scale_factor = 0.2);
ALTER TABLE hive_accounts SET (autovacuum_analyze_scale_factor = 0.1);
ALTER TABLE hive_state SET (autovacuum_vacuum_scale_factor = 0.2);
ALTER TABLE hive_state SET (autovacuum_analyze_scale_factor = 0.1);
ALTER TABLE hive_reblogs SET (autovacuum_vacuum_scale_factor = 0.2);
ALTER TABLE hive_reblogs SET (autovacuum_analyze_scale_factor = 0.1);
ALTER TABLE hive_payments SET (autovacuum_vacuum_scale_factor = 0.2);
ALTER TABLE hive_payments SET (autovacuum_analyze_scale_factor = 0.1);
-- more aggresive
ALTER TABLE hive_posts SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE hive_posts SET (autovacuum_analyze_scale_factor = 0.005);
ALTER TABLE hive_post_tags SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE hive_post_tags SET (autovacuum_analyze_scale_factor = 0.005);
ALTER TABLE hive_feed_cache SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE hive_feed_cache SET (autovacuum_analyze_scale_factor = 0.005);
-- very aggresive
ALTER TABLE hive_posts_cache SET (autovacuum_vacuum_scale_factor = 0.0050); -- @ 36M posts, every 180k (~240k new posts/day)
ALTER TABLE hive_posts_cache SET (autovacuum_analyze_scale_factor = 0.0025); -- @ 36M posts, every 90k
ALTER TABLE hive_blocks SET (autovacuum_vacuum_scale_factor = 0.0100); -- @ 20M blocks, once a week
ALTER TABLE hive_blocks SET (autovacuum_analyze_scale_factor = 0.0014); -- @ 20M blocks, once a day
ALTER TABLE hive_follows SET (autovacuum_vacuum_scale_factor = 0.0050); -- @ 47M follows, once a day (~300k new follows/day)
ALTER TABLE hive_follows SET (autovacuum_analyze_scale_factor = 0.0025); -- @ 47M follows, 2-3 times a day
# consider using scale_factor = 0 with a flat threshold:
#autovacuum_vacuum_threshold = 50 # row updates before vacuum
#autovacuum_analyze_threshold = 50 # row updates before analyze
from hivemind.
roadscape
commented on September 28, 2024
basic config added to readme in d3757f8
autovac tuning added to setup in 4ad89a0
from hivemind.
bobinson
commented on September 28, 2024
any plan to use multiple (primary - secondary) postgreSQL setup ? Just curious as WAL etc will come into picture in that scenario.
from hivemind.
roadscape
commented on September 28, 2024
@bobinson currently the plan is for each instance to have its own db; a separate trailing service creates snapshots to quickly initialize new instances.
from hivemind.
bobinson
commented on September 28, 2024
so, every node is complete with the logic and the data-store.
- How will we scale ? We will not be able to horizontally scale as the logic is not independent of the db
- If we take out the db / data-store from these instances, we can scale the logic layer horizontally and apply sharading etc on the db to scale ?
Just curious.
a separate trailing service creates snapshots to quickly initialize new instances.
So, the performance criteria should also try to include the "snapshot" process to capture the full life-cycle of the application ?
from hivemind.
roadscape
commented on September 28, 2024
@bobinson - hive does not require this specific configuration, but it should suffice for now. For the hive indexer, the vast majority of time is spent between reading from upstream and writing to db.
The snapshot process alleviates initial sync time; it's important to keep an eye on how long it takes, but real-time chain indexing and API performance are far more critical.
from hivemind.
roadscape
commented on September 28, 2024
- checkpoint_timeout = 5min
+ checkpoint_timeout = 30min
- max_wal_size = 1GB
+ max_wal_size = 4GB
from hivemind.
roadscape
commented on September 28, 2024
Initial sync concerns moved to #95 and awaiting test results; everything else complete.
from hivemind.
roadscape
commented on September 28, 2024
autovacuum/autoanalyze stats after a few days of operation, looks reasonable:
relname | sf | last_vacuum | last_autovacuum | n_tup | dead_tup | av_threshold | expect_av | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
------------------+-------+-------------+------------------+----------------+----------------+----------------+-----------+--------------+------------------+--------------+------------------+---------------+-------------------
hive_follows | 0.005 | | 2018-02-27 22:30 | 49,818,488 | 220,487 | 249,142 | | | 2018-03-02 06:49 | 0 | 2 | 0 | 13
hive_posts_cache | 0.005 | | 2018-03-02 06:29 | 36,921,216 | 425,835 | 184,656 | * | | 2018-03-02 06:38 | 0 | 6 | 0 | 11
hive_posts | 0.01 | | | 33,824,896 | 40,426 | 338,299 | | | 2018-03-02 01:25 | 0 | 0 | 0 | 7
hive_post_tags | 0.01 | | | 26,052,486 | 35,668 | 260,575 | | | 2018-03-02 01:36 | 0 | 0 | 0 | 6
hive_blocks | 0.01 | | | 20,320,516 | 2,033 | 203,255 | | | 2018-03-02 08:23 | 0 | 0 | 0 | 6
hive_feed_cache | 0.01 | | | 10,936,753 | 64,004 | 109,418 | | | 2018-03-01 18:56 | 0 | 0 | 0 | 6
hive_reblogs | 0.2 | | | 3,289,994 | 1,070 | 658,049 | | | | 0 | 0 | 0 | 0
hive_accounts | 0.2 | | 2018-03-02 07:57 | 1,388,210 | 345,826 | 277,692 | * | | 2018-03-02 08:03 | 0 | 11 | 0 | 20
hive_payments | 0.2 | | | 8,598 | 20 | 1,770 | | | 2018-03-01 18:29 | 0 | 0 | 0 | 5
hive_state | 0.2 | | 2018-03-02 11:03 | 1 | 46 | 50 | | | 2018-03-02 11:03 | 0 | 54 | 0 | 54
from hivemind.
Related Issues (20)
- del
- review types 2 & 3 validation
- Sync doesn't work! HOT 1
- clarify startup config output
- nsfw validation HOT 1
- resolve feed/blog inconsistency HOT 1
- schema changes
- openmic tag missing HOT 2
- standardize new vs. legacy mute logic
- notifs: mark read HOT 1
- iredeemables/blacklists issues
- additional community props HOT 5
- iredeemables spamming reply notifications
- get_community timings HOT 1
- Allow pagination for method 'list_subscribers'
- master merge prep
- Fat Node without market_history is apparently useless for hivemind
- I can't specify specialized endpoints
- Issue with the bridge.get_ranked_posts API
- The community title contains unexpected string HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from hivemind.