Support » Networking WordPress » Struggling to getting the best performance out of my WordPress news site

  • Hello Everyone

    We are a news site running a WordPress multisite install.
    We get around 1.2 million users a month. We have experience 400% growth in the past 6 months.
    We have 5 editors loading up to 250 posts a day.
    Our database at the moment is sitting at 470.8 MB.

    We have 57288 posts and 14,777 Comments, about 10 custom post types and two more taxonomies besides categories.

    Our problem has been slow queries which causes the database to stop responding and the site subsequently not responding.
    We implemented ‘no_found_rows’ => true and transient in our queries to get rid some of the slow queries.

    However, slow queries continue to be a problem.

    We installed Query Monitor and realised that even WordPress core queries like the default query that load the archives and categories below are running slow:

    SELECT SQL_CALC_FOUND_ROWS wp_18_posts.ID
    FROM wp_18_posts
    INNER JOIN wp_18_term_relationships
    ON (wp_18_posts.ID = wp_18_term_relationships.object_id)
    WHERE 1=1
    AND ( wp_18_term_relationships.term_taxonomy_id IN (35,117,144,145,147,148,151,153,434,435,436,437) )
    AND wp_18_posts.post_type IN ('post', 'afp_feed_article')
    AND (wp_18_posts.post_status = 'publish'
    OR wp_18_posts.post_status = 'private')
    GROUP BY wp_18_posts.ID
    ORDER BY wp_18_posts.post_date DESC
    LIMIT 0, 10

    Here are a few of the queries running slow:

    SELECT wp_posts.ID
    FROM wp_posts
    INNER JOIN wp_18_term_relationships
    ON (wp_posts.ID = wp_18_term_relationships.object_id)
    WHERE 1=1
    AND ( wp_18_term_relationships.term_taxonomy_id IN (161) )
    AND wp_posts.post_type IN ('post', 'afp_feed_article', 'page')
    AND (wp_posts.post_status = 'publish'
    OR wp_posts.post_status = 'private')
    GROUP BY wp_posts.ID
    ORDER BY wp_posts.post_date DESC
    LIMIT 0, 1
    SELECT wp_posts.ID
    FROM wp_posts
    INNER JOIN wp_18_term_relationships
    ON (wp_posts.ID = wp_18_term_relationships.object_id)
    WHERE 1=1
    AND ( wp_18_term_relationships.term_taxonomy_id IN (162) )
    AND wp_posts.post_type IN ('post', 'afp_feed_article', 'page')
    AND (wp_posts.post_status = 'publish'
    OR wp_posts.post_status = 'private')
    GROUP BY wp_posts.ID
    ORDER BY wp_posts.post_date DESC
    LIMIT 0, 4
    
    SELECT wp_posts.ID
    FROM wp_posts
    WHERE 1=1
    AND wp_posts.ID NOT IN (101572,130445,103158,130463,129903,112440)
    AND wp_posts.post_type IN ('post', 'afp_feed_article')
    AND (wp_posts.post_status = 'publish'
    OR wp_posts.post_status = 'private')
    ORDER BY wp_posts.post_date DESC
    LIMIT 0, 9
    SELECT wp_posts.ID
    FROM wp_posts
    INNER JOIN wp_18_term_relationships
    ON (wp_posts.ID = wp_18_term_relationships.object_id)
    WHERE 1=1
    AND ( wp_18_term_relationships.term_taxonomy_id IN (189) )
    AND wp_posts.post_type IN ('post', 'afp_feed_article', 'page')
    AND (wp_posts.post_status = 'publish'
    OR wp_posts.post_status = 'private')
    GROUP BY wp_posts.ID
    ORDER BY wp_posts.post_date DESC
    LIMIT 0, 4
    
    SELECT wp_posts.ID
    FROM wp_posts
    INNER JOIN wp_18_term_relationships
    ON (wp_posts.ID = wp_18_term_relationships.object_id)
    WHERE 1=1
    AND ( wp_18_term_relationships.term_taxonomy_id IN (174) )
    AND wp_posts.post_type IN ('post', 'afp_feed_article', 'page')
    AND (wp_posts.post_status = 'publish'
    OR wp_posts.post_status = 'private')
    GROUP BY wp_posts.ID
    ORDER BY wp_posts.post_date DESC
    LIMIT 0, 4
    
    SELECT wp_posts.ID
    FROM wp_posts
    INNER JOIN wp_18_term_relationships
    ON (wp_posts.ID = wp_18_term_relationships.object_id)
    WHERE 1=1
    AND ( wp_18_term_relationships.term_taxonomy_id IN (186) )
    AND wp_posts.post_type IN ('post', 'afp_feed_article', 'page')
    AND (wp_posts.post_status = 'publish'
    OR wp_posts.post_status = 'private')
    GROUP BY wp_posts.ID
    ORDER BY wp_posts.post_date DESC
    LIMIT 0, 4

    We really tried beefing up our server infrastructure. Our environment at the moment looks like this:

    – Two Database servers replicating– one for logged in users (read and write), one for visitors – read only.
    – Two load balancers
    – 4 Web servers
    – Varnish cache
    – We are not using W3Total Cache
    Software: Ubuntu, Lampstack (Apache, PHP, MySQL), Nginx

    14 plugins:
    Better WordPress reCAPTCHA
    6 whose function are invoked by plugins
    Mobile detection and switcher
    Image Watermark
    Regenerate Thumbnails
    Video Thumbnails
    UberMenu 2 – WordPress Mega Menu Plugin
    WordPress SEO
    WordPress SEO News
    WP-DBManager
    Akismet
    Gravity Forms
    Gravity Forms Polls Add-On
    Gravity Forms User Registration Add-On
    WP Clean Up

    Had WP Smush.it but uninstalled it because it was making the dashboard slow.

    The question is: Is WordPress ideal for a site our size and the size growing up so rapidly(in terms of size and visitors)? Is there a limit as to the size of a size that WordPress can run optimally?
    If is WordPress is ideal for this kind of a site, how to I set up my environment(infrusture) and my WordPress installation to get the best performance in terms of speed and faster queries.
    Some people have suggested changing hosts and switching to dedicated WordPress hosting like WP-Engine, siteground or flywheel.

    What do I do to solve the slow queries and how do I set up my environment to get the best performance?

