Enforcing enterprise conventions with Spatie guidelines skills and Laravel Boost

https://origin-main.com/wp-content/uploads/2026/08/feat-8813-1.png

🕒  6 Minute Read    📅  Date Published: August 9, 2026

AI coding agents write functional PHP fast, but speed isn’t the same as adherence. Left alone, an agent will happily produce verbose control flow, stale docblocks, and framework anti-patterns that any senior reviewer would bounce in code review. Spatie tackled this by packaging their production coding standards as spatie guidelines skills for Laravel Boost, a machine-readable format that agents like Claude Code, Cursor, and GitHub Copilot can load on demand instead of guessing at your team’s conventions.

This article is part of our wider AI Architecture Module coverage of agent tooling, where Laravel Boost and MCP infrastructure provide coding agents the context they need to ensure AI-generated code follows vendor-specified conventions, not just that it compiles.

If you haven’t wired up the transport layer yet, our guide to rolling out Boost across a Laravel 13 project covers the installation and agent registration steps this article assumes.

The skill activation pipeline

Why machine-readable skills beat static prompt files

A .cursorrules file or a bloated CLAUDE.md gets loaded into every single turn whether it’s relevant or not. That’s wasted context on a JavaScript formatting rule while you’re editing a migration. Skills packages solve this differently.

  • Context on demand. Agents activate spatie-laravel-php or spatie-security only when the file being touched matches, not on every prompt.
  • Team-wide consistency. Installing through Composer means every engineer, and every CI runner, pulls the identical ruleset. No copy-pasted markdown drifting between machines.
  • One-command updates. Spatie ships changes to the package; your team pulls them with a single sync command instead of manually re-pasting guideline files.

[Architect’s Note] The distinction matters more than it sounds. A prompt file is advisory, the agent reads it and might follow it. A skill is conditional, it fires deterministically on file type and task. That’s the difference between a suggestion and a guardrail.

Installing Spatie’s guidelines skills via Laravel Boost

Laravel Boost is the primary transport for registering skill packages inside a Laravel 13 project. Install both dev dependencies through Composer:

composer require laravel/boost --dev
composer require spatie/guidelines-skills --dev

Link the repository immediately, before you run anything else: spatie/guidelines-skills on GitHub. That’s the source of truth for the skill definitions you’re about to install, and it’s the first place to check when a rule looks off.

Running the installer

php artisan boost:install

Select Spatie guidelines from the vendor list, then choose your active coding agent. Boost writes the agent binding files, one skill per convention area: spatie-laravel-php, spatie-javascript, spatie-version-control, and spatie-security.

[Production Pitfall] Don’t skip .boost/skills/ in your .gitignore by habit. Teams that treat generated agent config as disposable end up with every developer running a different skill version locally, which defeats the entire point of pulling this through Composer in the first place.

Running outside Laravel Boost: skills.sh and global Claude Code

Not every project runs Boost. A standalone JavaScript service, a non-Laravel PHP microservice, or a developer who just wants Spatie’s PHP conventions available everywhere still needs a path in.

Global Claude Code integration

To apply the conventions across every local repository without touching individual projects, register them in your user home directory:

mkdir -p ~/.claude && touch ~/.claude/CLAUDE.md
curl -o ~/.claude/laravel-php-guidelines.md https://spatie.be/laravel-php-ai-guidelines.md
echo -e '\n## Coding standards\nWhen working with Laravel/PHP projects, first read the coding guidelines at @~/.claude/laravel-php-guidelines.md' >> ~/.claude/CLAUDE.md

Runtime-agnostic install via skills.sh

For non-Laravel services, install through the cross-runtime skills.sh registry directly:

npx skills add spatie/guidelines-skills

This gets you the same four skills without a Composer dependency, and it’s the path that works across Claude Code, Cursor, Codex, and Copilot uniformly.

[Word to the Wise] If your team runs a mixed stack, decide up front which install method is canonical for which repo type. Having half your services pull skills through Boost and the other half through skills.sh isn’t wrong, but it does mean two update commands to remember, and someone will forget the second one.

Keeping guidelines current: Context7 and CI sync

Enforcing syntax conventions only solves half the problem. When an agent generates code against a specific Spatie package, spatie/laravel-permission, spatie/laravel-medialibrary, it needs accurate, current API signatures, not whatever the model’s training data happened to capture. That’s a separate failure mode from style drift, and it’s the one that produces confidently wrong method calls.

To close that gap, wire in the Context7 MCP server. For background on building or extending your own protocol gateways, see our guide on production MCP server infrastructure in Laravel.

{
  "mcpServers": {
    "context7": {
      "command": "npx",
      "args": ["-y", "@upstash/context7-mcp@latest"]
    }
  }
}

With this configured, an agent implementing a Spatie package feature queries Context7 for live documentation instead of relying on stale training data.

Automating the sync

Guidelines evolve. Keeping every developer’s local skill definitions current, and catching drift in CI before it ships, means treating the sync as a scripted step rather than a reminder in Slack:

{
    "scripts": {
        "update-ai-skills": [
            "composer update spatie/guidelines-skills",
            "php artisan boost:update"
        ]
    }
}

Run composer run update-ai-skills during local setup and again as a pull-request check. This is the same discipline we cover in our CI pipeline guide for AI applications: the goal isn’t just passing tests, it’s guaranteeing the agent instructions in front of every contributor match the security and architectural rules currently in force.

[Efficiency Gain] Bundling boost:update into the same CI step you already run for schema validation costs you nothing extra in pipeline time and removes an entire class of “works on my machine, why is the agent doing something different” tickets.

Operational guardrails for teams at scale

A few practices separate a clean rollout from one that quietly rots over six months.

  • Prune unused skill drivers. If your application is a pure API backend, disable spatie-javascript. Every active skill costs agent context; loading rules for code you don’t write is pure waste.
  • Audit generated output, don’t trust it blindly. Skills reduce style violations, they don’t eliminate the need for review. Check that agent-produced code actually respects PSR-12 and Spatie’s return-type conventions rather than assuming the skill fired correctly.
  • Keep automated formatting as your final gate. AI guidelines catch structural and naming issues; a vendor/bin/pint pre-commit hook is still your deterministic backstop for whitespace and formatting drift that skills won’t always reach.
  • Match your local environment to what CI enforces. If your team’s dev containers differ from what agents run against locally, guideline enforcement gets inconsistent fast. Our breakdown of a 2026 Laravel AI development stack covers keeping that parity intact.

[Edge Case Alert] Watch for skill collisions when a project pulls in guidelines from more than one vendor package. If a second guidelines package also defines a spatie-laravel-php-equivalent skill, Boost doesn’t merge them, it’s whichever one installs last. Audit .boost/skills/ after any multi-package install, not just after a single-package one.

Where this fits in your AI governance stack

Guidelines skills solve a narrower problem than most of what we cover in Production-Grade AI Architecture in Laravel: they’re about what the agent writes, not what it decides or how much it costs to run. But narrow doesn’t mean small. Style and convention drift is one of the quieter failure modes in agentic development, it doesn’t throw an exception, it just accumulates until a codebase written by five different agent sessions looks like it was written by five different developers who never talked to each other.

