Archive slower than InnoDB?
Dear MySQL Archive engine experts,
Compared Archive with InnoDB for exclusive select statements. Expecting the former to emerge as a clear winner, found that was not the case.
Mysql ver = 5.1.23rc
machine = redhat-linux-gnu(i686)
These were the settings used in my.cnf (for InnoDB optimization):
innodb_buffer_pool_size = 256M
innodb_flush_method = O_DIRECT
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 4M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
Used mysqlslap for bombardment.
With query cache disabled, archive was almost twice slower than InnoDB.
InnoDB,mixed,1.119,1.113,1.128,1,10000
InnoDB,mixed,0.559,0.235,0.697,25,400
InnoDB,mixed,0.644,0.343,0.710,50,200
InnoDB,mixed,0.710,0.698,0.721,100,100
ARCHIVE,mixed,2.111,2.095,2.144,1,10000
ARCHIVE,mixed,0.800,0.101,1.207,25,400
ARCHIVE,mixed,0.991,0.183,1.192,50,200
ARCHIVE,mixed,1.062,0.435,1.203,100,100
With query cache enabled, archive was nearly as good but not better than InnoDB.
InnoDB,mixed,0.416,0.410,0.435,1,10000
InnoDB,mixed,0.250,0.205,0.274,25,400
InnoDB,mixed,0.269,0.261,0.274,50,200
InnoDB,mixed,0.282,0.274,0.289,100,100
ARCHIVE,mixed,0.432,0.407,0.468,1,10000
ARCHIVE,mixed,0.226,0.185,0.264,25,400
ARCHIVE,mixed,0.271,0.266,0.284,50,200
ARCHIVE,mixed,0.278,0.264,0.287,100,100
Similar is the case with mysql enterprise version 14.12 dist 5.0.56
In the above test mysqlslap test were run as follows:
mysqlslap --query="SELECT intcol1,intcol2,charcol1,charcol2,charcol3 FROM t1 WHERE intcol2=773273718;" --create=create1.sql --debug-check -T --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 --number-char-cols=3 --engine=$1 --auto-generate-sql-load-type=mixed --number-of-queries=10000 --user=monty --password=monty --csv=slap-test-select.csv
Can anyone suggest optimization techniques for archive type engine, that I may have missed?
Really appreciate your feedback.
cheers
Srinivas