Performance measurements

[Bradley Baetz]

On Mon, 4 Nov 2002, Gervase Markham wrote:

> Bradley Baetz wrote:
> > When you run teh query from mysql it shows the time taken at the end :)
> > 
> > I have a script to generate n bugs; I'll tidy it up and attach it this 
> > evening.
> 
> Ping? :-)


Umm, er... Attached.  Its not a full schema, but its reasonably easy to 
extend.

Note that I was using the same basic script for pg, too, so it does 
indiviual INSERTs, which may take quite a while if you make the numbers 
too big.

Thre are a few off-by-one errors in there, but thats not important for our 
purposes.

Bradley

-------------- next part --------------
#!/usr/bin/perl -w

use strict;

my $numUsers = 30000;
my $numProducts = 1;
my $numBugs = 10000;
my $numCcs = 100000;
my $numLongDescs = 100000;

print "BEGIN;\n";

print "CREATE TABLE profiles (
  userid integer NOT NULL auto_increment,
  login_name varchar(255),
  PRIMARY KEY (userid)
);
";

my %users;
foreach my $i (1..$numUsers) {
    my $username = '';
    foreach my $x (1..rand(9)+1) {
        $username .= chr(65+int(rand(26)));
    }
    redo if ($users{$username});
    $users{$username} = 1;
    print "INSERT INTO profiles(login_name) VALUES ('$username');\n";
}

print "
CREATE UNIQUE INDEX profiles_login_name_idx ON profiles(login_name);
";

print "CREATE TABLE products (
  id integer NOT NULL auto_increment,
  name varchar(64) NOT NULL,
  PRIMARY KEY (id)
);
";

my %products;
foreach my $i (1..$numProducts) {
    my $prod = '';
    foreach my $x (1..rand(9)+1) {
        $prod .= chr(65+int(rand(26)));
    }
    redo if ($products{$prod});
    $products{$prod} = 1;
    print "INSERT INTO products(name) VALUES ('$prod');\n";
}

print "
CREATE UNIQUE INDEX products_name_idx ON products(name);
";

print "CREATE TABLE bugs (
  bug_id integer NOT NULL auto_increment,
  product_id integer NOT NULL, -- XXX REFERENCES products(id),
  assigned_to integer NOT NULL, -- REFERENCES profiles(userid),
  reporter integer NOT NULL, -- REFERENCES profiles(userid),
  PRIMARY KEY (bug_id)
);
";

foreach my $i (1..$numBugs) {
    print "INSERT INTO bugs(product_id, assigned_to, reporter) VALUES (" .
      (int(rand($numProducts-1))+1) . "," .
      (int(rand($numUsers-1)) + 1) . "," .
      (int(rand($numUsers-1)) + 1) .
      ");\n";
}

print "
CREATE INDEX bugs_product_id_idx ON bugs(product_id);
CREATE INDEX bugs_assigned_to_idx ON bugs(assigned_to);
CREATE INDEX bugs_reporter_idx ON bugs(reporter);
";

print "CREATE TABLE cc (
  bug_id integer NOT NULL, -- REFERENCES bugs(bug_id),
  who integer NOT NULL -- REFERENCES profiles(userid)
);
";

my %bugs;
foreach my $i (1..$numCcs) {
    my $bug = int(rand($numBugs-1)) + 1;
    my $user = int(rand($numUsers-1)) + 1;
    if (defined($bugs{$bug}) && $bugs{$bug}{$user}) {
        redo;
    }
    $bugs{$bug}{$user} = 1;
    print "INSERT INTO cc(bug_id, who) VALUES ($bug,$user);\n";
}

print "
CREATE INDEX cc_who_idx ON cc(who);
CREATE UNIQUE INDEX cc_bug_id_who_idx ON cc(bug_id,who);
";

print "CREATE TABLE longdescs (
  bug_id integer NOT NULL, -- REFERENCES bugs(bug_id),
  who integer NOT NULL -- REFERENCES profiles(userid)
);
";

foreach my $i (1..$numLongDescs) {
    my $bug = int(rand($numBugs-1)) + 1;
    my $user = int(rand($numUsers-1)) + 1;
    print "INSERT INTO longdescs(bug_id, who) VALUES ($bug,$user);\n";
}

print "
CREATE INDEX longdescs_bug_id_idx ON longdescs(bug_id);
CREATE INDEX longdescs_who_idx ON longdescs(who);
";

print "COMMIT;\n";