Benchmarking Drupal from the shell command line

Sometimes, you are trying to find a bottleneck in Drupal, and after broadly knowing where the problem might be, you reach a point where you want to benchmark something specific and granular.

For this kind of benchmarks, you can write a standalone script and run it from the command line.

For this you need PHP to be available in CLI mode (Command Line Interface) on your platform.

Here is an example to benchmark the impact of converting the node table from MyISAM to InnoDB.

<?php
require_once('./includes/bootstrap.inc');
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);
function getmillisecs() {
list($usec, $sec) = explode (' ', microtime());
return (double) $sec + $usec;
}
function measureit($arg) {
$start = getmillisecs();
$arg();
$end = getmillisecs();
$result = $end - $start;
return round($result * 1000, 3);
}
function select_inno() {
db_query("SELECT COUNT(*) FROM {node_inno}");
}
function select_myisam() {
db_query("SELECT COUNT(*) FROM {node}");
}
$total = measureit('select_inno');
print "InnoDB $total ms\n";
$total = measureit('select_myisam');
print "MyISAM $total ms\n";  

Here is another example comparing the use of converting to lower case in the database on an index field, vs. doing it inside PHP.

<?php
require_once('./includes/bootstrap.inc');
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);
function getmillisecs() {
list($usec, $sec) = explode (' ', microtime());
return (double) $sec + $usec;
}
function measureit($arg) {
$start = getmillisecs();
$ret = $arg();
$end = getmillisecs();
$result = $end - $start;
return array_merge($ret, array('time' => round($result * 1000, 3)));
}
function user_select_1() {
$sql = "SELECT * FROM {users} WHERE LOWER(name) = LOWER('%s')";
$num = db_num_rows(db_query($sql, 'Paritoh'));
return array('sql' => $sql, 'rows' => $num);
}
function user_select_2() {
$sql = "SELECT * FROM {users} WHERE name = '%s'";
$num = db_num_rows(db_query($sql, strtolower('Paritoh')));
return array('sql' => $sql, 'rows' => $num);
}
function user_select_3() {
$sql = "SELECT * FROM {users} WHERE name = LOWER('%s')";
$num = db_num_rows(db_query($sql, 'Paritoh'));
return array('sql' => $sql, 'rows' => $num);
}
$r = measureit('user_select_1');
print "Time=" . $r['time'] . " Rows=" . $r['rows'] . " SQL=" . $r['sql'] . "\n";
$r = measureit('user_select_3');
print "Time=" . $r['time'] . " Rows=" . $r['rows'] . " SQL=" . $r['sql'] . "\n";
$r = measureit('user_select_2');
print "Time=" . $r['time'] . " Rows=" . $r['rows'] . " SQL=" . $r['sql'] . "\n"; 

Here are the results from this last script:

Results
Time=23.24 Rows=1 SQL=SELECT * FROM {users} WHERE LOWER(name) = LOWER('%s')
Time=0.897 Rows=1 SQL=SELECT * FROM {users} WHERE name = LOWER('%s')
Time=0.805 Rows=1 SQL=SELECT * FROM {users} WHERE name = '%s'

 

Contents: 

Comments

timer api

drupal has a timer api. no need for those small functions. see http://api.drupal.org/api/HEAD/function/timer_start and other timer_ functions.

Formatting

I am aware of those functions, and the ones I wrote are actually derived from them. There was a specific reason for that, but I forget exactly what it was. Perhaps it was formatting decimals for float or something like that.

In any case, here is an example of a script, modified to use Drupal's timer functions:


require_once('./includes/bootstrap.inc');
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

function select_inno() {
db_query("SELECT COUNT(*) FROM {node_inno}");
}

function select_myisam() {
db_query("SELECT COUNT(*) FROM {node}");
}

timer_start('inno');
select_myisam();
$total = timer_stop('inno');

print "InnoDB ". $total['time'] ." ms\n";

timer_start('myisam');
select_myisam();
$total = timer_stop('myisam');

print "MyISAM ". $total['time'] ." ms\n";

--
2bits -- Drupal consulting

Trouble connecting to database.

I used this a few weeks ago and it worked fine for me.

I came back and when I ran the same scrips I was getting the html page that asks if the site is installed because it is unable to attach to the database. That page lists what user it is trying to use and that looks correct. I am able to log into the database with mysql using the credentials specified in the settings.php file within the default site (sites/default). I can also log onto the drupal site using a web browser pointed at localhost/drupal

This machine (localhost) has a few virtual hosts installed and is a MAMP laptop.

Turns out I had to specify the php executable explicitly e.g.
/Applications/MAMP/bin/php5/bin/php.

If I look at what I get by just invoking php I get:

$ which php
/usr/bin/php