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 :

modified code

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?

for those of you who are looking for more specific information...

I've set up the drupalized version of the site here: http://208.75.86.76

I have also set up the Devel module so that anonymous viewers can see the statistics in the footer. Have at it -- any and all observations welcome.