Thanks for that Chris, interesting thoughts.
For clarification, there is *NO* UPDATEs running on this table. Not a
single one! :) Many more SELECTs than INSERTs
Chris Nolan wrote:
> 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!