Fixing slow queries and database deadlocks in Drupal without PHP profiling tools

Fixing slow queries and database deadlocks in Drupal without PHP profiling tools

Difficulty: 
Piece of Cake

Today I am going to show you how to autopilot monitoring and diagnose of

  • Slow queries
  • Long query wait times due to database locks
  • Database deadlocks

All of these

  • On your production environment
  • With little to no performance overhead
  • Without enabling any modules or resorting to PHP profiling/debugging extensions

Even if you are already continuously monitoring site performance with tools suchs as XHProf or New Relic, what we are going to do is free and extremely easy to setup with no aditional modules or PHP extensions.

The metodology here described provides you with information about the query being executed, and more important, the PHP callstack that lead to it. Sometimes the solution is not to optimize the queries but to avoid them all along! (or both...)

The first thing you need to do is tell the database driver to output trace as comments in your queries and to use PDO emulate prepares.

In settings.php:

$conf['MSSQL_APPEND_CALLSTACK_COMMENT'] = TRUE;
$conf['MSSQL_DEFAULT_EMULATE_PREPARES'] = TRUE;

Start a new trace in the SQL Server Profiler:

Filter the events by database name:

And by query duration (important to prevent your SQL profiler from flooding):

Start running the profiling session and let the magic happen:

Nice. With the driver tweaks we made in settings.php the profiler is now colleting queries with replaced params (emulate prepares) and the stack trace is being prepended as a comment in the query.

In this example we can see that there is probably an uncached view embeded into a node with EVA that is adding 62ms only for query execution (and that the page itself was also uncached).

If you haven't noticed already, you can see that this strategy will also serve to diagnose database deadlocks or long wait times - that was indeed the first use we gave to in-query stack trace as comments.

The all mighty SQL Server Profiler deadlock analysis tools will provide you with the pair of queries (and stack traces now!) that are locking each other making deadlock diagnose a piece of cake (solving the deadlock or long waits might not be as easy).

To do so just follow these instructions on how to generate deadlock graphs and deadlock profiling sessions:

Analyze Deadlocks with SQL Server Profiler

How to Track Down Deadlocks Using SQL Server 2005 Profiler

Save Deadlock Graphs (SQL Server Profiler)

You can also use the query optimizer to create indexes for you queries with 0 effort. Simply copy paste them into Management Studio and analize it with the Engine Tuning Advisor:

The advisor will output a list of SQL Statements that you can use to create indexes. Be warned that doing this requires some knowledge of what you are doing because

  • The proposed indexes might have negative performance impact
  • The proposed indexes might be too heavy
  • The tuning advisor might be missing something, and you could believe nothing can be done to optimize that query. Make sure that a real human optimizes manually the query execution plan before giving up on a query.

You will spend some big time looking for new indexes to speed up things in Drupal, and my recommendation is to keep track of all theses indexes in a custom module that you deploy to all your sites.

Add new comment

By: root Sunday, July 19, 2015 - 00:00