MySQL Forums
Forum List  »  Oracle

Re: MySQL Tablespaces
Posted by: Jay Pipes
Date: February 06, 2006 10:08PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
4168
February 06, 2006 06:35PM
Re: MySQL Tablespaces
9952
February 06, 2006 10:08PM
3241
February 07, 2006 12:48PM
3249
February 07, 2006 01:01PM
3074
February 09, 2006 10:10AM
3221
February 09, 2006 01:30PM
3004
February 09, 2006 01:47PM
2892
February 09, 2006 01:58PM
2995
February 09, 2006 02:09PM
2965
February 10, 2006 02:26PM
2556
March 25, 2007 11:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.