Alan Williamson wrote:
> A quick question for the hardcore MySQL experts out there.
> I have a simple table;
> ID varchar (PK)
> DATA longblob
> This table is a simple persistence cache for one of our servers. It
> regularly INSERTs and SELECTs into this table data of approximately
> 2KB - 200KB, although the majority of inserts are around the 2KB mark.
> No fancy queries are ever performed, merely a single SELECT on a given
> key and no range queries are ever done.
> So with that in mind, I just noticed the table was created as a
> MyISAM. In your experience how does this compare to a table using
> INNODB? Should it have been created as a INNODB for better performance?
> Any thoughts, insights, would be listened to intensely! :)
How often are DELETE and UPDATE statements executed on this table?
MyISAM is damned quick when it comes to workloads that always result in
INSERTs ending up at the end of the tablespace. As MyISAM can allow
SELECTs to execute while INSERTs are in progress at the end of the table
(i.e When no DELETEs have been issued) thanks to it's versioning you'll
find that thousands of queries a second is quite doable on modest hardware.
That said, InnoDB's speed defies belief. Given that it's multiversioned,
transactional and able to lock at the row level the fact that it's even
in the same leauge as MyISAM performance-wise for these sorts of loads
is impressive. When you have UPDATEs flying around, InnoDB may edge
MyISAM out for heavy workloads. Many places have moved to InnoDB due to
concurrency issues of that type.
In summary, test test test!