How do you partition tables in mysql 5?

Hi...

According to the MySQL 5.0 documentation, for setting up advanced
innodb, the following is stated:

An advanced my.cnf example. Suppose that you have a Linux computer with
2GB RAM and three 60GB hard disks at directory paths /, /dr2 and /dr3.
The following example shows possible configuration parameters in my.cnf
for InnoDB.

[mysqld]
# You can write your other MySQL server options here
# ...
innodb_data_home_dir =
#
# Data files must be able to hold your data and indexes
innodb_data_file_path =
/ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend

OK....let's assume that I have 2-tables, X and Y, and I want to create
them, BUT have X point to the ibdata1 path and Y to ibdata2. According
to the CREATE TABLE doc, the DATA DIRECTORY option only works for
MyISAM.

Anybody know how to do this using innodb?

Thanks,
Joe

"JoeT" <trubisz@yahoo.com> wrote:

> ... let's assume that I have 2-tables, X and Y, and I want to create
> them, BUT have X point to the ibdata1 path and Y to ibdata2. According
> to the CREATE TABLE doc, the DATA DIRECTORY option only works for
> MyISAM.
>
> Anybody know how to do this using innodb?

You cannot do that. InnoDB decides how it distributes data over
tablespaces. OTOH you can use --innodb-file-per-table to have
InnoDB create a separate tablespace for each table. IMHO there
is no particularly good reason for doing that anyway. YMMV.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke wrote:
> you can use --innodb-file-per-table to have
> InnoDB create a separate tablespace for each table. IMHO there
> is no particularly good reason for doing that anyway. YMMV.

IMHO the best reason for using innodb-file-per-table is that when you
drop a table, the filesystem space is released.

If you put all your InnoDB tables in one filespace file, the only way to
get the file to shrink is to dump all your other InnoDB tables, remove
the filespace file, and re-import the tables.

Regards,
Bill K.

Actually, having done really huge systems (we are talking about very
high speed/high volume OLTP systems), I can utilize the advantages of
the hardware as well. So, it becomes a major performance advantage.

Joe

"JoeT" <trubisz@yahoo.com> wrote:

> Actually, having done really huge systems (we are talking about very
> high speed/high volume OLTP systems), I can utilize the advantages of
> the hardware as well. So, it becomes a major performance advantage.

Ahh. I guessed that already.

My experience as well as the experience of MySQL customers shows,
that multiple disks are best used as RAID. The most appropriate
RAID level is RAID-10 (a stripe of mirrors). If you need the last
performance kick, you might go for RAID-0. However you should
avoid RAID-5 because it has very bad write performance.

Compared to hand-tuned distribution of data on multiple disks,
RAID has many advantages

- it's simple
- it's self-adjusting - no need to change the disk layout
if your data distribution or access pattern changes
- it's easily extensible

There is a nice bluprint from SUN concerning that issue:
<http://www.sun.com/blueprints/1000/layout.pdf>

I know the old Oracle rule-of-thumb "separate data and indizes, put
in different tablespaces, put each tablespace onto it's own disk" -
but IMNSHO this rule is pretty outdated in the time of massively
parallelized storage systems.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/