~drizzle-trunk/drizzle/development

GetOptions("host=s","db=s","user=s","password=s","loop-count=i","skip-create","skip-in","skip-delete","verbose","fast-insert","lock-tables","debug","fast","force","threads=i") || die "Aborted";

$opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$opt_force=undef; # Ignore warnings from these

print "Test of multiple connections that test the following things:\n";

print "insert, select, delete, update, alter, check, repair and flush\n";

@testtables = ( ["bench_f31", ""],

["bench_f32", "row_format=fixed"],

["bench_f33", "delay_key_write=1"],

["bench_f34", "checksum=1"],

["bench_f35", "delay_key_write=1"]);

$abort_table="bench_f39";

$numtables = $#testtables+1;

srand 100; # Make random numbers repeatable

####

#### Start timeing and start test

####

$start_time=new Benchmark;

$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",

$opt_user, $opt_password,

{ PrintError => 0}) || die $DBI::errstr;

if (!$opt_skip_create)

{

my $table_def;

foreach $table_def (@testtables)

{

my ($table,$extra)= ($table_def->[0], $table_def->[1]);

print "Creating table $table in database $opt_db\n";

$dbh->do("drop table if exists $table");

$dbh->do("create table $table".

" (id int(6) not null auto_increment,".

" info varchar(32)," .

" marker timestamp," .

" flag int not null," .

" primary key(id)) $extra")

or die $DBI::errstr;

# One row in the table will make future tests easier

$dbh->do("insert into $table (id) values (null)")

or die $DBI::errstr;

}

# Create the table we use to signal that we should end the test

$dbh->do("drop table if exists $abort_table");

$dbh->do("create table $abort_table (id int(6) not null) ENGINE=heap") ||

die $DBI::errstr;

}

$dbh->do("delete from $abort_table");

$dbh->disconnect; $dbh=0; # Close handler

$|= 1; # Autoflush

####

#### Start the tests

####

for ($i=0 ; $i < $opt_threads ; $i ++)

{

test_insert() if (($pid=fork()) == 0); $work{$pid}="insert";

}

for ($i=0 ; $i < $numtables ; $i ++)

{

test_insert($i,$i) if (($pid=fork()) == 0); $work{$pid}="insert_one";

}

for ($i=0 ; $i < $opt_threads ; $i ++)

{

test_select() if (($pid=fork()) == 0); $work{$pid}="select_key";

}

test_join() if (($pid=fork()) == 0); $work{$pid}="test_join";

test_select_count() if (($pid=fork()) == 0); $work{$pid}="select_count";

test_delete() if (($pid=fork()) == 0); $work{$pid}="delete";

test_update() if (($pid=fork()) == 0); $work{$pid}="update";

test_flush() if (($pid=fork()) == 0); $work{$pid}= "flush";

test_check() if (($pid=fork()) == 0); $work{$pid}="check";

test_repair() if (($pid=fork()) == 0); $work{$pid}="repair";

test_alter() if (($pid=fork()) == 0); $work{$pid}="alter";

100

#test_database("test2") if (($pid=fork()) == 0); $work{$pid}="check_database";

101

102

print "Started " . ($opt_threads*2+4) . " threads\n";

103

104

$errors=0;

105

$running_insert_threads=$opt_threads+$numtables;

106

while (($pid=wait()) != -1)

107

{

108

$ret=$?/256;

109

print "thread '" . $work{$pid} . "' finished with exit code $ret\n";

110

if ($work{$pid} =~ /^insert/)

111

{

112

if (!--$running_insert_threads)

113

{

114

# Time to stop other threads

115

signal_abort();

116

}

117

}

118

$errors++ if ($ret != 0);

119

}

120

121

122

# Cleanup

123

124

125

if (!$opt_skip_delete && !$errors)

126

{

127

my $table_def;

128

$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",

129

$opt_user, $opt_password,

130

{ PrintError => 0}) || die $DBI::errstr;

131

132

$dbh->do("drop table $abort_table");

133

foreach $table_def (@testtables)

134

{

135

$dbh->do("drop table " . $table_def->[0]);

136

}

137

$dbh->disconnect; $dbh=0; # Close handler

138

}

139

140

print ($errors ? "Test failed\n" :"Test ok\n");

141

$end_time=new Benchmark;

142

print "Total time: " .

143

timestr(timediff($end_time, $start_time),"noc") . "\n";

