How to improve performance ? I would like to know your thoughts ...

Aurélien's picture

I would like to know your thoughts on Drupal and its performance, and especially how to improve it.

We currently use a system based on Drupal 6.22 Project Mercury (Pressflow, Varnish, APC ...) specially designed for heavy traffic. And we have installed more 50 modules like (Views, CCK, Panel, VBO, Rules, Workflow, UberCart, Solr…)

But our production server is a virtual environment with 4 CPUs and 8 GB of Ram, which is really huge for a single site. During our peak traffic, we have over 1,800 visits and more than 50 connected users for one day.

We are always looking for information to improve performance and to reduce costs.

If you run a high traffic website, I would like to know your point of view and your working methods or tips to improve performance.

Comments

Hi

julien's picture

Hi,

The problem with 50 modules (even the core) is the database queries. What is your database setup?, have you tuned up your db?

We use the basic

Aurélien's picture

We use the basic configuration of mysql. We are not experts in tuning mysql.

I found a script "tuning-primer.sh" to help me configure Mysql with little results.

Here is some information on the configuration of our mysql.

max_connect_errors = 10
max_connections = 80

key_buffer_size         = 32M
max_allowed_packet    = 128M
thread_stack     = 192K
thread_cache_size       = 8
sort_buffer_size = 2M
read_buffer_size   = 200k
join_buffer_size = 1M
bulk_insert_buffer_size    = 8M

query_alloc_block_size = 8192
query_cache_limit   = 1048576
query_cache_min_res_unit =4096
query_cache_size = 268435456
query_cache_type  = ON
query_cache_wlock_invalidate = OFF

table_open_cache   = 150
table_definition_cache =  256
thread_cache_size   = 8

mysql

julien's picture

there is a great module to tune your db called dbtuner, but the choice of the database (mysql, postgresql, firebird, oracle or whatever your need) is based on your data structure and the traffic you have evaluate to get on your site. To me now, i reckon that using mysql can be a bottleneck for Drupal.
The great thing in Drupal is the abstract db layer, so it's better give it a go. (at least for testing and check the performance you need).

Regarding the server you can change to lighthttpd, or something else, to see if you get improvements.

But keep in mind that is the way you use the api in a clever way in your custom modules code and theming, also respect the Drupal coding standards, that your website performance rely on.

Swapping out the database

dalin's picture

Swapping out the database might be something fun to play with, but I'd be very surprised if you found any big performance improvements. Especially for someone like the OP who has little experience with performance tuning there is far more that can be gained by optimizing other areas.

--
Dave Hansen-Lange
Technical Manager
Advomatic LLC
Great White North Office
Canada

i'm not too sure that using

julien's picture

i'm not too sure that using mysql even with Drupal core will give you the best performance honestly, especially if you want to build big web apps

Sure you can get better

dalin's picture

Sure you can get better performance for a web app with MongoDB, but the OP is asking about a low traffic site that requires heavy hardware. There's likely some really heavy blocks somewhere that can be cured with better indexing or caching. Changing to a different database in this scenario will be a lot of work and possibly zero (or even negative) gain.

--
Dave Hansen-Lange
Technical Manager
Advomatic LLC
Great White North Office
Canada

I see where you're coming

julien's picture

I see where you're coming from, but in my opinion with a lamp setup experience, apache + php can be only tuned up by apc (or other php accelerator) and varnish. Regarding all the db queries that the Drupal API is doing, i reckon that the best optimizing track to look is the db one.

Different Definitions

ao5357's picture

There are a ton of [effective] database optimizations to try before replacing the engine outright. It would be most effective, regardless of engine, to see what sort of queries are being run. Aurélien can then determine which queries can be re-written for a direct performance gain, cached to reduce query volume, or perhaps scrapped altogether. If what the database is doing hasn't been determined, what difference does it make which database is being used?

We likewise don't know the infrastructure setup. Hardware-wise, Aurélien might be on [slow] shared database hosting, on a server that's slow for this-that-or-the-other reason, or other variables.

Since it's a D6 install, switching to a different database engine would be much more painful than on D7, and there's really no guarantee of benefit (with a possibility of worse performance).

i agree that the first thing

julien's picture

i agree that the first thing to do is to try to tune up the existing database. In a second hand, you can start doing with standard drupal install some testing to see which database fit the most your website requirements, which i think mysql doesn't always fit for different web app data structure.
Regarding D6, looking at the D7 db abstraction layer, it require more php functions to build simple sql queries (which had some bugs btw), that it was on D6, so regarding the php side that you can improve accelerator with limits, i'm not sure about D7 bastraction layer SQL queries performance at the end.
The D7 new DB abstration layer is about making drupal usable on more database, there is no performance gain because of all the php added to it. it's my opinion, maybe i'm wrong.

Optimizing What's There

ao5357's picture

A few things to check:

Is apc.stat off and the shm_size sufficiently large? Turning off stat reduces file reads even beyond normal APC operation, while a large shm_size will assure that all php code gets cached.

Are you using mod_expires? By getting users to cache common files you're reducing a lot of the requests you get for resources. mod_deflate is good too, though not necessarily for performance server-side. Google Page Speed can be a real helper in finding speed/performance optimizations.

Along with overall site caching, have you configured caching for individual views? The devel module is good for finding slow queries and finding places you can lifetime Views output.

Simple Tips

mikeytown2's picture

Get a profiler (xhprof, Xdebug) up and running on a dev box to see whats slow (using CacheGrind). Once you've found the slow code, see if any of these patches will help http://groups.drupal.org/node/187209

For the database give http://drupal.org/project/dbtuner a try. It will help with CCK/Views performance.

Start with devel module

ghankstef's picture

Start with devel module and turn on database query. Turn on collect query info and display query log and set it sort by duration. This will give you any slow queries that you can go about optimizing by adding indexes and such.

MySQL indexes

xaris.tsimpouris's picture

With pressflow and as mush as possible perfomance workarounds we had tried, above all that we also tried MySQL indexes.
We checked to log all slow queries and left the system for a few days to see what we can get.
Then, we created new indexes for all the slow queries. It is ammazing how may of them speed up the MySQL server.
Some of them were just checking a cck field from the cck module (but if this happens for 70000 nodes, doing full scan -> temporary table -> filesort is a serious perfomance hit). Other, were from various views.
Creating the indexes made the MySQL server fly even more.

--
Xaris
yet another drupal developper
http://1204.gr

What about your infrastructure?

amatusko's picture

Do you have a separate VM for your database or are you using the same machine to host mysql and the application server? There are performance gains to be had with good cacheing schemes, etc but you still have to locate the single source of your bottleneck. Then we can talk about memcached, Mongo, and perhaps even membase for a solution. Relational data is just too slow, but you might want to try MongoDB/

NewRelic

rjbrown99's picture

My default recommendation for anyone that wants to do any type of performance tuning is to sign up for NewRelic. The full version comes with a trial and there is a free option for ongoing analysis. It should pinpoint exactly where you are slow - PHP level, memcache, database, etc. It's not quite as granular as you would get from xhprof/xdebug/cachegrind/etc but you can leave it on in production.

I am a paying Newrelic customer and leave it on in prod, and when I see bottlenecks I go to the dev environment with xhprof to really pinpoint what's slowing things down. The only thing I suggest is NOT enabling the Drupal Modules support in prod as it does have significant overhead and will drop your apdex score (mine drops by ~10-20% with that on.) Otherwise the rest of it is a minimal hit to run it in prod.

High performance

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds:

Hot content this week