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.
💡Tip: Even with Full Page Cache enabled, frequent cache invalidations can expose underlying performance issues that need to be addressed at the code level.
Investigation Process
Initial page load time measurements showed critical performance issues. New Relic monitoring revealed significant performance spikes during peak hours, making investigation urgent.
New Relic monitoring showing performance spikes during peak hours
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.
Full Page Cache Analyzer showing cache miss patterns
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.
| 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 Findings
Freento SQL Log trace analysis identified the root cause:
- 🔴
Identical SQL queries executed multiple times
The same queries were being run hundreds of times per page load, fetching the same data repeatedly instead of caching or reusing results.
- 🔴
Loop-based single queries instead of collection bulk operations
Instead of loading multiple records in one query, the code was executing individual queries inside loops, creating thousands of unnecessary database round-trips.
- 🔴
Missing query result caching for repeated data access
Frequently accessed data was not being cached, forcing the application to re-query the database for information that rarely changes.
Duplicate queries overview in Freento SQL Log
Solutions Implemented
Primary Optimizations
- 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.
⚠️Important Note: During investigations, we often discover additional issues beyond the primary problem. In this case, our cache invalidation findings led to a separate optimization case that further improved performance.
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.
Technical Implementation Details
Detailed analysis of SQL query traces and performance monitoring data allowed us to pinpoint exact code locations causing the performance degradation. Using Freento's diagnostic tools, we were able to identify not only the primary issue but also related performance problems that compounded the slow page loads.
✅Best Practice: Always use Magento collections for bulk operations instead of loading models individually in loops. This simple pattern change can reduce query counts by orders of magnitude.
Is Your Magento Store Experiencing Slow Load Times?
Let us analyze your database queries and optimize your code for maximum performance. Get a comprehensive performance audit with actionable solutions.