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

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

PrestaShop merchants often face the daunting challenge of slow product imports, especially with large catalogs. What seems like a database or image processing bottleneck can sometimes be a more elusive, systemic issue. A recent PrestaShop forum thread illuminated a prime example, where a user's meticulous profiling uncovered a surprising culprit behind agonizingly slow CSV imports and overall back-office sluggishness on PrestaShop 9.1.4. This deep dive offers invaluable lessons for anyone struggling with PrestaShop performance.

The Mystery of the Slow Import

The original poster, RCTSH, was attempting to update 13,000 existing products via CSV import on a dedicated VPS running PrestaShop 9.1.4. Despite a robust server environment (ARM Ampere A1, 2 cores, 16GB RAM, PHP-FPM 8.3, MariaDB), import speeds were dismal: 14-22 products per minute, translating to 11-15 hours for the full catalog. Detailed profiling with perf and top revealed a single PHP-FPM process hogging one core, with almost half its time spent in in_array() or array_search() operations. Crucially, disk I/O and database activity were minimal, indicating a PHP-level processing bottleneck.

The Unexpected Culprit: ps_employee_session

The breakthrough came with a PHP-FPM slowlog. It consistently pointed to in_array() calls originating from Doctrine's ArrayCollection and PersistentCollection during the loadEmployeeByIdentifier() process. Astonishingly, PrestaShop's back-office, on every single request (including each import batch), was loading all employee sessions from the ps_employee_session table. The shop in question had only two employees but a staggering 36,694 rows in ps_employee_session. Doctrine was hydrating all these sessions and performing strict in_array() checks against them, causing the massive CPU overhead. Old, expired sessions were never automatically deleted, leading to a table that grew indefinitely and progressively slowed down the entire back-office over time.

The Fix and Dramatic Results

The immediate solution was to prune the ps_employee_session table. RCTSH used a simple SQL query to delete old sessions, keeping only the 50 most recent ones:

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

This single change had a profound impact. Import speeds on the VPS skyrocketed from 14-22 products/minute to 85-126 products/minute. On the old shared hosting, speeds improved from 600-900 products/hour to approximately 2,500 products/hour.

Beyond Sessions: Further Optimizations

While the session table was the primary bottleneck, other optimizations further refined the import process:

  • Module Indexing: Adding an index on id_product, position to an "additional tabs module" table (which was scanning 316,000 rows per product) boosted speeds to 165-220 products/minute. This highlights the importance of reviewing custom module performance.
  • Local Image Fetching: The VPS was downloading product images from the old host during import. Pointing the domain to the server itself via the hosts file eliminated this network overhead, pushing speeds to 186 products/minute, with peaks of 377-645.
  • Browser Throttling: Keeping the import tab visible was crucial, as browsers throttle background tabs, slowing down AJAX-based import batches.

Collectively, these optimizations reduced the 13,000-row import from an estimated 11-15 hours to just about 1 hour on the VPS.

Maintenance and Future Considerations

To prevent recurrence, a nightly cron job was set up to regularly prune the ps_employee_session table. For PrestaShop users, an alternative to custom SQL is the "Clear outdated sessions manually" button found under Advanced Parameters > Security > Employee Sessions in the back-office, which respects the PS_COOKIE_LIFETIME_BO setting. For automated cron jobs, a similar SQL approach can be used:

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

(Replace X with your PS_COOKIE_LIFETIME_BO value).

This issue is a known pain point within the PrestaShop ecosystem. The community notes that loadEmployeeByIdentifier() should ideally not load all sessions, and automatic cleanup of expired sessions is a much-needed feature. An open pull request (#42875) on GitHub aims to address automatic cleanup in future PrestaShop versions.

Key Takeaways for Merchants and Developers:

This thread serves as a powerful reminder:

  • Profile, don't guess: Detailed performance analysis is key to identifying true bottlenecks.
  • Database hygiene matters: Regularly pruning old data, especially in system tables, is critical for long-term performance.
  • Module impact: Third-party modules can introduce unforeseen performance issues, often solvable with proper indexing.
  • Infrastructure awareness: Even subtle network configurations (like image fetching) can significantly impact import times.

By understanding and addressing these often-overlooked areas, PrestaShop users can dramatically improve their store's performance and efficiency.

Start with the tools

Explore migration tools

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

Explore migration tools