144

145

exit(0);

146

147

148

149

# Insert records in the table

150

151

152

sub test_insert

153

{

154

my ($from_table,$to_table)= @_;

155

my ($dbh,$i,$j,$count,$table_def,$table);

156

157

if (!defined($from_table))

158

{

159

$from_table=0; $to_table=$numtables-1;

160

}

161

162

$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",

163

$opt_user, $opt_password,

164

{ PrintError => 0}) || die $DBI::errstr;

165

166

for ($i=$count=0 ; $i < $opt_loop_count; $i++)

167

{

168

for ($j= $from_table ; $j <= $to_table ; $j++)

169

{

170

my ($table)= ($testtables[$j]->[0]);

171

$dbh->do("insert into $table values (NULL,'This is entry $i','',0)") || die "Got error on insert: $DBI::errstr\n";

172

$count++;

173

}

174

}

175

$dbh->disconnect; $dbh=0;

176

print "Test_insert: Inserted $count rows\n";

177

exit(0);

178

}

179

180

181

182

# select records

183

# Do continously select over all tables as long as there is changed

184

# rows in the table

185

186

187

sub test_select

188

{

189

my ($dbh, $i, $j, $count, $loop);

190

191

$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",

192

$opt_user, $opt_password,

193

{ PrintError => 0}) || die $DBI::errstr;

194

195

$count_query=make_count_query($numtables);

196

$count=0;

197

$loop=9999;

198

199

$i=0;

200

while (($i++ % 100) || !test_if_abort($dbh))

201

{

202

if ($loop++ >= 100)

203

{

204

$loop=0;

205

$row_counts=simple_query($dbh, $count_query);

206

}

207

for ($j=0 ; $j < $numtables ; $j++)

208

{

209

my ($id)= int rand $row_counts->[$j];

210

my ($table)= $testtables[$j]->[0];

211

simple_query($dbh, "select id,info from $table where id=$id");

212

$count++;

213

}

214

}

215

$dbh->disconnect; $dbh=0;

216

print "Test_select: Executed $count selects\n";

217

exit(0);

218

}

219

220

221

# Do big select count(distinct..) over the table

222

223

224

sub test_select_count

225

{

226

my ($dbh, $i, $j, $count, $loop);

227

228

$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",

229

$opt_user, $opt_password,

230

{ PrintError => 0}) || die $DBI::errstr;

231

232

$count=0;

233

$i=0;

234

while (!test_if_abort($dbh))

235

{

236

for ($j=0 ; $j < $numtables ; $j++)

237

{

238

my ($table)= $testtables[$j]->[0];

239

simple_query($dbh, "select count(distinct marker),count(distinct id),count(distinct info) from $table");

240

$count++;

241

}

242

sleep(20); # This query is quite slow

243

}

244

$dbh->disconnect; $dbh=0;

245

print "Test_select: Executed $count select count(distinct) queries\n";

246

exit(0);

247

}

248

249

250

# select records

251

# Do continously joins between the first and second table

252

253

254

sub test_join

255

{

256

my ($dbh, $i, $j, $count, $loop);

257

258

$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",

259

$opt_user, $opt_password,

260

{ PrintError => 0}) || die $DBI::errstr;

261

262

$count_query=make_count_query($numtables);

263

$count=0;

264

$loop=9999;

265

266

$i=0;

267

while (($i++ % 100) || !test_if_abort($dbh))

268

{

269

if ($loop++ >= 100)

270

{

271

$loop=0;

272

$row_counts=simple_query($dbh, $count_query);

273

}

274

for ($j=0 ; $j < $numtables-1 ; $j++)

275

{

276

my ($id)= int rand $row_counts->[$j];

277

my ($t1,$t2)= ($testtables[$j]->[0],$testtables[$j+1]->[0]);

278

simple_query($dbh, "select $t1.id,$t2.info from $t1, $t2 where $t1.id=$t2.id and $t1.id=$id");

279

$count++;

280

}

281

}

282

$dbh->disconnect; $dbh=0;

283

print "Test_join: Executed $count joins\n";

284

exit(0);

285

}

286

287

288

# Delete 1-5 rows from the first 2 tables.

289

# Test ends when the number of rows for table 3 didn't change during

290

# one loop

291

292

293

sub test_delete

294

