Gentlemen, Slap your Engines!

Once again, I was unable to attend all of the sessions I wanted to at this year's User Converence, but I was happy to make it to Bob Burgess' talk on bash scripting with mysql. The slides and examples aren't up yet, but when they are (which may be as you read this, check the last link), they would probably also be a great tutorial.


So, I got bore\^D\^D\^D\^D inspired later that day to put some of the practices into use, and worked up a script to run mysqlslap in various ways against a server, and then added a couple funcitons to try it out on each storage engine. The script is below in its entirety - bash scripters, please be kind in your comments. No, I didn't write all this just for the pun in the subject. But I'm not above that.


The result?


Why don't I use more BLACKHOLE tables? They are blazing fast!


 My results (on my lenovo T61, Fedora 10):



SLAP Base values:
 50 simultaneous connections ||  10 runs through
 Writes : 1000, 500 unique (Commit every 500) || Queries: 1000, 200 unique
 Schema: 4 character columns, 8 numeric with auto-increment PK and 10 secondary indexes
For InnoDB: 0.389 Average, 0.299 Min, 0.651 Max
For MyISAM: 0.364 Average, 0.355 Min, 0.377 Max
For BLACKHOLE: 0.137 Average, 0.124 Min, 0.147 Max
For CSV: n/a Average, n/a Min, n/a Max
For MEMORY: 0.375 Average, 0.363 Min, 0.444 Max
For ARCHIVE: n/a Average, n/a Min, n/a Max
For MRG_MYISAM: n/a Average, n/a Min, n/a Max


The "n/a" ones are tables that, generally for obvious reasons, couldn't do the slap. My error handling needs work.


There are some expected trends that are good to validate - InnoDB improves with more concurrency (in a relative sense), MEMORY has remarkably little fluxuation in response time, things like that. But the marketing guys really have to capitalize on those BLACKHOLE numbers :-)



#!/bin/bash

shopt -s -o nounset

printf "Enter root pwd: "

read -s PASSWORD

# get the list of active engines from MySQL

ENGINES=`mysql -uroot -p$PASSWORD -B -N -e "SELECT ENGINE from ENGINES WHERE SUPPORT<>'NO'" INFORMATION_SCHEMA`

#for e in $ENGINES; do

#    printf "\\nFound engines: %s" $e

#done


printf "\\nStarting test at %s \\n" `date +%H:%M:%S`

# default initial settings

ITERATIONS=10

CONCURRENCY=50

COMMIT=500

WRITES=1000

let "UNIQUE_WRITES=$WRITES/2"

QUERIES=1000

let "UNIQUE_QUERIES=$QUERIES/5"

LOAD_TYPE=mixed

CHARS=4

INTS=8

INDX=10

SLAP="mysqlslap -u root -p$PASSWORD -h 127.0.0.1 -a -c $CONCURRENCY -i $ITERATIONS --auto-generate-sql-add-autoincrement --auto-generate-sql-secondary-indexes=$INDX --auto-generate-sql-write-number=$WRITES --auto-generate-sql-unique-write-number=$UNIQUE_WRITES --auto-generate-sql-unique-query-number=$UNIQUE_QUERIES -x $CHARS  -y $INTS --number-of-queries=$QUERIES --commit=$COMMIT --auto-generate-sql-load-type=$LOAD_TYPE "

function parse_slap {

    if [ $# -lt 1 ]; then

        AVERAGE="n/a"

        MINIMUM="n/a"

        MAXIMUM="n/a"

    else

        AVERAGE=$1

        MINIMUM=$2

        MAXIMUM=$3

    fi    

}

function run_slap {

        printf "%s\\n" "SLAP $1:"

        printf "%s\\n" " $CONCURRENCY simultaneous connections ||  $ITERATIONS runs through "

        printf "%s\\n" " Writes : $WRITES, $UNIQUE_WRITES unique (Commit every $COMMIT) || Queries: $QUERIES, $UNIQUE_QUERIES unique "

        printf "%s\\n" " Schema: $CHARS character columns, $INTS numeric with auto-increment PK and $INDX secondary indexes"

for engine in $ENGINES

    do

        SLAPPED=`$SLAP -e $engine 2>/dev/null | cut -c48-53 | tr -d \\n`

        echo $SLAPPED >> $0.txt

        parse_slap $SLAPPED

            printf "For %s: %s Average, %s Min, %s Max\\n" $engine $AVERAGE $MINIMUM $MAXIMUM

    done

}

run_slap "Base values"

echo

let WRITES=WRITES\*10

let QUERIES=QUERIES\*100

let UNIQUE_QUERIES=QUERIES/4

run_slap "more reads"

echo

UNIQUE_QUERIES=$QUERIES

UNIQUE_WRITES=$WRITES

let COMMIT=COMMIT\*3

run_slap "More unique reads and writes"

echo

let INTS=INTS\*5

let CHARS=CHARS\*5

let INDX=INTS+CHARS-1

run_slap "wide indexed tables"

echo

let CONCURRENCY=CONCURRENCY\*10

run_slap "massive concurrency"





Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Philip Antoniades

Search

Archives
« July 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  
       
Today