1#!/usr/bin/perl
2# Copyright (c) 2000-2003, 2006 MySQL AB, 2009 Sun Microsystems, Inc.
3# Use is subject to license terms.
4#
5# This library is free software; you can redistribute it and/or
6# modify it under the terms of the GNU Library General Public
7# License as published by the Free Software Foundation; version 2
8# of the License.
9#
10# This library is distributed in the hope that it will be useful,
11# but WITHOUT ANY WARRANTY; without even the implied warranty of
12# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
13# Library General Public License for more details.
14#
15# You should have received a copy of the GNU Library General Public
16# License along with this library; if not, write to the Free
17# Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston,
18# MA 02110-1301, USA
19#
20# This test is for testing how long it takes to create tables,
21# make a count(*) from them and finally drop the tables. These
22# commands will be done in different ways in this test.
23# Using option --fast will drop all the tables in the end
24# of this test with one command instead of making own
25# 'drop' command for each and every table.
26# By changing the variable '$table_amount' value you can make
27# this test a lot harder/easier for your computer to drive.
28# Note that when using value bigger than 64 for this variable
29# will do 'drop table'-command	in totally different way because of that
30# how MySQL handles these commands.
31
32##################### Standard benchmark inits ##############################
33
34use Cwd;
35use DBI;
36use Benchmark;
37
38$opt_loop_count=10000; # Change this to make test harder/easier
39# This is the default value for the amount of tables used in this test.
40
41$pwd = cwd(); $pwd = "." if ($pwd eq '');
42require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
43
44$create_loop_count=$opt_loop_count;
45if ($opt_small_test)
46{
47  $opt_loop_count/=100;
48  $create_loop_count/=1000;
49}
50
51$max_tables=min($limits->{'max_tables'},$opt_loop_count);
52
53if ($opt_small_test)
54{
55  $max_tables=10;
56}
57
58
59print "Testing the speed of creating and dropping tables\n";
60print "Testing with $max_tables tables and $opt_loop_count loop count\n\n";
61
62####
63####  Connect and start timeing
64####
65
66$dbh = $server->connect();
67
68### Test how the database can handle many tables
69### Create $max_tables ; Access all off them with a simple query
70### and then drop the tables
71
72if ($opt_force) # If tables used in this test exist, drop 'em
73{
74  print "Okay..Let's make sure that our tables don't exist yet.\n\n";
75  for ($i=1 ; $i <= $max_tables ; $i++)
76  {
77    $dbh->do("drop table bench_$i" . $server->{'drop_attr'});
78  }
79}
80
81if ($opt_fast && defined($server->{vacuum}))
82{
83  $server->vacuum(1,\$dbh);
84}
85
86print "Testing create of tables\n";
87
88$loop_time=$start_time=new Benchmark;
89
90for ($i=1 ; $i <= $max_tables ; $i++)
91{
92  if (do_many($dbh,$server->create("bench_$i",
93				   ["i int NOT NULL",
94				    "d double",
95				    "f float",
96				    "s char(10)",
97				    "v varchar(100)"],
98				   ["primary key (i)"])))
99  {
100    # Got an error; Do cleanup
101    for ($i=1 ; $i <= $max_tables ; $i++)
102    {
103      $dbh->do("drop table bench_$i" . $server->{'drop_attr'});
104    }
105    die "Test aborted";
106  }
107}
108
109$end_time=new Benchmark;
110print "Time for create_MANY_tables ($max_tables): " .
111  timestr(timediff($end_time, $loop_time),"all") . "\n\n";
112
113if ($opt_fast && defined($server->{vacuum}))
114{
115  $server->vacuum(1,\$dbh);
116}
117
118#### Here comes $max_tables couples of cont(*) to the tables.
119#### We'll check how long it will take...
120####
121
122print "Accessing tables\n";
123
124if ($limits->{'group_functions'})
125{
126  $query="select count(*) from ";
127  $type="select_group_when_MANY_tables";
128}
129else
130{
131  $query="select * from ";
132  $type="select_when_MANY_tables";
133}
134
135$loop_time=new Benchmark;
136for ($i=1 ; $i <= $max_tables ; $i++)
137{
138  $sth = $dbh->do("$query bench_$i") or die $DBI::errstr;
139}
140
141$end_time=new Benchmark;
142print "Time to $type ($max_tables): " .
143    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
144
145####
146#### Now we are going to drop $max_tables tables;
147####
148
149print "Testing drop\n";
150
151$loop_time=new Benchmark;
152
153if ($opt_fast && $server->{'limits'}->{'multi_drop'} &&
154    $server->{'limits'}->{'query_size'} > 11+$max_tables*10)
155{
156  my $query="drop table bench_1";
157  for ($i=2 ; $i <= $max_tables ; $i++)
158  {
159    $query.=",bench_$i";
160  }
161  $sth = $dbh->do($query . $server->{'drop_attr'}) or die $DBI::errstr;
162}
163else
164{
165  for ($i=1 ; $i <= $max_tables ; $i++)
166  {
167    $sth = $dbh->do("drop table bench_$i" . $server->{'drop_attr'})
168      or die $DBI::errstr;
169  }
170}
171
172
173$end_time=new Benchmark;
174print "Time for drop_table_when_MANY_tables ($max_tables): " .
175    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
176
177if ($opt_fast && defined($server->{vacuum}))
178{
179  $server->vacuum(1,\$dbh);
180}
181
182#### We'll do first one 'create table' and then we'll drop it
183#### away immediately. This loop shall be executed $opt_loop_count
184#### times.
185
186print "Testing create+drop\n";
187
188$loop_time=new Benchmark;
189
190for ($i=1 ; $i <= $create_loop_count ; $i++)
191{
192  do_many($dbh,$server->create("bench_$i",
193			       ["i int NOT NULL",
194				"d double",
195				"f float",
196				"s char(10)",
197				"v varchar(100)"],
198			       ["primary key (i)"]));
199  $sth = $dbh->do("drop table bench_$i" . $server->{'drop_attr'}) or die $DBI::errstr;
200}
201
202$end_time=new Benchmark;
203print "Time for create+drop ($create_loop_count): " .
204    timestr(timediff($end_time, $loop_time),"all") . "\n";
205
206if ($opt_fast && defined($server->{vacuum}))
207{
208  $server->vacuum(1,\$dbh);
209}
210
211#
212# Same test, but with a table with many keys
213#
214
215my @fields=(); my @keys=();
216$keys=min($limits->{'max_index'},16);		# 16 is more than enough
217$seg= min($limits->{'max_index_parts'},$keys,16);	# 16 is more than enough
218
219# Make keys on the most important types
220@types=(0,0,0,1,0,0,0,1,1,1,1,1,1,1,1,1,1);	# A 1 for each char field
221push(@fields,"field1 tinyint not null");
222push(@fields,"field2 mediumint not null");
223push(@fields,"field3 smallint not null");
224push(@fields,"field4 char(16) not null");
225push(@fields,"field5 integer not null");
226push(@fields,"field6 float not null");
227push(@fields,"field7 double not null");
228for ($i=8 ; $i <= $keys ; $i++)
229{
230  push(@fields,"field$i char(5) not null");	# Should be relatively fair
231}
232
233# Let first key contain many segments
234my $query="primary key (";
235for ($i= 1 ; $i <= $seg ; $i++)
236{
237  $query.= "field$i,";
238}
239substr($query,-1)=")";
240push (@keys,$query);
241
242#Create other keys
243for ($i=2 ; $i <= $keys ; $i++)
244{
245  push(@keys,"index index$i (field$i)");
246}
247
248$loop_time=new Benchmark;
249for ($i=1 ; $i <= $opt_loop_count ; $i++)
250{
251  do_many($dbh,$server->create("bench_$i", \@fields, \@keys));
252  $dbh->do("drop table bench_$i" . $server->{'drop_attr'}) or die $DBI::errstr;
253}
254
255$end_time=new Benchmark;
256print "Time for create_key+drop ($opt_loop_count): " .
257    timestr(timediff($end_time, $loop_time),"all") . "\n";
258
259if ($opt_fast && defined($server->{vacuum}))
260{
261  $server->vacuum(1,\$dbh);
262}
263
264####
265#### End of benchmark
266####
267
268$dbh->disconnect;				# close connection
269end_benchmark($start_time);
270