Commit, Close, and what exactly is the cost? - Brian "Krow" Aker's Idle Thoughts

Last week I noticed a blog port by Morgan Tocker on "Big transactions
suck.."

http://mtocker.livejournal.com/30197.html

His post made me think a bit about Innodb's behavior both in cases
where user's use small numbers of transactions, and the case where
user's write applications that connect and then disconnect from the
database.

The connect/disconnect data came out as I expected it. Reconnecting
to the database can cost you in performance. In each of the cases
below the test was run with an increasing number of users.

The commit vs autocommit was bit more interesting. From the graph I
can't find any discernible pattern showing which might be better
(though this highlights that I need to add standard deviation to
mysqlslap so that I can tell if the results are valid). The lack of a
true "this is most definitely better" could be grounds to justify
that grouping 1-10 transactions together has no real effect on your
application.





These tests were all run on an 8way machine with the following script:


MYSQLSLAP="/home/brian/mysql-builds/mysql-5.1-arch/bin/mysqlslap --protocol=tcp"
CONCURRENCY="1,25,50,75,100,125,150,175,200,225,250,275,300"
ENGINE="innodb"

# Write Tests, simple INSERT test
$MYSQLSLAP --concurrency=$CONCURRENCY --iterations=10 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql --csv=/tmp/$ENGINE-write-scale.csv
--engine=$ENGINE --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write --auto-generate-sql-execute-number=500

$MYSQLSLAP --concurrency=$CONCURRENCY --iterations=10 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql --csv=/tmp/$ENGINE-write-scale.csv
--engine=$ENGINE --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write --auto-generate-sql-execute-number=500 --commit=1

$MYSQLSLAP --concurrency=$CONCURRENCY --iterations=10 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql --csv=/tmp/$ENGINE-write-scale.csv
--engine=$ENGINE --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write --auto-generate-sql-execute-number=500 --commit=5

$MYSQLSLAP --concurrency=$CONCURRENCY --iterations=10 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql --csv=/tmp/$ENGINE-write-scale.csv
--engine=$ENGINE --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write --auto-generate-sql-execute-number=500 --commit=10