The birth, death, (and hopefully rebirth) of Ubersoft.net on Drupal 5.1 -- a site performance autopsy

Nice posting! Well written, and I think you've captured the essence of what is going on. Unfortunately, I don't have the Drupal-specific skills to help you, but I think this would make an excellent case study and/or white paper on how to optimize a Drual site while trading off resource requirements against functionality.

Hopefully someone with the necessary background will take this on, I would be very interested in seeing the results, and I think that it would be a benefit to others as well. I believe that I've seen others post some good work in regard to specific parts of the solution process, which may fit in well as references.

--
Joe Kyle
--jjkd--

(Subscribing... I'd love to hear about query caching if anyone has used it with large Drupal sites. We started caching queries on our MySQL server for a large osCommerce site and saw incredible results.)

Well, I was the one who promoted your announcement to the front page the first time and this time as well. Oops. :D

The reason I did so the first time was because of what you stated here. You took a static site and migrated it and 10 years of content leveraging Drupal and it's abilities very well. It was a good example of why designing and building sites can be complicated and you had a very nice write up about your objectives and the benefits you received in using Drupal for your site as well as some of the learning curve as well.

This write up is also good. We have a variety of levels of users. From the folks who work on a high end designing and building enterprise level sites (mtv, sony, warner) to those converting over from static HTML who know how to look up the word web server but don't necessarily understand the relationship with the database much less the performance impact of the database on RAID1 or RAID5 or even a virtual server.

Performance documentation is something we don't have a lot of in the community. It's something that is not hard to do, in theory, but does take time, research and practice practice practice. With the recent Lullabot performance seminar a few weeks ago this type of conversation is starting to occur in a wider audience. Over time I hope that white papers can get added to our collection of documentation on the issue. Drupal is only one part of the equation, as you learned, the database is another important part as well.

As a note, if you look in the development archives, performance is always an on going concern for the active developers. For instance for Drupal 6, one of the large issue with menu module's has been addressed. This doesn't really help you now but be assured folks are always trying to keep performance considerations in mind while going forward with each version of Drupal.

-Steven Peck
---------
Test site, always start with a test site.
_{Drupal Best Practices Guide -|- Black Mountain}

Subscribing to this thread because my problems are similar. I have a very low-traffic site, but I keep topping out my CPU usage allotment on my shared hosting account. I would be very interested on how to optimize a Drupal site when storage and bandwidth are not a problem, but CPU usage is.
----------
"Being tired is like playing mind games with some of the pieces missing."
- Canadrian

I am a proud member of the ElectricTeaParty.net online community.

Personally, I think your webhost was out of line. If they are selling "unlimited MySQL accounts" then going back on that, they are ripping people off. It is no different than Comcast selling "unlimited" Internet connections but shutting off high-bandwidth users. Granted, I'd understand if they shut off a customer due to abuse of the MySQL database, but I would hardly consider Drupal to be "abusive".

I would suggest finding another webhost shows more clue in these matters. For example, my webhost allows unlimited MySQL usage -- but, if you are in the top 10% of MySQL users, they will bill you a surcharge of $0.01 per day. Not only are they up front about it, but they are reasonable about what is charged and their conditions for charging it.

My 2 cents.

-- Doug

Just a quick question, but did you turn on caching?

the real problem here is drupal's continuing lack of static page caching.

it's not that hard to do, there's a promising contrib module for this for 4.7 (http://drupal.org/project/boost), and in my opinion this is one of the most important roadblocks to larger scale adoption of drupal.

imo this should be a high priority for drupal 6, and devs should look to the smarty template system as a guide. smarty has an awesome static page caching system that provides a separate html cache file for each template - implementing something like this for drupal would allow for block level caching. you could have a page with dynamic content, but cached menus and blocks - just like block cache, but with static pages.

there's no reason for a site the receives primarily anonymous traffic to be doing a ton of php compilation and database calls on a page that doesn't change. database caching is the worst idea drupal ever had.

I agree that a single page shouldn't normally take 100 database queries to display. Everyone who has commented about the *serious* need for improved caching (including for logged-in users) is right and echoes some of my serious concerns with Drupal performance in an environment where users may be logged in.

I am not a database "guru", but I have a fair amount of database performance experience and I've seen a handful of modules and slightly-slow database query times render a site almost unusable for logged-in users.

-an

Hi

Are you able to use something like apache's mod_cache to cache the generated web pages to disk or memory.
This should reduce the amount of database accesses - (Drupal holds its cache held in the database).
I've not needed to try it, (I've used other caching solutions with jsps, which will not work with php).
The latest (online) documentation is very good.
This module may not be available on a shared server, but for those using a dedicated server, I think it would be worth trying.

Nick

100 queries per page is quite normal for some web-apps (OsCommerce springs to mind) but quite high for Drupal in my experience. Of course it depends on how you were using it; but looking at your current (static) site, it seems to me that there is little in the way of the sort of interactive content that makes database queries unavoidable. As long as users aren't logged in (and I guess that most of your pageviews would be from non-logged-in visitors), a page could be loaded almost entirely from cache.

If you had something like a "recently-viewed articles" block whose content constantly changes, that would be an example of something you might consider removing to optimise performance.

You said you didn't try aggressive caching, and that's a little strange. Caching is exactly what you need, and the nature of your site suggests it would be very effective. You might look at this thread on additional caching options: http://drupal.org/node/97347 .

As for your hosting provider - a typical hosting situation is to allow 1,000 accounts on a single server, each of which is entitled to (say) 10Gb diskspace and 100Gb monthly transfer. But of course that doesn't mean the server has 10,000 Gb of diskspace, nor do they pay their ISP for 100,000 Gb of monthly bandwidth. They are overselling, safe in the knowledge that statistically most users won't ever use a fraction of their limits. (This applies to all hosting providers - no one could compete by being "honest" about their allowances.) If one of the 1,000 accounts happens to use say, 10Gb of diskspace, this could be 2% of the total available, but it wouldn't be a problem because everyone else is using less. So 4% of CPU usage might be over your limit, but it's not overwhelmingly unreasonable.

I wonder about your statement that you couldn't afford a virtual private server, given that you run advertising. They start at under $50 per month, and you do run advertising on your site. Or as an in-between, many hosts offer so-called "e-commerce" hosting, which is basically a shared package that allows for fairly high CPU usage.

100 queries per page is a bit too much, no matter what site it is.

i too had problems with a moderate-traffic drupal site recently, because of the fact that drupal logs errors into the database. the site lived on a server at a shared hosting provider which was not very well managed (too many users, bad security, ...). several times the mysql-connection "went away" and so did the cache table of my drupal installation one or two times. as a result thousands of error-messages were triggered which strained the db additionally. after some minutes the server went down completely.

in some circumstances it would be usefull to be able to log errors into a logfile instead of the database. logging make things worse if something goes wrong with the db.

Just tracking

I don't see why money is an issue. You can get a fully managed VPS from about $50. Try servint.

Hi,

This is indeed very important topic. I am in similar situaion except having a somewhat technical background I never moved my site to Drupal except for a very short amount of time.

For background : I host a "small" forum based on phpbb. I was initially hosted on shared hosting but I have moved to dedicated server sometime ago. I realized that when I move to drupal my shared hosting account wont be able to handle the load. I am trying since days of Drupal 4 but have not made the switch yet.

Reasons why I wanted to move to drupal: Many here might be aware that to add features to phpbb , you have to change the code of many files. What it means is when a new patch is released you have to carefully redo everything. My last update costed me almost days to move to latest version. As the patches are security related you cant avoid it. All this can be very painful.

Now comes Drupal which has very well designed , elegant api. Allows you to extend functionality "very" easily. There comes the catch as it leave everything at hands of module developers , not all modules will be equally developed causing uneven performance.

All those who feel caching will solve the problem, they are only partially right. Part of blame is the design of Drupal itself. I wil try to explain but main core issue is loding by object vs loading by page. Drupal is designed to load by object.let see how it effects the performance.

I will take a simple case , lets say there are 10 nodes. To display the main page:
Drupal will load 10 nodes ( 1 Query )
For each node it will read author info ( 10 queries )
For each node it will read count of comments ( 10 Queries )

So that makes it 21 queries just to load 10 nodes, with only comments modules enabled. Depending on number of modules you add , you could be looking at 10 queries/module to be added.

Alternatively if drupal could load grouped items it would look like
Drupal will load 10 nodes ( 1 Query )
read user info for all 10 nodes ( 1 query )
read count of comments for all 10 nodes ( 1 Query )

now thats 3 queries vs 21 queries.

From development core code in node.module in function node_page_default:
original code :

while ($node = db_fetch_object($result)) {
      $output .= node_view(node_load($node->nid), 1);
    }

modified code

$nodes = db_fetch_objects($result); // $nodes is an array containing all nodes
all_node_load($nodes) ; // loads node objects in groups
foreach ($node in $nodes) {
      $output .= node_view($node);
    }    

This is just an idea not something I have experimented yet. I am not an expert in Drupal API level but have spent some amount of time trying to make it work on my test system. It may be a stupid idea but someone more experienced can comment on it.

But I have seen that Drupal loads same objects again and again in same page.

Now I know many people will say that it has been working for xyz and even for drupal itself but they should understand that not everyone will be able to host on multiple servers. Most of us can only afford shared hosting and unless number of queries are reduced actively this problem will not go away. Caching will help but nowhere near as actually reducing number of queries.

Hi,

nice post thanks to share ;-)

