> Not to start a war or anything, but I think David's right. Ever since 1.5 WP > has become notorious for its rather in-efficient database. I'm NOT (let me > repeat: NOT) a database expert and all I know about MySQL is just how to > create a database that does the job. But I think there is something missing > from WP's database performance.
> When you have forums like phpBB and IPB that can take up to 100 or more > times the load as WP _without caching_ and they serve quite a lot more > queries, I think that means something is wrong.
> IMO, WP being inefficient at DB-querying is something that already happened, > and unless someone is volunteering to completely redo the database with full > optimizations and proper schemas taking advantage of all of MySQL's > (admittedly few) SQL-Side power tools, there's nothing to be done. But > ignoring these features for future changes and feature-sets is another > story...
He used to have a fairly attractive and heavily-trafficked WP site but when push came to shove, WP couldn't handle the load. MT's static publishing might be a pain in the butt and have its own downsides, but MT-run sites run circles around WP sites when it comes to a digging, /.'ing, Penny Arcade "wanging" or an Instalanche. I can't tell you how many frickin' times I've seen that WP "Error reaching the database" error screen on blog posts carelessly dugg. I loathe Diggmirror, but such circumstances seemingly leave regular readers with no choice.
So a hearty +1DAM, +20 to hit on doing whatever we have to do to make the mysql portion of WP work better.
Besides the DB, the entire frontend is also just as inefficient. Take a look at http://lightpress.org/
I've launched several clients with LightPress, because I was custom-coding their entire site anyway. But for the average user, that's just not an option.
LightPress is a total revamp of the theming system and the GUI. It's installed as a plugin on top of WP, and is _NOT_ a fork of WP. Unfortunately, development there is (understandably) slow.
My point in all this isn't that WP sucks or that you should use Lightpress along with it, but rather that from the very bottom (the database) to the very top (the frontend), WP is inefficient. Very inefficient.
With WP 1.5 that wasn't a problem. *THEN* it was "just a blogging platform," but now, WP is much, much more. The sad thing is, WP is getting too large to rewrite. Because a rewrite is the only thing that can fix it. And a rewrite at this point would kill compatibility.
Ideally, we'd say 'screw compatibility,' release 2.1, take a 6-8 month break from coding new features and in this time re-create the database taking advantage of all MySQL-specific optimizations, bump up the MySQL minimum version and take advantage of all the new performance features offered, and then re-implement LightPress as a part of the core. The actual WP code is marvelously well-written, clear, and fairly concise compared to other projects of its size - only the frontend needs to be redone per the LightPress specifications.
But that's not going to happen. I don't see why not, Firefox was rewritten from scratch several times (not that it helped, Fx's code is absolutely ghastly and leaks memory more than Hoover Dam has water) - and that was a MUCH bigger project than WP. The only thing is, Firefox was rewritten to improve the quality of the code, and not the performance. i.e. the rewritten Firefox was kept 100% backwards compatible during the rewrite. However, for WP, that would require the breaking of quite a few plugins just to make it even slightly faster.
No matter how you look at it, WP is horribly inefficient and now it's definitely big enough that this warrants a second look. We need to do something, the only question is, what?
As far as the DB goes, I feel we can (if we wanted to) rewrite it without breaking compatibility, since we just have to keep the functions the same. Officially, WP doesn't support plugins or themes that grab info from the database themselves - that's what the hooks are for, so we don't have to worry about breaking those kind of things.
But no DB-rewrite is complete without rewritten functions (the LightPress bit), and that's a bit of a conundrum. After all, WP's biggest advantage is the millions of plugins and themes and the huge community that builds them - not something to be taken lightly or trifled with in this manner.
.... I hate endings like this.. Please, someone, think of something!
> -----Original Message----- > From: wp-hackers-boun...@lists.automattic.com [mailto:wp-hackers- > boun...@lists.automattic.com] On Behalf Of Doug Stewart > Sent: Thursday, November 30, 2006 8:40 PM > To: wp-hack...@lists.automattic.com > Subject: Re: [wp-hackers] Re: Changing MySQL minimum version
> On 11/30/06, Computer Guru <computerg...@neosmart.net> wrote: > > Not to start a war or anything, but I think David's right. Ever since > 1.5 WP > > has become notorious for its rather in-efficient database. I'm NOT > (let me > > repeat: NOT) a database expert and all I know about MySQL is just how > to > > create a database that does the job. But I think there is something > missing > > from WP's database performance.
> > When you have forums like phpBB and IPB that can take up to 100 or > more > > times the load as WP _without caching_ and they serve quite a lot > more > > queries, I think that means something is wrong.
> > IMO, WP being inefficient at DB-querying is something that already > happened, > > and unless someone is volunteering to completely redo the database > with full > > optimizations and proper schemas taking advantage of all of MySQL's > > (admittedly few) SQL-Side power tools, there's nothing to be done. > But > > ignoring these features for future changes and feature-sets is > another > > story...
> Amen to all of that. Take, for instance, Flopping Ace's recent > disparaging of WordPress due to the fact that his WP site was taken > down by traffic generated by the bogus "6 burned Sunnis" story: > http://floppingaces2.blogspot.com/2006/11/msm-patting-themselves-on- > back.html
> He used to have a fairly attractive and heavily-trafficked WP site but > when push came to shove, WP couldn't handle the load. MT's static > publishing might be a pain in the butt and have its own downsides, but > MT-run sites run circles around WP sites when it comes to a digging, > /.'ing, Penny Arcade "wanging" or an Instalanche. I can't tell you > how many frickin' times I've seen that WP "Error reaching the > database" error screen on blog posts carelessly dugg. I loathe > Diggmirror, but such circumstances seemingly leave regular readers > with no choice.
> So a hearty +1DAM, +20 to hit on doing whatever we have to do to make > the mysql portion of WP work better.
On 11/30/06, Computer Guru <computerg...@neosmart.net> wrote:
> No matter how you look at it, WP is horribly inefficient and now it's > definitely big enough that this warrants a second look. We need to do > something, the only question is, what?
I'm not saying you're wrong, but could you quantify this inefficiency? In other words, how are you measuring it especially in comparison with other platforms? _______________________________________________ wp-hackers mailing list wp-hack...@lists.automattic.com http://lists.automattic.com/mailman/listinfo/wp-hackers
> On 11/30/06, Computer Guru <computerg...@neosmart.net> wrote: > > No matter how you look at it, WP is horribly inefficient and now it's > > definitely big enough that this warrants a second look. We need to do > > something, the only question is, what?
> I'm not saying you're wrong, but could you quantify this inefficiency? > In other words, how are you measuring it especially in comparison > with other platforms?
No problem. My site has a blog (WordPress), a gallery (Gallery2), and a forum (hacked MyTopix). Each has been dugg and slashdotted several times. It used to be that Gallery2 was the weak point, it would kill my server the minute it made even OSNews, never mind Slashdot. The Menalto Gallery2 team worked really hard and fixed that up.
The forums were simultaneously dugg and slashdotted - they upheld wonderfully, and the server only slowed down a bit.
The blog - once it gets a whiff of Slashdot or Digg without WP-Cache installed (I *STILL* can't get it working on IIS and the author isn't really helpful) my server collapses. I have eAccelerator installed, I gzip content, and I have IIS tuned to the hilt (much faster than Apache :P)
Flash Back a couple of months
WP + WP-Cache goes down much faster than the forums - no eAccelerator.
Gallery2 has some kind of very inefficient caching taking place, the forums have no caching, and WP used to have WP-Cache.
The gallery & forums make a ton of more (and more complex) queries to the database - and forums are especially heinous because of how often the content changes. Yet something like WP gives me hell. And I'm not alone.
I removed all my plugins and installed LightPress for 24 hours - the effect was instantaneous bliss. Still no caching, but all of a sudden not only did my server not die on me every 5 minutes, but pages also loaded fast and snappily.
On 11/30/06, Austin Matzko <if.webs...@gmail.com> wrote:
> On 11/30/06, Computer Guru <computerg...@neosmart.net> wrote: > > No matter how you look at it, WP is horribly inefficient and now it's > > definitely big enough that this warrants a second look. We need to do > > something, the only question is, what?
> I'm not saying you're wrong, but could you quantify this inefficiency? > In other words, how are you measuring it especially in comparison > with other platforms?
WordPress performance under load vs. Movable Type (static publishing) performance under load. MT whips WP, but it's actually kind of a "cheat" - the entries themselves are statically generated and stored. Any dynamic elements (comments, plug-ins, etc.) are then generated on-the-fly.
As I stated above, a good metric is the consummate *thrashing* out-of-the-box WP sites take when dugg/slashdotted/etc.
My 2c. I have been seriously in the throes of the MT/WP performance issues for months now. Neither platform really has it nailed. In MT if you go to static publishing, which is faster for the end user, the editorial experience is impossibly slow. I finally found and used an undocumented page caching scheme (built into MT!) to help the overall scene. Nevertheless, we have bitten the bullet and moved to WP.
On WP, out of the box there are some expensive queries that are significantly remedied simply by adding indexes to certain tables. We run one installation under eAccelerator, and one under phpAccelerator. And after the database optimization, I still plan to install a hard page caching scheme, since the queries for paged category archives are the only slow queries that remain. (FWIW, WP- Cache does not behave well in either install.)
> On 11/30/06, Austin Matzko <if.webs...@gmail.com> wrote: >> On 11/30/06, Computer Guru <computerg...@neosmart.net> wrote: >> > No matter how you look at it, WP is horribly inefficient and now >> it's >> > definitely big enough that this warrants a second look. We need >> to do >> > something, the only question is, what?
>> I'm not saying you're wrong, but could you quantify this >> inefficiency? >> In other words, how are you measuring it especially in comparison >> with other platforms?
> WordPress performance under load vs. Movable Type (static publishing) > performance under load. MT whips WP, but it's actually kind of a > "cheat" - the entries themselves are statically generated and stored. > Any dynamic elements (comments, plug-ins, etc.) are then generated > on-the-fly.
> As I stated above, a good metric is the consummate *thrashing* > out-of-the-box WP sites take when dugg/slashdotted/etc.
Computer Guru wrote: > I removed all my plugins and installed LightPress for 24 hours - the effect > was instantaneous bliss. Still no caching, but all of a sudden not only did > my server not die on me every 5 minutes, but pages also loaded fast and > snappily.
Well we need to determine what makes lightpress so much faster. Is it the queries, the templates, the cache? Is it bypassing expensive filters such as texturize and autop? It's actually quite a bit of extra code, so what is speeding it up? A little thing I see in lightpress that I would like for WP is plugin contexts.
Maybe someone using lightpress could dump their queries and query times for comparison.
Komra Moriko wrote: > On WP, out of the box there are some expensive queries that are > significantly remedied simply by adding indexes to certain tables. We > run one installation under eAccelerator, and one under phpAccelerator. > And after the database optimization, I still plan to install a hard page > caching scheme, since the queries for paged category archives are the > only slow queries that remain. (FWIW, WP-Cache does not behave well in > either install.)
What indexes did you add? We added a few more in 2.1, but if there are still more we should add do let us know.
Also, are we talking about 2.0.x or 2.1 here? We've done some query optimization in 2.1, but we could use still more.
These are the ones I added, made a huge difference in performance -- we have 17000 posts and fairly heavy traffic. I checked my slow query log, and used EXPLAIN to examine what was going on and added indexes for columns referenced in where statements:
CREATE INDEX cat_name ON wp_categories (cat_name); CREATE INDEX post_date ON wp_posts (post_date); CREATE INDEX post_date_gmt ON wp_posts (post_date_gmt); CREATE INDEX post_status ON wp_posts (post_status);
Additionally, I have hacked the WP admin interface to make it more usable, adding more metadata to to the list of posts, and adding a browse category drop down and a separate Drafts page. Don't know if these can be incorporated into the new version, or if other UI optimizations are already taking place...
> Komra Moriko wrote: >> On WP, out of the box there are some expensive queries that are >> significantly remedied simply by adding indexes to certain tables. >> We run one installation under eAccelerator, and one under >> phpAccelerator. And after the database optimization, I still plan >> to install a hard page caching scheme, since the queries for paged >> category archives are the only slow queries that remain. (FWIW, WP- >> Cache does not behave well in either install.)
> What indexes did you add? We added a few more in 2.1, but if there > are still more we should add do let us know.
> Also, are we talking about 2.0.x or 2.1 here? We've done some > query optimization in 2.1, but we could use still more.
That said, let's not loose sight that maybe something like wp-cache/Staticize needs to be adopted into the core (even if it's as a plugin still). Nothing beats the performance of fully-static HTML, but mostly-static php can get close. ;)
Maybe that should branch off into another thread... ;)
----- Original Message ----- From: "Doug Stewart" <zamo...@gmail.com> To: <wp-hack...@lists.automattic.com> Sent: Thursday, November 30, 2006 1:40 PM Subject: Re: [wp-hackers] Re: Changing MySQL minimum version
> He used to have a fairly attractive and heavily-trafficked WP site but > when push came to shove, WP couldn't handle the load. MT's static > publishing might be a pain in the butt and have its own downsides, but > MT-run sites run circles around WP sites when it comes to a digging, > /.'ing, Penny Arcade "wanging" or an Instalanche. I can't tell you > how many frickin' times I've seen that WP "Error reaching the > database" error screen on blog posts carelessly dugg. I loathe > Diggmirror, but such circumstances seemingly leave regular readers > with no choice.
> So a hearty +1DAM, +20 to hit on doing whatever we have to do to make > the mysql portion of WP work better.
On 11/29/06, Matt Mullenweg <m...@mullenweg.com> wrote:
> Sub-queries are pretty rough in 4.1, they're really not fast until > 5.0/5.1. It's just blog software, we don't really need anything that fancy.
Trying to get back to the point (this thread isn't about Wordpress being slow, or about moving up to PHP 5) I'm wondering what this comment from Matt means.
It sounds like the support for 4.1 isn't really that much different from 4.0, but I still like the idea of having sub-query support, and I'm listening for any reasons as to why choosing 4.0 would be any better. If it's a simple "the lower the version number the better" kind of answer, that's cool.
I agree with Viper007bond, though - isn't having a slower than ideal implementation of sub-queries better than none?
> I agree with Viper007bond, though - isn't having a slower than ideal > implementation of sub-queries better than none?
It depends upon how many sites you eliminate from the user base because the generic/popular hosts have only updated to the last release of 4.0. A worst case scenario would be that WP releases a version that requires 4.1 and quarter to half of the blogs out there attempt an upgrade that fails, all because one routine in a key area is the only routine in the entire core that uses sub-queries.
I haven't joined this conversation to any degree, because it seems that we are arguing from a vacuum. How can anyone answer whether or not it is safe to upgrade until someone provides proof that a roll out of 4.1 based WP is feasible. Nobody has asking questions like "Who are the top 20 must popular webhosts and what version of MySQL are they running?". If everyone else has upgraded and 1and1.com is the exception, then perhaps it should be considered even if 1and1 is as big as they say they are. I doubt 1and1 is the sole exception.
> Is there a compelling reason to choose 4.0 over 4.1?
Not, in my mind, for WP 2.1. Even if a sub-query allows some whiz bang feature that could be thrown in at the last minute, the cost of all of the sites that cannot upgrade, I suspect, would be too great. And it's getting pretty late in the game to start rewriting stuff to wrap it in "if version < 4.1" statements.
If a move is gonna be made, I think it should the decision should be made before the design takes place. Or, at least, before the majority of the coding for the release takes place. There certainly are a lot of things that subqueries allow. The best way to take advantage of that is to design any new features around having that ability. That is so much better than retrofitting.
Anyway, that's my 2 cents...
_______________________________________________ Brian Layman www.TheCodeCave.com
On 11/30/06, Brian Layman <Br...@thecodecave.com> wrote:
> I haven't joined this conversation to any degree, because it seems that we > are arguing from a vacuum. How can anyone answer whether or not it is safe > to upgrade until someone provides proof that a roll out of 4.1 based WP is > feasible. Nobody has asking questions like "Who are the top 20 must popular > webhosts and what version of MySQL are they running?". If everyone else has > upgraded and 1and1.com is the exception, then perhaps it should be > considered even if 1and1 is as big as they say they are. I doubt 1and1 is > the sole exception.
I don't know how to determine the most popular hosts, but Wordpress.org recommends [1] the following hosts, which according to their sites use the listed version of MySQL:
By the way, I have a number of sites on a budget host that still uses 3.23, so we're going to cause a disruption to some people anyways.
> > Is there a compelling reason to choose 4.0 over 4.1? > Not, in my mind, for WP 2.1. Even if a sub-query allows some whiz bang > feature that could be thrown in at the last minute, the cost of all of the > sites that cannot upgrade, I suspect, would be too great. And it's getting > pretty late in the game to start rewriting stuff to wrap it in "if version < > 4.1" statements.
Aren't there advantages to announcing the minimum version *before* including features that require it? It would give people time to make the db change before errors start appearing.
On Thu, 30 Nov 2006, Komra Moriko wrote: > CREATE INDEX cat_name ON wp_categories (cat_name); > CREATE INDEX post_date ON wp_posts (post_date); > CREATE INDEX post_date_gmt ON wp_posts (post_date_gmt); > CREATE INDEX post_status ON wp_posts (post_status);
Could take another look at this one as well, although i see it was recently bumped to 2.2. But it has the potential for making a significant difference in some cases.
>> CREATE INDEX cat_name ON wp_categories (cat_name); >> CREATE INDEX post_date ON wp_posts (post_date); >> CREATE INDEX post_date_gmt ON wp_posts (post_date_gmt); >> CREATE INDEX post_status ON wp_posts (post_status);
> Could take another look at this one as well, although i see it was > recently bumped to 2.2. But it has the potential for making a > significant difference in some cases.
The get/set option API enforces unique option names, I believe, but I've seen some databases that had duplicate keys nonetheless. What happens when we make the key unique but have dupe names in the DB?
> These are the ones I added, made a huge difference in performance -- we > have 17000 posts and fairly heavy traffic. > I checked my slow query log, and used EXPLAIN to examine what was going > on and added indexes for columns referenced in where statements:
> CREATE INDEX cat_name ON wp_categories (cat_name); > CREATE INDEX post_date ON wp_posts (post_date); > CREATE INDEX post_date_gmt ON wp_posts (post_date_gmt); > CREATE INDEX post_status ON wp_posts (post_status);
I don't know if we need post_date_gmt. We need to audit our queries to see. I thought we used post_date when we needed to key.
As for cat_name, we only key off of that when retrieving all links or posts for a specific category specified by name. Most templates don't do that. I guess we can add a cat_name key for those few themes that do query by cat_name.
> Additionally, I have hacked the WP admin interface to make it more > usable, adding more metadata to to the list of posts, and adding a > browse category drop down and a separate Drafts page. Don't know if > these can be incorporated into the new version, or if other UI > optimizations are already taking place...
2.1 allows browsing by category on the Manage->Posts screen. There is talk of providing separate pages for drafts and private posts. That probably won't make 2.1 though.
> Announcing now, fits nicely with my argument to wait till version 2.2
for the new version requirement.
I should elaborate and say that it fits nicely because we can publicize that "2.1 will be the last version with MySQL 3.23 (or 4.0 whatever) support. Now is the time to upgrade to version 4.1 (or perhaps, even better, 5.0)."
-----Original Message----- From: Brian Layman [mailto:Br...@TheCodeCave.com] Sent: Thursday, November 30, 2006 8:42 PM To: 'wp-hack...@lists.automattic.com' Subject: RE: Re: [wp-hackers] Re: Changing MySQL minimum version
> Aren't there advantages to announcing the minimum version *before* > including features that require it? > It would give people time to make the db change before errors start
appearing.
Sure, but you don't announce it and cause people extra work when it is not needed. Annoucing now fits nicely with my argument to wait till version 2.2 for the new version requirement.
I've got a list of the top 100 webhosts according to google page rank. It's not a perfect list, but it has the all of the big boys I expected to see, everything form DreamHost to 50megs. That include all the ones you found on the site (Good job BTW).
If no one objects, for those that don't list it, I'll put out inquiries to their sales staff. I'll ask what MySQL version they provide with their most basic package that includes MySQL databases. This is very optomistic for a survey, but just maybe we can get 20-30 responses back in a reasonable amount of time.
> Aren't there advantages to announcing the minimum version *before*
including features that require it?
> It would give people time to make the db change before errors start
appearing.
Sure, but you don't announce it and cause people extra work when it is not needed. Annoucing now fits nicely with my argument to wait till version 2.2 for the new version requirement.
I've got a list of the top 100 webhosts according to google page rank. It's not a perfect list, but it has the all of the big boys I expected to see, everything form DreamHost to 50megs. That include all the ones you found on the site (Good job BTW).
If no one objects, for those that don't list it, I'll put out inquiries to their sales staff. I'll ask what MySQL version they provide with their most basic package that includes MySQL databases. This is very optomistic for a survey, but just maybe we can get 20-30 responses back in a reasonable amount of time.
Komra Moriko wrote: > CREATE INDEX cat_name ON wp_categories (cat_name); > CREATE INDEX post_date ON wp_posts (post_date); > CREATE INDEX post_date_gmt ON wp_posts (post_date_gmt); > CREATE INDEX post_status ON wp_posts (post_status);
Just a note for those following along at home, MySQL can only use one index for any given query. Even if an index exists, it will be ignored by the optimizer if there is a more primary one available, or depending on the table size it'll just do a scan. To keep it to the indicies you need to do multi-field ones, like we added in 2.1.
|
