From 70c549dad8320a2bd2f46bb39521b571423bef36 Mon Sep 17 00:00:00 2001 From: Dessalines Date: Mon, 23 Jan 2023 09:59:25 -0500 Subject: [PATCH] Speeding up comment-ltree migration, fixing index creation. Fixes #2664 (#2670) * Speeding up comment-ltree migration, fixing index creation. Fixes #2664 * Adding some logging lines, fixing for missing posts. * Adding more postgres config --- Cargo.lock | 2 + crates/db_schema/Cargo.toml | 2 + crates/db_schema/src/utils.rs | 3 ++ docker/dev/docker-compose.yml | 7 ++-- .../2022-07-07-182650_comment_ltrees/up.sql | 37 ++++++++++++++++++- 5 files changed, 47 insertions(+), 4 deletions(-) diff --git a/Cargo.lock b/Cargo.lock index 67a8b5cb..83a9494d 100644 --- a/Cargo.lock +++ b/Cargo.lock @@ -2488,6 +2488,8 @@ dependencies = [ "strum", "strum_macros", "tokio", + "tracing", + "tracing-error", "typed-builder", "url", ] diff --git a/crates/db_schema/Cargo.toml b/crates/db_schema/Cargo.toml index d996e2ac..83a32ba3 100644 --- a/crates/db_schema/Cargo.toml +++ b/crates/db_schema/Cargo.toml @@ -40,6 +40,8 @@ typed-builder = { workspace = true } async-trait = { workspace = true } tokio = { workspace = true } bb8 = { version = "0.8.0", optional = true } +tracing = { workspace = true } +tracing-error = { workspace = true } [dev-dependencies] serial_test = { workspace = true } diff --git a/crates/db_schema/src/utils.rs b/crates/db_schema/src/utils.rs index f6accb60..cf96b5e7 100644 --- a/crates/db_schema/src/utils.rs +++ b/crates/db_schema/src/utils.rs @@ -26,6 +26,7 @@ use lemmy_utils::{error::LemmyError, settings::structs::Settings}; use once_cell::sync::Lazy; use regex::Regex; use std::{env, env::VarError}; +use tracing::info; use url::Url; const FETCH_LIMIT_DEFAULT: i64 = 10; @@ -154,9 +155,11 @@ pub fn run_migrations(db_url: &str) { // Needs to be a sync connection let mut conn = PgConnection::establish(db_url).unwrap_or_else(|_| panic!("Error connecting to {}", db_url)); + info!("Running Database migrations (This may take a long time)..."); let _ = &mut conn .run_pending_migrations(MIGRATIONS) .unwrap_or_else(|_| panic!("Couldn't run DB Migrations")); + info!("Database migrations complete."); } pub async fn build_db_pool(settings: &Settings) -> Result { diff --git a/docker/dev/docker-compose.yml b/docker/dev/docker-compose.yml index e38d0ca1..639f798a 100644 --- a/docker/dev/docker-compose.yml +++ b/docker/dev/docker-compose.yml @@ -104,17 +104,18 @@ services: # "-c", "effective_cache_size=9GB", # "-c", "maintenance_work_mem=768MB", # "-c", "checkpoint_completion_target=0.9", + # "-c", "checkpoint_timeout=86400", # "-c", "wal_buffers=16MB", # "-c", "default_statistics_target=100", # "-c", "random_page_cost=4", # "-c", "effective_io_concurrency=2", - # "-c", "work_mem=7864kB", + # "-c", "work_mem=4GB", # "-c", "min_wal_size=1GB", - # "-c", "max_wal_size=4GB", + # "-c", "max_wal_size=30GB", # "-c", "max_worker_processes=4", # "-c", "max_parallel_workers_per_gather=2", # "-c", "max_parallel_workers=4", - # "-c", "max_parallel_maintenance_workers=2", + # "-c", "max_parallel_maintenance_workers=2" ] networks: - lemmyinternal diff --git a/migrations/2022-07-07-182650_comment_ltrees/up.sql b/migrations/2022-07-07-182650_comment_ltrees/up.sql index 401b1fb6..fde9e1b3 100644 --- a/migrations/2022-07-07-182650_comment_ltrees/up.sql +++ b/migrations/2022-07-07-182650_comment_ltrees/up.sql @@ -1,4 +1,3 @@ - -- Remove the comment.read column, and create a new comment_reply table, -- similar to the person_mention table. -- @@ -60,6 +59,19 @@ FROM q ORDER BY breadcrumb; +-- Remove indexes and foreign key constraints, and disable triggers for faster updates +alter table comment disable trigger all; + +alter table comment drop constraint if exists comment_creator_id_fkey; +alter table comment drop constraint if exists comment_parent_id_fkey; +alter table comment drop constraint if exists comment_post_id_fkey; +alter table comment drop constraint if exists idx_comment_ap_id; + +drop index if exists idx_comment_creator; +drop index if exists idx_comment_parent; +drop index if exists idx_comment_post; +drop index if exists idx_comment_published; + -- Add the ltree column update comment c set path = ct.ltree_path @@ -75,9 +87,32 @@ from ( ) as c2 where ca.comment_id = c2.id; +-- Delete comments at a depth of > 150, otherwise the index creation below will fail +delete from comment where nlevel(path) > 150; + +-- Delete from comment where there is a missing post +delete from comment c where not exists ( + select from post p where p.id = c.post_id +); + +-- Delete from comment where there is a missing creator_id +delete from comment c where not exists ( + select from person p where p.id = c.creator_id +); + +-- Re-enable old constraints and indexes +alter table comment add constraint "comment_creator_id_fkey" FOREIGN KEY (creator_id) REFERENCES person(id) ON UPDATE CASCADE ON DELETE CASCADE; +alter table comment add constraint "comment_post_id_fkey" FOREIGN KEY (post_id) REFERENCES post(id) ON UPDATE CASCADE ON DELETE CASCADE; +alter table comment add constraint "idx_comment_ap_id" unique (ap_id); + +create index idx_comment_creator on comment (creator_id); +create index idx_comment_post on comment (post_id); +create index idx_comment_published on comment (published desc); + -- Create the index create index idx_path_gist on comment using gist (path); -- Drop the parent_id column alter table comment drop column parent_id cascade; +alter table comment enable trigger all; -- 2.44.1