Optimize by measuring: downsizing a server with a single query fix

Context

After the storefront moved to ikas, only the admin panel and the integrations remained on our servers. The logical next step was to downsize them. But even without traffic, the database server was under significant load; downsizing without knowing the source was risky.

Approach

1. Measure first: The database’s load and wait statistics were collected regularly over several days.
2. Finding: About 85% of the database CPU load came from a single query. A sort value was being calculated on every new record, which meant scanning the entire table every time on tables with millions of rows.
3. Fix:
   • A suitable index was added; the query’s cost dropped by a factor of thousands.
   • The calculation was removed entirely on tables that didn’t need a sort value.
   • The database’s parallelism settings were reconfigured.
   • Unnecessary logging was removed and a regular cleanup job was set up for old logs.
4. Downsizing: After the fix, the database server went from 24 to 12 vCPUs and from 48 to 36 GB of memory, and the application server from 12 to 6 vCPUs.

Lessons learned

• “The server isn’t enough” often means “one query is wrong.” A few days of measurement before a hardware decision was the cheapest investment.

My contribution

I did the measurement, the analysis and the fixes; the server sizing decision was made together with the company.