From 2ea78cff47fd05d658a5f3c02f64f44c4404d995 Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Sun, 7 Jul 2019 21:23:18 +0100 Subject: Tweak how logs are stored Previously, the query for the jobs page was really slow, as it checked the load_new_guix_revision_job_log_parts table for each job, doing a sequential scan through the potentially large table. Adding an index didn't seem to help, as the query planner would belive the query could return loads of rows, where actually, all that needed checking is whether a single row existed with a given job_id. To avoid adding the index to the load_new_guix_revision_job_log_parts table, and fighting with the query planner, this commit changes the load_new_guix_revision_job_logs table to include a blank entry for jobs which are currently being processed. This is inserted at the start of the job, and then updated at the end to combine and replace all the parts. This all means that the jobs page should render quickly now. --- guix-data-service/jobs/load-new-guix-revision.scm | 22 ++++++++++++++++------ ...x_revision_job_logs_contents_to_be_nullable.sql | 7 +++++++ ...x_revision_job_logs_contents_to_be_nullable.sql | 7 +++++++ sqitch/sqitch.plan | 1 + ...x_revision_job_logs_contents_to_be_nullable.sql | 7 +++++++ 5 files changed, 38 insertions(+), 6 deletions(-) create mode 100644 sqitch/deploy/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql create mode 100644 sqitch/revert/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql create mode 100644 sqitch/verify/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql diff --git a/guix-data-service/jobs/load-new-guix-revision.scm b/guix-data-service/jobs/load-new-guix-revision.scm index 7eb6cdf..fc76416 100644 --- a/guix-data-service/jobs/load-new-guix-revision.scm +++ b/guix-data-service/jobs/load-new-guix-revision.scm @@ -96,7 +96,8 @@ (string-append "SELECT " (sql-html-escape (get-characters "contents")) - " FROM load_new_guix_revision_job_logs WHERE job_id = $1")) + " FROM load_new_guix_revision_job_logs" + " WHERE job_id = $1 AND contents IS NOT NULL")) (define parts-query (string-append @@ -113,6 +114,13 @@ (((contents)) contents))))) +(define (insert-empty-log-entry conn job-id) + (exec-query + conn + "INSERT INTO load_new_guix_revision_job_logs (job_id, contents) VALUES +($1, NULL)" + (list job-id))) + (define (combine-log-parts! conn job-id) (with-postgresql-transaction conn @@ -120,10 +128,13 @@ (exec-query conn (string-append - "INSERT INTO load_new_guix_revision_job_logs (job_id, contents) " - "SELECT job_id, STRING_AGG(contents, '' ORDER BY id ASC) FROM " + "UPDATE load_new_guix_revision_job_logs SET contents = " + "(" + "SELECT STRING_AGG(contents, '' ORDER BY id ASC) FROM " "load_new_guix_revision_job_log_parts WHERE job_id = $1 " - "GROUP BY job_id") + "GROUP BY job_id" + ")" + "WHERE job_id = $1") (list job-id)) (exec-query conn @@ -613,8 +624,6 @@ SELECT ), EXISTS ( SELECT 1 FROM load_new_guix_revision_job_logs WHERE job_id = load_new_guix_revision_jobs.id - UNION ALL - SELECT 1 FROM load_new_guix_revision_job_log_parts WHERE job_id = load_new_guix_revision_jobs.id ) AS log_exists FROM load_new_guix_revision_jobs ORDER BY load_new_guix_revision_jobs.id DESC") @@ -686,6 +695,7 @@ ORDER BY load_new_guix_revision_jobs.id DESC") (let ((result (with-postgresql-connection (lambda (logging-conn) + (insert-empty-log-entry logging-conn id) (let ((logging-port (log-port id logging-conn))) (set-current-output-port logging-port) (set-current-error-port logging-port) diff --git a/sqitch/deploy/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql b/sqitch/deploy/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql new file mode 100644 index 0000000..2ca9187 --- /dev/null +++ b/sqitch/deploy/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql @@ -0,0 +1,7 @@ +-- Deploy guix-data-service:change_load_new_guix_revision_job_logs_contents_to_be_nullable to pg + +BEGIN; + +ALTER TABLE load_new_guix_revision_job_logs ALTER COLUMN contents DROP NOT NULL; + +COMMIT; diff --git a/sqitch/revert/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql b/sqitch/revert/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql new file mode 100644 index 0000000..509aace --- /dev/null +++ b/sqitch/revert/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql @@ -0,0 +1,7 @@ +-- Revert guix-data-service:change_load_new_guix_revision_job_logs_contents_to_be_nullable from pg + +BEGIN; + +ALTER TABLE load_new_guix_revision_job_logs ALTER contents SET NOT NULL; + +COMMIT; diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan index a03da58..7a37548 100644 --- a/sqitch/sqitch.plan +++ b/sqitch/sqitch.plan @@ -14,3 +14,4 @@ license_support 2019-05-13T20:37:40Z Christopher Baines # Add dates_to_load_new_guix_revision_jobs 2019-06-02T07:39:49Z Christopher Baines # Add dates to the load_new_guix_revision_jobs table load_new_guix_revision_job_events 2019-06-02T15:44:41Z Christopher Baines # Add new table for guix_revision_job_events load_new_guix_revision_job_logs 2019-06-21T14:33:09Z chris # Add load_new_guix_revision_job_logs +change_load_new_guix_revision_job_logs_contents_to_be_nullable 2019-07-07T20:10:54Z Christopher Baines # Change the contents field in the load_new_guix_revision_job_logs table\nto be nullable.\n\nwith '#' will # be ignored, and an empty message aborts the add. #\nChange to add: # #\nchange_load_new_guix_revision_job_logs_contents_to_be_nullable #\nsqitch/deploy/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql\nsqitch/revert/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql\nsqitch/verify/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql diff --git a/sqitch/verify/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql b/sqitch/verify/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql new file mode 100644 index 0000000..a165548 --- /dev/null +++ b/sqitch/verify/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql @@ -0,0 +1,7 @@ +-- Verify guix-data-service:change_load_new_guix_revision_job_logs_contents_to_be_nullable on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- cgit v1.2.3