Ask your WordPress questions! Pay money and get answers fast! Comodo Trusted Site Seal
Official PayPal Seal

Need to clean up wp_postmeta database table WordPress

  • SOLVED

I have a problem with one of my client sites.

The database is over 250MB in size and it is causing issues. 191MB of that is within the wp_postmeta table with over 3,000,000 rows.

Im getting a lot of 'mysql server has gone away' and blank pages.

Is there a way to optimize that table and delete what is not needed? How do I know what I can and cannot delete from that table with affecting anything serious? How can I check to see which row is taking up the most space, etc....

Thanks

Answers (3)

2011-07-27

fuscata answers:

Do you have a lot of revisions?

SELECT COUNT(*) FROM `wp_posts` WHERE `post_type` = 'revision';

If so, you can <strong>back up your database</strong>, then delete them. See [[LINK href="http://www.ambrosite.com/blog/clean-up-wordpress-revisions-using-a-mysql-multi-table-delete"]]http://www.ambrosite.com/blog/clean-up-wordpress-revisions-using-a-mysql-multi-table-delete[[/LINK]]

If that doesn't work, you may need a better hosting plan, caching and/or to tweak MySQL settings. The "gone away" error is usually caused by MySQL hitting its connection limit.


Dan | gteh comments:

I'm on a cpanel VPS server with a 2ghz processor, 2 cores, 1.5GB of ram, I have w3 total cache enabled as well.

I deleted revisions but it barely made a dent.

3,000,000 rows for post_meta seems really high to me

2011-07-27

Navjot Singh answers:

You can use this query to delete postmeta values which are not attached to any posts:

<blockquote>DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL</blockquote>

If you know of any custom key which was used by a plugin which you no longer use, you can run this query to remove it from table completely:

<blockquote>DELETE FROM wp_postmeta WHERE meta_key = 'your-meta-key';</blockquote>


Dan | gteh comments:

that only deleted 1 row from the 3,000,000+


Navjot Singh comments:

Try this plugin: [[LINK href="http://wordpress.org/extend/plugins/delete-custom-fields/"]]http://wordpress.org/extend/plugins/delete-custom-fields/[[/LINK]] - Delete those custom fields which you know you don't need/use now.


Dan | gteh comments:

that plugin did not work at all. it claimed to delete some custom fields, but didn't.

I'll play around with some queries and see what I can do.

thanks for suggestions.

2011-07-27

Peter Michael answers:

Does it get smaller if you run OPTIMIZE TABLE wp_postmeta ?


Dan | gteh comments:

No, I already ran an optimize. The overhead was very small.