Hi...

I'm trying to get partitioning to work on a 64-bit Debian system.
MySQL 5.1.20 beta.
The transcript is as follows; question follows later.

mysql> show variables like '%part%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> create table xxx(a int) engine=innodb partition by range(a)
(partition p0
values less than (10) data directory='/p0' index directory='/p0',
partition p1 values less than maxvalue data directory='/p1' index
directory='/p1');
Query OK, 0 rows affected (0.10 sec)

mysql> insert into xxx values(1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into xxx values(1000);
Query OK, 1 row affected (0.03 sec)

mysql> select * from xxx;
+------+
| a |
+------+
| 1 |
| 1000 |
+------+
2 rows in set (0.00 sec)

mysql:> quit
Bye
dev:~> cd /p0
dev:/p0> ls -la
total 24
drwxrwxrwx 3 root root 4096 2007-08-16 12:59 .
drwxr-xr-x 31 root root 4096 2007-08-14 15:28 ..
drwx------ 2 root root 16384 2007-08-14 15:24 lost+found

dev:/p0 cd ../p1
dev:/p1> ls -la
total 24
drwxrwxrwx 3 root root 4096 2007-08-16 12:59 .
drwxr-xr-x 31 root root 4096 2007-08-14 15:28 ..
drwx------ 2 root root 16384 2007-08-14 15:24 lost+found

Question: Where's the data/index in either partition?
Did the partitioning fail or did I forget to do something?

TIA,
Joe

JoeT <trubisz@yahoo.com> wrote:

> I'm trying to get partitioning to work on a 64-bit Debian system.
> MySQL 5.1.20 beta.
....

> mysql> create table xxx(a int) engine=innodb partition by range(a)
> (partition p0
> values less than (10) data directory='/p0' index directory='/p0',
> partition p1 values less than maxvalue data directory='/p1' index
> directory='/p1');

The DATA DIRECTORY and INDEX DIRECTORY options have a meaning only for
the MyISAM engine. Also those are optional - without MySQL decides by
itself how to name the partitions (but will put them in the same dir).

> dev:~> cd /p0
> dev:/p0> ls -la
> total 24
> drwxrwxrwx 3 root root 4096 2007-08-16 12:59 .
> drwxr-xr-x 31 root root 4096 2007-08-14 15:28 ..
> drwx------ 2 root root 16384 2007-08-14 15:24 lost+found
>
> dev:/p0 cd ../p1
> dev:/p1> ls -la
> total 24
> drwxrwxrwx 3 root root 4096 2007-08-16 12:59 .
> drwxr-xr-x 31 root root 4096 2007-08-14 15:28 ..
> drwx------ 2 root root 16384 2007-08-14 15:24 lost+found
>
> Question: Where's the data/index in either partition?
> Did the partitioning fail or did I forget to do something?

InnoDB by default uses a global table space to store all tables or -
in this case: all partitions of all tables. To see an effect of
partitioning in the file system you have to use innodb-file-per-table
or use MyISAM.

IMHO partitioning is overrated. Of course there are some applications,
i.e. partitioning data into live data and historic data and sometimes
dropping very old data by dropping a whole partition.
But especially your attempt to distribute partitions do different
physical disks is rather oldschool. Put all your disks in a fat RAID
and let the RAID driver distribute the load.

More technically spoken: the benefit from partitions is
- when the workload cannot use indexes and
- when the partitioning rules in place allow to restrict the
table scan to a single (or very few) partition(s)
then scanning only k out of n partitions will increase performance
by factor n/k; this is a constant factor of at most n.

OTOH: if you manage to make your workload use an index to scan a
table with n rows, the performance will boost by factor n/log(n) -
the factor is getting higher(!) with more data.

Conclusion: indexes are the way to go!



XL
--
Axel Schwenke, Support Engineer, MySQL AB

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