1-3% performance regression with 5.1-micro

[Philip Stoev]

As compared to mysql-5.1.54, 5.1-micro exhibits a repeatable performance degradation of 1 to 3%.

The following scenarios were tested:

* inserting rows into a table that contains 6 datetime/timestamp/time fields with precision 0
* selecting individual rows based on key lookup of a datetime column
* selecting the entire table via a full table scan

Both versions were compiled with ./BUILD/compile-pentium-max and then started with

sh -c "cd /home/philips/bzr/mysql-5.1-micro-release/mysql-test && MTR_VERSION=1 perl mysql-test-run.pl --start-and-exit 1st --master_port=3306 --mysqld=--skip-grant-tables --mysqld=--log-output=none"
sh -c "cd /home/philips/bzr/mysql-5.1.54/mysql-test && MTR_VERSION=1 perl mysql-test-run.pl --start-and-exit 1st --master_port=3308 --mysqld=--skip-grant-tables --mysqld=--log-output=none"

Test script:

use strict;
use DBI;
use Time::HiRes;

my $rows = 60000;
my $selects = 10000;
my $cycles = 5;
my @ports = (3306, 3308);
my @precisions = (0);

foreach my $port (@ports) {
        print "Benchmarking port $port\n";
        foreach my $precision (@precisions) {
                print "Benchmarking precision $precision\n";
                my $precision_string = "($precision)" if $precision > 0;
                foreach my $cycle (1..$cycles) {
                        my $dbh = DBI->connect("dbi:mysql:host=127.0.0.1:port=$port:database=test", 'root', undef);
                        $dbh->do("DROP TABLE IF EXISTS t1");
                        my $now = $dbh->selectrow_array("SELECT NOW()");
                        $dbh->do("CREATE TABLE t1 (ts_key TIMESTAMP$precision_string, ts_nokey TIMESTAMP$precision_string, dt_key DATETIME$precision_string, dt_nokey DATETIME$precision_string, t_key TIME$precision_string, t_nokey TIME$precision_string, KEY(ts_key), KEY(dt_key), KEY (t_key)) ENGINE=MyISAM");

                        my $start_insert = Time::HiRes::time();
                        foreach my $i (1..$rows) {
                                my $value = "DATE_ADD( '$now' , INTERVAL $i SECOND )";
                                $dbh->do("INSERT INTO t1 VALUES ( $value, $value, $value, $value, $value, $value)");
                        }
                        my $end_insert = Time::HiRes::time();
                        my $duration_insert = $end_insert - $start_insert;
                        print "INSERT time for port $port, precision $precision, cycle $cycle: $duration_insert\n";

                        my $start_select1 = Time::HiRes::time();
                        foreach my $i (1..$selects) {
                                $dbh->selectall_arrayref("SELECT SQL_NO_CACHE * FROM t1 FORCE KEY (dt_key) WHERE dt_key = DATE_ADD( '$now' , INTERVAL 1 MINUTE )");
                        }
                        my $end_select1 = Time::HiRes::time();
                        my $duration_select1 = $end_select1 - $start_select1;
                        print "SELECT1 time for port $port, precision $precision, cycle $cycle: $duration_select1\n";

                        my $start_select2 = Time::HiRes::time();
                        foreach my $i (1..($selects/100)) {
                                $dbh->selectall_arrayref("SELECT SQL_NO_CACHE * FROM t1");
                        }
                        my $end_select2 = Time::HiRes::time();
                        my $duration_select2 = $end_select2 - $start_select2;
                        print "SELECT2 time for port $port, precision $precision, cycle $cycle: $duration_select2\n";
                        $dbh->do("DROP TABLE IF EXISTS t1");
                }
        }
}

Sample output:

Benchmarking port 3306
Benchmarking precision 0
INSERT time for port 3306, precision 0, cycle 1: 19.8615231513977
SELECT1 time for port 3306, precision 0, cycle 1: 5.81605505943298
SELECT2 time for port 3306, precision 0, cycle 1: 56.4764399528503
INSERT time for port 3306, precision 0, cycle 2: 20.8059520721436
SELECT1 time for port 3306, precision 0, cycle 2: 5.92798614501953
SELECT2 time for port 3306, precision 0, cycle 2: 56.6977560520172
INSERT time for port 3306, precision 0, cycle 3: 20.1634759902954
SELECT1 time for port 3306, precision 0, cycle 3: 5.81903505325317
SELECT2 time for port 3306, precision 0, cycle 3: 56.8458869457245
INSERT time for port 3306, precision 0, cycle 4: 20.905021905899
SELECT1 time for port 3306, precision 0, cycle 4: 5.80147504806519
SELECT2 time for port 3306, precision 0, cycle 4: 56.9855148792267
INSERT time for port 3306, precision 0, cycle 5: 21.4851200580597
SELECT1 time for port 3306, precision 0, cycle 5: 5.851646900177
SELECT2 time for port 3306, precision 0, cycle 5: 57.0004291534424
Benchmarking port 3308
Benchmarking precision 0
INSERT time for port 3308, precision 0, cycle 1: 20.400643825531
SELECT1 time for port 3308, precision 0, cycle 1: 5.79694509506226
SELECT2 time for port 3308, precision 0, cycle 1: 55.2143778800964
INSERT time for port 3308, precision 0, cycle 2: 20.6638879776001
SELECT1 time for port 3308, precision 0, cycle 2: 5.77916097640991
SELECT2 time for port 3308, precision 0, cycle 2: 55.1443750858307
INSERT time for port 3308, precision 0, cycle 3: 20.4039659500122
SELECT1 time for port 3308, precision 0, cycle 3: 5.69990205764771
SELECT2 time for port 3308, precision 0, cycle 3: 54.2140741348267
INSERT time for port 3308, precision 0, cycle 4: 19.715136051178
SELECT1 time for port 3308, precision 0, cycle 4: 5.67466902732849
SELECT2 time for port 3308, precision 0, cycle 4: 54.381961107254
INSERT time for port 3308, precision 0, cycle 5: 20.8377220630646
SELECT1 time for port 3308, precision 0, cycle 5: 5.80834484100342
SELECT2 time for port 3308, precision 0, cycle 5: 54.8164939880371

average for the SELECT2 test for 5.1-micro : 56,80 seconds , average for mysql 5.1.54: 54,75 seconds

[Sergei Golubchik (sergii)]

I cannot reliably repeat it.

INSERT is few per cent faster in the 5.1-micro, SELECT1 too, SELECT2 a bit slower.
But all differences are below the standard deviation.