MyISAM vs. INNODB for a single blob table
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 -- Alan Williamson, City Planner w: http://www.BLOG-CITY.com/ e: [EMAIL PROTECTED] b: http://alan.blog-city.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. INNODB for a single blob table
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. INNODB for a single blob table
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! :) 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! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. INNODB for a single blob table
Hi Alan, 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 If you value your data, and these INSERTs are part of a multi-insert batch of related data, go with the table-type that supports transactions: InnoDB. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
