By greenc on

Hello,

I have a large-ish website and I'm having a helluva time dealing with performance. By largish I mean about 150,000 nodes (profiles). I had a long talk with a great guy at my web host and he agreed that the issue is neither memory allocation, table indexes, or bad modules. Although trowing some serious hardware at the problem will be needed in the future, the issue is a software problem.

After reviewing the site in question it seems that the site appears the be taking a lot of data from mysql and storing within the code, causing the high memory usage. We need to break up the queries or limit the amount of data Drupal pulls at one time.

Some URLs that are giving us problems are:

http://mysite.com/search/node/search-term - the search gives the WSOD

http://mysite.com/admin/content/node - Drupal tries to index 150,000 nodes and just can't handle it.

http://mysite.com/admin/content/taxonomy/48 - a couple million terms in this taxonomy. Taxonomy Manager does the trick by and large, but still I would like to see what other solution are out there.

http://mysite.com/admin/content/node-type/profile/fields/field_x - no idea why I get the WSOD for trying to configure a content type field in the field manager, but I'm getting the WSOD here too.

I'd also like to impliment some performance enhancing modules such as

http://drupal.org/project/boost
http://drupal.org/project/memcache
http://drupal.org/project/cacherouter
http://drupal.org/project/authcache

There is also a bit of data migration to do. Mainly moving data to CCK fields already stored in Drupal and integrating a few other fields from an external database.

I guess writing some intelligent table indexes should so the trick.

This is just a test site - no traffic so I'm really surprised by these issues. I guess writing some good database indexes wouldn't hurt either. I have been thinking about moving up to a very high performance server, but don't want to throw hardware at what right now seems to be a software issue.

If this is something you can do... email me. Immediate need for this project. We can start anytime. Looking for an experienced take the bull by the horns developer that can give us a firm estimate.

Thanks

Comments

vm’s picture

vm commented

I'd suggest contacting kbahey over at 2bits.com

roshan_shah’s picture

roshan_shah commented

I also second what Verymisunderstood recommended. Contact Khalid of 2bits.com

Roshan

greenc’s picture

greenc commented

Khalid must be very good to have his name raised and then seconded. I'll try that, but I'll remain open to other bids as well. I'm interested in getting this done well and done fast - hopefully a week or two.

The project has been gestating for a painfully long time due to the performance issues and I'm anxious to top it off.

Many thanks.

shenzhuxi’s picture

shenzhuxi commented

Are you using solr or just mysql for full-text search?

You need to know the bottle neck. Is it high traffic or too many register users or too large data to search?

http://drupal.org/project/boost
http://drupal.org/project/memcache
http://drupal.org/project/cacherouter
are static cache for traffic jam.

http://drupal.org/project/authcache is for dynamic content for register users.

Solr is for high perform search engine.

greenc’s picture

greenc commented

I was planning on using Solr for search, but right now the issue is just too much data for the standard Drupal framework / LAMP stack to handle. It just doesn't scale.

What I think is happening is this...

Say I want to look at all the nodes on the site and navigate here: http://mysite.com/admin/content/node

The site has 150,000 nodes.

I think what Drupal is doing is trying to read all 150,000 nodes first, store them in memory, and then after it has gone through all 150,000 serve a response. What we need is a way for Drupal to scale this by breaking apart the queries so that it, say, it reads 50 nodes, gives a response, then reads the next 50 if needed, gives a response, etc.

Just trying to enter http://mysite.com/admin/content/node maxes out my server's memory, causes the WSOD, and even has caused my whole network to go down by crashing the server.

Too much data, slow queries, inefficient calls, LAMP hard to scale is the crux of the issue.

smk-ka’s picture

smk-ka commented

Just a hint: I suppose it's not the 150K nodes that cause your issues, Drupal can normally handle these very well. However, you mentioned millions of taxonomy terms, and they're known to cause issues: on the content admin page, Drupal loads all of them into the category filter drop-down. I suppose you'll also hit a WSOD on the node/add/node-type page, if you'd display the standard taxonomy select. If my assumption turns out to be true, the real culprit here would be the core taxonomy module, which would also mean it cannot be fixed by just adding some table indexes (but needs some coding/hacking around).

-Stefan

greenc’s picture

greenc commented

