Scale WooCommerce: Database, Cache & PHP Worker Tuning

Running a WooCommerce store that attracts thousands of visitors per minute is a rewarding challenge. The platform’s flexibility lets you sell anything, but high traffic also puts pressure on the underlying database, PHP processes, and caching layers. In this article we’ll walk through practical steps to keep your store fast and reliable without relying on vague promises. We’ll cover three core areas:

• Optimizing database queries
• Implementing object caching
• Setting sensible PHP‑FPM worker limits

All recommendations apply to typical LAMP/LEMP stacks on Linux. Where commands differ between Debian‑based (apt) and RHEL‑based (dnf) distributions, we provide separate blocks. Windows Server uses different package managers and service controls, so the concepts remain the same but the exact commands will differ.

1. Audit and Trim Heavy WooCommerce Queries

WooCommerce stores a lot of meta data in wp_postmeta and wp_woocommerce_order_items. Over time, unnecessary rows and missing indexes can cause the same queries to scan millions of rows.

1.1 Identify Slow Queries

Enable the MySQL slow‑query log and set a low threshold (e.g., 0.5 seconds). Then run a few typical user actions (product page, cart update, checkout) and review the log.

# Debian/Ubuntu (apt)
sudo apt-get install -y mysql-client
sudo mysql -u root -p -e "
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
"

# AlmaLinux/Rocky/RHEL (dnf)
sudo dnf install -y mysql
sudo mysql -u root -p -e "
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
"

The command turns on the slow‑query log and tells MySQL to record any query that runs longer than half a second. After testing, view the log (default location /var/log/mysql/slow.log) to see which WooCommerce queries appear most often.

1.2 Add Missing Indexes

Common culprits are meta‑key lookups without an index. For example, the query that fetches product visibility often uses meta_key = '_stock_status'. Adding a composite index speeds it up dramatically.

# Debian/Ubuntu
sudo mysql -u root -p -e "
USE your_database;
ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key(191), meta_value(191));
"

# AlmaLinux/Rocky/RHEL
sudo mysql -u root -p -e "
USE your_database;
ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key(191), meta_value(191));
"

The 191 length works for most MySQL versions that limit indexed column length for VARCHAR. Adjust if you use a different collation.

1.3 Clean Up Orphaned Meta

Run a one‑off script to delete meta rows that no longer belong to an existing post. This reduces table size and improves index efficiency.

# Debian/Ubuntu
sudo mysql -u root -p -e "
USE your_database;
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;
"

# AlmaLinux/Rocky/RHEL
sudo mysql -u root -p -e "
USE your_database;
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;
"

Always back up the database before running delete statements.

2. Enable Persistent Object Caching

WordPress (and therefore WooCommerce) can store the results of expensive queries in an object cache. When a cache backend such as Redis or Memcached is available, repeated page loads avoid hitting the database altogether.

2.1 Install Redis Server

Redis is lightweight, supports persistence, and integrates well with popular caching plugins.

# Debian/Ubuntu (apt)
sudo apt-get update
sudo apt-get install -y redis-server

# AlmaLinux/Rocky/RHEL (dnf)
sudo dnf install -y redis

After installation, start and enable the service so it survives reboots.

# Debian/Ubuntu
sudo systemctl enable --now redis-server

# AlmaLinux/Rocky/RHEL
sudo systemctl enable --now redis

2.2 Configure WordPress to Use Redis

Add the following constants to wp-config.php. They tell WordPress to use the Redis object cache and set a reasonable timeout.

define( 'WP_CACHE', true );
define( 'WP_REDIS_HOST', '127.0.0.1' );
define( 'WP_REDIS_PORT', 6379 );
define( 'WP_REDIS_TIMEOUT', 1 );

Then install a compatible object‑cache drop‑in, such as the Redis Object Cache plugin. Upload the object-cache.php file to wp-content and activate the plugin from the admin dashboard.

2.3 Tune Redis Memory Policy

