Here is my changes for the 8-way, sata disk system:
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10000M:autoextend
innodb_log_file_size=512M
innodb_log_buffer_size=8M
innodb_log_files_in_group = 2
innodb_checksums=0
innodb_support_xa=0
innodb_doublewrite=0
innodb_thread_concurrency=36
innodb_locks_unsafe_for_binlog=1innodb_a
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10000M:autoextend
innodb_log_file_size=512M
innodb_log_buffer_size=8M
innodb_log_files_in_group = 2
innodb_checksums=0
innodb_support_xa=0
innodb_doublewrite=0
innodb_thread_concurrency=36
innodb_locks_unsafe_for_binlog=1
Almost none of these made a real difference in performance except, innodb_thread_concurrency. In 5.1.11 the default was changed from 20 to 8. http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html
Even the though the manual says that "A value of 20 or higher is interpreted as infinite concurrency", I am finding that adjusting the number above 20 can significantly change performance (or hell just set it to Zero since in almost all cases where you are scaling that number turns out the best). Let me present to you four runs of mysqlslap generating 100 new rows per connection (the third number being smaller is better):
Columns:
innodb_thread_concurrency=8->
innodb,write,0.876,0.833,1.000,1,100
innodb,write,5.455,5.226,6.076,25,100
innodb,write,10.894,10.611,11.545,50,100
innodb,write,16.183,15.926,17.046,75,100
innodb,write,21.574,21.387,21.817,100,10
innodb_thread_concurrency=36->
innodb,write,0.877,0.834,0.922,1,100
innodb,write,1.777,1.722,1.900,25,100
innodb,write,2.506,2.456,2.591,50,100
innodb,write,3.729,3.660,3.880,75,100
innodb,write,4.968,4.893,5.116,100,100
innodb,write,6.299,6.113,6.914,125,100
innodb,write,7.464,7.321,7.672,150,100
innodb,write,10.493,10.025,10.972,175,10
innodb,write,16.685,16.457,17.166,200,10
innodb_thread_concurrency=100 ->
innodb,write,0.858,0.833,0.875,1,100
innodb,write,1.775,1.720,1.873,25,100
innodb,write,1.885,1.755,2.570,50,100
innodb,write,1.842,1.786,1.920,75,100
innodb,write,2.046,1.820,3.122,100,100
innodb,write,2.388,2.298,2.492,125,100
innodb,write,3.480,3.089,3.726,150,100
innodb,write,7.652,7.524,7.768,175,100
innodb,write,11.099,10.873,11.528,200,10
innodb_thread_concurrency=200 ->
innodb,write,0.861,0.833,0.875,1,100
innodb,write,1.797,1.755,1.956,25,100
innodb,write,1.806,1.754,1.923,50,100
innodb,write,1.844,1.788,1.965,75,100
innodb,write,1.952,1.833,2.590,100,100
innodb,write,1.965,1.892,2.357,125,100
innodb,write,2.286,1.902,3.805,150,100
innodb,write,2.060,1.992,2.261,175,100
innodb,write,2.312,2.199,2.557,200,100
On at least an 8way machine adjusting innodb_thread_concurrency to 200 made a significant improvement (this brings doubt that the change in 5.1.11 from 20 to 8 as a default value was a good idea). One other thing to note, in the past I have rewritten the code around innodb_file_io_threads to allow it to be modified on UNIX systems, and that too has made a difference.
I need to rerun the test and change innodb_flush_method. It gets adjusted a lot for tuning, and I am curious to see how this modifies the above numbers. Update I tried O_DSYNC, the sync method that is sometimes believed to be faster. At least on a 2.6 Kernel is made the run slightly slower (almost insignificant though).
All of this makes me wonder how this would effect the Sun T1000 Sun I have.
BTW SubEthaEdit is pretty awesome. I can copy and paste between a shared buffer between my two macs.... I wonder if there is someway to do shared editing with vi without resorting to screen....
