database error when there is heavy traffic on the site

Hello @siutek,

Thank you for reaching out.

A large database can indeed cause performance issues, especially during high-traffic periods like promotional events. When the database is large, queries can take longer to execute, leading to slower site performance.

However, to improve your site’s efficiency, you can consider archiving old orders. WooCommerce doesn’t have a built-in feature for this, but you can use plugins like WooCommerce Customer / Order / Coupon Export to export old orders and then delete them from your database.

Another method to slim down your database is to use a plugin to clean up your database by removing unnecessary data like trashed posts, revisions, unused tags etc. You can do a quick search in the WordPress plugin repo for third-party plugins that can handle this. A quick search yielded the following results: https://www.google.com/search?q=Database+optimization+plugins

Remember to always back up your database before making any changes. If you’re not comfortable performing these tasks, consider hiring a professional to ensure everything goes smoothly.

I hope this helps! If you have any other questions, feel free to ask.

We’re here to help.

Can anyone of you share your experience in optimizing large tables using partitioning? Does it generally make sense for WordPress and WooCommerce?

Is it capable of increasing the performance of a database that is 5.4 GB and has around 40 million records, including 20 million in wp_postmeta?

Hi @siutek,

I understand you’re considering database partitioning to enhance performance by dividing large tables into more manageable segments.

Since WordPress and WooCommerce don’t support partitioned tables by default, setting this up will need custom development. This includes changing core queries and making sure your plugins and theme stay compatible.

It’s worth noting that, while partitioning can improve performance, the benefits depend on how queries are set up and how data is accessed. If not done carefully, partitioning might provide little benefit or even make performance worse.

Before pursuing database partitioning, here are a few alternative approaches that may achieve similar results:

• You can implement HPOS, which stores order data in custom tables optimized for WooCommerce. This helps reduce the load on the wp_posts and wp_postmeta tables.
• Ensure your database tables, especially large ones like wp_postmeta, are properly indexed. Effective indexing can significantly improve query performance.
• Regularly clean up your database to remove unnecessary data, such as expired transients, spam comments, and old revisions. This helps keep your database lean and efficient.

For further guidance, you may find these resources helpful:

• WooCommerce HPOS Documentation
• Optimizing WordPress/WooCommerce DB Indexes
• WooCommerce Database Optimization

Let me know if you have any questions or need further assistance!