Viewing 5 replies - 1 through 5 (of 5 total)
  • Hi Kirsten-SA dev,

    wow its great news to hear your news site is growing, thats so awesome.

    You mentioned you are not using W3Total Cache, but you are using a load balancer (thats cool), try experimenting with a good caching plugin, one of my sites gets about 10 thousand hits a month, with about 10 new users a month and i had to install a caching plugin to get it down to a reasonable page load time. However it sounds like you should upgrade your host (1.2 mill users wow), a good dedicated WordPress host will handle caching for you and when they do it it works realllly well, the reason i suggest upgrading is because WordPress is more than capable of handling well over 1 million users and visitors, and your site growth is a peace of cake for WordPress, and it is more than likely your server set up, I am not a IT network engineer so i cannot say for certain that your set is ‘bad’, it looks to me like a ‘bad ass’ set up, However, they call it IT network engineering for a reason 😛 and your site growth sounds like its time to get serious cause your headed for the major leagues.

    I use wp-engine, and i really love those guys, i went with them because i wanted to retain control over customization (im trying to get into development), But if i was hired to set up a website for a news paper client with 1 million+ visitors/users, i would try and get them to use WordPress.com, and since you sound like you already know how to use WordPress, the guys over at Automattic and WordPress.com would get you set up so quickly, Times magazine uses WordPress.com and so does a bunch of other high profile magazines, all of which have HUGE traffic and probably HEAPS of authors posting tons of articles. WordPress.com doesnt limit you in any way technically, its exactly the same as WordPress.org, they even have e-commerce upgrades, but you can only modify CSS (not core files), you have to pay for every single extra upgrade like domian name, modify CSS, e-commerce, and you can only use their Free or Premium themes (i.e you can’t install one from theme forest, but you can get one specially made for wordpress.com i think) etc… If you are serious about upgrades and you just need WordPress to work, i’d go with WordPress.com, you pay them and they will deliver, wp-engine are great, but their business model is, Here is a WordPress install on a beefed up server, go nuts, but that means you still have to set it up properly and you can modify any code you want, they also handle server load, caching, backups etc.. its pretty good but you have to do more work, so going with WordPress.com sounds like a good idea for a magazine of your size (even as a manager or dev with many users i bet you have your hands full, plus you will probably be required to make CSS modifcations etc), who can’t afford to waste time. i encourage you to go and checkout WordPress.com, but also to send their customer service team an email asking them some questions, i bet you will find they will pretty much just copy your existing site and you will be ready to go.

    You haven’t mentioned any caching. I suppose you don’t have one. Even your tramsients are stored in db and read every time a request hits php.

    1. Upgrade PHP to 5.5 with OPcache
    2. Install Memcached and php5-memcached extension
    2.1 Possibly, configure more than 1 Memcached servers
    3. Install Memcached Redux plugin

    This will dramatically improve performance and reduce the number of queries. Actually, your read-only db will have some rest as all incoming selects will be cached by Memcached. When properly configured, read-only requests NEVER hit the database at all.

    Going further. What is the point of using Varnish, Apache, Nginx in one setup? Please, choose 2 of them. Varnish as proxy / balancer, Nginx + PHP-FPM as a backend or even Nginx only. It has fastcgi_cache, it can talk directly to Memcached (without even touching a php-fpm process). Why you still use Apache here? What is the purpose?

    Also, consider switching from plain-ol’ MySQL to Percona or MariaDB. I prefer MariaDB 10, it’s faster and better than MySQL.

    That’s the first level of optimization. Later you’ll need some cache pre-population, tempopary tables etc.

    P.S. I’m running a WP news site with 3.5M views per month on a single VPS with 2Gb of RAM and 2 CPU cores.

    I’d try the following plugins:

    http://www.websharks-inc.com/product/quick-cache/ (As far as I know this is the only multisite compatible caching plugin)
    https://premium.wpmudev.org/project/multi-db/
    https://wordpress.org/plugins/ewww-image-optimizer/
    https://wordpress.org/plugins/p3-profiler/

    You might want to post this question on the WPMUDEV forums (paid forums). They have quite a lot of experience with WordPress multisites.

    Moderator Ipstenu (Mika Epstein)

    (@ipstenu)

    🏳️‍🌈 Advisor and Activist

    Just to point something out, WordPress.COM is a multisite, so … yeah, this can totally be done. But it comes at a hardware cost.

    PHP 5.5 (or anything with the opcode cache). Memcache. Nginx. Maybe SPDY and Varnish.

    I do nginx as a proxy, since I love Apache and .htaccess 😉

    I am interested in this topic but cannot contribute towards a solution. We have a very large WP system, but it is large in a different way than yours.

    What I would find disturbing are the slow database queries. Caching at any level will only mask that problem; and eventually as your site grows larger the queries will get slower. I would implement some of the above caching options people have mentioned. At the same time I would have someone look at the database queries to analyze why they are slow: Is it a WP core problem? Is it the way you configured WP? Are plugins causing problems? Is your database server powerful enough? etc

    FYI, our system only implements WP Super cache and no other caching. We have 300 sites. Our biggest site has about 10k posts, but no comments. Our database is 2GB.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Struggling to getting the best performance out of my WordPress news site’ is closed to new replies.