I have looked into the current site and have some questions:

1.) the js was from jquery or in every page or in a/some .js files
2.) I hope the pics are not in the db, isn't it ;-)?
3.) apache can deliver the css/js/png without asking drupal
4.) Have your provider give you some infos about the mysql like:

• query_cache_*
• max_user_connections
• version
• table_cache
• key_buffer_size
• ...

5.) is a slow-query log available?
6.) what php-settings was used e.g.: mysql*, accelerator, ...
7.) Is it possible to see the drupalied site?

I must also say that I'am new with drupal but I have done a lot with php/perl/.. and some webservers like: nginx/apache/lighty.

Maybe I haven't fully understand your site nor drupal but the new drupal book is on the way to me ;-)

BR

Aleks

Migrating large static sites means that many visitors will suddenly have access to all the increased functionality.
Such a large but static site fits nicely in a shared hosting account, but its dynamic equivalent won't.
Conversely, drupal can power many kind of smaller sites with a small expenditure of resources.
And there's middle grounds and plenty of suggested solutions, optimizations, etc. but making the jump still requires careful planning.

To find bottlenecks there's three main points to consider: queries, cache and resources.

Using devel you can find what queries are being run, not only the amount.
It won't matter if there's hundreds if most of them are calls to simple items like cached blocks and menu links.
But there's always a handful slow queries, beside actual content work like views and category.
Things like statistics and cache write to the database intensively and can take a toll on speed.

