Why is drupal slow?

Question

I have made site on drupal

My site has 7500 users and approx (20 to 50 without logged in)(2 to 10 logged in) users are online (and this is not heavy traffic I think)

The site is on dedicated server. I have enabled setting in performance from drupal admin and also installed memcache and eaccelerator

I looked in query logs from using devel module. it is firing total 600 to 900 queries on each page

When I have installed patch of path.inc to reduce the queries of drupal_look_path(). It has reduced queries to around 400

I have also made some positive changes in mysql (my.cnf) file, but still there are many same queries run form user_load() function again and again

I have 60 to 70 modules enabled and all are use full. I can't remove the modules

Still the site is running slow it is taking approx 10 to 15 sec

Now I don't know why the site is running so slow

• Is it because the drupal has the large php code ?
• Is it because it is firing so many queries on each page?
• Does the InnoDB engine improve the performance?

Please, any kind of suggestions are welcome

Answer 1

400 queries for each requests is a sucidie (but even 50+).

You should implement some html cacher. My website generally doens't even make the db connection. It just fires the html cached in a file.

Answer 2

Some additional things to look into:

• Install a tool like Yslow/PageSpeed to see how much of those 10-15s are client and server time.

• Instal XhProf (on a development site, not live) together with Devel to see which are the functions that use the most time. Look into these first. Edit, now with link: http://groups.drupal.org/node/82889

• Using pressflow might help a bit, but since you are alrady using the path.inc patch, probably not so much.

• You mentioned that you installed memcache. Did you also install the memcache module and configure the cache plugin to use memcache?

• EDIT: Yes, switching to InnoDB can help. One of the main performance advantages of InnoDB is row-level locking (as opposed to table-level locking of MyISAM), which means that multiple INSERT/UPDATE queries against the same table won't block each other unless really necessary. However, InnoDB does not perform well at all out of the box, you really need to fine-tune your mysql configuration for your specific site. So this is a step that you should only take carefully and after testing and optimizing on a development site. There are various questions already on this site and elsewhere about InnoDB tuning...

Anything else than that is then site specific and depends on the modules you are using. But especially things like complex node_access setups and multiple languages (i18n!) tend to either cause slow queries and/or a lot of them.

Answer 3

Not all modules make use of the caching mechanisms you can switch on in the performance settings area. It would be worth trying to identify which ones are doing the most/slowest queries and attempting to get the developer(s) to improve them.

Alternatively, examine whether you could achieve things with fewer modules. Some modules do overlap somewhat in functionality, so you may be able to reorganise the way the site functions a bit.

Additionally, you need to look at whether your settings MySQL are allowing enough memory for these queries to be carried out. Most MySQL distributions come with different versions of my.ini labelled 'small', 'medium', 'huge' etc. Copy the 'huge' one to my.ini (back up the old one first) and restart the DB to see if maxing out all the cache sizes makes a difference. You may well have a bottle neck there, but it can be hard to work out what setting is causing it.

Same goes for PHP. Set memory_limit in php.ini to 500MB or something and see if it helps. Of course, you may not be able to do this, depending on your hosting arrangements, but it will eliminate one possible cause (or not) if you can.