Database Benchmarking Best Practices

20 posts · Aug 2021 – May 2026

What this trail covers

PlanetScale Insights provides automated schema recommendations and query monitoring tools to improve database performance, reduce memory and storage usage, and enhance schema design. This post highlights how these tools were used to detect and resolve slow queries in the Arewefastyet benchmarking tool, resulting in an 85% decrease in query latency by adding missing indexes. The new error tracking feature in Insights helps users quickly view and understand database errors, enabling faster trouble. This post adds index usage tracking to Insights, allowing users to see which indexes are used by specific query patterns over time and to search for queries by index usage or lack thereof.

All PlanetScale trails

May 2026Problem solving with PlanetScale InsightsThis post details how to use PlanetScale Insights to diagnose and resolve database performance issues. It explains the meaning and utility of latency percentiles (p50, p95, p99, p99.9) for identifying query performance bottlenecks. It also describes how to leverage the query list, including sorting by p99 latency and using search filters like `indexed:false` and `p50:>250`, to find specific slow queries. The post further elaborates on analyzing individual query details, including row reads vs. rows returned as an indicator of missing indexes, and discusses strategies for addressing slow queries, such as adding indexes (including GIN trigram indexes for wildcard searches) or rethinking query and schema design. Finally, it introduces the concept of using AI agents via the MCP server to automate query analysis and suggest fixes.
May 2026On benchmarkingThis post introduces best practices for benchmarking databases, focusing on client-server architecture, resource selection, workload definition (traffic ratios, data distributions), open vs. closed loop benchmarks, and key metrics like throughput (QPS/TPS) and latency (percentiles). It emphasizes the importance of fair resource allocation, realistic workload simulation, and comprehensive measurement to avoid biased results.
Mar 2026Enhanced tagging in Postgres Query InsightsThis post introduces enhanced tagging capabilities to PlanetScale's Postgres Query Insights. It details how query tags, previously only metadata on individual logs, are now integrated into aggregated query data. This enables users to view tag distributions, search queries by tag, and see per-tag breakdowns of database-level statistics. The implementation leverages SQL comments (SQLCommenter) for tag injection and includes automatic tagging of `application_name`, `username`, and `remote_address`. The post also describes cardinality reduction strategies (per-tag limits and per-interval limits) to manage high-cardinality tags and optimize the data pipeline for efficient storage and processing of tag-enriched query data.
Nov 2025AI-Powered Postgres index suggestionsThis post introduces AI-powered index suggestions for PostgreSQL databases within PlanetScale Insights. It details the process of using LLMs to generate index recommendations by filtering candidate queries based on resource usage and schema, and then validating these suggestions using the HypoPG extension to estimate performance improvements. The feature aims to provide high-quality, validated index suggestions to users.
Oct 2025Benchmarking Postgres 17 vs 18This post benchmarks Postgres 17 against Postgres 18, specifically evaluating the performance impact of the new `io_method` settings (`sync`, `worker`, `io_uring`) on read-only workloads. It details extensive benchmark configurations using sysbench on various AWS EC2 instances with different storage types (gp3, io2, local NVMe) and IOPS/throughput settings. The analysis compares query-per-second (QPS) results for single and high concurrency scenarios, demonstrating how storage latency and IOPS, as well as workload characteristics (range size, concurrency), influence the performance of each `io_method`. It concludes that `io_method=worker` is a strong default, local NVMe storage offers superior price-performance, and `io_uring`'s benefits are workload-dependent.
Advertisement
Jul 2025Benchmarking PostgresThis post details the methodology and results of benchmarking PlanetScale Postgres against other cloud providers using latency, TPCC, and OLTP read-only workloads, emphasizing fairness in resource allocation and transparent reporting. It introduces an internal tool 'Telescope' for creating, running, and assessing benchmarks and provides links to detailed benchmark results against specific competitors.
Aug 2024Tracking index usage with InsightsThis post introduces index usage tracking to PlanetScale Insights. It details how index usage information is extracted from MySQL's InnoDB storage handler via VTGate and aggregated into time-series data. The feature allows users to view index usage per query pattern, search for queries by index usage, and identify unindexed queries. It also explains the limitations of `EXPLAIN` and MySQL's built-in performance schema for this purpose.
Apr 2024The MySQL adaptive hash indexThis post details the MySQL Adaptive Hash Index (AHI), an in-memory optimization that accelerates B-tree index lookups by creating hash tables for frequently accessed index values. It explains the underlying B-tree structure, the InnoDB buffer pool, and how AHI acts as a layer between MySQL execution and the buffer pool. The post demonstrates AHI's performance benefits through a benchmark, showing a ~16% speedup in query execution time by enabling the `innodb_adaptive_hash_index` option. It also highlights that AHI's effectiveness is dependent on a sufficiently large buffer pool and adaptive usage by MySQL.
Mar 2024Identifying and profiling problematic MySQL queriesThis post details how to use native MySQL's `performance_schema` and `sys` schemas to identify and profile problematic queries. It covers using `events_statements_summary_by_digest` to find expensive queries, `statements_with_runtimes_in_95th_percentile` to identify queries with high row examination, `table_io_waits_summary_by_index_usage` to detect full table scans, and `statements_with_full_table_scans` for queries triggering full table scans. It also explains how to use `EXPLAIN ANALYZE` for detailed query plan inspection.
Feb 2024Introducing schema recommendationsThis post introduces 'Schema recommendations' as a new feature within PlanetScale Insights. It details the 'Schema Advisor' system that generates recommendations by analyzing query performance data and schema. Specific recommendation types include adding indexes for inefficient queries (using query parsing and column cardinality estimation), removing redundant indexes (exact and left prefix duplicates), preventing primary key ID exhaustion (monitoring AUTO_INCREMENT values and foreign key joins), and dropping unused tables. The post provides an example of adding an index to improve query latency.
Nov 2023Introducing Insights AnomaliesThis post introduces PlanetScale Insights Anomalies, a new feature that automatically detects and highlights performance issues in production databases. It defines an anomaly as periods where the percentage of queries exceeding two standard deviations above their historical mean execution time (for the same query pattern) increases. The implementation involves aggregating query response times using probabilistic sketches, calculating per-query pattern thresholds, and counting outlier queries. The post details how anomalies are presented with correlated metrics (rows read/written, CPU, disk IOPS) and specific query patterns identified using Pearson correlation coefficient, aiding in root cause analysis.
Oct 2023Introducing database reportsThis post introduces weekly database reports delivered via email. These reports summarize slow database queries (powered by Query Insights), recent deploy requests and schema changes, and database storage changes (table-by-table). Users can manage their subscriptions in their settings. This builds upon existing database insights and reporting capabilities.
Jun 2023Using redundant conditions to unlock indexes in MySQLThis post introduces techniques for unlocking MySQL indexes that are otherwise obfuscated by functions or complex expressions. It explains how to use redundant conditions, which are logically superfluous but allow MySQL to utilize existing indexes. Two types of redundant conditions are detailed: logically impossible ones (e.g., `id < 5 AND id < 10`) and domain-specific ones (e.g., `updated_at < X AND created_at < X` where `created_at` is always <= `updated_at`). The post provides `EXPLAIN` output to demonstrate how these techniques enable index usage, improving query performance without requiring database schema changes.
Mar 2023How to read MySQL EXPLAINsThis post provides a detailed guide on how to interpret MySQL's `EXPLAIN` and `EXPLAIN ANALYZE` output. It explains the various columns and their significance, such as `SELECT_TYPE`, `rows`, `key_len`, and `extra`. It also introduces `EXPLAIN ANALYZE` as a tool that runs the query to provide actual execution costs alongside estimates. The post outlines the benefits and limitations of these tools and provides a breakdown of MySQL join types with performance indicators.
Feb 2023What are the disadvantages of database indexes?This post details the disadvantages of excessive secondary database indexes, specifically increased storage consumption and slower write operations (inserts, updates, deletes). It provides SQL queries to identify unused indexes based on cardinality and explains how to use MySQL's invisible indexes feature to test the impact of dropping an index before permanent removal. It also highlights PlanetScale's `Insights` feature for performance testing and its deploy request process for safe schema changes.
Oct 2022Optimizing queries in arewefastyetThis post details the process of using PlanetScale Insights to identify and resolve two slow queries in the Arewefastyet benchmarking tool. The identified queries were missing indexes on `finished_at` and `status` (for the OLTP/TPCC query) and `started_at` (for the benchmark comparison query). Adding these indexes via PlanetScale's deploy request workflow led to a significant reduction in query latency.
Sep 2022Debugging database errors with InsightsIntroduces a new error tracking feature to PlanetScale Insights, allowing users to view and debug database errors. Details an internal use case where the feature was used to identify and resolve `AlreadyExists` errors caused by a race condition in the `database_branch_password` table. Explains how the error tracking, combined with application-level debugging and understanding of uniqueness constraints, led to the identification of the root cause and a solution. Also enhances tag searching functionality for queries and errors in Insights.
Jul 2022Safely dropping MySQL tablesThis post introduces a feature within PlanetScale's deploy requests that warns users if a table they intend to drop has been recently queried. It also details how PlanetScale Insights can be used to identify table usage by filtering queries with `table:<name>` syntax and by leveraging SQL comment tags to trace query origins, thereby enabling safer table drops.
Jun 2022Identifying slow Rails queries with sqlcommenterThis post introduces the integration of the sqlcommenter format with Rails applications, building upon the native query commenting features in Rails 7. It details how to enable sqlcommenter using the `activerecord-sql_commenter` gem and configure Rails query log tags. The primary contribution is demonstrating how these enhanced query comments, when used with PlanetScale Query Insights, allow for more precise identification and resolution of slow queries by providing richer context for performance monitoring.
May 2022Introducing PlanetScale Insights: Advanced query monitoringIntroduces PlanetScale Insights, an in-dashboard query statistics tool with an interactive time-based graph for monitoring rows read, rows written, query latency, and queries per second. Adds a section showing all queries run in the last 24 hours with metrics like run count, total time, time per query, rows returned, rows read, and rows affected. Enables drilling down into individual queries to see slow instances and generate EXPLAIN plans. Demonstrates a practical use case of identifying a slow query linked to a schema change and confirming performance improvements.
Aug 2021Optimizing SQL with Query StatisticsThis post details the optimization of a slow SQL query within PlanetScale's database backup feature. By analyzing query statistics and MySQL explain plans, the team identified that a full table scan was being performed instead of using an existing composite index. Through analysis of data selectivity (percentage of soft-deleted rows vs. expired rows), they determined that a reordered index `(deleted_at, expires_at, data_deleted_at)` would be more effective. This change reduced the query's average runtime from 719ms to under 20ms.