Now, these queries happen per page read, so tuning up the navigation is important.
An archive with a calendar can prompt more browsing than needed.
Galleries, forums, categories or searchable content add functionality and visitors but also put the database to work.
Many of these things are cached, but there could be so many items that cache is outperformed.

The benefit of cache depends on the amount of content, as cache expires everyday.
The amount of nodes that could be statically cached is often not significant against the many dynamic pages that are needed for navigation.
The usage of dynamic listings, like archive pages, can make page reads build up quickly and probably won't benefit from any kind of caching either.
The nodes and pages with static content are fine and won't really benefit from any caching if there's pages that slow the site.
Ideally, the sites must be optimized at this point, rather than rely on the measure of queries and cache alone.

This is also behind the reason of why pages aren't cached for authenticated users.
As a middle ground, cached pages can still be shown to users if pulled from a multisite mirror.
But the real issues arise with the content that can't really be cached or isn't cached when viewed.
For example, i have a site with 500 nodes, but spread through 300 categories with sortable views.
Every category page has a pager with links to the first ten pages of results, 10 nodes listed per page, and filters by price, location and brand.
This means there's multiple pages to be cached for the same set of nodes.

That makes the site collect entries in the cache table, and while those build up it behaves as if there were no cache at all:
It always crash in the hours following a cache clear. In the next few hours it collects about 2000 saved entries.
Hopefully, before the peak hour it'd get 6000-8000 and no longer crash. By 9000-12000 it speeds like a charm.
By the end of the day the cache table has 15000 but the cycle is bound to repeat because the daily addition of new nodes change all pages,
most lists are sorted by date and it would happen even if there were no expiration on cache entries.

