Solving High Page Load Times with Freento Operations Counter + Freento SQL Log
This case study demonstrates how we identified and resolved severe performance issues on category pages experiencing 10-second load times. Using Freento's diagnostic tools, we discovered excessive database queries caused by inefficient code loops, ultimately achieving a 4x performance improvement.
Client Story
The client operates a Magento store with numerous customer groups. Despite having Full Page Cache enabled, category pages were loading slowly for all visitors, including guests. The issue was particularly noticeable during high-traffic periods, with page load times reaching 10-12 seconds.
Investigation Process
Initial page load time measurements showed critical performance issues. New Relic monitoring revealed significant performance spikes during peak hours, making investigation urgent.
Full Page Cache was working, but analysis using Freento Full Page Cache Analyzer revealed approximately 100 uncached page loads per day for each category page. This happened due to cache invalidations from customer group switches and post-order processes.
Database Query Analysis
The most concerning discovery was the 10-second load time for uncached pages. Using Freento Operations Counter, we measured and logged MySQL and Redis operations on the category pages.
Investigation Summary
| Metric | Before | After |
|---|---|---|
| MySQL Queries | ~7,000 | ~800 |
| Redis Operations | ~1,200 | ~600 |
| Page Load Time | 10s | 2.5s |
These numbers significantly exceeded expectations for a standard Magento installation. Further investigation with Freento SQL Log revealed numerous duplicate and similar SQL queries, indicating inefficient loop-based operations instead of bulk queries.
Key Finding: Query Pattern Issues
Freento SQL Log trace analysis identified the root cause:
- Identical SQL queries executed multiple times
- Loop-based single queries instead of collection bulk operations
- Missing query result caching for repeated data access
The trace clearly showed problematic code sections executing queries in loops.
Duplicate queries overview in Freento SQL Log
Solutions Implemented
Primary Solution
- Replaced single queries in loops with bulk collection operations
- Implemented proper Magento collection usage patterns
- Added query result caching for frequently accessed data
- Optimized product attribute loading
Secondary Findings
- Identified Full Page Cache invalidation patterns
- Created separate optimization ticket for cache issues
- Documented all performance bottlenecks found
- Established monitoring for query counts
Performance Improvements
Results
9x Query Reduction
MySQL queries reduced from ~7,000 to ~800 per page load, dramatically decreasing database load.
4x Speed Improvement
Uncached page load time reduced from 10 seconds to 2.5 seconds, improving user experience.
Conclusion
The combination of Freento Operations Counter and SQL Log provided crucial visibility into performance bottlenecks. By transforming inefficient loop-based queries into bulk operations, we achieved a 4x improvement in page load times. The secondary discovery of cache invalidation issues, addressed in a follow-up case, demonstrates the value of comprehensive performance analysis.