{

295

my ($dbh, $i,$j, $row_counts, $count_query, $table_count, $count);

296

297

$table_count=2;

298

$count=0;

299

$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",

300

$opt_user, $opt_password,

301

{ PrintError => 0}) || die $DBI::errstr;

302

303

$count_query=make_count_query($table_count+1);

304

305

sleep(5); # Give time to insert some rows

306

$i=0;

307

while (($i++ % 10) || !test_if_abort($dbh))

308

{

309

sleep(1);

310

$row_counts=simple_query($dbh, $count_query);

311

312

for ($j=0 ; $j < $table_count ; $j++)

313

{

314

my ($id)= int rand $row_counts->[$j];

315

my ($table)= $testtables[$j]->[0];

316

$dbh->do("delete from $table where id >= $id-2 and id <= $id +2") || die "Got error on delete from $table: $DBI::errstr\n";

317

$count++;

318

}

319

}

320

$dbh->disconnect; $dbh=0;

321

print "Test_delete: Executed $count deletes\n";

322

exit(0);

323

}

324

325

326

# Update the flag for table 2 and 3

327

# Will abort after a while when table1 doesn't change max value

328

329

330

sub test_update

331

{

332

my ($dbh, $i, $j, $row_counts, $count_query, $count, $loop);

333

$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",

334

$opt_user, $opt_password,

335

{ PrintError => 0}) || die $DBI::errstr;

336

337

$count_query=make_count_query(3);

338

$loop=9999;

339

$count=0;

340

341

sleep(5); # Give time to insert some rows

342

$i=0;

343

while (($i++ % 100) || !test_if_abort($dbh))

344

{

345

if ($loop++ >= 100)

346

{

347

$loop=0;

348

$row_counts=simple_query($dbh, $count_query);

349

}

350

351

for ($j=1 ; $j <= 2 ; $j++)

352

{

353

my ($id)= int rand $row_counts->[$j];

354

my ($table)= $testtables[$j]->[0];

355

# Fix to not change the same rows as the above delete

356

$id= ($id + $count) % $row_counts->[$j];

357

358

$dbh->do("update $table set flag=flag+1 where id >= $id-2 and id <= $id +2") || die "Got error on update of $table: $DBI::errstr\n";

359

$count++;

360

}

361

}

362

$dbh->disconnect; $dbh=0;

363

print "Test_update: Executed $count updates\n";

364

exit(0);

365

}

366

367

368

369

# Run a check on all tables except the last one

370

# (The last one is not checked to put pressure on the key cache)

371

372

373

sub test_check

374

{

375

my ($dbh, $row, $i, $j, $type, $table);

376

$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",

377

$opt_user, $opt_password,

378

{ PrintError => 0}) || die $DBI::errstr;

379

380

$type= "check";

381

for ($i=$j=0 ; !test_if_abort($dbh) ; $i++)

382

{

383

sleep(1000);

384

$table=$testtables[$j]->[0];

385

$sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n";

386

$sth->execute || die $DBI::errstr;

387

388

while (($row=$sth->fetchrow_arrayref))

389

{

390

if ($row->[3] ne "OK")

391

{

392

print "Got error " . $row->[3] . " when doing $type on $table\n";

393

exit(1);

394

}

395

}

396

if (++$j == $numtables-1)

397

{

398

$j=0;

399

}

400

}

401

$dbh->disconnect; $dbh=0;

402

print "test_check: Executed $i checks\n";

403

exit(0);

404

}

405

406

407

# Do a repair on the first table once in a while

408

409

410

sub test_repair

411

{

412

my ($dbh, $row, $i, $type, $table);

413

$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",

414

$opt_user, $opt_password,

415

{ PrintError => 0}) || die $DBI::errstr;

416

417

$type= "repair";

418

for ($i=0 ; !test_if_abort($dbh) ; $i++)

419

{

420

sleep(2000);

421

$table=$testtables[0]->[0];

422

$sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n";

423

$sth->execute || die $DBI::errstr;

424

425

while (($row=$sth->fetchrow_arrayref))

426

{

427

if ($row->[3] ne "OK")

428

{

429

print "Got error " . $row->[3] . " when doing $type on $table\n";

430

exit(1);

431

}

432

}

433

}

434

$dbh->disconnect; $dbh=0;

435

print "test_repair: Executed $i repairs\n";

436

exit(0);

437

}

438

439

440

