Typical performance bottlenecks patterns

Both a large number of queries and slow queries can be problematic. Devel can already list the executed queries and their execution time. XHProf can be useful when trying to figure out where the most or slowest queries are coming from. To start, look for "PDOStatement::execute" in the function list (usually far up in there when sorting by exclusive wall time). The number of calls to this function and the execution time basically matches the number of executed queries and the time they take as they are executed by that function. Not directly, but since it's a function/method provided by PHP itself, we can't see which functions are called by it.

Then, select it and navigate up the parent functions until you reach "SelectQuery::execute". In the parent functions, you can see what type of queries are executed. db_query() are static queries (usually SELECT), SelectQuery::execute are dynamic SELECT queries. There shouldn't be much else on a normal page. If there are any Delete or Update queries, follow their calling path to figure out where they are coming from. Make sure they are really necessary. A typical case is statistics.module, which can be replaced with Google Analytics or Piwik. Or cache writes, mor about that later on.

You can also investigate the dynamic and static SELECT queries, especially if there is a large number of them. When clicking on db_query() or SelectQuery::execute(), you can see where they are executed. If a large number or slow ones come from a single function, you could either see if you can implement some kind of caching or improve database indexes or the query itself. You can't see the executed query directly in XHProf, you have to go back to the Devel Query log and search for the relevant function.

If many queries come from the DrupalDatabaseCache class, you might want to consider using an alternative cache backend like Memcache. It will still have to be loaded from somewhere, but fetching from Memcached is much faster and at least as important, it takes away the load from your database.

Keep in mind that MySQL uses a query cache which will make most queries very fast if you're visiting the same page multiple times but that cache will quickly fall apart when many users visit different pages. So you might want to disable the query cache to get better results on how long the queries actually take. More about that in a separate tutorial later on.

A good indicater that something is wrong is if you have cache writes on every page visit. It is common and perfectly fine that there are a few cache writes when you visit a page the first time (for example menu trees) but there should be none after a refresh. If there are, follow the calliing path of cache_set() to figure out where they are coming from. There can be a number of reasons for that, some examples which I encountered in a recent project include:

• The cached data is too big for a single cache entry. For example Memcached is limited to 1 MB and if you many fields and tables, the Views cache might get bigger than that. This means that it will try to write it, but it will be ignored by Memcached. On the next request, there is nothing to fetch from the cache, so it begins again. Not only does this mean that slow cache writes happen, but also that hook_views_data() is executed on every request. Solution: Use a cache backend that can handle larger caches. Or, what would even be better, try to cut down the cache size by removing unused modules, fields and so on.
• Bugs in the cache backend. getMulti() in the Memcached cache backend was broken until recently, which caused it to return the entries but with a wrong key. This meant that the calling module did not find any cache entries and had to rebuild it. See the following issue: http://drupal.org/node/1078626.
• Bugs in the code that uses a cache. For example, when a module stores an empty array in the cache but takes that as a cache miss when loading from the cache on the next request. Context.module is currently affected by this, see http://drupal.org/node/1247412
• Cache implementations which can be disabled but still write to the cache if they are.
• Using variable_set() or variable_del() on every or many requests. This results in cache clears of the variables and bootstrap cache. Avoid the variables system for frequently changing or big values, see http://stackoverflow.com/questions/6041471/where-to-declare-variables-common-to-different-include-files-in-a-module-drupal for more information.

Thee are some slow functions in Drupal, which should not be used on every page and if they are, the resulting output should be cached. one example is menu_tree_all_data(), which loads, among other things, all objectes like nodes and taxonomy terms related to a menu.

These functions can be hard to detect, especialy since their execution time is often distributed among many child functions. Look at the inclusive wall time to see if there is anything unusual as these functions are usually very similar on Drupal sites.