Analytical queries on a live system.
WHAT THE PROBLEM IS
Reports scan large amounts of data, competing with the application.
WHAT THAT CAUSES
Slow pages while a report runs Locks held Buffer pool filled with data nobody else needs
WHY THAT LAST POINT IS SUBTLE
A report reading a year of history evicts the current data everyone needs.
WHAT TO DO
Run reports somewhere else.
WHAT OPTIONS EXIST
A read replica A separate copy refreshed periodically Summary tables maintained incrementally A separate analytical database
WHAT A REPLICA PROVIDES
Live data without touching the primary.
WHAT IT COSTS
Another server, and lag.
WHAT SUMMARY TABLES PROVIDE
Reports that read a small precomputed table.
WHY THAT IS FREQUENTLY BEST
It is cheap, and reporting becomes instant.
WHAT THEY REQUIRE
Maintaining them, and agreeing how current they must be.
WHAT TO SCHEDULE OFF-PEAK
Anything heavy that must run on the primary.
WHAT TO LIMIT
How long a report may run.
WHAT TO SET
A timeout, so a runaway report is stopped.
WHAT TO AVOID
Giving analysts direct access to the production database.
WHY
One unbounded query takes the application down.
