The client operates a multi-carrier shipping and fulfillment platform used by e-commerce sellers to manage order routing, courier allocation, and delivery tracking at scale. The platform runs on a single production MySQL database supporting shipment creation, courier assignment, tracking updates, and billing, alongside a secondary analytics schema.

Business Challenges

The database had scaled organically alongside rapid order volume growth, and early configuration decisions had not been revisited against current load.

Performance bottlenecks

Several high-frequency queries on order, shipment, and courier assignment tables were running full table scans instead of using indexes, adding latency to core workflows.

Over-provisioned capacity

Connection limits were configured far above historic peak usage, masking real capacity needs and complicating planning.

Limited visibility into access risk

Multiple database accounts, including administrative and service accounts, had non-expiring passwords and were reachable from any host.

Rising storage costs

Several transactional tables had grown past a billion rows with no active archival process, driving up storage and backup costs.

Engineering resource drain

Without a documented configuration baseline across the primary server and its read replicas, the in-house team spent time reacting to issues rather than following a tuning standard.

Project Objectives

Geopits scoped the engagement to give the client's engineering team a documented, actionable baseline for the environment, one that would hold up under continued transaction growth.

Key Goals:

Establish an industry-aligned configuration baseline across the primary server and both read replicas
Eliminate full table scans on business-critical queries through targeted indexing
Right-size connection capacity and tighten access controls without disrupting live operations

Solution Provided by Geopits

Geopits ran the audit as a structured, four-part engagement, reviewing the primary server and both read replicas against current MySQL 8.0 best practices and the client's actual production workload. Geopits also reviewed a cost-optimization path: migrating from AWS RDS to a self-managed MySQL deployment on EC2 using read-replica-based replication for a validated, zero-downtime cutover.

Configuration and security audit

Close to 720 tables, connection limits, and user access controls were reviewed across the primary server and two read replicas.

Query and index analysis

The slow query log was analyzed, full table scans identified, and composite index recommendations issued for the ten highest-impact queries.

Capacity and archival planning

Connection limits were right-sized against real historic usage, and 24 tables exceeding a billion rows were flagged for phased archival and partitioning.

Remediation roadmap

A prioritized, business-aligned roadmap was delivered covering configuration changes, index additions, access hardening, and a version upgrade path.

Results & Business Impact

This engagement was a configuration, security, and performance audit. The figures below reflect audit scope and findings, not measured before-and-after performance gains, since the client's engineering team is still implementing the roadmap in phases.

Capabilities

Before

After

Connection ceiling

16,000 configured, 6,390 historic peak

7,500, right-sized with headroom above peak

Buffer pool dump on shutdown

25% of pool cached

75%, for faster recovery

Dirty page management

4 page cleaners, 10% low watermark

8 page cleaners, 20% low watermark

Binary log retention

Unmanaged (expire_logs_days = 0)

Automatic 5-day purge

Key outcomes

53% lower connection ceiling

The connection limit was right-sized from 16,000 to 7,500, based on a historic peak of 6,390, reducing capacity masking without cutting into real headroom.

10 high-impact queries re-indexed

Composite index recommendations were issued for the ten highest-cost queries in the slow query log, covering core order, shipment, courier assignment, and billing workflows that were previously running full table scans.

Zero duplicate indexes found

The existing schema had no duplicate index definitions, confirming the team's indexing discipline and letting the engagement focus entirely on genuine coverage gaps.

1.6B+ rows scoped for archival

24 high-growth transactional tables, several exceeding a billion rows, were identified as candidates for a phased partitioning and archival strategy to control long-term storage and backup costs.

Access control gaps flagged

16 database accounts, including administrative and service accounts, were found with non-expiring passwords and unrestricted host access, each paired with a recommended host or subnet-based restriction.

Conclusion

The audit gave the client's engineering team a clear, prioritized baseline for its MySQL environment: where indexing gaps sat, where access controls needed tightening, and which tables were ready for archival. Geopits then worked with the team to phase these changes into production without disrupting live shipment volume.

Ready to Transform Your Data?

Geopits works alongside your team as a strategic partner, starting with stabilizing your current databases, then modernizing your data infrastructure, and ultimately helping you unlock the full potential of AI.

Book a Demo Learn More

170

Happy Clients so far

2100+

Databases Managed

142+

Successful Migrations

At Geopits, our mission is to empower businesses to achieve reliable, secure and high-performing database environments and to derive meaningful business insights.