September 2022 Summaries

VARCHAR is a variable-length data type for strings in MySQL, storing up to 65,535 characters. It allocates extra space with each value stored, depending on the character set used, and has an overhead of 1 or 2 bytes. CHAR is a fixed-length data type, right-padded with empty spaces to always store n characters, regardless of the string's length. Character sets affect storage space allocation when saving data. When using CHAR, trailing spaces are effectively lost and not returned in queries. The choice between VARCHAR and CHAR depends on the specific use case, such as needing more than 255 characters or fixed-length strings with known lengths.

The PlanetScale team used a new error tracking feature to troubleshoot an issue with their Insights application. They noticed occasional upticks in the "Query errors" graph and investigated further, finding that errors were coming from the `DatabaseBranchPasswords#create` action. The errors occurred when multiple nearly-simultaneous API requests were issued, causing a race condition where two threads could both query the database at the same time before either thread had created the record, resulting in an error. To solve this issue, they modified their internal tool to avoid issuing multiple password create requests in parallel. Additionally, the team improved how tags work for Insights errors, allowing users to search queries and errors based on tags with new syntax options.

JavaScript Object Notation (JSON) is a lightweight text-based file format that simplifies data exchange, invented by Douglas Crockford in the early 2000s. JSON supports various data types such as strings, numbers, booleans, objects, and arrays, with MySQL implementing rudimentary support for the JSON data type since version 5.7.8 in mid-2015. While JSON provides flexibility, it also comes with caveats, including increased storage capacity and potential performance impact due to indexing limitations. However, MySQL's robust set of JSON functions enables operations such as creating, updating, reading, and validating JSON documents, making it a viable option for storing log output, API responses, configuration data, or complex relations in relational databases.

The PlanetScale serverless driver for JavaScript allows developers to connect to their databases over HTTP, enabling them to build serverless APIs on AWS using a Lambda function and API Gateway. To use the driver, developers need to create a database on PlanetScale, enable the beta feature, and set up environment variables. They then create a Lambda function with the driver installed, upload it to AWS, and configure API Gateway to integrate with the Lambda function. The driver supports common query types such as SELECT, INSERT, UPDATE, and DELETE, allowing developers to build RESTful APIs for their databases. With the driver, developers can also use HTTP methods to interact with their databases, enabling more flexibility in building serverless applications.

The DevOps movement has introduced the concept of Infrastructure as Code, which allows defining infrastructure in specially formatted files and automating its creation or modification using automation tools. Atlas CLI is a command line tool that helps manage database schemas by keeping a representation of the schema in a file, allowing for automated updates based on a definition file. To use Atlas CLI, one needs to create a PlanetScale account, install and configure the PlanetScale CLI and Atlas CLI, and set up a new database with a specific connection string. The tool generates a schema definition file that can be modified and applied using the `atlas schema apply` command. This approach allows for version control of database schemas, providing accountability and historical references to track changes over time. However, when used with PlanetScale, it requires use on a non-production branch due to disabled DDL on production branches.

The article describes setting up a pipeline to deploy an API to two environments, QA and Production, using AWS CodeBuild, GitHub, and PlanetScale. The pipeline automates the process of testing and merging changes between two instances of a database by creating isolated branches in PlanetScale for each environment. The pipeline uses AWS CodeBuild to build and deploy the container image to ECR and Lightsail, and also updates the schema in PlanetScale using the PlanetScale CLI. The pipeline is configured to trigger on GitHub commits to the QA branch and on pull requests to the main branch. The article provides step-by-step instructions for setting up the pipeline and configuring the necessary AWS services.

The PlanetScale team has published a benchmarking exercise using the TAOBench workload, which is designed to simulate social media networks and their scalability challenges. The results show that PlanetScale's multi-tenant serverless database offering can sustainably scale to one million queries per second under extreme resource pressure, with sustained stability even when approaching 100% CPU utilization. The benchmark highlights the importance of finding a balance between resource efficiency and graceful failure handling in distributed database systems, and demonstrates PlanetScale's capabilities in managing complex database workloads.

Gated Schema Deployments` is a new feature introduced by PlanetScale to simplify and streamline the process of making changes to a sharded database. The deployments can now be thought of as atomic operations, reducing complexity and allowing multiple schema changes to be made simultaneously without disrupting the database. This feature addresses two main challenges: multi-dimensional deployments, where multiple changes are applied in parallel, and multi-shard deployments, where different shards have different schemas. With `Gated Deployments`, users can now choose when to apply changes, giving them more control over their schema migrations, and the deployment process is now more closely tied to data changes, ensuring that no change is reflected in production until it's fully complete.

PlanetScale, a database service built on top of Vitess, has demonstrated its ability to scale horizontally through sharding, achieving a goal of one million queries per second. The company ran benchmarks using the tpc-c sysbench workload and found that adding more shards increased throughput capacity, but eventually reached diminishing returns due to resource saturation. By doubling the number of shards, PlanetScale was able to exceed the target query volume, with 40 shards providing over 1 million QPS sustained for a 5-minute run. This showcases the scalability of PlanetScale's database service and highlights its ability to handle large query volumes predictably through horizontal scaling.