List:General Discussion« Previous MessageNext Message »
From:Chris Nolan Date:March 18 2004 8:21pm
Subject:Re: MyISAM vs. INNODB for a single blob table
View as plain text  
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! :)
>
> thanks
>
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!

Regards,

Chris
Thread
Wish List of FeaturesMark Manning16 Mar
  • Re: Wish List of FeaturesDavid Griffiths16 Mar
    • Re: Wish List of FeaturesDaniel Gibby16 Mar
  • RE: Wish List of FeaturesDaevid Vincent17 Mar
RE: Wish List of FeaturesDarran Kartaschew16 Mar
Re: Wish List of FeaturesEldon Ziegler18 Mar
  • MyISAM vs. INNODB for a single blob tableAlan Williamson18 Mar
    • Re: MyISAM vs. INNODB for a single blob tableChris Nolan18 Mar
      • Re: MyISAM vs. INNODB for a single blob tableAlan Williamson18 Mar
  • Re: MyISAM vs. INNODB for a single blob tableMartijn Tonies18 Mar