TechPrufer
Get started
GitLab
GitLab logoGitLab · Feature trail

Database Migration Debugging and Cross-Database Compatibility

15 posts · Dec 2014 – Oct 2024

What this trail covers

GitLab is decomposing its monolithic PostgreSQL database into independent databases, starting with the separation of CI-related data. This involved extensive planning, proof-of-concept work, and the development of tools and documentation to manage cross-database dependencies, such as cross-join detection, cross-database transaction detection, and loose foreign keys. The project leveraged Rails' multi-database support and addressed challenges like mirroring data. This post details a major version of this decomposition, highlighting a specific issue encountered with MySQL during a Rails migration that worked in PostgreSQL, leading to the discovery of a bug in MySQL's index rebuilding behavior when dropping columns. The fix involved explicitly removing the index before dropping the columns. This experience led to a refinement of their migration testing strategy to include cross-database compatibility checks.

All GitLab trails
  1. Seamlessly migrate from Jira to GitLab with Jira2Lab at scaleIntroduces Jira2Lab, a tool designed for large-scale Jira to GitLab migrations. It addresses challenges like massive data migration, custom field/workflow mapping, and the lack of full DevSecOps integration in existing tools. Key features include efficient data handling, automatic mapping of custom elements, CI/CD pipeline integration, pilot migrations, real-time monitoring, and flexibility. The post outlines a five-phase migration methodology (Discovery and Planning, Setup, Pilot Migrations, Scaled Migrations, Wrap-up and Post-Migration Support) and includes a case study of a large enterprise migration.
  2. We are upgrading the operating system on our Postgres database clustersThis post details the upgrade of the operating system on GitLab.com's Postgres database clusters from Ubuntu 16.04 to Ubuntu 20.04. The upgrade required a maintenance window due to glibc changes affecting Postgres indexes. The approach involved setting up new Ubuntu 20.04 clusters with streaming replication, promoting them during downtime, and reindexing potentially corrupt indexes. Performance improvements were made to the `amcheck` script for faster index corruption detection and to the reindexing process itself. Specific strategies were outlined for reindexing B-Tree, GiST, Hash, and GIN indexes.
  3. Decomposing the GitLab backend database, Part 1: Designing and planningThis post details the initial design and planning phase of decomposing the GitLab backend database. It covers the motivation for moving away from a monolithic Postgres database due to scaling limitations, the exploration of horizontal sharding versus vertical partitioning, and the analysis that identified CI tables as a prime candidate for extraction due to high write traffic. It also outlines the proof-of-concept process, the strategy for managing the 'moving target' of application evolution during the decomposition, and the development of tools like cross-join detection, cross-database transaction detection, query analyzer metrics, and a Rubocop rule to enforce explicit database connections. The post also discusses the use of Rails' multiple database support and the implementation of 'loose foreign keys' to handle inter-database dependencies asynchronously.
  4. Decomposing the GitLab backend database, Part 3: Challenges and surprisesThis post details specific challenges and surprises encountered during the production migration of GitLab's monolithic Postgres database to a decomposed architecture. It highlights issues with autovacuum on large CI tables delaying write blocking triggers, the need to track down and close long-running SSH database connections, the oversight of certain cron jobs, and an overload on the Sentry server due to client-side error tracking during downtime. It also discusses the impact of cascading replication on CI replica latency and the intricate process of incrementally re-balancing PGBouncer connections without saturating the main Postgres database, which required small, carefully analyzed steps during low utilization periods. The post concludes by noting that despite the added complexity of an additional database, developer productivity has remained largely unaffected due to Rails model abstraction, and the project has provided significant scalability headroom.
  5. GitLab database decomposition: Final migration and resultsThis post details the final migration process for decomposing the GitLab backend database, specifically focusing on the separation of CI-related data. It contrasts a planned zero-downtime approach with the chosen two-hour downtime strategy, highlighting the advantages of a simpler rollback mechanism and the necessity of extensive rehearsals. The post presents the positive results of the migration, including decreased primary database CPU utilization, significant storage savings, reduced dead tuples and vacuuming saturation, and a substantial reduction in average query duration for Sidekiq PGBouncer query pools.
  7. Upgrade your external PostgreSQL 12 & 13 servers nowThis post addresses a critical external dependency on PostgreSQL versions, specifically highlighting a use-after-free bug in PostgreSQL 12.x (<12.7) and 13.x (<13.3) that can cause segmentation faults. It informs users that GitLab EE versions 14.9+ and CE versions 15.1+ are affected if they are using these vulnerable PostgreSQL versions. It strongly recommends upgrading to patched PostgreSQL versions (12.7+ or 13.3+) and clarifies that Omnibus GitLab instances with bundled PostgreSQL are not affected. A link to an internal issue for further details is provided.
  8. We are splitting our database into Main and CIThis post details the planned split of GitLab.com's single PostgreSQL database into two distinct clusters: 'main' for general features and 'ci' for Continuous Integration-related features. This functional decomposition aims to increase database capacity by approximately 2x, improve performance through independent tuning, and enhance stability by reducing database saturation. The post outlines the background, impact, preparation steps, and the planned execution process, including a scheduled downtime.
  9. How to bring DevOps to the database with GitLab and LiquibaseThis post introduces a tutorial on integrating Liquibase with GitLab CI/CD to manage database changes. It details the setup of a GitLab project with Liquibase, including configuring CI/CD variables and a self-managed GitLab Runner. The tutorial demonstrates making database changes via a SQL changeset, merging them to trigger a pipeline, and showcases Liquibase commands like `checks run`, `update`, `history`, `diff`, and `snapshot` within the pipeline jobs. It also highlights the use of Liquibase Hub for reporting.
  10. Why we spent the last month eliminating PostgreSQL subtransactionsThis post details the debugging and resolution of performance issues caused by PostgreSQL subtransactions, specifically the `SAVEPOINT` SQL query, which was found to cause stalls on database replicas and impact CI/CD job processing. The team developed new instrumentation to track subtransactions and identified that the `SubtransControlLock` was the bottleneck. The fix involved eliminating all `SAVEPOINT` queries from the codebase.
  11. How to configure Sidekiq for specialized or large-scale GitLab instancesThis post details how to configure Sidekiq for specialized or large-scale GitLab instances. It covers identifying pain points through metrics (queue size, running jobs, Redis CPU), customizing Sidekiq configurations using routing rules and queue selectors for dedicated workloads (e.g., project exports), and the benefits of a one-queue-per-shard approach. It also provides examples for using queue selectors with `gitlab.rb` and Helm charts.
  12. GitLab's data migration process for Advanced SearchIntroduced a new, automated, background migration framework for Elasticsearch data, inspired by Rails DB migrations. This framework uses time-stamped migration files stored in Elasticsearch itself, with a background worker to execute them. It supports features like batched migrations, throttling, pausing indexing, and space requirement checks. This framework has been used to migrate issues, comments, and merge requests, and is crucial for the ongoing separation of document types into their own indices.
  13. These are your favorite GitLab engineering storiesThis post details the execution and lessons learned from several major infrastructure migrations and debugging efforts at GitLab. It covers the migration of GitLab.com from Azure to GCP, including performance improvements post-migration. It also describes the upgrade of a major PostgreSQL cluster, highlighting the planning, testing, and automation involved. The migration of GitLab.com to Kubernetes is discussed, along with the challenges and triumphs experienced in the year following the migration. The post also details debugging efforts for a Docker client library bug, an NFS bug impacting the Linux kernel, and a scaling problem on GitLab.com related to clock synchronization. Finally, it touches on the migration from legacy NFS to Gitaly, including performance diagnosis and fixes, and the use of Prometheus for anomaly detection and alerting.
  14. How we upgraded PostgreSQL at GitLab.comThis post details the successful major version upgrade of GitLab.com's main PostgreSQL cluster from version 9.6 to 11 using pg_upgrade. It outlines the planning, testing, and automation involved, including the use of blueprints and design documents. Key aspects covered are the reasons for the upgrade (EOL of 9.6, new features in 11), the existing architecture (Patroni, Consul, GCS), upgrade requirements (no regressions, automated process, short maintenance window), the phased project execution (isolated environment, staging tests, production upgrade), and the rollback plan. It also explains how pg_upgrade works and the considerations for external modules.
  15. Why we're ending support for MySQL in 12.1This post details the decision to end support for MySQL in GitLab 12.1, citing limitations in supporting nested groups, column limits, partial indexes, and the inability to leverage specific database features like PostgreSQL's LATERAL JOIN. It also highlights the performance impact of maintaining MySQL-specific code and running dual test suites, as well as the cost savings from removing MySQL support. The post confirms that most customers are on PostgreSQL and provides migration guidance.
  16. The road to Rails 5This post details the strategy and technical implementation of upgrading GitLab from Rails 4 to Rails 5. Key contributions include: developing a method to run the application on both Rails 4 and Rails 5 simultaneously using environment variables (`RAILS5`, `BUNDLE_GEMFILE`), splitting the upgrade into smaller, manageable issues, using a helper method (`Gitlab.rails5?`) to conditionally apply code for each Rails version, and a phased production release strategy to ensure stability. It also highlights challenges encountered with Active Record changes and monkey patches, and the importance of robust CI testing for both versions.
  17. How we used delayed replication for disaster recovery with PostgreSQLThis post details the implementation and benefits of using delayed replication with PostgreSQL for disaster recovery. It explains how to set up delayed archive recovery using `recovery_min_apply_delay` and how to use a delayed replica for point-in-time recovery by pausing replication and setting `recovery_target_time`. It also provides insights into PostgreSQL's internal implementation of `recoveryApplyDelay` and discusses the benefits over cold backups, such as eliminating the need to restore a full basebackup and having a fixed window of WAL to replay.
  18. Introducing Patroni as the Postgres Failover Manager on GitLab.comThis post announces the planned deployment of Patroni as the PostgreSQL Failover Manager on GitLab.com. It details two upcoming maintenance windows: a dry-run on December 8, 2018, and the actual cutover on December 15, 2018. During these windows, several GitLab services including the SaaS website, Git SSH/HTTPS, registry, CI/CD, and Pages will be unavailable. The maintenance windows are planned to be 30 minutes each.
  19. GitLab 11.5 adds Elasticsearch 6, removes ES 5.5 supportGitLab 11.5 introduces support for Elasticsearch 6.x and removes support for versions 5.5 and earlier. This change necessitates a reindex of Elasticsearch data due to incompatible index changes. The post details the technical reasons for this upgrade, specifically the removal of mapping types and the introduction of the `join` datatype in Elasticsearch 6.x, and explains why Elasticsearch 5.6 remains compatible. Users are instructed to upgrade Elasticsearch before upgrading GitLab.
  20. Postmortem of database outage of January 31This post details a major database outage caused by accidental data removal during an attempt to restore replication after WAL segment loss. It highlights critical failures in backup procedures (pg_dump version mismatch, DMARC rejection of error emails), reliance on LVM snapshots for DR, and the lack of documented procedures for pg_basebackup. The post also describes the immediate recovery steps and outlines future improvements to operations and recovery procedures, including implementing WAL archiving, improving backup verification, and enhancing runbooks.
  21. GitLab.com database incidentThis post details a critical database incident where production data was lost due to accidental deletion during recovery attempts. It outlines the sequence of events, including spam attacks, database lockups, replication lag, and the accidental wiping of a production database. The post also highlights severe problems encountered with backup and replication mechanisms, including non-functional regular backups, failing pg_dump, disabled Azure disk snapshots for DB servers, and fragile replication procedures. Recovery involved restoring from a staging backup and manually copying data. The incident led to a commitment to improve backup and replication strategies.
  22. Moving all your data, 9TB editionThis post details a large-scale data migration of 9TB of GitLab.com data from dedicated hardware to AWS. The migration utilized DRBD for block-level replication, employing a stacked resource configuration to mirror data from two production servers to a third AWS server. Networking challenges were overcome with IPIP tunnels and IPsec encryption to handle AWS NAT and ensure connectivity between on-premises and AWS environments. The process involved significant effort in setting up a test environment, configuring VPCs, and adapting failover scripts to accommodate the new three-way DRBD setup.
  23. GitLab got bit: MySQL fails at Rails migrations that work in PostgreSQLThis post details a specific failure mode encountered during a Rails database migration on MySQL, where dropping columns with a composite index caused MySQL to rebuild the index using a subset of columns, leading to duplicate index errors. The fix involved explicitly removing the composite index before dropping the columns. This incident highlighted a gap in their testing strategy, prompting a commitment to more rigorous cross-database compatibility testing for migrations.