70,000 posts WP site lags when posting/editing

  • Hi guys, as topic suggested. My site will always (100% of the time) be inaccessible for about 3-7 seconds after I click Publish or Edit a post. This problem started almost wayyy back from since I only had 30,000 posts.

    What I did to try resolve the issue:
    Research and searched. And narrowed down to mySQL problem (probably)
    Ran mysqltuner and fixed all the settings it suggest
    Changed caching system settings (SUper Cache and W3 TOtal)
    Changed caching system (from Super Cache to W3)
    disabled and enabled most of my plugins (some did not because of high reputation and the mini size of them, like WP-PageNavi)

    What I think can fix but do not know how to start:
    I came across a topic called mySQL table locking. Some of the mySQL gurus said that when a WP post numbers are too large, it will lock the table and causing my site’s symptoms. But none suggest how to fix them for a database beginner like me… So here I am.

    I am also doubtful of my theme being the culprit. Since the problem only occurs when I am in the backend trying to post/edit something. Regardless of how busy/easy my site was at the time.

    My site is https://www.theborneopost.com

    My Plugins (as of now):
    Jetpack
    W3 Total Cache
    WP-PageNavi
    Disqus
    Contact Form 7
    Print Friendly and PDF
    Use Google Libraries
    WP-Optimize

    Server Specs:
    2Mbit with 1TB CDN
    8GB RAM
    quadcore Xeon
    1TB RAID 0 HDD
    PHP5, MSQL 5
    fastCGI

    Most, if not all, the plugins are installed after I noticed the problem (since 2 years ago). So They are probably not related to it…

    I would really, really appreciate any help to this (haunted me for 2 years+…). If anyone need more info, please leave a reply here. Thank you all, in advance.

Viewing 14 replies - 1 through 14 (of 14 total)
  • jack randall

    (@theotherlebowski)

    i’m going to go out on a limb here and suggest you archive a bunch of those posts and also think about getting more bandwidth! 70,000 posts is a hell of a lot of information for your server to wade through…

    Thread Starter nders

    (@nders)

    Hi Lebowski, surprisingly, with W3 Total Cache’s awesome caching techniques and CDN, my bandwidth constantly only stay on 30-90% consumption. And I am very sure bandwidth are not related to my issues.

    About archiving, is there a specific WordPress function that I can use to seperate the large post tables but still accessible to the public (we are a news portal, data must be visible to the public at all times) ? I’d like to know some if possible.

    That said, I am still gladful of your time and suggestion. thank you.

    Your site checks well at several online tools, the post write time may be due to the CDN ???

    jack randall

    (@theotherlebowski)

    maybe set up a site in a subdirectory and have posts older than a certain date on there… have the archive.php files on the sub-site set up to list the titles and dates of previous articles so it’s like a repository…

    Thread Starter nders

    (@nders)

    Hi, Seacoast Web Design

    This problem occurs way before (2 years) I had CDN (a year go). So CDN is not the culprit. Thank you for checking my site with online tools with me. Gives me some confidence ??

    Hi Lebowski,
    Will certainly look into that. Thank you. But since categorizing posts did not seperate the posts table (and had nothing to do with publishing/editing posts) I am still doubtful it will solve my problems. It is a good way to organise my site, though.

    It is not ‘nothing’, it is ‘something’ :

    to properly troubleshoot, rule out ‘nothing’ ??

    @nders: have you deleted post/page revisions in the database? I’ve dropped DBs from 500 megs down to 25 megs by cleaning revisions, with a huge increase in speed and a huge decrease in mysql load. If your posts table is hundreds of megs, delete revisions. Make a DB backup (it will be way too large to import in phpmyadmin if need be, so learn how to import the backup on the mysql command line) and then run this query in phpmyadmin:

    DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

    Change DB table prefix if needed in the query above.

    FYI, CDNs and caching do nothing on the admin side. Separating posts by category will do nothing; they’re still in the same DB and in the same table.

    Thread Starter nders

    (@nders)

    Hi Seacoast Web Design,

    I’ve tried posting in a completely empty category, and the problem persists. So I think even if I categorize all 70k of my posts into a category called “archives”, I will still experience the problem when I’m posting into the already-empty categories.

    Is my understanding correct? thanks ??

    Any theme or plugin can mod what happens on POST. Watch what happens on POST. With such a site, hire someone to, if you do not know how to.

    Thread Starter nders

    (@nders)

    I am the one that is hired. sadly ??

    I was a web designer, not developer, but my company can’t find any mySQL expert to hire in this little town. so I had to pick up.

    btw songdogtech, yes I deleted revisions using WP-Optimize. but my database size is still enormous due to the sheer amount of posts.

    I am sure there’s a way to fix it, since huge sites like mashable use wordpress/W3 Total Cache, and AllthingsD using wordpress too. just that I am coming from a creative background, can’t really do much about thoose mysql stuff ??

    I have the same problem my site locks up and I get the error estblishing database message, I have almost 13k posts

    @profootballzone,
    Instead of posting on this 8-month-old thread, please start your own topic.
    https://www.ads-software.com/support/forum/how-to-and-troubleshooting#postform

    o I didn’t think duplicate topics were OK

    Some forums work that way, but here, not usually because it’s unlikely that your issue is actually the same. Hence the forum welcome:

    “Unless you are using the same version of WordPress on the same physical server hosted by the same hosts with the same plugins, theme & configurations as the original poster, do not post in someone else’s thread. Start your own topic. “

    It also helps the forum search work better when threads are a single person and topic.

Viewing 14 replies - 1 through 14 (of 14 total)
  • The topic ‘70,000 posts WP site lags when posting/editing’ is closed to new replies.