MySQL Forums
Forum List  »  Archive Storage Engine

Archive slower than InnoDB?
Posted by: Srinivas Rao
Date: March 17, 2008 02:56AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Archive slower than InnoDB?
9074
March 17, 2008 02:56AM
6269
March 17, 2008 12:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.