~drizzle-trunk/drizzle/development

is a diagnostic program designed to emulate client load for a MySQL server and to report the timing of each stage. It works as if multiple clients are accessing the server.

.PP

Invoke

\fBmysqlslap\fR

like this:

.sp

.RS 3n

.nf

shell> \fBmysqlslap [\fR\fB\fIoptions\fR\fR\fB]\fR

.fi

.RE

.PP

Some options such as

\fB\-\-create\fR

\fB\-\-query\fR

enable you to specify a string containing an SQL statement or a file containing statements. If you specify a file, by default it must contain one statement per line. (That is, the implicit statement delimiter is the newline character.) Use the

\fB\-\-delimiter\fR

option to specify a different delimiter, which enables you to specify statements that span multiple lines or place multiple statements on a single line. You cannot include comments in a file;

\fBmysqlslap\fR

does not understand them.

.PP

\fBmysqlslap\fR

runs in three stages:

.TP 3n

Create schema, table, and optionally any stored programs or data you want to using for the test. This stage uses a single client connection.

.TP 3n

Run the load test. This stage can use many client connections.

.TP 3n

Clean up (disconnect, drop table if specified). This stage uses a single client connection.

.sp

.RE

.PP

Examples:

.PP

Supply your own create and query SQL statements, with 50 clients querying and 200 selects for each:

.sp

.RS 3n

.nf

mysqlslap \-\-delimiter=";" \\

\-\-create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" \\

\-\-query="SELECT * FROM a" \-\-concurrency=50 \-\-iterations=200

.fi

.RE

.PP

Let

\fBmysqlslap\fR

build the query SQL statement with a table of two

INT

columns and three

VARCHAR

