Brian "Krow" Aker (krow) wrote,
Brian "Krow" Aker
krow

Commit, Close, and what exactly is the cost?

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


Subscribe
  • Post a new comment

    Error

    Comments allowed for friends only

    Anonymous comments are disabled in this journal

    switch
    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    Help
  • 8 comments