PrestaShop

From Hours to Minutes: Unmasking PrestaShop's Hidden Performance Bottleneck in Large Imports

A speedometer indicating a rapid increase in speed, with a PrestaShop logo, symbolizing optimized import performance.
A speedometer indicating a rapid increase in speed, with a PrestaShop logo, symbolizing optimized import performance.

Unmasking the Silent Killer: How a Bloated Session Table Crippled PrestaShop Imports and Back-Office Performance

At Migrate My Shop, we frequently encounter PrestaShop merchants grappling with agonizingly slow product imports, especially when managing extensive catalogs. While common culprits like database configuration, server resources, or image processing often come to mind, sometimes the true bottleneck lies hidden in plain sight, a systemic issue that can cripple not just imports but overall back-office responsiveness. A recent deep dive by a meticulous PrestaShop user, shared on the official forums, perfectly illustrates this elusive problem, offering invaluable lessons for anyone struggling with PrestaShop performance.

The Mystery of the Agonizingly Slow Import

The original poster, RCTSH, faced a daunting challenge: updating 13,000 existing products via CSV import on a PrestaShop 9.1.4 installation. Despite a robust, dedicated VPS environment—featuring an ARM Ampere A1 processor, 2 cores, 16GB RAM, Nginx, PHP-FPM 8.3, and MariaDB—the import speeds were dismal. Initial measurements showed a mere 14-22 products per minute, projecting an unbearable 11-15 hours for the entire catalog update. This was far from the efficiency expected from such a powerful setup.

RCTSH's detailed profiling efforts, using tools like perf and top, revealed a peculiar pattern: a single PHP-FPM process was monopolizing one CPU core at 100%, while the other core remained largely idle. Crucially, disk I/O (wa=0%) and MariaDB activity were minimal, immediately ruling out common database or storage bottlenecks. The profiling data pointed to a PHP-level processing issue, with almost half of the busy core's time consumed by in_array() or array_search() operations, specifically with strict comparison (zend_is_identical). This indicated a large array being scanned repeatedly for every product processed.

A magnifying glass hovering over a complex database schema, highlighting a 'sessions' table, symbolizing the deep investigation into hidden PrestaShop performance issues.
Featured Image: Uncovering the hidden database bottlenecks in PrestaShop.

The Unexpected Culprit: The Bloated ps_employee_session Table

The breakthrough arrived with a PHP-FPM slowlog, which consistently highlighted in_array() calls originating from Doctrine's ArrayCollection and PersistentCollection during the loadEmployeeByIdentifier() process. The astonishing discovery was that PrestaShop's back-office, on every single request—including each batch processed during the product import—was loading all employee sessions from the ps_employee_session table. In RCTSH's case, a shop with only two employees had accumulated a staggering 36,694 rows in this table. Doctrine was hydrating these tens of thousands of sessions and then performing strict in_array() checks against them, leading to the observed CPU hogging.

This meant that with every click in the back office, and every product processed during an import, PrestaShop was sifting through an ever-growing list of old, expired sessions. As these sessions are not automatically purged in PrestaShop 9.1.4 (and earlier versions), the back office progressively slows down over time, a silent killer of performance.

The Fix: Pruning and Prevention

The primary solution was to prune the bloated ps_employee_session table. RCTSH implemented a simple SQL query to delete old sessions, retaining only the most recent ones to avoid logging out active employees:

SET @m = (SELECT MAX(id_employee_session) FROM ps_employee_session);
DELETE FROM ps_employee_session WHERE id_employee_session < @m - 50;

Important: Always back up your database before running direct SQL queries. Replace ps_ with your actual database prefix if it's different.

For those less comfortable with direct SQL, PrestaShop offers a manual option: navigate to Advanced Parameters > Security > Employee Sessions and click the "Clear outdated sessions manually" button. This method respects the back-office cookie lifetime (PS_COOKIE_LIFETIME_BO), ensuring active sessions are preserved.

To prevent future bloat, RCTSH set up a nightly cron job to automatically prune old sessions. A similar approach using date_upd can be implemented:

DELETE FROM ps_employee_session WHERE date_upd < NOW() - INTERVAL X HOUR;

Replace X with a value corresponding to your PS_COOKIE_LIFETIME_BO setting (e.g., 24 for 24 hours). It's worth noting that an open pull request (#42875) aims to introduce automatic cleanup for outdated sessions in future PrestaShop versions, addressing this systemic issue.

Beyond Sessions: Other Optimization Wins

While the session table was the biggest factor, RCTSH's thorough investigation uncovered other areas for improvement:

  • Indexing Custom Module Tables: An additional index on a custom module table (id_product, position) that was scanning 316,000 rows per product significantly boosted performance. Always ensure custom modules have appropriate indexes for their queries.
  • Localizing Image Fetching: The VPS was downloading every product image from the old host during the import. A simple hosts file entry, pointing the domain to the server itself, resolved this external dependency and sped up image processing.
  • Browser Tab Visibility: A crucial, often overlooked detail: browsers throttle background tabs, which can slow down AJAX-based import batches to a crawl. Keeping the import tab visible ensures consistent processing speed.
A digital speedometer showing a dramatic increase in speed, with a blurred PrestaShop logo in the background, illustrating the significant performance improvement achieved.
Dramatic performance gains after implementing optimizations.

Dramatic Results: From Hours to Minutes

The cumulative effect of these optimizations was transformative:

  • Before any cleanup: 14-22 products/minute (VPS), 600-900 products/hour (shared hosting).
  • After pruning ps_employee_session (36,694 → 50 rows): 85-126 products/minute (VPS), ~2,500 products/hour (shared hosting).
  • After adding module index and localizing image fetching: 186 products/minute, with peaks of 377-645 products/minute on the VPS.

The 13,000-row import that once took 11-15 hours was now completed in approximately 1 hour. This dramatic improvement underscores the power of meticulous profiling and targeted optimization.

Key Takeaways for PrestaShop Merchants & Developers

This case study offers vital lessons for anyone managing a PrestaShop store, especially those with large catalogs or experiencing general back-office sluggishness:

  1. Don't Overlook Hidden Database Bloat: "Invisible" tables like ps_employee_session can become performance killers over time. Regularly audit your database for excessively large tables, especially those that grow without automatic cleanup.
  2. The Power of Profiling: When performance issues arise, generic "my site is slow" complaints are unhelpful. Tools like perf, top, and PHP-FPM slowlogs are invaluable for pinpointing the exact code paths consuming resources.
  3. Implement Regular Maintenance: Automated cron jobs for database cleanup (like session pruning) are essential for long-term store health and performance.
  4. Context Matters: Even seemingly minor details like browser tab visibility or internal network routing can impact performance during resource-intensive operations.

At Migrate My Shop, we specialize in optimizing PrestaShop environments and ensuring smooth, high-performance operations, whether you're migrating to a new platform or fine-tuning an existing one. If you're struggling with PrestaShop performance, don't let hidden bottlenecks cripple your business. Our experts are here to help you diagnose, optimize, and achieve the speeds your e-commerce store deserves.

Share:

Start with the tools

Explore migration tools

See options, compare methods, and pick the path that fits your store.

Explore migration tools