Proposal: Explore adding hooks to allow plugins to track database queries, e.g. to enable integration with plugins like Query Monitor
One of my favourite plugins in the WordPress space is Query Monitor. Among other things it allows you to monitor at a glance what database calls have been made for a specific request, making it easy to spot inefiencient code and queries.
Currently CiviCRM does not have anything analogous to this. The CiviCRM docs recommend using the MySQL query log, but this isn't easy to view at a glance, and it doesn't make it easy to correlate queries to sepecific web requests. CiviCRM's database queries don't show in Query Monitor because CiviCRM does not use WordPress' WPDB
class. Therefore, I've been looking to see if it would be possible to extend Query Monitor to know about CiviCRM's database queries. Its reasonably easy to make this work if you patch the CRM_Core_DAO::query
method to the following:
public function query($query, $i18nRewrite = TRUE) {
// rewrite queries that should use $dbLocale-based views for multi-language installs
global $wpdb, $dbLocale, $_DB_DATAOBJECT;
if ( defined( 'SAVEQUERIES' ) && SAVEQUERIES ) {
$wpdb->timer_start();
}
if (empty($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
// Will force connection to be populated per CRM-20541.
new CRM_Core_DAO();
}
$conn = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
$orig_options = $conn->options;
$this->_setDBOptions($this->_options);
if ($i18nRewrite and $dbLocale) {
$query = CRM_Core_I18n_Schema::rewriteQuery($query);
}
$ret = parent::query($query);
if ( defined( 'SAVEQUERIES' ) && SAVEQUERIES ) {
$wpdb->num_queries++;
$wpdb->queries[] = array(
$query,
$wpdb->timer_stop(),
$wpdb->get_caller(),
$wpdb->time_start,
array(),
'trace' => new QM_Backtrace( array(
'ignore_frames' => 1,
)),
'result' => is_countable($ret) ? count($ret) : 1
);
}
$this->_setDBOptions($orig_options);
return $ret;
}
Esentially the $wpdb->queries
array is being populated with knowledge of the CiviCRM database queries, which query monitor can then read.
With this in place you can start to see some interesting insights. For example, we can see that the new mailing screen contains a number of database queries that are reapeated 40 times each, which is probably ripe for optimisation:
The WordPress admin bar shows the total count of queries (243) combined from both CiviCRM and WordPress.
Hacking the core files works for my purposes, but obviously doesn't allow for this to be packaged into a distributable plugin. Therefore, I think it'd be cool if there were a generic hook at the end of CRM_Core_DAO::query
which could be used to save the query information in a format ameanable to Query Monitor. Alternatively CiviCRM could copy WordPress idea of the $wpdb->queries
array, creating an array of queries which plugins like Query Monitor could make use of (in WordPress this is behind a flag so there is no performance hit when not in a development environment).
I've not given too much thought to exactly how this hook should look, but before I take this further (e.g. to the Pull Request state) it'd be good to get some views from the community:
- Is this something others in the community would find useful if this were distributed as a plugin?
- Does anyone know of plugins similar to Query Monitor in the Drupal or Joomla space which may benifit from this type of solution?
- Would people find this sort of hook useful for other use cases - for example, for writing debugging tools native to CiviCRM.
Keen to get others views on this one. I'll be interested to know what people think.