Fastest MySQL storage engine for storing data without concurrency

Question

I'm using a mysql base to store results from various test over a large amount of data (hundred of millions recordings).

I'm the only user of the base so there won't be any problem of concurrency. I also want to use simple mathematical functions such as 'avg', 'std' etc

What is the best engine in your opinion for such a task?
I'm using InnoDB right now and it seems to me a bit heavy.

Regards

Guillaume

Pumphrey

first - MEMORY second - MyIsam – Value

Answer 1

Using an InnoDB table comes with an overhead of transactional support, rollbacks etc. If you don't need this support for transactions then you should really go with an MyISam table as it doesn't have any transactional support and can be faster for lookups etc. They have been doing a lot of work on InnoDB to bring it up to speed but there is always that overhead to contend with. I would suggest some further reading on the topic before switching.

With a table as large as your suggesting a memory table may cause you issues with performance and storage. For these sorts of tables I would recommend an MyISam structure or an InnoDB table with innodb_flush_log_at_trx_commit set to 0 (this switches to a mode where transactions are not commited to disk before control is returned to the caller).

Corves

Answer 2

Tokudb would be a better alternative.

You can have a look at these links for more justification: Tokudb bench mark for insertion and Tokudb versus InnoDB Comparison

Please take into consideration that Tokudb allows multiple clustering indices which could improve selection operation very much. Also tokudb uses fractal indexing which provide good performance.

Rutherford

Answer 3

do you think innodb clustered indexes would enhance your query performance in any way ?

http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

Further information:

Optimal MySQL settings for queries that deliver large amounts of data?

Rewriting mysql select to reduce time and writing tmp to disk

if you think not then go with myisam i guess.

Changsha