columns. Use five clients querying 20 times each. Do not create the table or insert the data (that is, use the previous test's schema and data):

.sp

.RS 3n

.nf

mysqlslap \-\-concurrency=5 \-\-iterations=20 \\

\-\-number\-int\-cols=2 \-\-number\-char\-cols=3 \\

\-\-auto\-generate\-sql

.fi

.RE

.PP

Tell the program to load the create, insert, and query SQL statements from the specified files, where the

\fIcreate.sql\fR

file has multiple table creation statements delimited by

\';'

and multiple insert statements delimited by

\';'. The

\fB\-\-query\fR

file will have multiple queries delimited by

\';'. Run all the load statements, then run all the queries in the query file with five clients (five times each):

.sp

.RS 3n

.nf

mysqlslap \-\-concurrency=5 \\

\-\-iterations=5 \-\-query=query.sql \-\-create=create.sql \\

\-\-delimiter=";"

100

.fi

101

.RE

102

.PP

103

\fBmysqlslap\fR

104

supports the options in the following list. It also reads option files and supports the options for processing them described at

105

Section\ 4.2.3.2.1, \(lqCommand\-Line Options that Affect Option\-File Handling\(rq.

106

.TP 3n

107

\(bu

108

\fB\-\-help\fR,

109

\fB\-?\fR

110

.sp

111

Display a help message and exit.

112

.TP 3n

113

\(bu

114

\fB\-\-auto\-generate\-sql\fR,

115

\fB\-a\fR

116

.sp

117

Generate SQL statements automatically when they are not supplied in files or via command options.

118

.TP 3n

119

\(bu

120

\fB\-\-auto\-generate\-sql\-add\-autoincrement\fR

121

.sp

122

Add an

123

AUTO_INCREMENT

124

column to automatically generated tables.

125

.TP 3n

126

\(bu

127

\fB\-\-auto\-generate\-sql\-execute\-number=\fR\fB\fIN\fR\fR

128

.sp

129

Specify how many queries to generate automatically.

130

.TP 3n

131

\(bu

132

\fB\-\-auto\-generate\-sql\-guid\-primary\fR

133

.sp

134

Add a GUID\-based primary key to automatically generated tables.

135

.TP 3n

136

\(bu

137

\fB\-\-auto\-generate\-sql\-load\-type=\fR\fB\fItype\fR\fR

138

.sp

139

Specify the test load type. The allowable values are

140

read

141

(scan tables),

142

write

143

(insert into tables),

144

key

145

(read primary keys),

146

update

147

(update primary keys), or

148

mixed

149

(half inserts, half scanning selects). The default is

150

mixed.

151

.TP 3n

152

\(bu

153

\fB\-\-auto\-generate\-sql\-secondary\-indexes=\fR\fB\fIN\fR\fR

154

.sp

155

Specify how many secondary indexes to add to automatically generated tables. By default, none are added.

156

.TP 3n

157

\(bu

158

\fB\-\-auto\-generate\-sql\-select\-columns=\fR\fB\fIstr\fR\fR

159

.sp

160

The string to use for the select columns used in automatic tests. You can use this to determine the effect on performance of selecting or excluding particular columns. This option was added in MySQL 6.0.3.

161

.TP 3n

162

\(bu

163

\fB\-\-auto\-generate\-sql\-unique\-query\-number=\fR\fB\fIN\fR\fR

164

.sp

165

How many different queries to generate for automatic tests. For example, if you run a

166

key

167

test that performs 1000 selects, you can use this option with a value of 1000 to run 1000 unique queries, or with a value of 50 to perform 50 different selects. The default is 10.

168

.TP 3n

169

\(bu

170

\fB\-\-auto\-generate\-sql\-unique\-write\-number=\fR\fB\fIN\fR\fR

171

.sp

172

How many different queries to generate for

173

\fB\-\-auto\-generate\-sql\-write\-number\fR. The default is 10.

174

.TP 3n

175

\(bu

176

\fB\-\-auto\-generate\-sql\-write\-number=\fR\fB\fIN\fR\fR

177

.sp

178

How many row inserts to perform on each thread. The default is 100.

179

.TP 3n

180

\(bu

181

\fB\-\-burnin\fR

182

.sp

183

Run the full test case in an infinite loop. This option was added in MySQL 6.0.3.

184

.TP 3n

185

\(bu

186

\fB\-\-commit=\fR\fB\fIN\fR\fR

187

.sp

188

How many statements to execute before committing. The default is 0 (no commits are done).

189

.TP 3n

190

\(bu

191

\fB\-\-compress\fR,

192

\fB\-C\fR

193

.sp

194

Compress all information sent between the client and the server if both support compression.

195

.TP 3n

196

\(bu

197

\fB\-\-concurrency=\fR\fB\fIN\fR\fR,

198

\fB\-c \fR\fB\fIN\fR\fR

199

.sp

200

The number of clients to simulate when issuing the

201

SELECT

202

statement.

203

.TP 3n

204

\(bu

205

\fB\-\-create=\fR\fB\fIvalue\fR\fR

206

.sp

207

The file or string containing the statement to use for creating the table.

208

.TP 3n

209

\(bu

210

\fB\-\-create\-schema=\fR\fB\fIvalue\fR\fR

211

.sp

212

The schema in which to run the tests.

213

.TP 3n

214

\(bu

215

\fB\-\-csv[=\fR\fB\fIfile_name\fR\fR\fB]\fR

216

.sp

217

Generate output in comma\-separated values format. The output goes to the named file, or to the standard output if no file is given.

218

.TP 3n

219

\(bu

220

\fB\-\-debug[=\fR\fB\fIdebug_options\fR\fR\fB]\fR,

221

\fB\-# [\fR\fB\fIdebug_options\fR\fR\fB]\fR

222

.sp

223

Write a debugging log. A typical

224

\fIdebug_options\fR

225

string is

226

\'d:t:o,\fIfile_name\fR'. The default is

227

\'d:t:o,/tmp/mysqlslap.trace'.

228

.TP 3n

229

\(bu

230

\fB\-\-debug\-check\fR

231

.sp

232

Print some debugging information when the program exits.

233

.TP 3n

234

\(bu

235

\fB\-\-debug\-info\fR,

236

\fB\-T\fR

237

.sp

238

Print debugging information and memory and CPU usage statistics when the program exits.

239

.TP 3n

240

\(bu

241

\fB\-\-delimiter=\fR\fB\fIstr\fR\fR,

242

\fB\-F \fR\fB\fIstr\fR\fR

243

.sp

244

The delimiter to use in SQL statements supplied in files or via command options.

245

.TP 3n

246

\(bu

247

\fB\-\-delayed\-start=\fR\fB\fIN\fR\fR

248

.sp

249

The maximum delay in microseconds. Startup of each thread is delayed by a random number of microseconds up to this maximum. The default is 0. This option was added in MySQL 6.0.3.

250

.TP 3n

251

\(bu

252

\fB\-\-detach=\fR\fB\fIN\fR\fR

253

.sp

254

Detach (close and reopen) each connection after each

255

\fIN\fR

256

statements. The default is 0 (connections are not detached).

257

.TP 3n

258

\(bu

259

\fB\-\-engine=\fR\fB\fIengine_name\fR\fR,

260

\fB\-e \fR\fB\fIengine_name\fR\fR

261

.sp

262

The storage engine to use for creating tables.

263

.TP 3n

264

\(bu

265

\fB\-\-host=\fR\fB\fIhost_name\fR\fR,

266

\fB\-h \fR\fB\fIhost_name\fR\fR

267

.sp

268

Connect to the MySQL server on the given host.

269

.TP 3n

270

\(bu

271

\fB\-\-ignore\-sql\-errors\fR

272

.sp

273

Ignore SQL errors during the test run. By default, errors cause

274

\fBmysqlslap\fR

275

to exit. This option was added in MySQL 6.0.4.

276

.TP 3n

277

\(bu

278

\fB\-\-iterations=\fR\fB\fIN\fR\fR,

279

\fB\-i \fR\fB\fIN\fR\fR

280

.sp

281

The number of times to run the tests.

282

.TP 3n

283

\(bu

284

\fB\-\-label=\fR\fB\fIstr\fR\fR

285

.sp

286

The label to use in printed and CSV output. This option was added in MySQL 6.0.3.

287

.TP 3n

288

\(bu

289

\fB\-\-number\-blob\-cols=\fR\fB\fIstr\fR\fR,

290

.sp

291

The number of

292

BLOB

293

columns to use if

294

\fB\-\-auto\-generate\-sql\fR

295

is specified.

296

\fB\-\-number\-blob\-cols=3:1024/2048\fR

297

would give you 3

298

BLOB

299

columns with a random size between 1024 and 2048. This option was added in MySQL 6.0.3.

300

.TP 3n

301

\(bu

302

\fB\-\-number\-char\-cols=\fR\fB\fIN\fR\fR,

303

\fB\-x \fR\fB\fIN\fR\fR

304

.sp

305

The number of

306

VARCHAR

307

columns to use if

308

\fB\-\-auto\-generate\-sql\fR

309

is specified.

310

.TP 3n

311

\(bu

312

\fB\-\-number\-int\-cols=\fR\fB\fIN\fR\fR,

313

\fB\-y \fR\fB\fIN\fR\fR

314

.sp

315

The number of

316

INT

317

columns to use if

318

\fB\-\-auto\-generate\-sql\fR

319

is specified.

320

.TP 3n

321

\(bu

322

\fB\-\-number\-of\-queries=\fR\fB\fIN\fR\fR

323

.sp

324

Limit each client to approximately this number of queries.

325

.TP 3n

326

\(bu

327

\fB\-\-only\-print\fR

328

.sp

329

Do not connect to databases.

330

\fBmysqlslap\fR

331

only prints what it would have done.

332

.TP 3n

333

\(bu

334

\fB\-\-password[=\fR\fB\fIpassword\fR\fR\fB]\fR,

335

\fB\-p[\fR\fB\fIpassword\fR\fR\fB]\fR

336

.sp

337

The password to use when connecting to the server. If you use the short option form (\fB\-p\fR), you

338

\fIcannot\fR

339

have a space between the option and the password. If you omit the

340

\fIpassword\fR

341

value following the

342

\fB\-\-password\fR

343

344

\fB\-p\fR

345

option on the command line, you are prompted for one.

346

.sp

347

Specifying a password on the command line should be considered insecure. See

348

Section\ 5.5.6.2, \(lqEnd\-User Guidelines for Password Security\(rq.

349

.TP 3n

350

\(bu

351

\fB\-\-pipe\fR,

352

\fB\-W\fR

353

.sp

354

On Windows, connect to the server via a named pipe. This option applies only for connections to a local server, and only if the server supports named\-pipe connections.

355

.TP 3n

356

\(bu

357

\fB\-\-port=\fR\fB\fIport_num\fR\fR,

358

\fB\-P \fR\fB\fIport_num\fR\fR

359

.sp

360

The TCP/IP port number to use for the connection.

361

.TP 3n

362

\(bu

363

\fB\-\-post\-query=\fR\fB\fIvalue\fR\fR

364

.sp

365

The file or string containing the statement to execute after the tests have completed. This execution is not counted for timing purposes.

366

.TP 3n

367

\(bu

368

\fB\-\-shared\-memory\-base\-name=\fR\fB\fIname\fR\fR

369

.sp

370

On Windows, the shared\-memory name to use, for connections made via shared memory to a local server. This option applies only if the server supports shared\-memory connections.

371

.TP 3n

372

\(bu

373

\fB\-\-post\-system=\fR\fB\fIstr\fR\fR

374

.sp

375

The string to execute via

376

system()

377

after the tests have completed. This execution is not counted for timing purposes.

378

.TP 3n

379

\(bu

380

\fB\-\-pre\-query=\fR\fB\fIvalue\fR\fR

381

.sp

382

The file or string containing the statement to execute before running the tests. This execution is not counted for timing purposes. This option was added in MySQL 5.1.18.

383

.TP 3n

384

\(bu

385

\fB\-\-pre\-system=\fR\fB\fIstr\fR\fR

386

.sp

387

The string to execute via

388

system()

389

before running the tests. This execution is not counted for timing purposes.

390

.TP 3n

391

\(bu

392

\fB\-\-preserve\-schema\fR

393

.sp

394

Preserve the schema from the

395

\fBmysqlslap\fR

396

run. The

397

\fB\-\-auto\-generate\-sql\fR

398

and

399

\fB\-\-create\fR

400

options disable this option. This option was removed in MySQL 6.0.5.

401

.TP 3n

402

\(bu

403

\fB\-\-protocol={TCP|SOCKET|PIPE|MEMORY}\fR

404

.sp

405

The connection protocol to use for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For details on the allowable values, see

406

Section\ 4.2.2, \(lqConnecting to the MySQL Server\(rq.

407

.TP 3n

408

\(bu

409

\fB\-\-query=\fR\fB\fIvalue\fR\fR,

410

\fB\-q \fR\fB\fIvalue\fR\fR

411

.sp

412

The file or string containing the

413

SELECT

414

statement to use for retrieving data.

415

.TP 3n

416

\(bu

417

\fB\-\-set\-random\-seed=\fR\fB\fIvalue\fR\fR,

418

.sp

419

The seed value for the randomizer.

420

.TP 3n

421

\(bu

422

\fB\-\-silent\fR,

423

\fB\-s\fR

424

.sp

425

Silent mode. No output.

426

.TP 3n

427

\(bu

428

\fB\-\-slave\fR

429

.sp

430

Follow master locks for other

431

\fBmysqlslap\fR

432

clients. Use this option if you are trying to synchronize around one master server with

433

\fB\-\-lock\-directory\fR

434

plus NFS.

435

.TP 3n

436

\(bu

437

\fB\-\-socket=\fR\fB\fIpath\fR\fR,

438

\fB\-S \fR\fB\fIpath\fR\fR

439

.sp

440

For connections to

441

localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use.

442

.TP 3n

443

\(bu

444

\fB\-\-ssl*\fR

445

.sp

446

Options that begin with

447

\fB\-\-ssl\fR

448

specify whether to connect to the server via SSL and indicate where to find SSL keys and certificates. See

449

Section\ 5.5.7.3, \(lqSSL Command Options\(rq.

450

.TP 3n

451

\(bu

452

\fB\-\-timer\-length=\fR\fB\fIN\fR\fR

453

.sp

454

The duration in seconds to run each test. Tests that run longer are terminated. This option was added in MySQL 6.0.3.

455

.TP 3n

456

\(bu

457

\fB\-\-user=\fR\fB\fIuser_name\fR\fR,

458

\fB\-u \fR\fB\fIuser_name\fR\fR

459

.sp

460

The MySQL user name to use when connecting to the server.

461

.TP 3n

462

\(bu

463

\fB\-\-verbose\fR,

464

\fB\-v\fR

465

.sp

466

Verbose mode. Print more information about what the program does. This option can be used multiple times to increase the amount of information.

467

.TP 3n

468

\(bu

469

\fB\-\-version\fR,

470

\fB\-V\fR

471

.sp

472

Display version information and exit.

473

.SH "COPYRIGHT"

474

.PP

475

476

.PP

477

This documentation is free software; you can redistribute it and/or modify it only under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.

478

.PP

479

This documentation is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

480

.PP

481

You should have received a copy of the GNU General Public License along with the program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110\-1301 USA or see http://www.gnu.org/licenses/.

482

.SH "SEE ALSO"

483

For more information, please refer to the MySQL Reference Manual,

484

which may already be installed locally and which is also available

485

online at http://dev.mysql.com/doc/.

486

.SH AUTHOR

487

Sun Microsystems, Inc. (http://www.mysql.com/).

Older »