To start with, are you running the wp-cache plugin yet?
No, I can’t get it loaded correctly (the symbolic links aren’t creating themselves, and I can’t figure out how to do it manually).
oh, but I did disable the internal cache as indicated elsewhere. (still on 2.0.3)
OK, I finally got WP-Cache working (just got shell access and have never been in there before, so it took some time to figure that out).
I checked the site and that process is still taking a long time to load, even with the cache enabled. It is now up to 9 sec now that more people are on the site.
I assume Cache should work immediately, and if so, then it is not affecting this process.
What next??
I’ve run Jerome’s Query Diagnostics (thank you SO much for that!), and there’s one process in particular that is taking a long time to load:
SELECT DISTINCT * FROM wp_posts…
That’s the main query. It retrieves your posts from the database in order to display them. You would expect that to take the most total time, because it’s the one most often run.
You’ll do better checking out the database, perhaps doing some EXPLAINs on the various queries you see, adding indexes where needed, etc.
Otto42 – Right, I just figured out what this script is — it is how WP pulls in the # of posts I told it to display on the front page.
The problem is it is saying to check all posts (<=) and then take the top 12. But we have over 8000 posts, and there is no need to read them all! No wonder it can take so long.
So is there a way around this? Or a reason WP is scripted this way? There’s just so many parameters in the process, that it is very inefficient.
I just want to prevent the site overloading the server again, like we had the last few days (with the worst being for approx 7 hours).
Ummm.. No, the query engine is not quite that dumb. That query is actually quite efficient, really. And pretty much the only way to do what it’s doing. 8000 rows is nothing. Databases are made to deal with millions of entries.
Please forgive my ignorance and frustration, but that is what it looked like it was doing, and someone else who is a db expert agreed.
I’m just trying to figure out why this script appears to be bottlenecking and therefore overloading the server at certain times. I’ve tried taking out plugins, outside sources (like google adsense), etc, and the server kept getting overloaded. This script was taking forever to load and seemed to be the culprit.
And as soon as I changed the front page to load by # of days instead of by # of posts, the time it took to process the script reduced dramatically.
So maybe this is all coincidence and circumstantial, but it was the likely culprit in all my work and research on my problem. For this particular site, it does not appear to be very efficient.
So if it *is* efficient, then why the problems and bottlenecking? And long time loads?
And the guy who has been helping me out disagrees that indexes will help. …..Every step I take I end up back where I started…..
Have a look at http://asymptomatic.net/2006/05/02/2327/high-performance-wordpress/, which links back to an article here on the forums. Might be useful reading.
Thanks, HandySolo. I found that yesterday, along with a couple of other similar posts, and I plan on reviewing it with my host to determine the best course of action.
Warning to others who are learning as you go….the learning curve jumps dramatically after a certain point 🙂
I thought I’d add that according to my host we’re on a server all by ourselves, so when we’re overloading the server, we’re overloading the entire server all by ourselves.
It coincided with the election (it is a political blog), but we do go down every now and then for 15 min at a time. Obviously something is wrong, and I just can’t figure out what it is.
Hopefully the cache will take care of the problem, otherwise I don’t know what else I can do (I don’t think the server upgrades are the issue here…at least according to my host).
Please forgive my ignorance and frustration, but that is what it looked like it was doing, and someone else who is a db expert agreed.
Tell your “db expert” that he needs to get more book-learnin’.
Yes, the query selects all posts and then reads the top 12. But the point is that it only SELECTS all posts. It only actually READS the top 12.
Generally speaking, the most expensive part of a database query, assuming it’s indexed properly and such, is the data transfer. Since you’re not actually transferring 8000 posts over the wire, but only 12, then yes, that’s actually quite efficient.
I can select all posts with a simple “select * from wp_posts”. That query runs in next to zero time. What makes a query take longer is not the amount of data that is being selected, it’s the operations being performed on that data.
Try doing an EXPLAIN on your query. With that, you can see how it’s optimizing the query, whether it’s doing sequential scan or indexed lookups, etc, etc.
I do this stuff daily. WordPress’s queries are extremely minimalistic. I commonly run queries in my own code which do JOINs on 11 tables and have 18 different parameters. These little piddly queries you’re talking about are nothing. You’ll get a much greater performance boost by a) caching pages and b) using a PHP optimizer like APC on your server.
Thanks, Otto42. I did EXPLAIN and didn’t know what I was looking at. What should I be looking for?
I also looked at the structure and it looks like there are indexes on the important fields within wp_posts. So that should be good, right?
I’m wondering if the problem we are having went away for some reason. I just got shell access, so I missed the outages, but I remember the host sending me a slice of a process script that was running. It seemed to match the above, except it was copying to tmp tables, which I was reminded of when I was just searching around on the support site for indexing mySQL.
I think all I can do now is just wait and see what happens.
