diff options
author | Andreas Brandl <abrandl@gitlab.com> | 2018-02-23 21:50:32 +0300 |
---|---|---|
committer | Andreas Brandl <abrandl@gitlab.com> | 2018-03-06 14:53:12 +0300 |
commit | f88d5132c2c94407838e589e450f1b4167fd2629 (patch) | |
tree | 235818918c40e209b83bd7bebccaae48e54284ec /db | |
parent | f579a08751b8f5f9be479812355cce623d29eb11 (diff) |
Simpler migration strategy for MySQL.
MySQL does not return the number of inserted rows (or at least not
expose them through cmd_tuples). Let's do one-shot INSERT instead.
Diffstat (limited to 'db')
-rw-r--r-- | db/post_migrate/20180223124427_build_user_contributed_projects_table.rb | 87 |
1 files changed, 58 insertions, 29 deletions
diff --git a/db/post_migrate/20180223124427_build_user_contributed_projects_table.rb b/db/post_migrate/20180223124427_build_user_contributed_projects_table.rb index 4f4e3617e12..40049d4ab65 100644 --- a/db/post_migrate/20180223124427_build_user_contributed_projects_table.rb +++ b/db/post_migrate/20180223124427_build_user_contributed_projects_table.rb @@ -6,31 +6,12 @@ class BuildUserContributedProjectsTable < ActiveRecord::Migration disable_ddl_transaction! - BATCH_SIZE = 100000 - SLEEP_TIME = 30 - def up - with_index(:events, [:author_id, :project_id], name: 'events_user_contributions_temp', where: 'project_id IS NOT NULL') do - iteration = 0 - records = 0 - begin - Rails.logger.info "Building user_contributed_projects table, batch ##{iteration}" - result = execute <<~SQL - INSERT INTO user_contributed_projects (user_id, project_id) - SELECT e.user_id, e.project_id - FROM (SELECT DISTINCT author_id AS user_id, project_id FROM events WHERE project_id IS NOT NULL) AS e - LEFT JOIN user_contributed_projects ucp USING (user_id, project_id) - WHERE ucp.user_id IS NULL - LIMIT #{BATCH_SIZE} - SQL - iteration += 1 - records += result.cmd_tuples - Rails.logger.info "Building user_contributed_projects table, batch ##{iteration} complete, created #{records} overall" - Kernel.sleep(SLEEP_TIME) if result.cmd_tuples > 0 - end while result.cmd_tuples > 0 - end - - execute "ANALYZE user_contributed_projects" if Gitlab::Database.postgresql? + if Gitlab::Database.postgresql? + PostgresStrategy.new + else + MysqlStrategy.new + end.up end def down @@ -39,10 +20,58 @@ class BuildUserContributedProjectsTable < ActiveRecord::Migration private - def with_index(*args) - add_concurrent_index(*args) unless index_exists?(*args) - yield - ensure - remove_concurrent_index(*args) if index_exists?(*args) + class PostgresStrategy < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + BATCH_SIZE = 100_000 + SLEEP_TIME = 30 + + def up + with_index(:events, [:author_id, :project_id], name: 'events_user_contributions_temp', where: 'project_id IS NOT NULL') do + iteration = 0 + records = 0 + begin + Rails.logger.info "Building user_contributed_projects table, batch ##{iteration}" + result = execute <<~SQL + INSERT INTO user_contributed_projects (user_id, project_id) + SELECT e.user_id, e.project_id + FROM (SELECT DISTINCT author_id AS user_id, project_id FROM events WHERE project_id IS NOT NULL) AS e + LEFT JOIN user_contributed_projects ucp USING (user_id, project_id) + WHERE ucp.user_id IS NULL + LIMIT #{BATCH_SIZE} + SQL + iteration += 1 + records += result.cmd_tuples + Rails.logger.info "Building user_contributed_projects table, batch ##{iteration} complete, created #{records} overall" + Kernel.sleep(SLEEP_TIME) if result.cmd_tuples > 0 + end while result.cmd_tuples > 0 + end + + execute "ANALYZE user_contributed_projects" + end + + private + + def with_index(*args) + add_concurrent_index(*args) unless index_exists?(*args) + yield + ensure + remove_concurrent_index(*args) if index_exists?(*args) + end end + + class MysqlStrategy < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + def up + execute <<~SQL + INSERT INTO user_contributed_projects (user_id, project_id) + SELECT e.user_id, e.project_id + FROM (SELECT DISTINCT author_id AS user_id, project_id FROM events WHERE project_id IS NOT NULL) AS e + LEFT JOIN user_contributed_projects ucp USING (user_id, project_id) + WHERE ucp.user_id IS NULL + SQL + end + end + end |