Loading…
Topic
MySQL
36 posts about MySQL. Every summary links to the original.
- 36 posts
- 9 companies
The Good Stuff
Paid picks we use ourselves. Links carry a referral code — a subscription through one earns us a commission at no extra cost to you.
Newest first
Posts
Real-time data ingestion in Grab
Service teams at Grab historically had to dual-write transactional data into databases and Kafka, creating data integrity issues during transaction failures alongside substantial schema maintenance overhead. To overcome these limitations and eliminate burst reads from SQL-based queries, the Caspian team built a real-time ingestion platform synchronising MySQL, Aurora, and DynamoDB directly to Kafka. For MySQL and Aurora, the platform uses Debezium with Kafka Connect on ROW-format binlogs, while DynamoDB changes are captured via DynamoDB streams with auto-scaling AWS Lambda functions. Messages encoded in Protobuf are transported via Kafka and ingested into Amazon S3 using a Golang stream processor. This architecture supports search indexing in Elasticsearch, automated data lake pipelines, cross-region disaster recovery replication, and audit trails.
Nerd Out on 10 of Our Favorite Posts From 2021
Shopify’s year-end roundup curates ten favorite engineering posts from 2021, spanning Ruby, storefronts, data analysis, databases, search, GraphQL, CI, APIs, and mobile development, while also noting several older reader favorites. The selection includes tutorials on building a web app with standard Ruby libraries, exploratory data analysis, GraphQL, Apache Beam for search, and React Native App Clips, alongside technical accounts of Hydrogen, YJIT, MySQL upgrades, and GraphQL rate limiting. YJIT was merged upstream and included in Ruby 3.1.0, while the CI team reduced the p95 of Shopify’s core monolith from 45 minutes to 18 minutes. The roundup also describes query-cost-based GraphQL rate limiting, rollback-testing roadblocks during a MySQL upgrade, and an App Clip effort that used a fail-fast, iterative approach.
Upgrading MySQL at Shopify
In September 2021, Shopify completed its cutover from Percona Server 5.7.21 to 5.7.32 across a MySQL fleet spanning hundreds of horizontally sharded databases and thousands of virtual machines. The upgrade targeted replication improvements from transaction writesets, along with security and stability fixes, while avoiding the larger risks of a major-version change. The Database Platform team extended Mason into a declarative topology system and used Chef roles to provision upgraded VMs, restore backups, run mysql_upgrade when required, configure replication, and populate replacement topologies. Extensive rollback testing exposed a critical downgrade bug; after patching 5.7.21, Shopify proceeded in staggered batches beginning August 25 and completed the final shards on September 7, while documenting a repeatable process intended to make future upgrades faster and safer.
Debugging Systems in the Cloud: MySQL, Kubernetes, and Cgroups
KateSQL, Shopify’s Database-as-a-Service platform on GKE, investigated why otherwise comparable MySQL Pods took between 10 and 30 minutes to be created during instance replacement. Measurements found extreme differences for Pods with identical resources and datasets: one required 2,120 seconds for initialization and 1,104 seconds for startup, while another took 74 and 17 seconds, respectively; affected Pods also accumulated more slow queries involving temporary memory tables. Profiling showed InnoDB’s single-threaded buffer pool initialization spending substantial time in mmap calls and the Linux memory cgroup function mem_cgroup_commit_charge, while affected nodes had roughly 50,000 memory cgroups. The team linked the slowdown to a Linux kernel cgroup memory leak and slab reparenting behavior, then improved performance through node replacement, memory.force_empty testing, and GKE 1.18 upgrades with relevant fixes.
Search indexing optimisation
Grab Deliveries uses MySQL as canonical storage and Elasticsearch as derived search storage, with Food-Puxian synchronising updates through Kafka. The original incremental-sync design treated Kafka events mainly as notifications, reloaded records from MySQL, and used separate consumers and in-memory event buffering, creating heavy database traffic, data-loss gaps, coupling to table schemas, redundant Elasticsearch updates, and duplicate cascade updates. A CDC system based on MySQL row-based Binlog provides common events containing Operation, PayloadBefore, and PayloadAfter, enabling filtering of irrelevant changes, partial Elasticsearch updates, event merging in the buffer, and deduplication of cascade updates through a dedicated Kafka stream. The reported results were an 80% reduction in database load from the first optimisation and an 80% reduction in cascade-update events. Overall, DSP saved about 91% of database reads and 90% of Elasticsearch reads, while stream-consumer throughput rose from 200 to 800 QPS, with peaks above 1000 and lower synchronisation latency.
Shard Balancing: Moving Shops Confidently with Zero-Downtime at Terabyte-scale
Shopify’s MySQL shards can become unevenly utilized as merchants grow after being assigned to arbitrary shards, increasing over-utilization risk while leaving other capacity underused. The post describes a strategy for selecting shop placements using historical utilization and traffic data, simulations, and potentially other metrics such as shop size, GMV, and move time. It then explains online shop migration with Ghostferry, which batch-copies records and tails MySQL’s binary log before a brief cutover updates the routing table and directs new writes to the target pod. Verification checks data completeness, integrity, and stale traffic before old data is pruned; the migration system’s central algorithm is also represented in a TLA+ formal specification. The stated outcome is a more balanced infrastructure that can isolate high-traffic merchants across pods with virtually zero consumer-facing downtime.
Dynamic ProxySQL Query Rules
Shopify uses ProxySQL to manage application connections to MySQL databases, but applying query rules across thousands of ProxySQL instances is difficult, especially during incidents. ProxySQL rules can reroute, rewrite, or reject regex-matched queries, yet incorrect patterns may cause unintended rejection, rewriting, data corruption, or downtime. To make rule changes safer, Shopify added dry-run support to a ProxySQL fork, using the mysql_query_rules table’s active and log fields; dry-run rules log matched queries without executing their actions. A sidecar polls a global key-value store, synchronizes dynamic rules to each ProxySQL instance, caches rules, supports optional TTLs, and uses exponential backoff for broad logging patterns. The system favors consistency during synchronization failures, while static startup rules cover requirements for full availability.
Capturing Every Change From Shopify’s Sharded Monolith
Shopify’s separate internal warehouse and Merchant Analytics Platform created duplicated work, differing extraction results, and incompatible tooling while demand grew for fresher, low-latency merchant data. Longboat, its query-based CDC service, polls sharded-monolith replicas in batch jobs using updated_at, but queries can run only hourly and cannot detect hard deletes or updates that bypass that field. Shopify therefore adopted Kafka and Debezium CDC to capture database changes, standardize extraction, and make the event stream usable by both streaming and batch consumers. CDC-backed Beam modeling reduced average freshness for one migration from one day to one hour, while Kafka became a shared source for operational and analytical data. Shopify still identifies incremental snapshotting and streaming-state problems, including reconciling MySQL table chunks with ongoing binlog events without expensive locks, as ongoing work.
Read Consistency with Database Replicas
Database replicas can improve read-heavy application performance and reduce pressure on primary servers, but differing replication lag can make multi-query reads produce incoherent datasets. Shopify’s Database Connection Management team compared tight consistency and GTID-based causal consistency before choosing opt-in monotonic read consistency, in which related reads are routed to one replica and therefore follow a consistent timeline rather than necessarily seeing the latest data. Their ProxySQL fork accepts a UUID in the consistent_read_id query comment, hashes it into a pseudorandom index over a weighted hostgroup server list, and disables later rebalancing that could override the selection. The implementation indexes configured servers before handling outages, though a selected server failure can still break consistency. The approach offers low overhead but tolerates occasional inconsistencies.
Simplify, Batch, and Cache: How We Optimized Server-side Storefront Rendering
The post explains how a new Ruby-based server-side Storefront Renderer reduced the time required to serve Shopify storefront requests. It combines MySQL multi-statement queries, handcrafted SQL, a thin data-mapping layer built from plain old Ruby objects, query book-keeping with eager- and lazy-loading, multiple LRU caching layers, and techniques for reducing memory allocations. For a product page, one database round trip can load the product, variants, images, shop, theme, and related resources; later requests can replay previously observed queries early, while less frequently used data remains lazy-loaded. The resulting renderer serves 75% of requests in under ~45ms, 90% in under ~230ms, and 99% in under ~900ms, with average response time nearly five times faster than the previous implementation.
How to Introduce Composite Primary Keys in Rails
Shopify addressed inefficient data access in its multi-tenant Rails application, where auto-incrementing primary keys interleaved records from many shops even though queries usually targeted one shop. InnoDB stores rows in B+ trees and loads pages into a buffer pool, so the team changed orders and other suitable tables to composite primary keys such as [shop_id, order_id] while keeping an auto-incrementing id secondary key and configuring Active Record to use id. Supporting Ghostferry, migration tooling, and data extraction systems also had to accommodate the schema change, with unique secondary constraints avoided in some cases because LHM migrations could deadlock. On the most queried table, common queries improved 5–6x, median buffer pool reads fell from 1.8 to 1.2, and elapsed database time dropped roughly one hour per day per shard, while inserts became about 10x slower.
How Shopify Manages Petabyte Scale MySQL Backup and Restore
Shopify manages a petabyte-scale fleet of MySQL servers across replica-sets, or shards, in three Google Cloud Platform regions, where file-based Xtrabackup archives made shard restores take more than six hours. To reduce that Recovery Time Objective, the team built Kubernetes CronJobs around the Compute Engine Persistent Disk snapshot API, scheduling snapshots every 15 minutes while accounting for regions, zones, instance roles, and MySQL consistency variables. Persistent Disk snapshots take about 20 minutes initially and typically under 10 minutes incrementally; restoring the latest snapshot to a new volume and starting MySQL, including InnoDB instance recovery and replication-lag recovery, typically brings RTO below 30 minutes. Retention tooling keeps the latest two snapshots plus policy-defined dailies and weeklies, while verification checks startup, GTID auto-positioning, and InnoDB page corruption; offsite tooling compresses, encrypts, and transfers exported data to another provider.
Data First, SLA Always
Grab's Data Engineering team transitioned from periodic batch ETL ingestion to a real-time change data capture architecture called Trailblazer after dataset sizes exceeded the petabyte mark. The previous system caused severe JDBC timeouts and heavy CPU loads when executing chunked or full-scan queries on unindexed upstream MySQL tables. To solve this, MySQL binary logs are captured via Debezium on Kafka Connect, buffered in Kafka, and ingested into a data lake using Spark Structured Streaming. Checkpoints are decoupled from local storage and persisted in a Redis cluster to simplify ingestion offset overrides and handle ephemeral compute clusters. The system incorporates extensive health monitoring across Airflow, Datadog, and custom services to maintain stream liveliness and avoid Kafka retention breaches.
How an Intern Released 3 Terabytes Worth of Storage Before BFCM
While preparing for Black Friday Cyber Monday (BFCM), Shopify’s Products team found that historical reporting records were causing MySQL to scan many InnoDB pages to return a single record. An intern built a Rails background job to delete the records safely, using Shopify’s job-iteration library to process small batches and an internal throttling enumerator to pause when database health or replication lag became a concern. Indexed fetch queries and cursor-based batching avoided repeated scans and timeouts, while the iterator deleted each batch. Run about two weeks before BFCM, the job deleted more than 5 billion records, peaked at approximately six million records per minute, and released three terabytes of storage. Afterward, the database scanned around three times fewer pages per returned record, improving resilience to flash-sale traffic and producing large cost savings.
Lost in Translations: Bringing the World to Shopify
Shopify describes the engineering challenge of internationalizing a platform whose twelve years of development left thousands of hardcoded English strings across millions of lines of code. The team first used cops, linters, and tests to stop new violations, then automated extraction of existing content into module-specific dictionaries using keys derived from file paths and strings. A Rails I18n-based pseudolocalization backend replaced characters and doubled vowels, making untranslated content visible while exposing UI problems caused by longer words; UTF-8 migrations and application fixes addressed character-set and internationalization bugs. Finally, an internal Translation Platform connected GitHub webhooks and a translation management system, automatically submitting changed language files and returning approved translations as commits or pull requests. After six months, Shopify was available in a variety of languages, while the team continued addressing localization issues involving formatting, geography, payments, shipping, iconography, and regional requirements.
Deep Dive into Database Timeouts in Rails
Following a production outage where a database failover caused a Ruby on Rails application to exhaust its Puma server threads, an investigation was conducted to understand how ActiveRecord and MySQL timeout settings behave. A reproduction environment using Docker, Puma, and Toxiproxy replicated how hanging requests to a failing database consume all available server threads, ultimately starving unrelated endpoints. The analysis breaks down ActiveRecord connection pooling mechanics alongside underlying mysql2 and libmysqlclient settings, specifically checkout_timeout, connect_timeout, and read_timeout. Testing confirmed how existing and new TCP connections transition through socket states during network interruptions while waiting on configured timeout intervals.
Related topics
All topics →Weekly digest
The best engineering reads, once a week.