tuning mysql / mariadb database for WooCommerce and WordPress

WooCommerce can really give mysql or mariadb a pounding.

On smaller hosting platforms WooCommerce will grind to a halt.

If you have a VPS, here are a few things you can tune, to give you fast database performance. The faster the database goes, the faster WooCommerce goes:

 

InnoDb cache

Assuming of course that you’re using innodb (which is a must – see below)

innodb_buffer_pool_size=1024M

The size should be as large as your wp_postmeta and wp_options tables, or 50% or more of your WooCommerce database. Don’t go above 25% of the RAM in your VPS.

 

Query Cache

There are a few articles out there saying disable query cache , but I think it really helps the WordPress wp_options table, which WooCommerce stores most of its data in.

query_cache_limit=2M

query_cache_size=64M

query_cache_type=1

If you have a lot more memory in your VPS, try this:

query_cache_limit=8M

query_cache_size=256M

query_cache_type=1

 

Make sure you’re using innodb

Innodb is the fastest and most reliable database engine for mysql , mariadb, WordPress and WooCommerce. Here’s how to check.

• Login to PHPMyAdmin
• Select the database for your WP site.
• Click the SQL tab.
• Type this:   show table status
• Click GO.

You are looking for engine and you want InnoDB.

If it’s myisam you need to fix it. Use this command in PHPMyAdmin (as above). make sure you have a good backup first.

• ALTER TABLE tablename ENGINE = InnoDB
• you’ll need to it for each table that was myisam  (replace tablename with each table)

Tune the wp_options table

I wrote another article on adding an index, and clearing transients, find it here.

By Scott Farrell on August 7th, 2018 , Follow @scott_WordPress Tweet to @scott_WordPress