1

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.

trincot
  • 317,000
  • 35
  • 244
  • 286
muncherelli
  • 2,887
  • 8
  • 39
  • 54

3 Answers3

2

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

Jon Black
  • 16,223
  • 5
  • 43
  • 42
1

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
  • 163,128
  • 34
  • 264
  • 314
  • 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). – Amy B Aug 19 '10 at 00:08
  • 1
    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 Aug 19 '10 at 00:11
  • ircmaxell - are you familiar with this article, it's a bit dated now but should put your straight on a few facts http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/ – Jon Black Aug 19 '10 at 00:52
  • 1
    @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 Aug 19 '10 at 00:57
0

I know that this question is getting dated and you've probably made a very good solution by now but I just wanted to point out to anyone who may be reading this that perhaps a relational database isn't the best way to solve this problem. To me this clearly looks like a case where a flat file database is the ideal solution. You could have saved yourself a ton of overhead by just writing these values out to a binary file and then use simple mathematical operations to select rows and fields.

krowe
  • 2,129
  • 17
  • 19