Support » Plugin: Optimize Database after Deleting Revisions » Analyzer outpu size breaks/crashes nrowsers

  • Resolved Reiner030

    (@reiner030)


    It would be nice to have a summarized statistic output first or alone for analyze and the detail listing only after summarize or as dedicated page.

    In my case it seems a developer forgot to activate some months/years ago to also delete orphaned objects so the database grows bigger and bigger. While testing out howto decrease size of staging database I found that there are many open revision posts because up to 10 revisions are held per page/posts/items:

    MariaDB> SELECT COUNT(*) FROM wp_posts WHERE post_type =’revision’;
    +———-+
    | COUNT(*) |
    +———-+
    | 19353 |
    +———-+
    1 row in set (0.18 sec)

    and there must be much more post orphaned object but I have no SQL quary for it yet to check the count of them.

    And I can’t check how much space/data is to be deleted by this plugin because it always didn’t finish it’s output caused by very very long listing details instead of an initial summary output.
    * w3m – a cli command browser is crashing after fetching around 200 MB content;
    * Chrome tab is crashing after around 3.000 items listed.

    So it would be nice to get commonly such summarize function I think.
    Bests

Viewing 4 replies - 1 through 4 (of 4 total)
  • “EDIT” – some 0 missing:
    * Chrome tab is crashing after around 300.000 items listed.

    Plugin Author cageehv

    (@cageehv)

    Hey Reiner,

    It doesn’t surprise me that a browser crashes when you try to render a 200MB page…

    I suggest to clean up one item at a time.
    For instance: first clean the revisions, then the trashed items, then the spammed items, and so on and on.

    After the database is in good shape again, you can use the plugin the regular way again.

    Let me know if that works for you.

    peace,
    Rolf

    Hello Rolf,

    I don’t think that it can still be done by webinterface 😉
    My search how you do the cleanup found this query:

    // DELETE POSTMETA ORPHANS
                           $sql = sprintf ("
                           SELECT '%s' AS site,
                                   <code>post_id</code> AS ID,
                                   'meta' AS type,
                                   '' AS post_title,
                                   '' AS post_modified,<code>meta_key</code>, <code>meta_value</code>
                             FROM %spostmeta
                            WHERE post_id NOT IN (SELECT ID FROM %sposts)
                            ORDER BY <code>meta_key</code>
                           ", $prefix, $prefix, $prefix);

    So I shortened it to check only count of orphaned post metadata; it also takes very long to quere the count while uncached:

    # time mysql ALL_staging_landing_pages -e 'SELECT COUNT(*) FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts);'
    +----------+
    | COUNT(*) |
    +----------+
    |   458809 |
    +----------+
    
    real    0m21.260s
    user    0m0.012s
    sys     0m0.008s

    Thats the intention to have some summary only before cleanup. But perhaps it’s only of interest if enough people are requesting for this 😉
    Our developer has to check then on staging system if the deletion is unproblematic (which I think so).

    (The pages/trash count is so high because there is an automatic update task setup every minute for multiple languages each which pushes new/updated/deleted pages to WordPress instance for a long time.)

    Plugin Author cageehv

    (@cageehv)

    Hey Reiner,

    I will see what I can do with a summary

    Thanks for your feedback..

    Rolf

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Analyzer outpu size breaks/crashes nrowsers’ is closed to new replies.