We've got a WP+woocommerce site that is over 8 years old. The site has over 60,000 users and a similar number of orders. The wp_postmeta has over 4,000,000 records and wp_usermeta has over 1,500,000. This is causing all kinds of issues because the site was not updated regularly. The site wants to update the DB and it crashes every time, likely because of these tables.

Any ideas?

  • This and this could help you – Mayeenul Islam Sep 3 '20 at 4:23
  • Hey Brent, I have answered your question, do you have any other queries? I am willing to discuss further. – Aditya Agarwal Sep 4 '20 at 11:57

There are multiple Things to consider,

1.) No database is too big, to be handles, if you have a big database you probably have enough revenue also to get a really good hosting to handle it.

2.) Databases have frequent reading and writing, not just postmeta and usermeta but in general, my advice is to optimize all database tables once a week. This will keep them great, you can do it via WP-Optimize plugin, or you can manually go to PHPmyAdmin and select all tables and run optimize command.

3.) This I haven't tried much, but because your site is WooCommerce, and probably has lot of plugins, it can be that the sheer size of database isnt impacting, but the number of post meta queries are impacting you more. Therefore you can try with W3 Total Cache, it has got something called Database Caching, this can speed up the process of queries made to the database, hence improving performance and decreasing load.

4.) On my own site, I use some code to store daily stats of all my authors in user meta, it used to add about 1600 rows every day, and it slowly started to choke the server. 1600 isn't much, but in 1 month, it becomes 48,000 and in a year, it can be 4,80,000 which is big. So I really investigated, and found 2 things,

  • if you are storing lot of meta data of your own, then rather than using seprate keys, its better to put a serialized array. I used to have 200 authors, and had to store 8 things daily. Making total 1600 rows, but then I made those 8 into an Array, and serialized and stored under 1 key, making it 200 rows a day. This will not have size significant size impact on database but it surely speeds up site over time.

  • the next one is much more complicated, yet effective. WordPress is messy, we often install and uninstall many plugins, or may be switch plugins based on our needs, plugins often store the settings and configurations, into the database, and do not delete it on Uninstall because, when you reinstall the plugin in future, you dont have to reconfigure. But many a times this can add extra weight. I used to use Page Builders earlier, but then I ditched them for custom code. This was a good move, but few days later I saw my database still had lots of postmeta of elementor. So, I ran SQL query to delete the elementor and deleted a few thousand rows, making things cleaner and effective.

Besides this as always, invest in a great hosting, and preferably get a dedicated server for unmatched experience, use latest possible versions of SQL and PHP

  • 1. The site is on a Siteground managed VPS on Google Cloud, it has more than enough resources. 2. The tables causing the issues have millions of rows. Even using phpmyadmin to try and repair or optimize just the single tables results in an error. – Brent Friar Sep 8 '20 at 17:08
  • 3. This isn't a cache issue. The site runs fine. The problem is that the Wordpress database update will not complete so we cannot update Wordpress. 4. Orphaned records have already been removed. That's how we got down to only 4.5 million records. The issue is that all of those are required. For each order there are dozens of postmeta entries that are made and the site takes tens of thousands of orders a year. Removing the remaining rows would be removing data from previous orders. – Brent Friar Sep 8 '20 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.