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'