While the cache is being built there's a higher RAM usage than on the hours afterwards.
I hit the limit on a VPS, so the choice of shared or non-shared doesn't make the difference.
A shared account with large resources would be nothing more than the cost of management and resources put together.
Both VPS and shared have pooled resources, like burstable RAM, but this is never available in the practice.

Where does the resources go? I can't tell, because i haven't compiled my apache server to read the memory usage per query.
But estimates can be done. I see 10 httpd processes keeping hold of 24 to 32 MB RAM each, but there's more on peak hours.
Using "systat", the log shows that the active processes, most of them httpd, go up from the average of 10 to 20-30 on peak hours, before hitting the RAM limit of 512 MB and crashing.
As my visitors double on peak hours, i can see why resource usage doubles as well, but fine tuning everything still takes some work.

CPU supposedly makes these processes finish faster, releasing the RAM that is currently held longer than necessary.
That's only available on a dedicated server. At that level there's more control on all resources, but it still takes some tuning to plan a site scalability.
To avoid conflicts, it'd be good to have the database run with its own resources, on a smaller VPS or similar separate account.
Perhaps serving images through lighthttpd or also in a separate account, which can't be done in a shared enviroment.
In any case, running a drupal site under heavy traffic requires careful planning, but the features are worth it.

I've been with a2hosting for almost 2 years. Over the past month they have gotten to where the server is down several times a day, including email. About a week ago, the mysql server literally lost my database (it was there but the tables were gone) for about 1/2 an hour. Scared me good.

Late last year they said I was causing a problem with their servers using up too much resources. They asked me to state what my websites were used for. I ended up upgrading immediately to patch some security issues that had recently come out. Since then I've been tracking their CPU usage which is always in the red and constantly hovering now around 6 or 7 (2 is green).

One of my sites uses a ton of memory. It has OG, views, and CCK. This site requires over 30MB of memory but there really isn't a ton of content on it.

I agree that the mysql database connections and bandwidth is not trackable on a2hosting. But for $12 a month and hosting 20 databases and 10 virtual domains, what can I do?

(subscribing)

Database Activity: the 40-ton feather that broke the camel's back

We found the same to be true. When HarryB built Open Source Community for us, we were so thrilled with what Drupal was able to do! It was our first Drupal site and we were so impressed. (Of course, HarryB is nothing short of brilliant and has *years* of experience with PHP-Nuke, PostNuke, Joomla!, and WP, so that helped!)

But, before go live, with only a few of us online, we noticed serious performance issues. Long story short, we were forced to find dedicated hosting or move away from Drupal. The number of database calls and latency from querying a remote database (i.e., using Drupal in a setup like most inexpensive hosts provide) is a back breaker.

Other than that, Drupal rocks and we love it! Thanks to all who have contributed to its success!

AmyStephen@gmail.com
http://OpenSourceCommunity.org

