Mike Dougherty wrote:
> I am new to MySQL. We have always used Oracle. I
> was able to migrate the schema from our Oracle
> Database to MySQL no problem, but I am not exactly
> clear how tablespaces and engines work.
Well, storage engines are different from tablespaces. MySQL storage engines facilitate the storage of data on various media; each storage engine (MyISAM, InnoDB, MEMORY, ARCHIVE, etc.) comes with its own set of strengths and weaknesses, often dependent on what capabilities the storage engine is designed to feature. For instance, MyISAM, while lacking in transactional support, is often a faster performing engine for many logging operations, or high read-to-write applications. InnoDB on the other hand, supports row-level locking, foreign key constraints, and transaction safety, but such features come with a few performance drawbacks in certain situations.
Tablespaces, on the other hand, have only to do with the InnoDB storage engine; no other storage engine uses the term nor the concept of tablespaces. Furthermore, InnoDB tablespaces are not quite the same as Oracle tablespaces. The InnoDB tablespace, at least the *shared* InnoDB tablespace, contains the InnoDB data dictionary and the data contained in the InnoDB tables and indexes. The tablespace is divided into a set of extents, and further divided into a collection of data pages. You can also set up InnoDB to use a single tablespace *per table* using the innodb_file_per_table option. You can read more about that in the manual.
Unlike Oracle and SQL Server (yes, you can actually control tablespaces in MS SQL Server), you can't allocate certain named tablespaces and assign tables into them. You either have a shared tablespace (which can be composed of multiple data *files* spread across multiple disks...) or you have multiple tablespaces, one for each table. Again, the manual has more information on that here:
http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html
In my experience, it's less beneficial, from a performance perspective, to split up the tablespace, than it is to house the *InnoDB log files* on a separate disk...
> Right now
> we create a data_lrg tablespace for our Oracle
> tables that hold a lot of data. We create a
> data_med and data_small for the rest of the
> tables. From what I can tell everything in MySQL
> used InnoDB from the schema I migrated. Can
> someone provide an explanation that makes sense?
No need for this really, because AFAIK, you can't control tablespaces in InnoDB in this way... though, I could be mistaken...
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com