?

Log in

No account? Create an account

Commit, Close, and what exactly is the cost?

« previous entry | next entry »
Aug. 21st, 2007 | 01:13 pm

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



Link | Leave a comment |

Comments {8}

(no subject)

from: jeremycole
date: Aug. 21st, 2007 08:21 pm (UTC)
Link

What hardware and configuration did you do the test on?

With a properly configured InnoDB, you should see a very real difference between single query transactions and multiple query ones.

Reply | Thread

Brian "Krow" Aker

(no subject)

from: krow
date: Aug. 21st, 2007 08:41 pm (UTC)
Link

I would have thought that as well, but between 1-10 I can't see much of a difference. I know from experience that once you reach the thoughts of queries it can make a difference, but thousands of queries is something you don't see in web (and I do admit a stronger interest in how MySQL works with web applications).

Reply | Parent | Thread

baix

from: baix
date: Aug. 23rd, 2007 09:03 pm (UTC)
Link

How much of a bottleneck was the disk in this test? I'm wondering if the disk, or something else, was the bottleneck and swamped the performance gains of multi-row commits.

It could be under the disconnect/reconnect scenario the machine was cpu bound, but under the stay connected scenario the machine was disk bound? I'm going to make a wild guess and suggest the disk was close to saturated at 25 clients.

Reply | Parent | Thread

Brian "Krow" Aker

Re: baix

from: krow
date: Aug. 24th, 2007 02:45 am (UTC)
Link

The disk being an issue is entirely possible. I can run with write cache enabled which will show off more direct issues in the engine, but disable sync() for a test like this somewhat defeats the purpose of the test.

One possible option is to run the entire test on a ram disk.

Reply | Parent | Thread

Morgan Tocker

Interesting

from: mtocker
date: Aug. 22nd, 2007 09:54 am (UTC)
Link

The connection/disconnection cost is a little influenced by the Operating system and the threading library. Were you using NPTL or LinuxThreads?

My assumption in why my simple test was much faster was because of the fsync cost. I will have to change innodb_flush_log_at_trx_commit=2 and see what the difference is.

(The test was not run on a real machine, but my laptop).

Reply | Thread

Brian "Krow" Aker

Re: Interesting

from: krow
date: Aug. 22nd, 2007 02:28 pm (UTC)
Link

NPTL, I don't keep around distributions old enough to still have LinuxThreads support.

The above was with innodb_flush_log_at_trx_commit=1. If you look back a couple of posts you will see the configuration I use for Innodb.

Did you have write cache enabled on your laptop's disk?

Reply | Parent | Thread

peter_zaitsev

Re: Interesting

from: peter_zaitsev
date: Aug. 28th, 2007 02:50 pm (UTC)
Link

Brian,

Did you check you actually have proper fsync in your case (or did you have BBU)

Size of transaction is tricky thing it is very dependent on the hardware and configuration

Reply | Parent | Thread

Brian "Krow" Aker

Re: Interesting

from: krow
date: Aug. 28th, 2007 03:47 pm (UTC)
Link

Regular sata driver with write cache disabled. Unfortunately, while I have large CPU machines, I lack any sort of RAID to really study (this may change).

Reply | Parent | Thread