logo
Ask your WordPress questions! Pay money and get answers fast! (more info)

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.

$5
Need to clean up wp_postmeta database table

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

This question has been answered.

Dan | gteh | 07/27/11 at 11:07am Edit


(3) Possible Answers Submitted...

See a chronological view of answers?

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

  • avatar
    Last edited:
    07/27/11
    11:39am
    Peter Michael says:

    Does it get smaller if you run 

    OPTIMIZE TABLE wp_postmeta
    ?

    • 07/27/11 11:44am

      Dan | gteh says:

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

  • avatar
    Last edited:
    07/27/11
    11:53am
    fuscata says:

    Do you have a lot of revisions?

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


    If so, you can back up your database, then delete them. See http://www.ambrosite.com/blog/clean-up-wordpress-revisions-using-a-mysql-multi-table-delete

    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.

    Previous versions of this answer: 07/27/11 at 11:53am

    • 07/27/11 12:09pm

      Dan | gteh says:

      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

  • avatar
    Last edited:
    07/27/11
    11:53am
    Navjot Singh says:

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

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


    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:

    DELETE FROM wp_postmeta WHERE meta_key = 'your-meta-key';

    • 07/27/11 12:07pm

      Dan | gteh says:

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

    • 07/27/11 12:09pm

      Navjot Singh says:

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

    • 07/27/11 12:21pm

      Dan | gteh says:

      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.

This question has expired.



Dan | gteh voted on this question.



Current status of this question: Completed



Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.