High Server loads and WorpPress

AndyII

Verified User
Joined
Oct 3, 2006
Messages
576
a while ago I was having issues with high server loads, well at the end I discover that WordPress and or the plug-ins that go with it can cause the server to choke, I have done much research on the WP troubles and there are many, but in particular I see a plug-in called autoblogg (there are a few types of these out there),
what happens if a site is doing a "remove Duplicate" action, it will cause a heavy load on mysql and then Apache as well, if more than one fire off well the trouble compounds. I have the load monitor script watching the server load and I know its a "band-aid" fix but it will restart Apache, which in turns kills the mysql connections, most of the time it will hold its own and recover the server, I dont really like using this tactic but untill I can resolve the WP troubles, it will have to do.
I know what you wil say, "KICK OFF WordPress" but I have a good paying customer with probably 20 WP sites so I want to find a better answer, none of the Cache plug-ins work, I even tweaked my.cfg to better handle the requests from the DA knowledge base and also some tips from the author of WP.
Made the site owner hire a programmer to look at getting them under control, so far he hasnt come up with any resolution and perhaps the client needs to hire a WP guru instead.
just fo ha ha's I will add the database query so you can see what is going on, now this may not be the culprit, just the result of something else.
This trouble is world wide and if someone could find a resolve to the WP dilemma, it may be profitable.
By the author's own admission, WP is a coding nightmare, more time was put into the looks section and the admin area. (not a direct quote)

What am I asking? well 1st is just a possible "headsup" for server owners, your not being DDOS'ed just some scripting trouble,
then perhaps someone here has some tips, pointers experience on how to leash the WP problems. Don't want to "Ban" WP from the servers, it is a well known, widely used Blog script and there are hundreds of nice plug-ins available to use, most are free.
Here is the DB action that is causing the trouble (I think), the longer this hangs, the worse the load.
(This "taxonomy" is an integral part of WP, I believe )

Code:
Removing duplicates SELECT DISTINCT p.ID, p.post_title, p.post_date, p.comment_count, count( t_r.object_id ) AS cnt
FROM wp_term_taxonomy t_t, wp_term_relationships t_r, wp_posts p
WHERE t_t.taxonomy = 'post_tag'
AND t_t.term_taxonomy_id = t_r.term_taxonomy_id
AND t_r.object_id = p.ID
AND (
t_t.term_id
IN (
'28940', '409', '11136', '2471', '16584', '11027', '11192', '28939', '28938', '2034', ''
)
)
AND p.ID !=52880
AND p.post_status = 'publish'
AND p.post_date_gmt < '2009-12-28 02:05:14'
GROUP BY t_r.object_id
ORDER BY cnt DESC , p.post_date_gmt DESC 
LIMIT 10
 
I'm having problems with WP as well, and you said I cannot just ban WP from my servers because I would lose an important share of my clients. Some other open source applications like SMF forums have horrible SQL queries, and when it has some popularity (I mean just a few dozens of users online) it starts making troubles.

As far as WP goes, it is useful to talk your users to keep it up-to-date (in case developers improve queries) and ask them to configure cache plugins like WP Cache. This plugin improves a lot the performance of the blog, creating cache files and reducing significantly the amount of database queries.

It's also useful at some point to enable MySQL cache and use some PHP cache extension like eAccelerator. This probably won't be a solution, but it will just help not making things worse (it could also help to make your Apache processes as light as possible, removing modules that you don't use... i.e. mod_dav). Unfortunately most SQL queries on PHP/MySQL applications do not use prepared statements, which improve dramatically the performance when using query cache... but there isn't much we can do here.

About the query you posted... it's awful! do you know when is that one executed? I mean... when are duplicates removed? Is that something done on a regular basis?
 
I have tried the cache and the Super Cache, if they help, its not noticeable at least when it comes to the "Removing Duplicates"
I am not sure what invokes that query, I do know that a single site can submit that same query more than 10 times all at once.
And Yes, it runs all the time, least on his sites, when they are quick and efficient everything is ok, when they hang and start accumulating is when the problems arise
I have asked many questions and searched a great deal on this, but never came up with any conclusive answers.
Why it has to remove duplicates so many times, over and over and over again :confused:
I have a suspicion that its not all the plug-ins fault, but a core function of wordpress that is utilized excessively .
I watch the processes and top, I see high CPU usage in the 60s, go to mysql processes and see a site doing the remove dups and sometimes hanging on for extended periods of time, then if another site, or sites start doing the same query, mysql will choke and stall the server, Apache will hang because it cant finish its job, and the mess escalates to bye bye server.
This is also random, I can have days with no word from server and then all hell breaks loose.
I am using a script that I got from Jeff that monitors server loads and when it gets to 5 it will restart httpd, send me notices on the warning and restarts.
Its just a protection system and not a cure.
A programmer told me if the second set of "AND" were changed to "OR" statements it would run smoother :eek:

Wanted to add this, I did set a couple of his sites to limits in Mysql, set to 5000 per hour on all 3 areas and the sites will use exceed this, easy to tell because WP will go into an Install mode when it cant connect to a database
seems high to me but what do I know LOL
MAX QUERIES PER HOUR
MAX UPDATES PER HOUR
MAX CONNECTIONS PER HOUR
 
Last edited:
I did a search on one of my WP blogs for that SELECT DISTINCT and the query does not appear to be in the core (I don't have that query on my blog).
So I think it might be one of the plugins.

I'd suggest you go to the folder of one of the problematic blogs and do this:

Code:
cd wp-content/plugins/
grep -ir "SELECT DISTINCT" --color *

That will output all the PHP files that have the string "SELECT DISTINCT". If you find one that could be part of the query that is giving you trouble, you can check it out using less command. If that's the query, you can try disabling the plugin that it belongs to and see if the problem persists.
 
Alright, searched a couple sites found a plug-in called "Related Posts", which has the SELECT DISTINCT
Wrote the owner to have him disable all sites with this plug-in for a while, see what happens.
Thanks
be nice to find a simple solution, that plug-in is a single file plug-in so perhaps it can be easily modded to perform better
 
wanted to note that disabling "Related Posts" solved the Mysql problem
guess it dont want to play nice with others :D
just dealing with Apache now and seems the feeds that people pull can also cause some discomfort :p
maybe putting them on a cron so they dont all fire around the same time will help
found a little plug-in called utopia-cron that will schedule any action needed, but the wp.cron.php needs a little modification to stop it from auto firing . I will give it a try and see if it helps
 
Back
Top