How does subpartitioning actually work and what physical files are created?

Question

I can't understand how the subpartitioning works in mysql.
I am reading the following example:

create table invoices (  
customer_id int not null,    
product_id int not null,    
amount decimal (8,2) not null,    
entry_date date not null default '2006-01-01',   
store_id int not null   
)  
partition by range (year(entry_date))  
subpartition by hash(customer_id)  
subpartitions 4 (  
partition p0 values less than (2008),   
partition p1 values less than (2009),   
partition p2 values less than (2010),  
partition p3 values less than (2011),  
partition plast values less than MAXVALUE  
);

I can not understand or visualize how this is formed in the HD.
I think that 4 * 5 = 20 physical files are created. Is this correct?
And what data are stored together? Is there a "clustering" of 2008 dates for all customers with the same hash? I am confused on that. Also I am confused how the data are fetched.
Is the customer_id hash used first to determine the range partition or the opposite. What are the exact steps to fetch the data?

UPDATE
Another example. I did the following:

mysql> create table nums_composite (  
    -> id int, happened datetime not null,  
    -> primary key (id, happened)  
    -> ) engine=InnoDB   
    -> partition by range (hour(happened))  
    -> subpartition by hash(id) subpartitions 2  
    -> (  
    -> partition p0 values less than (10),  
    -> partition p1 values less than (20),  
    -> partition p2 values less than MAXVALUE  
    -> );  
Query OK, 0 rows affected (0.28 sec)  


mysql> select partition_name, subpartition_name as sub, partition_method as method,   partition_description ,table_rows from information_schema.partitions where table_name='nums_composite';  
+----------------+-------+--------+-----------------------+------------+  
| partition_name | sub   | method | partition_description | table_rows |  
+----------------+-------+--------+-----------------------+------------+  
| p0             | p0sp0 | RANGE  | 10                    |         17 |  
| p0             | p0sp1 | RANGE  | 10                    |         24 |  
| p1             | p1sp0 | RANGE  | 20                    |         20 |  
| p1             | p1sp1 | RANGE  | 20                    |         17 |  
| p2             | p2sp0 | RANGE  | MAXVALUE              |         13 |  
| p2             | p2sp1 | RANGE  | MAXVALUE              |          9 |  
+----------------+-------+--------+-----------------------+------------+  
6 rows in set (0.00 sec)

It seems that I am right it creates hash_no* subpartition files but where are they?

root@jim-Linux:/# find . -iname "*_composite*"  
./var/lib/mysql/partioning_chapter_test/nums_composite.frm  
./var/lib/mysql/partioning_chapter_test/nums_composite.par

Why can't I see the actual files?

RolandoMySQLDBA · Accepted Answer · 2014-08-31T20:37:58.443

Sample in Windows

I ran the following on my Laptop in MySQL 5.6.15

mysql> use test
Database changed
mysql> drop table if exists nums_composite;
Query OK, 0 rows affected (0.70 sec)

mysql> create table nums_composite (
    -> id int, happened datetime not null,
    -> primary key (id, happened)
    -> ) engine=InnoDB
    -> partition by range (hour(happened))
    -> subpartition by hash(id) subpartitions 2
    -> (partition p0 values less than (10),
    -> partition p1 values less than (20),
    -> partition p2 values less than MAXVALUE);
Query OK, 0 rows affected (1.91 sec)

mysql> show create table nums_composite\G
*************************** 1. row ***************************
       Table: nums_composite