Interesting... the big taxonomy has to do with location data. That is, we have a tree of every country , state (first-level division), and city in the world. We've built a custom ajax module that works quite well in the front end and we can use the taxonomy manager to administer it, but I didn't consider that it might be loading the entire taxonomy just to go to admin/content/node page. I guess this core element of Drupal could be recreated with Views to the same effect, maybe even with better functionality. Still, I have to admit my knowledge of optimizing and scaling Drupal is pretty limited. My areas of expertise are really translating vertical markets to CCK, SEO, branding, and finding profitable niches in the ocean of web markets. Done well at it, but moving from web 1.0 to 2.0 is proving quite a challenge. I've got some pretty spectacular failures under my belt over the last few years with proprietary platforms, hence the move to Drupal. The site in question got 150,000 members or so in just a couple months and then hit the wall. It's been idle now for about 2 years as I just can't seem to solve the performance issues. The Drupal site I'm building is attempt #3.

Thanks a lot for your input, by the way, its so nice when someone takes time out to give some aid and comfort to the weary.

I had no idea that about Drupal trying to read the entire taxonomy. If it tries to do it independently for each node (as in 150,000 nodes each trying to read 1,500,000 taxonomy terms that's... well, a big mess. That could be the issue. I'm going to delete the Taxonomy Select field and see what happens.

I could reconstruct the content type (which is just a content profile module node) without CCK using the core Drupal 6x way (checking the box for content types on the taxonomy page). I'd have to sacrifice all the great widgets and a lot of image functionality I think, but maybe that would help matters.

I really just don't know. I read on Mr. Buytaert´s personal blog the following:

It is not always easy to scale Drupal -- not because Drupal sucks, but simply because scaling the LAMP stack (including Drupal) takes no small amount of skill. You need to buy the right hardware, install load balancers, setup MySQL servers in master-slave mode, setup static file servers, setup web servers, get PHP working with an opcode cacher, tie in a distributed memory object caching system like memcached, integrate with a content delivery network, watch security advisories for every component in your system and configure and tune the hell out of everything.

I'm a bit mortified by this, but I want to attack the problem at the software level and make sure it's optimized before throwing hardware and related maintenance efforts at it.

... so I´m guessing it would be best to hire a big brain rather than trying to feel my way around in the dark on this one.

I'd not only be willing to pay but I would consider a revenue share for a year or two for someone that can help me get this site up and running. It's sort of my personal Sisyphean challenge. So, I'm looking for a Drupal performance God to break the curse and bring this project home.

dman’s picture

dman commented

Yeah, node handling scales well, but if taxonomy is your problem (it probably is) you may be interested in what was done when we had a go at millions of taxons working at scaling improvements during the Encyclopedia of Life workshop. Simon Rycroft was the one touching that part of the elephant. See LeftAndRight which fixes the indexing of hierarchical taxa.

A stock-core version of taxonomy_get_tree() will collapse when hitting tens of thousands of terms. It doesn't do that for every READ, of course, but it does on the edit screen, even with taxonomy-manager or taxonomy-select (I think).

At the architectural level, I'd say that although some software bottlenecks need to be looked at, don't write-off a hardware upgrade too quick. In my experience (I've been called on to do this several times) hiring a "Big Brain" to optimize needs to be balanced against the real fixed cost of just getting a bigger machine. For the price of 1 week specialist tracing and work-arounds, my clients could have just bought a machine that was 4x better and gave them better results.

- I'd love to see your 'millions' of location terms :-) So far I've run taxonomy_xml over about 300,000, but not had anything bigger.

.dan. is the New Zealand Drupal Developer working on Government Web Standards

greenc’s picture

greenc commented

Thanks dman...

I'll look into the modules and read up on the references you've mentioned.

The truth is that I've got a truly embarrassingly large amount of money invested already in this white elephant of a project. I look at it as a sunk cost at this point so my bottom line is how much it will take to get it up and running. I've got lots and lots of members so the project is still viable. Once it's relaunched, I'll be able to recoup my investment, no doubt there, but ufff... I have to get it up and running. V1 (non-drupal) is still up but I had to block all robots from indexing it to cut down on the traffic as well as block IPs from much of the globe for the same reason, stop all advertising, etc... it still gets some good traffic, but really it's sad and just limping along now. Very painful to have had to do it, but I didn't want it to collapse completely.

It's hard to know which fork in the road to take. I'm sort of on the Yogi Berra plan right now. You know... "If you come to a fork in the road, take it." ... so all options are on the table.

Let's say the site could grow to the size of plentyoffish.com in two or three years time (it might be that size now if I had studied coding at the university instead of statistics or been luckier in my development team. Water under the bridge. The bounce rate on the site is about 10% which if you study google analytics is really quite impressive and about 70% direct traffic which which means that users love the heck out of the thing and it's got good brand recognition already (the other 30% or so of the traffic comes from sites linking in)... which is why I can't bear to drop it.

What sort of server config / RAM would you recommend... to start out? Say up to 25,000 logged in users. I'd say 90 to 95% of users will be logged in users... which is why it's so tough. It's that sort of site so boost and traditional caching won't help too much.

I'm looking into minimizing calls, looking a query times, doing the YSlow thing and all that... but I'd be open to improving the hardware situation, sure. The thing is that we´re running into all these problems on the test site... maybe 3 logged in users, no traffic. That's what's scary at the moment.

dman’s picture

dman commented

I'm a Drupal maven of course, but there are some domains where custom code may be a better answer. Just saying I don't know if it can/should really be done.

I plead ignorance of actual hardware. Bigger is better so whatever you can afford will probably pay for itself. Sorry, other folk know the buzzwords better than me.

Drupals memory issues are not neccessarily database ones, so a load-balancer (say three apaches) talking to one DB, plus a fourth Apache for content delivery might be what you'd look at for user-account-heavy traffic like that. I just don't know.

What you are looking at on the test site could well be the taxonomy thing. It could also be some naive SQL somewhere, possibly trivial, possibly easy to root out. Or some views that would be better-written if hard-coded. I can't really guess, but I can imagine.

Look in the usual places. MySQL slow-queries, and trace with XDebug or a profiler. It may be easy. But it may be insurmountable also. *shrug*

.dan. is the New Zealand Drupal Developer working on Government Web Standards

yasheshb’s picture

yasheshb commented

million+ taxonomy terms is a performance issue for us too. we hacked taxonomy.module file as follows

--- taxonomy.module.1   2009-09-15 11:13:08.000000000 +0000
+++ taxonomy.module     2009-11-19 11:06:21.000000000 +0000
@@ -431,6 +431,10 @@ function taxonomy_form($vid, $value = 0,
  * Generate a set of options for selecting a term from all vocabularies.
  */
 function taxonomy_form_all($free_tags = 0) {
+
+  $options = array();
+  return $options;
+
   $vocabularies = taxonomy_get_vocabularies();
   $options = array();
   foreach ($vocabularies as $vid => $vocabulary) {

hope it helps.

yashesh

p.s. this was a quick and dirty one time hack we used. there would be cleaner ways to remove the taxonomy multiselect box on the admin/content/node
using a form alter hook.

Yashesh Bhatia

greenc’s picture

greenc commented

Thanks for that! I'm going to pass it on to our developer along with the rest of the info and see what the results are. Something odd is happening actually. Although the taxonomy module is clearly working and can be accessed normally at http://mysite.com/admin/content/taxonomy It has disappeared completely from http://mysite.com/admin/build/modules. It should show up under Syslog, but it's missing. I was going to install the Left and Right module to test it as was suggested by dman, but it's not there. I get Taxonomy Left and Right dice: Depends on: Taxonomy (missing).

Strangest thing. I'm at a total loss. Working to figure out how to bring it back.

sudeepg’s picture

sudeepg commented

Just curious. What is the memory limit you have set in your php.ini? After how long is your cron runs? What is your server configuration assuming 1,50,000 nodes and millions taxonomy terms.

Looks like some module may be interfering or your server configuration too low.

I had the same problem for one of my sites and the problem was with xmlsitemap module. Sitemap was creating maps on every cron run. I rewrote the code for xmlsitemap and that solved the problem for sometime.

Eventually, what came to the rescue was memcache and boost.

greenc’s picture

greenc commented

Hello ebizondrupalservices,

I believe it's set to 256M if I'm not mistaken. The server has 2 G RAM and I can upgrade to as much as 8 G if need for only a few hundred USD. Most of the folks that I talk to however suggest that 2 G should be enough for the test site.

Would you agree with that or am I off base here?

Thanks so much!

dave reid’s picture

dave reid
he/him
Primary language English
Location Nebraska USA
commented

Might be interested in the 2.0 version of XML sitemap since it's been re-written for performance and scalability.

greenc’s picture

greenc commented

Nice... I'm doing it now. Thanks...

bojanz’s picture

bojanz commented

Did you solve your problem? Did any particular step help?

I'm interested to hear your experience.

greenc’s picture

greenc commented

Still a work in progress but disabling a lot of native Drupal pages and creating Views for them instead seems to be a big help.

I'm considering moving to Drupal 7 as it's going to be more scalable, but there seems to be no i18n solution for Drupal 7 yet so I'm waiting to see what happens with that.