WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It only takes a minute to sign up.
Sign up to join this community
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?
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
