I was testing some mysqlslap runs which Brian ‘Krow’ Aker was running testing the effect of changing the commit interval. Whilst I was doing that I noticed that he had added the option to add an auto incrementing column to the generated table.

Having read elsewhere here and here that the way innodb handles auto incrementing columns can cause issues, I thought I would test that out. And as the picture shows, it certainly does limit scalability. Here is the mysql documentation on the subject of auto incrementing columns.

That is not the end of the story for auto incrementing columns, if you are looking at replication, in particular master-master replication you have to be very careful how to you handle them.
Auto incrementing and Replication
Auto increment increment and offset

Plus if you are looking at scaling out rather than scaling up, having natural primary keys rather than synthetic primary keys (read primary keys based on sequences or auto incrementing numbers) moving data between shards with natural keys will be a lot easier. Much less work required to prove the ID column in one shard is unique across all shards.

Have Fun

Paul

Commands:

mysqlslap --concurrency="1,25,50,75,100" --iterations=10 --number-int-cols=2 --number-char-cols=3 \
--auto-generate-sql --csv=/tmp/innodb-write-scale.csv \
--engine=$ENGINE --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write \
--auto-generate-sql-execute-number=500

mysqlslap --concurrency="1,25,50,75,100" --iterations=10 \
--number-int-cols=2 --number-char-cols=3 --auto-generate-sql \
--csv=/tmp/test_concurr_noincr.csv --engine=innodb \
--auto-generate-sql-load-type=write --auto-generate-sql-execute-number=500 \
--user=root --password

Results:

cat /tmp/innodb-write-scale.csv

innodb,write,0.178,0.128,0.369,1,500
innodb,write,6.106,5.846,6.546,25,500
innodb,write,13.481,13.196,13.737,50,500
innodb,write,24.853,22.928,38.379,75,500
innodb,write,39.932,39.283,41.079,100,500

cat /tmp/test_concurr_noincr.csv

innodb,write,0.178,0.125,0.255,1,500
innodb,write,5.111,4.765,5.503,25,500
innodb,write,10.590,10.052,11.234,50,500
innodb,write,16.091,15.312,16.820,75,500
innodb,write,22.645,21.720,23.698,100,500