Hi,
This is very important problem we ( I mean Drupaler's) are facing. Wouldn't it be useful if we rewrite some functions like variable_get, watchdog,access log etc.. so they can use other storage methods instead of database, for example file, xml schema etc, to reduce database load?

I'm not sure exactly what your demands are, or how loyal you are to your hosting company. I would like to recommend my hosting company. I just switched to them this year and they are outstanding. A lot of useful features, but here is the best part.

The basic hosting plan includes
1.614 TB of monthly transfer
161.4 GB of storage space
2 GB max file size
Hosting Of Unlimited Domains
And of course the usual, PHP, MySql support, and many other features.

This looks to be quite a bit more than what you are used to. The plan, I think is about $130 dollars per year if you pay for a year at a time. I wouldn't know though, because there are a lot of coupon codes in the wiki and I only paid about $28 dollars for my first year. I will gladly pay the full price next year.

Be sure to check them out, it might be away to get your drupal on with out crashing any servers or paying bandwidth overage fee's.

Arthur Malcolm Page
www.synapsemultimedia.org

the posting is the 'Cool, thanks ;-)'.
sorry for mistake.

Dreamhost is not the place for a Drupal site. Had one there...response time was terrible, mainly due to the load Drupal put on the database. Dreamhost runs Mysql on separate servers so every transaction goes over the LAN, making matters that much worse.

But, I have Wordpress and Joomla! sites there that work just fine....

I've pretty much convinced myself that Drupal needs a dedicated host...

HarryB
http://wwww.opensourcecommunity.org (Drupal @ dedicated server)
http://www.hrpr.com (Joomla! @ Dreamhost)
http://www.halfvast.com (Wordpress @ Dreramhost)

This is an excellent thread and directly addresses one of the major concerns a web developer is likely to have when building a site using Drupal. One of the sites I'm working on will eventually outgrow shared hosting but I can't afford a dedicated server at this time.

I started a discussion for hosting busy Drupal sites without a dedicated server:

http://drupal.org/node/138425

I'm hoping some developers who have accomplished this will read the thread and contribute a few benchmarks. It would be useful for Drupal users to have this info.

------------------------------------
ThemeBot - Find and Share Web Design Templates

Don't restrict your website to one host. Your database is obviously taking a beating.

I recommended ModWest.com in your first post and I am doing it here again. They have a highly organized database backend. I hosted a popular statistics tracking service on Modwest for a year, before selling it.

http://www.ComputingNews.com
http://www.BusinessLetters.com
http://www.AcmeTutorials.com

Another site that was converted to Drupal recently is the1secondfilm.com. I saw them pop up on the Digg front page and decided to check it out. The site kept going off-line and having extremely slow page loads for maybe a day or two after the Digg effect.

The thing is, they were on a dedicated server already and thought they were prepared for some major traffic. Apparently they needed a second dedicated server to handle the load.

http://www.the1secondfilm.com/forums/t1sf_producer_forum/suggestions_sit...

------------------------------------
ThemeBot - Find and Share Web Design Templates

I became a drupal fan since version 4.6...so I saw 4.7 and 5.x coming to life.
I was surprised by the lack of development in REAL solutions for the problem of big resource usage. Come on, drupal is a COMMUNITY tool, so it is supposed to have a lot of users logged in, yet, the cache is best effective only for unregistered users.

I think it must be an absolute priority for next release, to focus on effective caching solutions: full static caching for logged in users or block static caching at the very least. It is time to resolve this problem once for all.

Drupal must become a CMS reputed for its moderate resource usage, not for being an ogre.

Brakkar

When a site gets that kind of traffic, shared hosting is simply not an option. You need a dedicated server. You are sitting on a gold mine and you don't realize it. You need to find ways to monetize your website. You can get a very decent dedicated server for $100 -$150. Long time ago, I have learned that ISPs don't talk about resources (mostly CPU utilization). They will give you unlimited bandwidth (in your case 200 GB) but to pump that kind of bandwidth, the machine needs lots of CPU cycles. My adivce: move to a dedicated server and if you are not making at least 5K a month from your traffic, use a part of your creativity towards monetizing your site.

We drupalized http://www.votefortheworst.com at the end of last year. At that time we were on a shared hosting plan and we have had to scale up to a 2-server cluster since then. Of course we get an average of 250k pageviews per day and we have had very intensive spikes usually right after Sanjaya's performances.

We're currently using the standard caching that comes with 4.7. We used Boost for a while but it killed apache performance and had to disable it. Other than that we try to not use very many modules and have had to add a couple of indexes here and there. But we still get some downtime during the big spikes (even on the 2-server setup.)

I feel like there is more than needs to be done to get our site optimized. But it seems at this point we need to dig into the code and really analyze what everything is doing to make some optimizations. It seems that TeamSugar had to make quite a few optimizations and a custom caching mechanism before their drupal site worked well.

Nathan

Been testing php-eAccelerator and php-apc on Mandriva.
Well, pretty fantastic results, page time reduced from 140ms to 46 ms, memory from 6MB to 0.4 MB (with eAccelerator). APC gave me some higher values for memory and time, so eAccelerator is the keeper (beside the fact that APC’s admin page is nicer and works better than eAccelerator’s).

Wander if Drupal could be squid reverse-proxy friendly…
I know wikipedia.org is using this architecture with great results.
As far as I know, Drupal is not writing some headers (I presume no-cache headers for logged-in users, or maybe some smart url-rewriting, with the username in it) and this is why squid reverse proxy is not suitable for.

Anybody having some experiences with squid reverse proxy and Drupal?

Thanks,
Tin Tin
Pinguin

Me thinks that heavy trafficked database driven sites (like Drupal, etc) need to be optimized in 2 ways.

While there is code tweaking there is also site (as well as internal modules) design and development that must take database activity into consideration. Too many such sites are being designed with a main page that does not just look like a swiss knife but more like a warehouse of swiss knives.

Would a site be less effective and popular if its main page was tastefully done but was not much more than a top level menu with half dozen items on it, instead of current tendencies of summarizing just about the whole site on that one front page ?

There seems to be too much tendency to add the various dynamic and data driven bells and whistles that create beatiful sculptures one sand grain at a time and then compare them with the old static pages that served whole slabs of etchings in the stone. The differences are night and day and not only can they not be compared but the overall site design and development must take this fact into consideration.

---
We are all prisoners of our own experiences.

I might sound crazy but it is just an idea.

Have a local server running drupal on which all edits happen.
Now, you wget this local website. this will convert the entire website into static pages. You can put these static pages on the server.

Have this done automatically, setup a wget job followed by ftp synchronization such that the changes get reflected on server in every 12 hours.

We are also in process of converting static pages to drupal. In case, it sucks I am definitely going to try it out.

--Alpha Zero
www.transbittech.com

subscribing...

and i'm sick of drupal query performance and non official solutions.

somebody tell us;
how can stand drupal.org? is there any difference with these?

- the front page required anywhere from 90-150 database queries
- individual comic pages in my comic archives averaged 70-80 database queries

ha-ha

My new site have :
- in Home Page (hole page ;) : 569 queries
- In an internal page (CCK w/ 130 field) : 537 queries

;(

Martin GERSBACH,
www.gersbach.net
Paris, FRANCE

A lot of folks have complained about the database load that Drupal seems to create, but I haven't seen too much talk about actually tuning MySQL (or Apache).

I recently upgraded my hardware and OS for my Drupal 4.7 site and assumed that it would easily handle an enormous amount of hits right out of the box. Unfortunately, when I ran some load-testing tools, the MySQL DB quickly ran out of steam and I started getting the Drupal/mySQL DB error page. My main page generation time for an authenticated user was anywhere from 300ms to 700ms with 129 queries (caching turned on). After some extensive MySQL (my.cnf) tuning, and loading up a lot more Apache instances, my page generation time is down to 65ms-80ms, but most importantly, I'm NOT getting the dreaded MySQL DB error page (out of connections/server not responding, etc.)

The next step for me is that when and if my site starts to get a crazy amount of traffic, I will start caching as much of the database as I can via memcached. With a caching layer like memcached in place, I believe I don't really need to care if Drupal does not cache blocks or authenticated user pages since the caching of all DB queries will happen with memcached.

Anyways, my point is,

Cache as much as you can, at the Drupal layer, and the MySQL layer.
Tune Apache httpd to make sure there are enough free servers available to handle incoming requests
Start looking into memcached, even if it means running 1 memcached instance of 256MB-512MB on the same machine as Drupal/MySQL.

-Moazam
www.unixville.com
the house of unix

Subscribed