Create Table: CREATE TABLE `nums_composite` (
  `id` int(11) NOT NULL DEFAULT '0',
  `happened` datetime NOT NULL,
  PRIMARY KEY (`id`,`happened`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (hour(happened))
SUBPARTITION BY HASH (id)
SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.01 sec)

mysql>

I went to the OS and ran the following:

C:\>cd \MySQL_5.6.15\data\test

C:\MySQL_5.6.15\data\test>dir nums_comp*
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF

 Directory of C:\MySQL_5.6.15\data\test

08/31/2014  04:09 PM            98,304 nums_composite#p#p0#sp#p0sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p0#sp#p0sp1.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p1#sp#p1sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p1#sp#p1sp1.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p2#sp#p2sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p2#sp#p2sp1.ibd
08/31/2014  04:09 PM             8,594 nums_composite.frm
08/31/2014  04:09 PM                96 nums_composite.par
               8 File(s)        598,514 bytes
               0 Dir(s)  686,691,409,920 bytes free

C:\MySQL_5.6.15\data\test>

Reason you cannot see the files ?

I can see my data files because I have innodb_file_per_table enabled by default in MySQL 5.6.

mysql> show global variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql>

In your case, you must have innodb_file_per_table disabled. The actual table was inside the system tablespace file ibdata1 (usually located in /var/lib/mysql)

My Guess

You are using a version before MySQL 5.6
innodb_file_per_table is set to 0 because of one of the following:
- You are running on defaults (no my.cnf)
- You have my.cnf with innodb_file_per_table=0

How to fix this:

STEP #1 : Enable `innodb_file_per_table`

Add this to the my.cnf

innodb_file_per_table=1

STEP 2 : Restart mysql

service mysql restart

STEP 3 : Regenerate the table

CREATE TABLE nums_composite_new LIKE nums_composite;
INSERT INTO nums_composite_new SELECT * FROM  nums_composite;
ALTER TABLE nums_composite RENAME nums_composite_old;
ALTER TABLE nums_composite_new RENAME nums_composite;
DROP TABLE nums_composite_old;

The .ibd files should now be visible

GIVE IT A TRY !!!

UPDATE 2014-08-31 16:37 EDT

You asked me

I am in ubuntu.Does it change anything?

The only thing that changes is where my.cnf should be. Run this

ls -l /etc/mysql/my.cnf

If the file exists, open it in an editor and add innodb_file_per_table=1 under the [mysql] header

If the file does not exist, run this

echo "[mysqld]" > /etc/mysql/my.cnf
echo "innodb_file_per_table=1" >> /etc/mysql/my.cnf

Then, you can do STEP 2 and STEP 3

Run the query show global variables like 'datadir';. cd that folder, and you will see the ibdata. Then cd test a dn run ls -l nums_comp*. That's where the files should be. since they are not there and the information_schema says they are there, the table is logically inside ibdata1. — RolandoMySQLDBA, Aug 31 '14 at 20:31
That was the problem. You were right (as usual).Oddly I was not able to change the file per table setting by updating the my.cnf file. I had to actually do SET global innodb_file_per_table=ON — Jim, Sep 06 '14 at 21:14
Now that I am thinking about it, should the command had been echo "innodb_file_per_table=ON" >> /etc/mysql/my.cnf — Jim, Sep 06 '14 at 21:14
Not necessarily. There is a group header [mysqld] in my.cnf. Doing echo "innodb_file_per_table=ON" >> /etc/mysql/my.cnf only works if [mysqld] is the last group header. If it is not, it will get missed. In fact, that might prevent mysqld from being restarted. You should manually edit the my.cnf file and make sure it is under the [mysqld] group header. — RolandoMySQLDBA, Sep 06 '14 at 22:13
You are right on that. The innodb_file_per_table was not under [mysqld] header. But the last line under an include.But is is innodb_file_per_table=ON or innodb_file_per_table=1?In the answer you mention 1. I had set it to ON via the SET GLOBAL — Jim, Sep 07 '14 at 09:05

How does subpartitioning actually work and what physical files are created?

1 Answers1

Sample in Windows

Reason you cannot see the files ?

My Guess

STEP #1 : Enable `innodb_file_per_table`

STEP 2 : Restart mysql

STEP 3 : Regenerate the table

GIVE IT A TRY !!!

UPDATE 2014-08-31 16:37 EDT

Linked

How does subpartitioning actually work and what physical files are created?

1 Answers1

Sample in Windows

Reason you cannot see the files ?

My Guess

STEP #1 : Enable innodb_file_per_table

STEP 2 : Restart mysql

STEP 3 : Regenerate the table

GIVE IT A TRY !!!

UPDATE 2014-08-31 16:37 EDT

Linked

STEP #1 : Enable `innodb_file_per_table`