Skip to content
EncryptTechBD
Home Industries Our Work Blog Pricing About Contact Discuss Your Project
বাংলা (BN)
Website & Infrastructure 6 min read

Database performance tuning checklist for production teams

A recurring production checklist for database performance: baseline, query review, indexes, connections, storage, backups and change sign-off.

E
EncryptTechBD · 0 comments
Close-up of computer hardware inside a server chassis
Photo: Thomas Kvistholt / StockSnap

Performance work fails when it is treated as a rescue mission. A page slows down, someone raises the connection limit, the site improves for a week and then degrades again. The alternative is a recurring checklist that keeps the database inside known limits and catches drift before users report it.

This checklist is written for the person who owns a production MySQL, MariaDB or PostgreSQL database behind a web application. It is a working document, not a diagnostic article. Each item names an owner, the evidence to keep and how often to check it. Adapt the frequencies to your traffic, but keep the evidence requirement: without it, the checklist becomes a set of opinions.

1. Scope, baseline and ownership

  • Production database inventory completed. List every instance, version, hosting location, application and responsible person. Owner: infrastructure lead. Evidence: one register with version and support status. Frequency: quarterly and on every new deployment.
  • Service level defined. Write down acceptable response time for the main user journeys, not for the database in isolation. Owner: product owner with engineering. Evidence: a one-page target with peak-hour definitions. Frequency: annually.
  • Baseline captured. Record query latency, throughput, CPU, memory, disk latency, connections and replication lag for a normal week and a peak day. Owner: database administrator. Evidence: saved dashboard or export. Frequency: monthly.
  • Peak calendar agreed. Note campaign dates, month-end closes, salary runs and reporting deadlines that change load. Owner: operations lead. Evidence: shared calendar linked from the runbook. Frequency: quarterly.

2. Query workload review

  • Slow query log enabled with a business threshold. Set the threshold from user patience, for example the point at which a page feels slow, not the default. Owner: database administrator. Evidence: configuration and a full-day sample including peak. Frequency: continuous, reviewed weekly.
  • Top queries ranked by total time. A fast query called constantly can cost more than a slow report. Use pg_stat_statements on PostgreSQL or the performance schema on MySQL. Owner: engineering lead. Evidence: top-twenty list with call counts and total time. Frequency: monthly.
  • Execution plans saved for the top candidates. Record EXPLAIN output before and after each change. Owner: engineer making the change. Evidence: plan attached to the change ticket. Frequency: per change.
  • N+1 patterns removed. Review list endpoints and admin screens for repeated lookups. Owner: application team. Evidence: query count per request before and after. Frequency: per release review.
  • Reporting queries separated. Move heavy reports to a replica, a summary table or a scheduled job. Owner: engineering lead. Evidence: architecture note and schedule. Frequency: quarterly.

3. Index and schema hygiene

  • Missing-index review completed. Check filters, joins and sort columns on the largest tables. Owner: database administrator. Evidence: candidate list with estimated benefit. Frequency: monthly.
  • Duplicate and unused indexes reviewed. Every index adds write cost and storage. Owner: database administrator. Evidence: usage statistics per index. Frequency: quarterly.
  • Index write cost measured. Compare insert and update latency before and after adding an index. Owner: engineer making the change. Evidence: benchmark result in the change ticket. Frequency: per index change.
  • Statistics freshness verified. Confirm autovacuum or auto-analyze is running and that large bulk loads are followed by a statistics update. Owner: database administrator. Evidence: last-analyze timestamps for the largest tables. Frequency: monthly.
  • Schema changes reviewed for locking. Check whether an alteration rewrites a table or blocks writes, and schedule it accordingly. Owner: database administrator. Evidence: migration review note. Frequency: per migration.

4. Connections, configuration and storage

  • Connection count compared with capacity. Record maximum connections, observed concurrent sessions and application worker counts. Owner: infrastructure lead. Evidence: twenty-four-hour graph. Frequency: monthly and before any traffic campaign.
  • Pooling configured deliberately. Set pool size against what the database can serve, not against the number of application instances. Owner: application team. Evidence: configuration file and a load test. Frequency: per deployment change.
  • Memory settings reviewed. Compare buffer cache or shared buffers with the working set and with available memory. Owner: database administrator. Evidence: configuration snapshot and cache hit ratio. Frequency: quarterly.
  • Storage limits checked. Confirm provisioned IOPS, throughput, burst balance and free space against growth. Owner: infrastructure lead. Evidence: capacity chart with projected exhaustion date. Frequency: monthly.
  • Long-running transactions monitored. Find sessions idle inside a transaction and set a policy for terminating them. Owner: database administrator. Evidence: alert rule and weekly sample. Frequency: continuous, reviewed weekly.

5. Recovery, maintenance and monitoring

  • Backup restore tested. A backup that has never been restored is an assumption. Owner: infrastructure lead. Evidence: restore log with elapsed time and verification query. Frequency: quarterly.
  • Replication lag alerted. Set a threshold that matters to reporting freshness. Owner: database administrator. Evidence: alert configuration and a test notification. Frequency: quarterly.
  • Maintenance jobs verified. Confirm vacuum, analyze, optimize, statistics and log rotation actually complete. Owner: database administrator. Evidence: job history for the last cycle. Frequency: monthly.
  • Disk growth forecast updated. Compare current size with the last forecast and explain any jump. Owner: infrastructure lead. Evidence: forecast table. Frequency: monthly.
  • Alert thresholds reviewed against the baseline. Remove noisy alerts and add missing ones for latency, locks, connections and disk. Owner: on-call lead. Evidence: alert catalogue. Frequency: quarterly.

6. Change control and sign-off

  • Every performance change has a hypothesis. State the suspected cause, the expected effect and the measurement method. Owner: engineer making the change. Evidence: change ticket. Frequency: per change.
  • Rollback is prepared before deployment. Include the reverse migration, configuration backup and the person authorised to trigger it. Owner: engineering lead. Evidence: rollback section in the ticket. Frequency: per change.
  • Result verified after deployment. Repeat the same measurement under comparable conditions. Owner: engineer making the change. Evidence: before-and-after result. Frequency: per change.
  • Monthly review signed. The database owner, application owner and operations lead confirm the checklist is complete and exceptions have dates. Owner: operations lead. Evidence: signed summary with open actions. Frequency: monthly.

How to run the cycle

Keep the monthly review to one hour. Walk the checklist in order, mark each item complete, deferred or not applicable, and record one owner for every exception. Deferred items need a date and a reason; otherwise they disappear until the next incident.

Use a simple sign-off table with four columns: section, reviewer, date and open exceptions. The table is the audit trail, and it also shows whether the same items are deferred every month. Repeated deferral is a capacity problem, not a documentation problem.

Measure coverage rather than activity: the percentage of production databases with a current baseline, the percentage of slow queries reviewed, the number of untested restores, and the age of the oldest deferred item. These four numbers tell you whether the checklist is changing behaviour.

Our Managed Technology & Cybersecurity division runs database and infrastructure reviews alongside monitoring, backup and incident readiness. The pricing page explains how an assessment and an ongoing retainer are scoped. For the diagnostic side of a live incident, read database performance tuning: problems and fixes.

Sources

Services that deliver this

Want this implemented properly?

Pricing →

Every engagement starts with a written scope. If a requirement is outside it, we quote it separately before starting.

Responses (0)

No comments yet. Add the first one below.

Leave a response

Your email address will not be published. Required fields are marked *.

Keep reading

More field notes