# Do a flush tables on table 3 and 4 once in a while

441

442

443

sub test_flush

444

{

445

my ($dbh,$count,$tables);

446

447

$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",

448

$opt_user, $opt_password,

449

{ PrintError => 0}) || die $DBI::errstr;

450

451

$tables=$testtables[2]->[0] . "," . $testtables[3]->[0];

452

453

$count=0;

454

while (!test_if_abort($dbh))

455

{

456

sleep(3000);

457

$dbh->do("flush tables $tables") ||

458

die "Got error on flush $DBI::errstr\n";

459

$count++;

460

}

461

$dbh->disconnect; $dbh=0;

462

print "flush: Executed $count flushs\n";

463

exit(0);

464

}

465

466

467

468

# Test all tables in a database

469

470

471

sub test_database

472

{

473

my ($database) = @_;

474

my ($dbh, $row, $i, $type, $tables);

475

$dbh = DBI->connect("DBI:mysql:$database:$opt_host",

476

$opt_user, $opt_password,

477

{ PrintError => 0}) || die $DBI::errstr;

478

479

$tables= join(',',$dbh->func('_ListTables'));

480

$type= "check";

481

for ($i=0 ; !test_if_abort($dbh) ; $i++)

482

{

483

sleep(120);

484

$sth=$dbh->prepare("$type table $tables") || die "Got error on prepare: $DBI::errstr\n";

485

$sth->execute || die $DBI::errstr;

486

487

while (($row=$sth->fetchrow_arrayref))

488

{

489

if ($row->[3] ne "OK")

490

{

491

print "Got error " . $row->[2] . " " . $row->[3] . " when doing $type on " . $row->[0] . "\n";

492

exit(1);

493

}

494

}

495

}

496

$dbh->disconnect; $dbh=0;

497

print "test_check: Executed $i checks\n";

498

exit(0);

499

}

500

501

502

# Test ALTER TABLE on the second table

503

504

505

sub test_alter

506

{

507

my ($dbh, $row, $i, $type, $table);

508

$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",

509

$opt_user, $opt_password,

510

{ PrintError => 0}) || die $DBI::errstr;

511

512

for ($i=0 ; !test_if_abort($dbh) ; $i++)

513

{

514

sleep(100);

515

$table=$testtables[1]->[0];

516

$sth=$dbh->prepare("ALTER table $table modify info char(32)") || die "Got error on prepare: $DBI::errstr\n";

517

$sth->execute || die $DBI::errstr;

518

}

519

$dbh->disconnect; $dbh=0;

520

print "test_alter: Executed $i ALTER TABLE\n";

521

exit(0);

522

}

523

524

525

526

# Help functions

527

528

529

sub signal_abort

530

{

531

my ($dbh);

532

$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",

533

$opt_user, $opt_password,

534

{ PrintError => 0}) || die $DBI::errstr;

535

536

$dbh->do("insert into $abort_table values(1)") || die $DBI::errstr;

537

$dbh->disconnect; $dbh=0;

538

exit(0);

539

}

540

541

542

sub test_if_abort()

543

{

544

my ($dbh)=@_;

545

$row=simple_query($dbh,"select * from $opt_db.$abort_table");

546

return (defined($row) && defined($row->[0]) != 0) ? 1 : 0;

547

}

548

549

550

sub make_count_query

551

{

552

my ($table_count)= @_;

553

my ($tables, $count_query, $i, $tables_def);

554

$tables="";

555

$count_query="select high_priority ";

556

$table_count--;

557

for ($i=0 ; $i < $table_count ; $i++)

558

{

559

my ($table_def)= $testtables[$i];

560

$tables.=$table_def->[0] . ",";

561

$count_query.= "max(" . $table_def->[0] . ".id),";

562

}

563

$table_def=$testtables[$table_count];

564

$tables.=$table_def->[0];

565

$count_query.= "max(" . $table_def->[0] . ".id) from $tables";

566

return $count_query;

567

}

568

569

sub simple_query()

570

{

571

my ($dbh, $query)= @_;

572

my ($sth,$row);

573

574

$sth=$dbh->prepare($query) || die "Got error on '$query': " . $dbh->errstr . "\n";

575

$sth->execute || die "Got error on '$query': " . $dbh->errstr . "\n";

576

$row= $sth->fetchrow_arrayref();

577

$sth=0;

578

return $row;

579

}

Older »