The pattern here generalizes past Spatie’s specific rules. Any team with an opinionated internal style guide can package it the same way: distilled into skills, distributed through Boost or skills.sh, kept current through Context7 and a CI sync step. The specific package changes. The architecture, deterministic, versioned, and enforced at the point of generation rather than the point of review, doesn’t.

Additional external references

– Laravel Boost documentation (https://spatie.be/guidelines/ai)
– Context7 MCP server on GitHub (https://github.com/upstash/context7)


Frequently Asked Questions

Does spatie/guidelines-skills replace spatie/boost-spatie-guidelines?

Yes. The older package was Boost-only; the new one works with both Boost and skills.sh, and covers everything the old package did.

Do I need Laravel Boost to use Spatie’s guidelines?

No. The skills.sh install path (npx skills add spatie/guidelines-skills) works independently of Boost, which matters for non-Laravel services in a polyglot stack.

Will these skills conflict with my own custom AI guideline files?

Not automatically, but they can collide if another package defines a similarly named skill. Audit .boost/skills/ after installing, especially in multi-package setups.

Does this replace code review?

No. Skills reduce the volume of style and convention violations an agent introduces; they don’t replace human review or your automated formatting gate (Pint).

Dewald Hugo

A software architect with 15+ years of experience in the PHP and Laravel ecosystem. Dewald created Origin Main to provide the engineering rigour required to integrate AI into professional, high-concurrency production systems. He writes for developers who care less about "getting it to work" and more about "getting it to last".

Laravel News Links

Laravel Discount: Coupon Codes, Usage Limits, and Stacking

https://laravelnews.s3.amazonaws.com/featured-images/Laravel-Discount-LN.png

Some Laravel applications that sell products or services need more than a flat percentage off. An e-commerce store might schedule a seasonal sale, or a SaaS product might hand out a launch coupon, and a discount that starts at 10% off can be capped at a maximum, tied to a minimum order total, a per-customer limit, and an expiry date. Laravel Discount by Milwad Khosravi stores all of that on Eloquent models and evaluates it through a single facade.

Here’s what the package covers:

  • Two discount types: DiscountType::Percentage and DiscountType::Fixed, with an optional max_discount_amount cap.
  • Coupon codes: Discounts with a code act as coupons, while codeless discounts apply automatically.
  • Time windows: starts_at and expires_at columns, paired with a valid() query scope.
  • Usage limits: Total usage_limit and per-customer usage_limit_per_user enforcement.
  • Guest support: Pass a session ID to enforce per-user limits for unauthenticated users.
  • Stacking rules: Mark discounts is_stackable and the package works out which combination saves the customer the most.
  • Discountable models: The HasDiscounts trait attaches discounts to any Eloquent model.
  • Cart integration: A CartDiscount service that applies codes to a Laravel Cart total or to a single cart item.

Percentage and Fixed Discounts

A discount is a standard Eloquent model, so you create one like any other record:

use Binafy\LaravelDiscount\Enums\DiscountType;

use Binafy\LaravelDiscount\Models\Discount;

 

$discount = Discount::query()->create([

'name' => 'Summer Sale',

'type' => DiscountType::Percentage,

'value' => 20,

]);

Applying it goes through the LaravelDiscount facade, which validates the discount before calculating anything and hands back a DiscountResult:

use Binafy\LaravelDiscount\Facades\LaravelDiscount;

 

$result = LaravelDiscount::apply($discount, 200);

 

$result->originalAmount; // 200.0

$result->discountAmount; // 40.0

$result->payableAmount(); // 160.0

The DiscountResult object holds the applied discounts, the original total, and the calculated discount value. payableAmount() subtracts the discount from the original total, clamping the result to a minimum of zero so that fixed discounts never yield negative totals.

A discount of either type can carry a ceiling through max_discount_amount, which is what covers the "20% off, up to $100" case that otherwise ends up hardcoded in a controller:

$discount = Discount::query()->create([

'code' => 'SAVE20',

'type' => DiscountType::Percentage,

'value' => 20,

'max_discount_amount' => 100,

]);

 

LaravelDiscount::apply($discount, 300)->discountAmount; // 60.0

LaravelDiscount::apply($discount, 1000)->discountAmount; // 100.0

Discount Codes, Expiry, and Usage Limits

Setting a code column turns a discount into a promotional coupon. applyCode() validates the code and throws DiscountNotFoundException if no match exists:

$result = LaravelDiscount::applyCode('WELCOME10', 200, $user);

For campaigns where every customer needs their own code, the package builds them with random_int() and leaves out ambiguous characters like 0/O and 1/I, so nobody misreads a code off a printed card:

LaravelDiscount::generateCode(); // "8FJ2K9QW"

LaravelDiscount::generateCodes(100, 'VIP'); // Collection of 100 unique codes

Length, alphabet, prefix, and separator all live under the codes key in config/laravel-discount.php.

Time-limited offers use starts_at and expires_at. Applying before the window throws DiscountNotStartedException, applying after it throws DiscountExpiredException and fires a DiscountExpired event. Querying for what is live right now is a scope:

Discount::query()->valid()->get();

usage_limit caps total redemptions, and usage_limit_per_user caps them per customer, but neither is settled at the time of application alone. You call redeem() when the order is actually finalised:

LaravelDiscount::redeem($discount, $user, $result->discountAmount);

That runs within a transaction and increments used_count by the limit in the where clause, so the database decides whether the increment occurs at all. Update zero rows and the limit was already reached, which surfaces as DiscountUsageLimitReachedException. Two customers claiming the hundredth spot at the same moment cannot both win it.

Guests get the same treatment through a session ID instead of a user model, tracked in the session_id column of discount_usages alongside the nullable user_id:

$result = LaravelDiscount::applyCode('GUEST10', $total, sessionId: session()->getId());

 

LaravelDiscount::redeem($discount, amount: $result->discountAmount, sessionId: session()->getId());

Conditional and Stackable Discounts

A min_order_value column gates a discount behind a spending threshold and throws MinimumOrderValueException when the total falls short. There’s also a conditions JSON column for storing your own condition data, which is where you’d hang anything the package doesn’t model natively. If you want conditions expressed as composable PHP objects instead, the Discountify package takes that approach.

Discounts can also attach to models. Add the HasDiscounts trait, and you get a polymorphic relationship backed by the discountables table:

use Binafy\LaravelDiscount\Traits\HasDiscounts;

 

class Product extends Model

{

use HasDiscounts;

}

$product->discounts()->attach($discount);

$product->validDiscounts();

$product->hasDiscount('TECH10');

 

$result = $product->applyDiscounts($product->price);

That last call routes through applyMany(), which is the stacking resolver. It drops any discount that fails validation, splits the rest into stackable and non-stackable, sums the stackable ones (capped at the order total), finds the single best non-stackable one, and returns whichever side saves more:

$result = LaravelDiscount::applyMany([$tenPercent, $tenFixed, $bigSolo], 100);

 

$result->discounts; // the ones that actually applied

$result->discountAmount; // the winning total

The discounts collection on the result matters here. After a stacking decision, you often need to show the customer which codes made the cut, and that collection is the answer.

Laravel Cart Integration

Install binafy/laravel-cart, the cart package from the same author that we covered previously, and a CartDiscount service becomes available for cart-level and item-level discounts:

use Binafy\LaravelDiscount\Integrations\LaravelCart\CartDiscount;

 

$cartDiscount = app(CartDiscount::class);

 

$result = $cartDiscount->applyToCart($cart, 'SUMMER-8FJ2K9QW');

 

$result = $cartDiscount->applyToItem($cartItem, $discount);

 

$result = $cartDiscount->applyItemDiscounts($cart);

applyToCart() checks the cart total against min_order_value and pulls the cart’s user for the per-user limit, so you don’t pass either one yourself. applyToItem() works against price multiplied by quantity for a single line. applyItemDiscounts() walks the cart and applies whatever each item’s underlying model has attached through HasDiscounts, which is how you run a product-level sale across a whole basket without touching the cart total.

Validation, Exceptions, and Events

Checkout forms need to reject invalid code before anything else happens, and the package includes a ValidDiscountCode rule for that. Its message names the actual reason rather than failing generically:

use Binafy\LaravelDiscount\Rules\ValidDiscountCode;

 

public function rules(): array

{

return [

'code' => ['required', new ValidDiscountCode(

orderAmount: $this->cartTotal(),

user: $this->user(),

)],

];

}

It’s a standard rule object, so it composes with the rest of a form request the same way any custom validation rule does.

Outside validation, every failure case has its own exception extending DiscountException: DiscountNotFoundException, DiscountNotActiveException, DiscountNotStartedException, DiscountExpiredException, DiscountUsageLimitReachedException, and MinimumOrderValueException. Each one exposes the discount that failed through getDiscount(), so you can catch the specific case you want to message differently and let the base class handle the rest:

try {

$result = LaravelDiscount::applyCode($code, $total, $user);

} catch (DiscountExpiredException $e) {

return back()->withErrors("Code {$e->getDiscount()->code} has expired.");

} catch (DiscountException $e) {

return back()->withErrors($e->getMessage());

}

Three events cover the lifecycle: DiscountApplied when discounts are applied to an amount, DiscountRedeemed after a redemption transaction commits, and DiscountExpired when validation hits an expired discount. The redeemed event carries both the discount and the usage row, which is enough to drive analytics or a notification without querying again.

Installation

Laravel Discount requires PHP 8.1+ and Laravel 9-13. Install the package via Composer and run the migrations:

composer require binafy/laravel-discount

php artisan migrate

The service provider registers itself, and the migrations create discounts, discount_usages, and discountables. Publishing the config is optional, and only worth doing if you need to change table names, point at a different user model, or adjust code generation defaults:

php artisan vendor:publish --tag="laravel-discount-config"

Two Artisan commands come along with it. discount:generate produces codes from the terminal, and discount:prune deletes expired discounts along with their usage records, which is a reasonable thing to put on the scheduler:

Schedule::command('discount:prune --days=30')->daily();

The full documentation, including the config reference, is on the Laravel Discount GitHub repository.

Laravel News

Laravel Truss 1.8: feed your real schema to a coding agent (MCP + context export)

https://albertoarena.it/images/posts/my-coding-agent-kept-inventing-columns/cover.jpg

Ask a coding agent to write a query against a table it hasn’t seen this session, and it will guess. Confidently, plausibly, and wrong: a foreign key named author_id when the column is actually created_by, a status treated as a free-text string when it’s a tinyint enum, a table it’s sure exists because a table like it usually does. It isn’t lying, it just doesn’t know, and nothing forces it to say so.

The usual fix is pasting a schema dump into the chat at the start of a session. That works until the next migration, at which point it’s just a different kind of wrong: confidently out of date instead of confidently invented.

There’s an obvious third option: let the agent run queries itself, most coding agents can already execute SQL. But that means handing over real database credentials, and a connection that can query can also see rows, not just structure. That’s a bigger grant than the problem needs.

Truss has always been a live, zoomable ER diagram of your Laravel app’s real database schema, structure only, never a row of data. Version 1.8 points that same live structure at a coding agent instead of a browser tab.

Give it meaning a type can’t carry

A column tells an agent its name and type, not what it means. status = 1 doesn’t say “paid” on its own. Annotate it once and every export carries it:

// config/truss.php
'annotations' => [
 'source' => ['config', 'database'],
 'tables' => [
 'orders' => [
 'note' => 'One row per checkout attempt, not per completed order.',
 'columns' => [
 'status' => 'tinyint: 0 pending, 1 paid, 2 refunded',
 ],
 ],
 ],
],

If your database already carries COMMENT strings on tables and columns, leave 'database' in annotations.source and Truss reads those directly, no duplicate config to keep in sync. Either way, a comment is part of the CREATE TABLE definition, not a row: still structure only. Strip them from any single export with --no-annotations when you just want the bare shape.

Trim it to what the question needs

A forty-table schema is a lot of tokens to spend on a question about one table. --compact drops column defaults and non-unique indexes without dropping a single table, column, or foreign key. --focus=orders --depth=1 narrows the export to one table and its foreign-key neighbourhood, the same idea as the dashboard’s focus mode, now available from the command line. And there’s a new llm format alongside the existing five (DBML, JSON, CSV, Markdown, Mermaid), a dense plaintext export tuned for a token budget rather than for a human reading a data dictionary:

php artisan truss:export --format=llm --focus=orders --depth=1 --compact

That’s what the export produces. Calling it is just as direct, in code or over HTTP.

Building the same thing in code goes through a new fluent, immutable Truss facade instead of the command:

Truss::snapshot()->focus('orders', depth: 1)->compact()->toDbml();

And a gated GET {prefix}/export/{format} route serves the identical output to any HTTP client, behind the same viewTruss gate as the dashboard. Command, facade, route, dashboard download: one pipeline underneath all four, so they can never quietly disagree with each other.

Ask it live, instead of pasting a snapshot

The part I actually wanted, though, wasn’t a better export. It was not exporting anything at all.

Truss 1.8 adds an optional server for MCP, the Model Context Protocol that Claude Code, Claude Desktop, and Cursor use to reach outside tools. Built on laravel/mcp, it talks to a coding agent directly over local stdio:

composer require laravel/mcp
php artisan mcp:start truss

Point one at it and the agent gets five tools, list_tables, describe_table, get_schema, focus_table, and get_structural_review, plus a truss://schema resource, all reading the live schema on demand. Every tool advertises MCP’s readOnlyHint, so a client can present them as read-only instead of prompting for write approval on a call that was never going to write anything. No row data, ever, and the same exclusion and managed-connection safeguards as the rest of Truss apply here too, opt-in and off by default behind truss.mcp.enabled.

I pointed it at a real project I’ve worked on for a while, in Claude Desktop, and the difference was immediate: instead of me pasting a schema dump at the start of the conversation, or the agent asking me to run a query to check a column name, it just called describe_table before it wrote anything, the same check that would have caught the guessed author_id from the start of this post. No staleness, because there’s nothing to go stale, it’s reading the same live introspection the diagram uses.

Try it

Update with composer update albertoarena/laravel-truss, and if you want the MCP server too, composer require laravel/mcp on top.

What’s next

More truss:doctor rules and CI-native output formats are next on the roadmap, followed by reading Eloquent relationships for semantic edge labels instead of raw foreign keys, and navigation aids for schemas with a hundred tables or more. If a tool the agent needs isn’t there yet, or an annotation source you’d want isn’t supported, open a discussion.

Laravel News Links

Slashdot Reader Builds a Photo-Verification App for iPhones

Long-time Slashdot reader BrianFagioli is announcing that he’s released a new iPhone app that creates a cryptographic witness for photos without uploading the original image.
The app hashes the file, signs the hash using a dedicated identity stored in Apple Keychain, and publishes the witness to Nostr relays while keeping the photo inside the app unless the user chooses to export it. Rather than trying to determine whether a scene was genuine, the app — named Veridenz — answers a narrower question, by verifying whether a photo file matches the one that was originally witnessed.
Users do not need a Nostr account because the app creates its own signing identity, keeping private documentation separate from a public Nostr profile. "The developer does not collect any data from this app," says its page in Apple’s iPhone store. The app’s tagline is "Capture. Prove. Verify."


Read more of this story at Slashdot.

Slashdot

MySQL Index Optimization: How to Speed Up Your Database Queries

https://webyog.com/wp-content/uploads/2026/08/Index-optimization.png

If there is one change that can turn a 10-second MySQL query into a millisecond one, it is adding the right index. And if there is one change that can quietly slow down your write heavy database over time, it is adding too many. 

Indexes are the most powerful performance tool in MySQL. Understanding how they work — and how they fail — is foundational to managing database performance at any scale.

How Indexes Work

An index is a separate data structure MySQL maintains alongside your table. Think of it like a book’s index: instead of reading every page to find a topic, you go directly to the right page number. 

Without an index on a filtered column, MySQL reads every row in the table on every query. With the right index, it locates matching rows in a fraction of the reads.

Reading EXPLAIN

EXPLAIN is your primary tool for understanding whether MySQL is using an index and how.

EXPLAIN SELECT * FROM customers WHERE email = ‘[email protected]’; 

The most important column in the output is type.

Other columns to watch: 

•  key — which index MySQL chose. NULL means no index used. 

•  rows — estimated rows MySQL will examine. Lower is better. 

•  Extra: Using filesort — sorting without an index. Can be expensive on large result sets.

•  Extra: Using index — the query was satisfied entirely from the index. Very efficient.

Index Types

Creating Effective Indexes

Single-Column Indexes 

The simplest case: a column you filter on frequently.

ALTER TABLE customers ADD UNIQUE INDEX idx_email (email);

Before and after:

Composite Indexes and the Left-Prefix Rule

When queries filter on multiple columns, a composite index is usually more effective than separate single-column indexes.

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

MySQL can use this index for queries that start with the leftmost column:

Order composite index columns by how they appear in your most common queries, with the most selective column first.

Covering Indexes

A covering index includes all the columns a query needs. MySQL satisfies the entire query from the index without reading the table at all — shown as Using index in EXPLAIN’s Extra column.

— Query only needs these three columns 
SELECT id, email, status FROM customers WHERE status = ‘active’; 

— Covering index includes all three 
ALTER TABLE customers ADD INDEX idx_covering (status, id, email);

On large tables, the performance difference between a regular index lookup and a covering index can be significant.

Common Mistakes

Over-Indexing

Every index you add slows down writes. MySQL updates all indexes on every INSERT, UPDATE, and DELETE. 

Find and remove unused indexes regularly:

SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

Functions on Indexed Columns

Using a function on an indexed column prevents MySQL from using the index:

Low-Cardinality Columns

Columns with very few distinct values (such as a boolean or a status with three possible values) are rarely worth indexing on their own. MySQL may choose a full table scan if it estimates the index would return a large fraction of rows anyway.

Maintaining Indexes

Want to analyze query performance visually? SQLyog’s Query Profiler shows detailed index usage and execution metrics for every query you run. Try SQLyog free.

Frequently Asked Questions

How do I know if a column needs an index?

Check your slow query log for queries with type: ALL in EXPLAIN. Any column that appears frequently in WHERE, JOIN, or ORDER BY clauses is a candidate. The test is always: run EXPLAIN before and after adding the index to confirm it is being used.

How many indexes should a table have?

There is no universal number, but most well-optimized tables have 3–8 indexes. Start with the primary key, add indexes for your most common query patterns, and stop before you over-index. Audit unused indexes quarterly.

Will adding an index lock my table in production?

In MySQL 5.6+ with InnoDB, most index additions are online and do not block reads or writes during the build. There is a brief metadata lock at the start and end. For very large tables, run the operation during low-traffic windows as a precaution.

My query shows an index in EXPLAIN but is still slow — why?

A few possibilities: the index has low cardinality and MySQL still reads many rows through it; you need a covering index (MySQL uses the index for filtering but reads full table rows for each match); or there is a Using filesort in EXPLAIN’s Extra column indicating additional sorting work after the index lookup.

What is the difference between a composite index and two separate indexes?

A composite index on (col1, col2) allows MySQL to filter on both columns in a single index lookup. Two separate indexes on col1 and col2 require MySQL to use one or perform a merge operation. For queries filtering on multiple columns together, a composite index almost always performs better.

How do I find duplicate indexes?

Query sys.schema_redundant_indexes . It shows indexes made unnecessary by another index on the same table — for example, if you have indexes on (a) and (a, b) , the first is redundant because all queries it can satisfy can also be satisfied by (a, b) .

Does MySQL automatically create indexes?

MySQL automatically indexes the primary key and any column declared with the UNIQUE constraint. It does not create indexes on foreign key columns or frequently queried columns automatically — those you must add manually based on your query patterns.

What does Using index in EXPLAIN’s Extra column mean?

It means MySQL satisfied the entire query from the index without reading the actual table rows. This is called a covering index and is one of the most efficient query execution patterns. When you see Using index , the query is well-optimized for index usage.

Planet for the MySQL Community

Why are databases so hard?

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCLW1tV9SsA-5gIBDDhoN-blfIzAECOag2YxyZZGigmK7V-6u6wi3EYODcxfDMvTILiGpw3m1AuN_uX2D-PwX3XZeCpWwJhlUM9zv7nFxbkljJBml754KlanoZOyA9z8xNMO0smkuFuyvnkjVU1WNkXvCKRu4kSdE5nSQmwbCr28kjHs8hjTpbTbQfBaw/s320/bobross.webp

You’ve probably all experienced it; another outage and the database is the root cause.  Why are databases such a frequent cause of problems in most tech stacks? Why can’t we seem to solve these problems industry-wide?  Are database engineers and database admins just bad at their jobs?

Over my career as a database reliability engineer, I’ve come to a conclusion which I don’t see repeated often:

All practical implementations have to balance the opposing concerns of correctness vs. performance & availability (this is kind of similar to CAP theorem , but not exactly the same).   Perfect correctness with no data loss across geographic distances would result in a database which is too slow or too costly to be useful for most applications. And these constraints cannot be overcome because it’s the physical bounds of reality which imposes these limits.

Why geographic distances? I’ll explain this step-by-step below.

Step 1: A Single Isolated Database Instance

You start with installing a copy of PostgreSQL or MySQL on a single instance, or using a managed database product from a cloud provider such as AWS’s RDS.  At this scale the database performs everything you need.  Full ACID compliance, fast reads and writes, transactions all work beautifully.  We’re done, right?

A happy little database on its own.  You’ll never see one this happy again.

Well only if you ignore that hardware and VM instances are not flawless.  Despite the database software being more than adequate, sometimes that underlying hardware or VM infrastructure will fail.  The naive response is to just wait until the original instance can be restored and you continue on your merry way.  However this could take minutes, or hours, or days.  If you have customers paying to use your service, they’re not going to be so patient.  So now you need High Availability! 

Step 2: High Availability

High Availability means that you want your system to recover to a usable state as quickly as possible.  How quick? It depends on the specific implementation.  For me, <10 seconds is common.  <1 second is the goal.  For systems like RDS their default gives you ~2 minutes average recovery time, with options that will get that down to ~30 seconds.  Not really HA-enough for my taste, but for most people it’s a vast improvement over hours or days of outage!

BUT THERE IS A TRADEOFF — A COST!

Do you see it yet? It’s that improving reliability means having another copy of your database ready to take over when the primary fails. Maintaining that copy takes time. Time is the tradeoff.

Every time you write data to your primary/live instance, that data must be recorded somewhere else where it will be available when another database instance takes over to maintain availability. Well, you could certainly build a system where this isn’t true, but imagine your customer’s surprise when you flip from one database instance to another and data they thought they had persisted suddenly disappears.  Even worse is the problems you would invite when you flip back to the original copy of the database and that data suddenly appears again.  This is our "correctness" problem.

 The correctness problem is this: when we have to maintain multiple copies of our "source of truth" data, how can we make sure they all stay in-sync?

I have some good news and bad news on that front: the good news is that we absolutely can keep all our copies perfectly in-sync and ensure perfect correctness.  The bad news is our database system will now be so slow it’s probably unusable on a practical level.

 It works like this:

 When a request comes to our primary/active database to update/insert/delete data, we can pause the transaction at the time of commit and go transfer that transaction data to our other copies.  Only once we have confirmed the data has been durably persisted to our other copies do we finish the commit and return a success to the original transaction’s client.  This adds time to the client’s request.  They have to wait for data to be transferred over the network between our databases.  For two database instances in the same datacenter, this could be microseconds — not terrible, maybe not even noticeable.

 However, that’s not the end of the problems we’ve added.  Now what happens if our backup database fails and can no longer accept updates, even when it’s not being actively used? To maintain correctness we would have to stop accepting writes to the primary database as well!  It’s the only way to ensure they always remain perfectly in sync is to treat a failure of one node as a whole-system failure.

 Wait, we were supposed to be increasing availability.  Did we just actually decrease it instead? Also when the primary database fails and we flip to the secondary we now no longer have a backup copy and we lose HA properties until the other instance is restored.

 We could just run more backup copies, but now we have more data transfers to keep everything in-sync.  We could just say we only need 2 out of N nodes to be in-sync at all times and mark the others as unusable temporarily until they can re-sync.  Or is that 3 out of N, so that we have a backup-for-the backup.  And our cost to serve a single copy of our data set has gone up to what? 3x? 5x?  It’s starting to get more expensive now too.

Now I hope you’re starting to see the complexity of the problem here.  We could go into permutations of redundant architectures until the cows come home, but I’ll spare you.  Suffice it to say that every single architecture we could examine or invent is going to run into the same fundamental limit — it takes time to keep copies of our data up-to-date perfectly. And the only way to mitigate the time constraint is to relax the correctness constraint. There is no way around this.

 And we’re not even done yet, because our highly available system still only operates in a single physical datacenter. A single disaster which takes out the whole datacenter still means we’re hosed.  Many companies just call this good enough and accept the risk (after all us-east-1 never goes down, right?)  But for others, their customers won’t be happy with an extended outage even if you can claim it’s not your fault.  For true fault-tolerance you need yet another copy of your data in some other physical location, usually far enough so that the same hurricane, or earthquake or power grid outage doesn’t affect both locations.  This is how we arrive at geographic distribution.

Step 3: Disaster Recovery & Geographic Databases

 The astute will note that this is just an extension of the same problem we have with transit times in our HA setup, now with larger distances involved.  This should be easy! How much more time could we possibly have to manage?

Let’s take New York to Los Angeles as an example.  If you were able to send data at the speed of light, it would take 16 milliseconds! And that’s a one-way trip.  To let our primary database receive a confirmation that the data was received we need a minimum of 32 ms.  And keep in mind this is the theoretical maximum the laws of physics allow for a straight-line path.  In practice even if our network was fiber from end-to-end, we still have stops at various routers along the way for processing.  A real network request therefore takes more like 66ms per trip, and a 130ms round-trip time.

I have yet to experience any commercial enterprise willing to accept database write latency of 130ms.  Using cloud services you might end up paying thousands, or even tens of thousands of dollars per year for a system that can process <100 write transactions per second.

 

Sad databases, so far apart.

 So what do you do?  How do you surmount the laws of physics? You don’t. You MUST compromise something.

And that’s the entire point of this article — you cannot escape the fundamental laws of physics.  You only choose what properties are desirable and know that you will be giving up other things.  If you absolutely cannot tolerate data loss, then your system will be incredibly slow (or costly).  If you want great performance and efficiency, there will be ways you can lose data.

 I talk about the laws of physics because someone might see the 130ms round-trip-time and think that we just need to do some fancy computing to optimize that.  Or change how we build networks. But even if we did that we’d get at most ~4x improvement. There is not even a single order of magnitude left between our current performance and the maximum allowed by the laws of physics.  We cannot optimize time much more than we already have!  No matter how advanced technology of the future becomes, this same problem will still exist until the end of the universe. Even at the speed of light, it takes a significant amount of time to move data.

 Most companies use a strategy of creating an HA cluster with strong consistency guarantees within a single datacenter only, and then using an "eventual consistency" approach to shipping data to another geographic location. If the need arises to run their application from a different geographic location, they call it "disaster recovery" and let clients know that recovery could take hours and some data loss is expected. This applies equally if you’re using async replication to a warm-standby database or if you’re taking backups or snapshots and filling in the gaps between full backups with transaction logs.  If your primary datacenter fails while processing user requests, there will always be some window of time where data written to your database at that location won’t make it to your backup. It could be seconds, or minutes. No matter what you cannot guarantee consistency with an async update model.

 This is why databases are hard — there’s never a perfect solution which will work all the time for all use-cases.  And this also ignores the entire other class of database problems which relate to availability which is what happens when someone writes a bad query that DOSes your database.  Just scale up your database, or just partition it, right? But as we see in this article, scaling a database is hard because everything takes time.  Partitioning is hard because you create new consistency problems.  Availability is hard because keeping things in-sync is hard.

A Postscript 

Communicating this is honestly the most challenging part of my job.  Initial development of most software projects starts with the single isolated database either from a cloud provider or just on someone’s laptop.  They develop against a non-distributed database system and a tiny database size and everything works! It’s blazing fast, it’s perfectly consistent. No durability issues, etc.  When engineers take this to production they are soon frustrated by the production database which seems slower and less reliable.  They underestimate that the production database has so much more demands and constraints against it.

Yet I will talk with an engineering team one week that stresses how important consistency guarantees are for them.  Sure, I can do that.  Then the next week I’ll talk to another team that demands the fastest performance possible.  Now we have a challenge.  Then after an incident I’ll be yelled at by a manager who says we need to make availability our highest priority because our largest customer is threatening to churn.  Then as we approach the end of our fiscal year I’ll have other people breathing down my neck saying we need to cut costs. Then this whole cycle repeats.

 You can try to fix this by running multiple database systems.  The slow-yet-scalable system; the ultra-fast-but-lossy system; the perfectly-consistent-but-tiny metadata store.  This is why so many companies run several different types of databases.  Redis for ephemeral data, MySQL/PostgreSQL for transactional guarantees, key-value stores for easy scaling of simple data.  But now the job is to make sure engineers are choosing the right location for their data.  Inevitably not all data will find the right home on the first try and migrating data from one system to another is always a big task which isn’t fun.  It all feels a bit like Sisyphus some days, but at least it’s job security!

Planet MySQL

Still simple, still powerful, BBEdit is a Mac app legend

https://media.appleinsider.com/gallery/68460-144241-000-lead-BBEdit-xl.jpg

You may like Pages and you may tolerate Microsoft Word, but text editor BBEdit has been rightly earning devotees on the Mac for 34 years. Here’s how.

There are very few Mac apps that go back this far, and even fewer that remain both actively developed and actually beloved by its users. And there is only one with the trademark "It doesn’t suck."

BBEdit is just a text editor, but rather than this meaning it is limited, it means that BBEdit is focused. It is and always has been a plain text editor, it does not also try to be a mind-mapping tool or an image editor or a chatbot.

This is going to read like an unqualified hymn of praise, although there are actually a few qualifications. But this is what BBEdit does to you. While there are other apps I write in for many different purposes, BBEdit does what it does extraordinarily well.

I don’t remember when I first came across it, but it would’ve been before 2000 and possibly before Steve Jobs came back to Apple. Its original version was written in 1989 because of a problem with editing text on the Mac.

After it was released as BBEdit in 1992, Bare Bones Software never stopped developing it.

The problem back then was just that Apple offered an API for handling text, but it couldn’t handle large files. The creators of BBEdit were working on a version of the Pascal programming language for Mac that effectively demanded longer files.

While Apple’s Pascal faded away, their text editor grew into this favorite for programmers and writers.

If you’ve never used it, though, it’s hard to convey just why BBEdit is so popular among its users. There are people who will never leave the Mac because there isn’t a PC version of BBEdit, for instance.

And as you use it, you have such a clear sense that so do its developers. This is not a day job or a side project, BBEdit keeps getting better because its developers add what they want it to do themselves.

"I use BBEdit for everything I produce that involves text: code (C, C++, Objective-C), web site content (HTML, Markdown, Textile), and more," said Rich Siegel, developer of the app, in a 2011 interview. "In a very real sense, BBEdit is written using itself — my daily-use build is the same one that’s being beta tested or used in the field by our customers."

Text editor on macOS showing a hex data view, with a Multi-File Search dialog open in front and a menu of text processing options such as Remove Blank Lines expanded

If BBEdit’s search feature can’t find something, it isn’t there.

So an app that has been available for more than three decades, two hardware architectures ago, is still being actively developed. An app that was meant to be just a "bare bones" text editor is still simple to use, yet it has so many features a keystroke away that it’s hard not to gush about this app.

To be fair, there are actually reasons not to use it, there are writers for whom BBEdit is just the wrong choice. But if it suits your writing or it suits how you like to work, BBEdit can make you into a passionate fan.

"BBEdit is legendary because we keep improving it to serve the needs of our customers," Siegel said on the release of version 9. It’s now on version 16, which is promoted as having "millions* of new features and refinements (*estimated)."

With marketing like that, BBEdit remains a taste of those early times with the Mac some 34 years after it was first launched. It’s serious software that doesn’t take itself too seriously.

What BBEdit does and how

The short version is that BBEdit is a text editor. That’s not the same thing as a word processor which offers formatting options like styles or margin control, as a text editor is just for getting the words down.

BBEdit Application Settings window on macOS showing sidebar categories and main panel options for opening documents, changing workspaces, reopening documents, automatic updates, sandbox access, and a Restore Defaults button

There aren’t countless settings you can choose in BBEdit, but there are more than you’d like to count.

As such a text editor, BBEdit is fast and responsive, and it generally doesn’t get in your way. You don’t have to set any settings, you can just open it and start typing.

When you have written a lot of text, maybe even over several years, you will start to appreciate a feature called grep. This is a form of regular expression, the feature takes you beyond normal search and replace.

For instance, in any word processor you could search for a date like 01/12/2026. But in BBEdit, you can search for all dates in the document, matching every one of them simultaneously.

It’s a form of regular expression. As well as being powerful, though, the BBEdit version includes an on-screen guide to help you through it as simply as possible.

"Our base started as Mac software developers, scientists, system administrators, and other technical users," Rich Siegel told Apple in 2019. "The first big change was popularity among HTML authors and web back-end developers."

Web design software window showing a Background settings dialog with selector, file path, color picker grid, and positioning options, overlaid on HTML/CSS code and a list of webpage files

Web designs can write CSS files in BBEdit – image credit: Bare Bones Software

"As word spread, we were able to help folks understand that the internet was built with text that you could treat as data or you could treat it as a document," he continued. "So the next wave brought in internet architects, cryptanalysts, and scientists from unexpected disciplines."

Thos unexpected disciplines, he said, included "writers and other content creators-folks who see their text not as data but as words, and who want as little as possible between them and their words."

Overall design

Beyond the simplicity of writing in BBEdit, part of this get-on-with-it design is in how it stores your documents. Unlike most apps other than Ulysses, BBEdit can in theory be a single repository for all of your writing.

So rather than opening and saving documents all the time, you can just open BBEdit, press Command-N and be writing in a new document. Everything you’ve written before is still there, listed in a navigation bar on the left, until you choose to close it.

You don’t even have to save that previous work before you start a new one.

But then if you want to, you can. Anything you write can remain in BBEdit’s list on the left of the screen, but you can also save any of it out to separate, individual .txt files.

Mac desktop showing a text editor window titled

You can keep all of your recent documents open in BBEdit. Or don’t even ever save them, just write in this text editor and export the final text to wherever you need it to go.

Then it might not require you to set anything up, and it might not get in your way, but BBEdit is built to help you. So for example, if you type an opening quote mark, BBedit immediately adds a closing one right after it and leaves your cursor in the middle.

It’s the same with brackets. Type an opening bracket, and you get that plus a closing one for free. That’s chiefly convenient in that it means you can’t ever forget to include a closing speech mark or bracket, you’d have to choose to delete them.

If you’re a programmer, that can also make the difference between your code working or not. It’s easy to forget to close brackets, unless you’re writing in BBEdit.

But it is also a little pain when you’re writing dialog. So often you’re going to follow a speech with a phrase like "she said," and to do that you have to cursor over the automatically-inserted end quote mark.

Split computer screen showing Markdown text editor on the left and rendered Markdown documentation with browser developer tools panel open on the right, demonstrating live preview and HTML structure

You can set BBEdit to wrap words and it also supports Markdown, plus a split-screen view.

You may well have just shrugged at that, and if you did, it’s because you are the true target audience for BBEdit. While any type of writer can use it, and every type of writer does, it is best for coders and programmers.

These are people whose writing tends to be in short lines rather than paragraphs. So they may never even need to notice something about BBEdit lines that will strike a novelist or a journalist immediately.

That’s how, by default, BBEdit does not wrap text at the end of the screen or at some margin stop. You could write a novel on the same long single line if you wanted to.

But then you could alternatively go to Settings, Editor Defaults, and turn on Soft Wrap. Set it to wrap your text at a specific character count, at the width of your default page size for printing, or the width of the window.

There are hundreds upon hundreds of settings you can change, but none that you have to in order to start writing. You can readily believe the gag that it has "millions" of new features, but this is still not an app where you have to study the manual.

Although, actually, there is a manual. In another throwback to the early days of the Mac, there exists to this day a 456-page BBEdit manual, and it is constantly updated.

How it got here

BBEdit began as this way to address a problem on the Mac. Today you know TextEdit as the name of Apple’s free text editing app, but back in the day, it was what the company called its text handling API.

If you wanted to include text in your app, from a word processor to just labels on a drawing, you could use this API and it did the job. But it wouldn’t handle anything over 32K, it wouldn’t handle anything long.

BBEdit was made to show that longer files could be handled on the Mac. It did the job, it was popular, and in 1992 when it first came out, BBEdit was also free.

Code editor showing CSS file with syntax highlighting and an autocomplete dropdown listing border-related properties as a developer types a style rule for a website layout

As well as automatically adding closing brackets for any user, if you’re a programmer or web designer then BBEdit will offer auto-completion suggestions – image credit: Bare Bones Software

That didn’t last. Around a year later, Bare Bones Software launched version 2.5 and began selling it for $99 (around $230 in today’s money). Users of the previous free ones, or owners of various programming languages such as THINK Pascal, could buy it for $49.

At the same time, Bare Bones Software released a cut-down edition called BBEdit Lite. That was free, and users of it could upgrade to the full BBEdit for $49.

They could get it on a floppy disk by mailing — not emailing, regular postal mailing — the developer on an Massachusetts address. Please enclose a check for $5.

BBEdit Lite no longer exists, and nor does its brief replacement, TextWrangler. That was released in 2003 for $49, offering what Bare Bones Software thought would be a compelling mix of BBEdit features for programmers and database administrators.

Compared to some apps, TextWrangler did well, sticking around 14 years, even if it quickly became free. It may have seemed like it spent much of that time nagging users to upgrade to BBEdit, but come 2017, there was no longer any choice.

TextWrangler was ostensibly folded back into BBEdit and users were encouraged to move to the full app.

"We promise that you will feel right at home, because BBEdit and TextWrangler are identical in every way that you’re used to," said the company at the time. "They’re built on the same foundation, by the same developers, with the same care; and they work the same way."

Behind the scenes, that must’ve been a reason to drop TextWrangler. Maintaining the same code base across two apps was surely taking up time that could be spent developing new features.

It certainly wasn’t the lure of cash from upgraders that made Bare Bones Software do this. As welcome as that would doubtlessly be, the company also sweetened the deal.

If you bought or upgraded to BBEdit at that time, you could use it for free for 30 days. It’s not unusual to have such a trial period, but what happens afterward is much more rare.

Bare Bones Software made it so that you could continue using the app for free, with just a limited feature set. What it left in was generous, and really it created what was effectively a new, free, BBEdit Lite in all but name.

BBEdit and Apple

If you’re a long-time Mac user, you may distantly remember technologies such as OpenDoc, but you’re unlikely to have used any of it. Bare Bones Software has, it even added OpenDoc support to BBEdit.

BBEdit has been a good Mac citizen from the start but sometimes you don’t know what you’ve got until it’s gone. That happened with Apple and BBEdit over the Mac App Store.

While it’s never really seen a fraction of the business that the iPhone App Store has, the Mac version has been around longer than you think. It debuted in 2011 and BBEDit was right there as one of the very first products on the Mac App Store.

By 2014, though, it was gone.

The reasons given at the time were not very specific, but they centered on how the Mac App Store sandboxes apps. Any app on the store is deliberately limited to prevent it being able to control the user’s Mac or other apps.

In BBEdit’s case, the app was reportedly not allowed full access to the user’s files and folders. It needed to be able to open and save wherever the user wanted, and at the time, Apple put limits on this.

There was also, though, an issue of money. While BBEdit was a one-time purchase, Bare Bones Software has always issued paid upgrades at intervals.

The Mac App Store did not allow for paid upgrades once a user had bought the app. So the company could have gained a true believer but never know it, never profit from it, and that user would never know what new features were available.

By 2019, enough developers had complained about sandboxing that Apple made some changes. They were sufficient to mean that BBEdit came back on the Mac App Store, but perhaps chiefly because there was now a solution to the upgrade problem.

Three colorful article cards: a serious man portrait titled Where Respect Is Due, a stylized crowd cutting paper titled BFGs: A Writer's Secret Weapon, and abstract coders titled Code. Write. Munge.

Apple welcomed BBEdit back to the Mac App Store with a trio of promotions – image credit: Apple

While Apple still does not allow paid upgrades, it both allows and encourages subscriptions. So today you can buy BBEdit 16 from the developer for a one-off $60, but you can subscribe via the Mac App Store for $5/month, or $50/year.

And if you were looking when BBEdit returned to the Mac App Store in 2019, you will have seen Apple rushing to make up for arguably ignoring it. That return saw an App Store front page profile of its main developer, Siegel, plus an article about BBEdit’s search features, and another about its overall feature set.

At that time, Apple’s senior vice president of worldwide marketing was Phil Schiller, who oversaw the App Store. He tweeted a welcome back to BBEdit, although that tweet is no longer available since Schiller quit Twitter/X back in 2022.

There’s something missing

BBEdit sounds perfect. Unless you’re a novelist or a screenwriter, or perhaps even a non-fiction author, maybe it is. And if you are any of those things, you can make BBEdit work for you if its comprehensive yet no-nonsense feature set appeals.

What you can’t do is make BBEdit work on anything other than a Mac. There’s no PC version, for example, although if you cared about that you wouldn’t be reading this far into an AppleInsider article.

The real missing piece is the iPad.

It’s been some years since Bare Bones Software has talked about why it’s stayed Mac-only. So perhaps things will change, but 37 years after starting it, and 34 since it went on sale, BBEdit is a legend among macOS apps.

Enough so that it’s one of the very, very few apps I would enthuse about this much. Bare Bones Software and Naomi Pearce helped out with finding images like that ancient photo of the boxed version, but they haven’t read this prior to publication, and they certainly didn’t pay for it.

Try it out for yourself. Once again, it may not be the right writing tool for you, but if it is, you’ll become a fan.

BBEdit 16 for Mac is available for $60 direct from the developer as a one-time purchase. You can also get it from the Mac App Store on subscription for $5/month, or $50/year.

AppleInsider News

MySQL Best Practice : not using date / time types, nor ENUM

Today, I was reminded of a MySQL Best Practice, probably generalizable to all databases : using simple types, not complex types.&nbsp; Such complex types to avoid include the date and time data types (including TIMESTAMP) and ENUM.&nbsp; Let’s see why.
A little history about this, Baron Schwartz, a MySQL Legend who is not involved in the community anymore, compared using the TIMESTAMP type toPlanet MySQL

The diagnostic data MongoDB Atlas doesn’t hand you

The diagnostic data MongoDB Atlas doesn’t hand you

Every MongoDB server keeps a flight recorder. It’s called FTDC, Full Time Diagnostic Data Capture, and it writes about 5,700 metrics every second into a folder called diagnostic.data, right next to your log. It’s delta-encoded and compressed so aggressively that days of history fit in a few hundred megabytes.

You’ve probably never looked at it. But if you’ve ever opened a performance ticket with MongoDB, it’s the first thing they asked you for, and there’s a good reason: when your cluster goes strange for twenty minutes on a Tuesday, this is usually the only artifact that can tell you what actually happened. Not a five-minute average. The WiredTiger ticket pool, second by second.

If you run your own servers, Percona Server for MongoDB, community MongoDB, whatever you manage yourself, that file is just sitting on disk. You copy it and you look at it.

On Atlas, the same file is written by the same code on a machine you’re paying for, and you can’t get to it. It isn’t in the UI. The log download gives you mongodb.gz and your audit logs and nothing else. Somebody asked how to do this on GitHub back in February 2021 and nobody ever answered.

There is a way. It just isn’t where you’d look. Everything below I ran against an Atlas M10 on MongoDB 8.0.29.


Ask the Admin API to build you a bundle

There’s an endpoint that packages FTDC on demand. Three calls and you have it:

BASE="https://cloud.mongodb.com/api/atlas/v1.0/groups/$GROUP_ID"
AUTH=(-u "$PUB:$PRIV" --digest -sS)

# 1. create the job
curl "${AUTH[@]}" -X POST "$BASE/logCollectionJobs" -H 'Content-Type: application/json' \
  -d '{"resourceType":"REPLICASET","resourceName":"<rs-name>","redacted":true,
      "sizeRequestedPerFileBytes":100000000,"logTypes":["FTDC"]}'

# 2. poll until it says SUCCESS
curl "${AUTH[@]}" "$BASE/logCollectionJobs/<job_id>"

# 3. download
curl "${AUTH[@]}" "$BASE/logCollectionJobs/<job_id>/download" -o ftdc.tar.gz

 

A few things that will trip you up. <rs-name> is the internal replica set name, not the display name you gave your cluster, run GET $BASE/processes and you’ll see it next to each host. You need a programmatic API key, not a database user. And if your organization requires an access list for API keys, add your IP first or the very first call comes back with ORG_REQUIRES_ACCESS_LIST.

What you get is the real thing: one diagnostic.data directory per replica set member, in exactly the layout every FTDC tool already understands.

Don’t ignore metrics.interim. That’s the chunk the server hasn’t flushed to a numbered file yet, and it holds your most recent samples. In my bundle the newest numbered file stopped at 22:33 while the interim carried data all the way to 22:38. If you’re chasing something that just happened, that’s the file you need.

 

Your data has a shorter shelf life than you think

Here’s the part that will hurt you if you don’t know it.

The bundle includes a metadata document with getCmdLineOpts in it, which tells you how Atlas starts mongod. There it is:

diagnosticDataCollectionDirectorySizeMB: 400

 

That’s twice the mongod default of 200 MB, and it’s a hard ceiling. When the directory fills up, the oldest file gets deleted. No warning, no archive.

How long 400 MB lasts depends entirely on how hard your cluster is working, because FTDC compresses by delta, a metric that sits still costs you almost nothing, a metric that moves every second costs real bytes. On an idle cluster I measured about 0.93 MB per 32 minutes per node, which works out to something close to ten days. On a busy production cluster, expect two to five.

So picture the usual sequence. Something goes wrong on a Thursday night. Nobody’s sure how bad it was. The postmortem gets scheduled for the following week, somebody finally asks what the cluster was actually doing at 3 AM, and the answer is gone. Not archived somewhere. Gone.

Collect during the incident, not during the retrospective. On your own servers this is a setting you control: raise diagnosticDataCollectionDirectorySizeMB, or copy the directory somewhere durable on a cron. On Atlas it’s a ceiling somebody else picked for you, and the only way around it is to pull the data yourself before it rolls off.


This shouldn’t have taken an afternoon

None of what I just showed you is documented anywhere.

Think about what FTDC actually is. It’s the first artifact MongoDB support asks for on a performance ticket. It contains no user data, it’s counters, and you can verify that yourself: I looked at 228 KB of diagnostic document and found 38 distinct strings, not one of them the name of a database or collection on the cluster. It’s the single most useful thing you can hand to somebody debugging your server, and it’s safe to share.

And on MongoDB’s own managed platform, the only way to get it is an endpoint that appears nowhere in the log download UI, isn’t mentioned in the Atlas docs, and sits on an API version that never made it to v2.

What you end up with is a two-tier arrangement. The engineers supporting your cluster work from the full second-by-second record. You work from a metrics page with a few dozen series and seven days of retention.

I don’t think anyone decided this. It reads like a capability nobody owned the job of surfacing, the endpoint exists and it works, after all. But intent doesn’t change what it costs you, and right now the gap is being filled by community projects with single-digit star counts. A “Download diagnostic data” button next to “Download logs” would close it in an afternoon.

This is the kind of thing that gets abstracted away when your database becomes somebody else’s service, and it’s almost never what anyone evaluates up front. You compare features and uptime. You don’t think to ask whether you’ll still be able to see what your own server was doing.


Now go read it

Once you have the folder, you need something to open it with, and the tooling here is thinner than the data deserves.

keyhole (https://github.com/simagix/keyhole) has been the reference for years and renders FTDC through Grafana. If you want dashboards and don’t mind standing up the stack, start there.

I built Big Hole (https://github.com/zelmario/Big-hole) for the other case, opening a capture the way you open a log file. It runs entirely in your browser: no backend, no container, nothing uploaded. You drop the folder in and it decodes on your machine. That turns out to matter a lot with this particular file, because the captures worth analysing usually belong to somebody else’s production cluster, and “nothing leaves your machine” is often the difference between being allowed to look at it and not. It opens the Atlas tarball as-is, puts every replica set member on one time axis, shows you who was primary when, overlays your mongod.log on the same timeline, and runs automated checks for the usual suspects, ticket pool exhaustion, cache pressure, flow control. MIT licensed, tested against MongoDB 4.4 through 8.0. You can see a live demo here: https://zelmario.github.io/Big-hole/

Pick whichever you like. Just don’t wait until you need it, by then the data you wanted is already gone.

 

 

The post The diagnostic data MongoDB Atlas doesn’t hand you appeared first on Percona.

Blog – Percona