1#!/usr/bin/perl -w
2
3# Copyright (C) 2000, 2001 MySQL AB
4# Use is subject to license terms
5#
6# This program is free software; you can redistribute it and/or modify
7# it under the terms of the GNU General Public License, version 2.0,
8# as published by the Free Software Foundation.
9#
10# This program is also distributed with certain software (including
11# but not limited to OpenSSL) that is licensed under separate terms,
12# as designated in a particular file or component or in included license
13# documentation.  The authors of MySQL hereby grant you an additional
14# permission to link the program and your derivative works with the
15# separately licensed software that they have included with MySQL.
16#
17# This program is distributed in the hope that it will be useful,
18# but WITHOUT ANY WARRANTY; without even the implied warranty of
19# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
20# GNU General Public License, version 2.0, for more details.
21#
22# You should have received a copy of the GNU General Public License
23# along with this program; if not, write to the Free Software
24# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
25
26# This is a test with uses 5 processes to insert, update and select from
27# two tables.
28# One inserts records in the tables, one updates some record in it and
29# the last 3 does different selects on the tables.
30# Er, hmmm..., something like that :^)
31# Modified to do crazy-join, � la Nasdaq.
32#
33# This test uses the old obsolete mysql interface. For a test that uses
34# DBI, please take a look at fork_big.pl
35
36$opt_loop_count=10000; # Change this to make test harder/easier
37
38##################### Standard benchmark inits ##############################
39
40use Mysql;
41use Getopt::Long;
42use Benchmark;
43
44package main;
45
46$opt_skip_create=$opt_skip_in=$opt_verbose=$opt_fast_insert=
47  $opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=0;
48$opt_host=""; $opt_db="test";
49
50GetOptions("host=s","db=s","loop-count=i","skip-create","skip-in",
51	   "skip-delete", "verbose","fast-insert","lock-tables","debug","fast",
52	   "force") || die "Aborted";
53$opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$Mysql::db_errstr=$opt_force=undef;  # Ignore warnings from these
54
55print "Testing 10 multiple connections to a server with 1 insert/update\n";
56print "and 8 select connections and one ALTER TABLE.\n";
57
58
59@testtables = qw(bench_f21 bench_f22 bench_f23 bench_f24 bench_f25);
60$numtables = $#testtables;	# make emacs happier
61$dtable = "directory";
62####
63####  Start timeing and start test
64####
65
66$start_time=new Benchmark;
67if (!$opt_skip_create)
68{
69  $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr;
70  $Mysql::QUIET = 1;
71  foreach $table (@testtables) {
72      $dbh->Query("drop table $table");
73  }
74  $dbh->Query("drop table $dtable");
75  $Mysql::QUIET = 0;
76
77  foreach $table (@testtables) {
78      print "Creating table $table in database $opt_db\n";
79      $dbh->Query("create table $table".
80		  " (id int(6) not null,".
81		  " info varchar(32),".
82		  " marker timestamp,".
83		  " primary key(id))")
84	  or die $Mysql::db_errstr;
85  }
86  print "Creating directory table $dtable in $opt_db\n";
87  $dbh->Query("create table $dtable (id int(6), last int(6))")
88      or die $Mysql::db_errstr;
89  # Populate directory table
90  for $i ( 0 .. $numtables ) {
91      $dbh->Query("insert into $dtable values($i, 0)");
92  }
93  $dbh=0;			# Close handler
94}
95$|= 1;				# Autoflush
96
97####
98#### Start the tests
99####
100
101#$test_index = 0;
102
103test_1() if (($pid=fork()) == 0); $work{$pid}="insert";
104test_2() if (($pid=fork()) == 0); $work{$pid}="simple1";
105test_3() if (($pid=fork()) == 0); $work{$pid}="funny1";
106test_2() if (($pid=fork()) == 0); $work{$pid}="simple2";
107test_3() if (($pid=fork()) == 0); $work{$pid}="funny2";
108test_2() if (($pid=fork()) == 0); $work{$pid}="simple3";
109test_3() if (($pid=fork()) == 0); $work{$pid}="funny3";
110test_2() if (($pid=fork()) == 0); $work{$pid}="simple4";
111test_3() if (($pid=fork()) == 0); $work{$pid}="funny4";
112alter_test() if (($pid=fork()) == 0); $work{$pid}="alter";
113
114$errors=0;
115while (($pid=wait()) != -1)
116{
117  $ret=$?/256;
118  print "thread '" . $work{$pid} . "' finished with exit code $ret\n";
119  $errors++ if ($ret != 0);
120}
121
122if (!$opt_skip_delete && !$errors)
123{
124  $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr;
125  foreach $table (@testtables) {
126      $dbh->Query("drop table $table");
127  }
128}
129print ($errors ? "Test failed\n" :"Test ok\n");
130
131$end_time=new Benchmark;
132print "Total time: " .
133  timestr(timediff($end_time, $start_time),"noc") . "\n";
134
135exit(0);
136
137#
138# Insert records in the ?? tables the Nasdaq way
139#
140
141sub test_1
142{
143    my ($dbh,$table,$tmpvar,$rows,$found,$i);
144
145    $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr;
146    $tmpvar=1;
147    $rows=$found=0;
148    for ($i=0 ; $i < $opt_loop_count; $i++)
149    {
150	$tmpvar^= ((($tmpvar + 63) + $i)*3 % $numtables);
151	# Nasdaq step 1:
152	$sth=$dbh->Query("select id,last from $dtable where id='$tmpvar'")
153	    or die "Select directory row: $Mysql::db_errstr\n";
154	# Nasdaq step 2:
155	my ($did,$dlast) = $sth->FetchRow
156	    or die "Fetch directory row: $Mysql::db_errstr\n";
157	$dlast++;
158	$sth=$dbh->Query("INSERT into $testtables[$did]".
159			 " VALUES($dlast,'This is entry $dlast',NULL)")
160	    || die "Got error on insert table $testtable[$did]:".
161		" $Mysql::db_errstr\n";
162	# Nasdaq step 3 - where my application hangs
163	$sth=$dbh->Query("update $dtable set last='$dlast' where id='$tmpvar'")
164	    or die "Updating directory for table $testtable[$did]:".
165		" Mysql::db_errstr\n";
166	$rows++;
167    }
168    $dbh=0;
169    print "Test_1: Inserted $rows rows\n";
170    exit(0);
171}
172
173#
174# Nasdaq simple select
175#
176
177sub test_2
178{
179    my ($dbh,$id,$tmpvar,$rows,$found,$i);
180
181    $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr;
182    $rows=$found=0;
183    $tmpvar=1;
184    for ($i=0 ; $i < $opt_loop_count ; $i++)
185    {
186	$tmpvar^= ((($tmpvar + 63) + $i)*3 % $numtables);
187	$sth=$dbh->Query("select a.id,a.info from $testtables[$tmpvar] as a,".
188			 "$dtable as d".
189			 " where a.id=d.last and $i >= 0")
190	    || die "Got error select max: $Mysql::db_errstr\n";
191	if ((@row = $sth->FetchRow()) && defined($row[0]))
192	{
193	    $found++;
194	}
195    }
196    $dbh=0;
197    print "Test_2: Found $found rows\n";
198    exit(0);
199}
200
201
202#
203# Nasdaq not-so-simple select
204#
205
206sub test_3
207{
208    my ($dbh,$id,$tmpvar,$rows,$i);
209    $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr;
210    $rows=0;
211    $tmpvar ||= $numtables;
212    for ($i=0 ; $i < $opt_loop_count ; $i++)
213    {
214	$tmpvar^= ((($tmpvar + 63) + $i)*3 % $numtables);
215	$id1 = ($tmpvar+1) % $numtables;
216	$id2 = ($id1+1) % $numtables;
217	$id3 = ($id2+1) % $numtables;
218	$sth = $dbh->Query("SELECT greatest(a.id, b.id, c.id), a.info".
219			   " FROM $testtables[$id1] as a,".
220			   " $testtables[$id2] as b,".
221			   " $testtables[$id3] as c,".
222			   " $dtable as d1, $dtable as d2, $dtable as d3".
223			   " WHERE ".
224			   " d1.last=a.id AND d2.last=b.id AND d3.last=c.id".
225			   " AND d1.id='$id1' AND d2.id='$id2'".
226			   " AND d3.id='$id3'")
227	    or die "Funny select: $Mysql::db_errstr\n";
228	$rows+=$sth->numrows;
229    }
230    $dbh=0;
231    print "Test_3: Found $rows rows\n";
232    exit(0);
233}
234
235#
236# Do an ALTER TABLE every 20 seconds
237#
238
239sub alter_test
240{
241    my ($dbh,$count,$old_row_count,$row_count,$id,@row,$sth);
242
243    $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr;
244    $id=$count=$row_count=0; $old_row_count= -1;
245
246    # Execute the test as long as we get more data into the table
247    while ($row_count != $old_row_count)
248    {
249      sleep(10);
250      $sth=$dbh->Query("ALTER TABLE $testtables[$id] modify info varchar(32)") or die "Couldn't execute ALTER TABLE\n";
251      $sth=0;
252      $id=($id+1) % $numtables;
253
254      # Test if insert test has ended
255      $sth=$dbh->query("select count(*) from $testtables[0]") or die "Couldn't execute count(*)\n";
256      @row = $sth->FetchRow();
257      $old_row_count= $row_count;
258      $row_count=$row[0];
259      $count++;
260    }
261    $dbh=0;
262    print "alter: Executed $count ALTER TABLE commands\n";
263    exit(0);
264}
265