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

Innodb Performance, Lets make this fast....

So at the moment I am interested in how fast I can make Innodb on a 8-way machine while it keeping durable (aka disk write cache is disabled). This setup is tuned to run without replication.

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_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=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:
  • Storage Engine
  • Built in test
  • Average of runs
  • Minimum run
  • Maximum run
  • Concurrent connections
  • Statements executed by each connection

    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,100

    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,100
    innodb,write,16.685,16.457,17.166,200,100

    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,100

    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....
  • Subscribe
    • Post a new comment

      Error

      Comments allowed for friends only

      Anonymous comments are disabled in this journal

      default userpic

      Your reply will be screened

      Your IP address will be recorded 

    • 22 comments