For high‑traffic stores you typically want Redis to keep the most frequently accessed keys and evict the least used when memory is full. Edit /etc/redis/redis.conf (or the appropriate config file for your distro) and set:

maxmemory 2gb
maxmemory-policy allkeys-lru

Replace 2gb with a value that fits your server’s RAM budget. Restart Redis to apply the changes.

3. Adjust PHP‑FPM Worker Settings for Concurrency

WooCommerce pages often require multiple PHP processes, especially during checkout where several API calls happen in parallel. PHP‑FPM (FastCGI Process Manager) controls how many workers are available and how long they live.

3.1 Locate the PHP‑FPM Pool File

On most distributions the default pool file is /etc/php/7.4/fpm/pool.d/www.conf (replace 7.4 with your PHP version). The same path works for both Debian‑based and RHEL‑based systems, though the package may place it under /etc/php-fpm.d/ on RHEL.

3.2 Calculate Safe Worker Limits

Use the formula:

max_children = (total RAM – RAM reserved for OS & other services) / average PHP worker memory

Assume a 8 GB VPS, reserve 2 GB for the OS, MySQL, and Redis, leaving 6 GB. If ps -ylC php-fpm7.4 --no-headers shows each worker uses ~30 MB, then:

max_children ≈ 6000 MB / 30 MB ≈ 200

3.3 Apply the Settings

# Debian/Ubuntu (edit /etc/php/7.4/fpm/pool.d/www.conf)
sudo nano /etc/php/7.4/fpm/pool.d/www.conf

# AlmaLinux/Rocky/RHEL (edit /etc/php-fpm.d/www.conf)
sudo nano /etc/php-fpm.d/www.conf

Set the following directives (adjust numbers based on your calculation):

pm = dynamic
pm.max_children = 200
pm.start_servers = 20
pm.min_spare_servers = 10
pm.max_spare_servers = 40
pm.max_requests = 5000

Explanation:

• pm = dynamic: PHP‑FPM will spawn workers based on demand.
• pm.max_children: Upper limit of concurrent PHP processes.
• pm.start_servers: Number of workers started on service launch.
• pm.min_spare_servers and pm.max_spare_servers: Keep a buffer of idle workers to handle traffic spikes.
• pm.max_requests: Recycles a worker after handling this many requests, preventing memory leaks.

After editing, restart PHP‑FPM:

# Debian/Ubuntu
sudo systemctl restart php7.4-fpm

# AlmaLinux/Rocky/RHEL
sudo systemctl restart php-fpm

4. Combine Caching Layers for Maximum Throughput

Object caching alone is not enough for a busy storefront. Pair it with a page‑cache solution such as W3 Total Cache or WP Rocket. Configure the plugin to:

1. Cache pages for logged‑out visitors (the majority of traffic).
2. Exclude the cart, checkout, and My Account pages from page cache to avoid stale data.
3. Serve cached assets (CSS/JS) from a CDN if you have one.

When a cached page is served, PHP‑FPM and the database are bypassed entirely, freeing resources for users who need dynamic content.

5. Monitor, Test, and Iterate

Optimization is an ongoing process. Use the following tools to keep an eye on performance:

• MySQL slow‑query log – review weekly.
• Redis INFO – redis-cli INFO memory shows hit/miss ratios.
• PHP‑FPM status page – enable pm.status_path = /status in the pool file and protect it with basic auth.
• Load testing – tools like Locust or JMeter simulate traffic spikes.

When you notice a metric degrading (e.g., high Redis miss rate or PHP‑FPM reaching pm.max_children), revisit the corresponding section and adjust settings.

Conclusion

Scaling WooCommerce for high‑traffic Indian markets doesn’t require exotic hardware—just disciplined database tuning, a reliable object cache, and well‑sized PHP‑FPM workers. By regularly auditing queries, keeping Redis healthy, and configuring PHP‑FPM based on actual memory usage, you create a resilient stack that can handle thousands of concurrent shoppers while keeping response times low. Remember to monitor continuously and iterate; the traffic patterns of an online store evolve, and your configuration should evolve with them.