I am creating an application which will store a (semi) real-time feed of a few different scales around a certain location. The weights of each scale will be put in a table with only as many rows as scales. The scale app feeds the MySQL database a new weight every second, which a PHP web app reads every 3 seconds. It doesn't seem like very much traffic that would page the hard drive very much, or if the difference would be negligible, but I'm wondering if it would be more efficient or make more sense to use a Memory/HEAP table vs a normal MyISAM table.
A:
Well, if you're expecting a large amount of data, I think you almost have to go MyISAM. You'll likely run out of memory if you store it all in a memory table. Not to mention that you'll lose all of your data upon power loss with a HEAP engine (Keep in mind, you may want that depending on your use case)...
ircmaxell
2010-08-19 00:05:56
Why MyISAM instead of InnoDB? I've always thought MyISAM is a dead engine because there was so many advantages to InnoDB (row-level locking, transactions, etc).
Coronatus
2010-08-19 00:08:58
MyISAM is significantly faster. That's the big advantage. Believe it or not, for some use cases, row-level locking can actually be slower than table level locking. Not to mention that not every application requires transactions.
ircmaxell
2010-08-19 00:11:24
ircmaxell - are you familiar with this article, it's a bit dated now but should put your straight on a few factshttp://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/
f00
2010-08-19 00:52:12
@f00: Benchmarks are relatively meaningless. Real world performance rarely aligns to benchmark results. And my comments about performance are based upon my experiences. It's definitely not faster for all use cases, but for some it definitely is (especially if your application is very read heavy and has lots of data to read through). I'm not knocking the benefits of InnoDB and its ACID compliance. I'm just saying that it's not the best for every usecase...
ircmaxell
2010-08-19 00:57:04
A:
With anything from 100's to 1000's of concurrent read/write requests (think typical OLTP usage) innodb will out perform myisam hands down.
It's not about other people's observations, it's not about transactional/acid support, it's about the architecture of innodb which is far superior to that of the legacy myisam engine.
For example, innodb supports clustered primary key indexes http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html.
Additionally, innodb has row level locking which is far more performant under concurrent load than myisam table level locking.
I could keep going but somone's already provided a really good summary of why innodb is a better choice for OLTP: http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB
f00
2010-08-20 17:36:20