Too large wp_woocommerce_sessions in Database [How-To Fix]

As I said in other articles, WooCommerce is an increasingly robust and flexible module, capable of transforming WordPress a Ideal online store for a start-up business.

For a small online store, WooCommerce can be run on a "shared hosting", and can easily support 5.000 products / 100 orders per day, if the theme used does not consume many resources and do not abuse other modules (plugins ).

Source code optimization WP Themes, modules (WP Plugins) and database optimization there are also two aspects that we need to be very careful about.

I noticed the other day that an online store of modest size is starting to consume enormously many RAM resources and CPU on a dedicated server generous. This high consumption of resources led to an increased "load" on the server and a long loading time of the onine store pages.

When we notice an unjustifiably high load on a web server, it is good to carefully investigate the causes before taking any action.

In the database I noticed that the table "wp_woocommerce_sessions"It's huge. It had over 6 GB. A huge volume for a table in a database that normally did not exceed 100 MB in total.

What is wp_woocommerce_sessions?

As we can guess from his name, the table "wp_woocommerce_sessions”Contains PHP sessions (PHP Sessions).
These sessions are actions of the users of the site (or of the web robots) and most of the time they reach the database through cookies. In wp_woocommerce_sessions are stored data about products placed by users in the shopping cart, coupons, shipping fees, customer data and many other information related to the product ordering process.

These sessions are created whether or not the user is registered on the site and normally should automatically expire and delete after a while.

Unfortunately, it is not always the case that these sessions are deleted and in some cases they remain permanently stored in wp_woocommerce_sessions, which makes this table quite large.

How to delete the table wp_woocommerce_sessions from SQL?

1. go to Dashboard →  WooCommerce →  Status →  Tools (tab).

2. Scroll down to the option "Clear customer sessions“. Careful! Deleting customer sessions means deleting all the products they put in the shopping cart. If while deleting these sessions there are customers who have products in the cart, they will disappear and the possible online order will not be completed.

3. Click "Clear" and confirm the operation.

At this point the "wp_woocommerce_sessions" table is empty, so all customer shopping sessions have been deleted.

The problem is far from solved. The wp_woocommerce_sessions SQL table will gather data in it again and by default it will gain proportions again, which is not desirable.

How do we stop customer sessions from being permanently stored in WooCommerce - wp_woocommerce_sessions?

We have shown above how the "wp_woocommerce_sessions" table can be emptied from the database, but the problem will appear again after a few days when customer sessions will gather again.

Prior to WooCommerce 2.5, customer sessions were captured via cookies and then saved to the table. WordPress "wp_options".
For larger stores, this method has often led to disasters. “Wp_options”Being the vital table of WordPress, used by general options and settings. Identifying and manually deleting client sessions stored in wp_options it was not an easy job.
WooCommerce performance was not the best, and scalability over time was a sensitive point.

With the launch of WooCommerce 2.5 in 2015, WooCommerce developers have introduced a new WooCommerce session handling and storage system, based on WP Sessions Manager. This system led to the emergence wp_woocommerce_sessions. A dedicated table for client sessions, which in the database does not interact with other tables. In case of major errors, the losses should be minimal.

From what I've seen, the online stores launched before WooCommerce 2.5 and which have received constant updates over time have some issues with the automatic deletion of client sessions. Most likely it is a bug that appeared on a newer version of WooCommerce 2.5 or a plugin WordPress / WooCommerce that didn't keep up with the updates.

In my case it was a conflict with a function added to the file functions.php of the theme, and which prevents automatic deletion after a time of expired sessions from woocommerce_sessions.
If you are facing such a problem you need to carefully investigate all possible causes. SQL error, SQL permissions, Cron Job, conflicts with other plugins and last but not least check the changes made by you in the code over time.

We saw that WooCommerce offers for only $ 29 a plugin capable of managing customer sessions. “Clear Cart and Sessions for WooCommerce“. Of course, in addition to the scheduled deletion of sessions, the module has several tools that could help you.

A simpler option scheduled deletion of “woocommerce_sessions”At one-day intervals, is available with the following code in functions.php:

// clean cart every day
if (!wp_next_scheduled('cron_wc_clean_cart')) {
    wp_schedule_event( time(), 'daily', 'cron_wc_clean_cart' );
}

add_action ( 'cron_wc_clean_cart', 'wc_clean_session_cart' );
function wc_clean_session_cart() {
    global  $wpdb;

    $wpdb->query( "TRUNCATE {$wpdb->prefix}woocommerce_sessions" );
    $wpdb->query( "DELETE FROM {$wpdb->usermeta} WHERE meta_key='_woocommerce_persistent_cart_" . get_current_blog_id() . "';" );
    wp_cache_flush();
}

Leave a comment if you need help or have another solution.