May 2009 – IndianGNU.org

Partitioning in MySQL

By Arun Bagul | May 10, 2009 | Comments 2 comments

Introduction –

Partitioning is available in Oracle since 8i (few feature or type) version but in MySQL it is available after 5.1 version. Partitioning allows you to distribute portions of table across a file system (physically at different location). User can define the partitioning of table so that some portion of table can stored at different physical location….

Table is structure which has columns and rows. Partitioning can be applied to rows or volumes.

* Horizontal partitioning ~ Storing different rows of table to different physical location is called as horizontal partitioning of table.
* Vertical partitioning ~ Storing different columns of table to different physical location is called as vertical partitioning of table.

MySQL supports only support “Horizontal partitioning” !!

** Advantages of using partitions ~

a) Smaller and more manageable pieces of data
b) Faster access of data
c) Import / export at the partition Level
d) Each partitions is independent of other
e) Easy to use
f) Reduced recovery time
g) Failure impact is less

1] How to check whether MySQL installed on your system supports ‘partitioning’ ?

* Check MySQL version as…

root@laptop:~# mysqladmin version -u root -p

* Check ‘partition’ plugin is installed or not… ?

+————+———-+—————-+———+———+
10 rows in set (0.00 sec)

mysql>

* Check above output. If you don’t see ‘partition’ entry in above query. Then MySQL version does not support partitioning. If it entry exist then check “have_partitioning” system variable is set to ‘YES’ or not….

2] Which storage engines (table type) supports partitioning?

MySQL partitioning engine runs in a separate layer and can interact with any of storage engine. So we can use most of the storage engine as backend/table type to store data. Note we must use same storage engine for all partitions of table.

3] Types of Partitions ~

There are 4 types of partitioning in MySQL or Oracle

a) Range Partitioning
b) List Partitioning
c) Hash Partitioning
d) Key Partitioning

NOTE ~ In any type of partitioning – partitions are always numbered automatically and in sequence starting with 0. When a new row is inserted into a partitioned table, these partition numbers that are used in identifying the correct partition. Also partition names are not case-sensitive. Number of partitions for the table, this must be expressed as a positive, non-zero integer literal with no leading zeroes, and may not be an expression.

MAXVALUE ~ It is an integer value that is always greater than the largest possible integer value (least upper limit).

** Let’s create database ‘mydata’ and create table with each partition type –

mysql> create database mydata;
Query OK, 1 row affected (0.33 sec)

mysql>

A] Range Partitioning ~ table is partitioned by range of values. Each partition contains row which lies within a given range. The ranges should be
contiguous but not overlapping…

mysql> use mydata;
Database changed

mysql> CREATE TABLE cricket (
-> player_name VARCHAR(30),
-> country_name VARCHAR(30),
-> match_count INT,
-> run_scored INT NOT NULL
-> )
-> PARTITION BY RANGE (run_scored)
-> (
-> PARTITION p0 VALUES LESS THAN (26),
-> PARTITION p1 VALUES LESS THAN (51),
-> PARTITION p2 VALUES LESS THAN (76),
-> PARTITION p3 VALUES LESS THAN (101),
-> PARTITION p4 VALUES LESS THAN MAXVALUE
->
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> show tables from mydata;
+——————+
| Tables_in_mydata |
+——————+
| cricket |
+——————+
1 row in set (0.00 sec)

mysql>

*** If cricketer’s score is…
<= 25 then row will go to p0 partition
<= 50 then row will go to p1 partition and so on ..
If score is more than 100 then row will go to p4 partition

* inserting few records to test behavior…

mysql> SELECT * FROM cricket;
+————-+————–+————-+————+
| player_name | country_name | match_count | run_scored |
+————-+————–+————-+————+
| Rahul | India | 2 | 16 |
| Sachin | India | 2 | 56 |
| Saurav | India | 2 | 80 |
| Sachin | India | 1 | 122 |
| Rahul | India | 1 | 153 |
| Saurav | India | 1 | 183 |
+————-+————–+————-+————+
6 rows in set (0.00 sec)

mysql>

** Let’s us check the physical location of database….

root@laptop:~# ls /var/lib/mysql/mydata/
cricket.frm cricket#P#p0.MYD cricket#P#p1.MYD cricket#P#p2.MYD cricket#P#p3.MYD cricket#P#p4.MYD db.opt
cricket.par cricket#P#p0.MYI cricket#P#p1.MYI cricket#P#p2.MYI cricket#P#p3.MYI cricket#P#p4.MYI
root@laptop:~#

B] LIST Partitioning ~ It is similar to range partitioning. But each partition is defined based on the columns value which is one of value in lists,
rather than in one of a set of contiguous ranges of values…

mysql> CREATE TABLE world (
-> country_name VARCHAR(25) NOT NULL,
-> country_id INT NOT NULL
-> )
-> PARTITION BY LIST (country_id)
-> (
-> PARTITION asia VALUES IN (91,95,35,20),
-> PARTITION europe VALUES IN (10,16,82,84),
-> PARTITION america VALUES IN (12,25),
-> PARTITION australia VALUES IN (60,65)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql>

* inserting few records to test behavior…

mysql> SELECT * FROM world;
+————–+————+
| country_name | country_id |
+————–+————+
| India | 91 |
| Sri Lanka | 95 |
| France | 10 |
| U.K. | 16 |
| Canada | 12 |
| Brazil | 60 |
+————–+————+
6 rows in set (0.00 sec)

mysql>

** What if ‘country_id’ value NOT matching with lists values ?

=> use IGNORE keyword while inserting records… let’s try

mysql> INSERT IGNORE INTO world VALUES (‘South Africa’,50);
Query OK, 0 rows affected (0.00 sec)

mysql>

C] HASH Partitioning ~ HASH is used primarily to ensure an even distribution of data among a all number of partitions. If you do not include a
PARTITIONS clause while creating table with HASH partitioning, the number of partitions by defaults is one(1). The partition in
which the record is stored is determined by which_partition = MOD(expression, total_no_partition)

mysql> CREATE TABLE africa (
-> country_name VARCHAR(50),
-> population INT,
-> census_date DATE NOT NULL DEFAULT ‘2000-01-01’
-> )
-> PARTITION BY HASH (YEAR(census_date))
-> PARTITIONS 5;
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> SELECT * FROM africa;
+————–+————+————-+
| country_name | population | census_date |
+————–+————+————-+
| Nigeria | 2278032 | 2008-09-18 |
| Egypt | 21278032 | 2008-05-28 |
| South Africa | 1678032 | 2009-04-20 |
+————–+————+————-+
3 rows in set (0.00 sec)

mysql>

D] KEY Partitioning ~ It is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression.

4] Subpartitioning ~

It is also known as composite partitioning in which partition is divided in to sub partition. Use SUBPARTITION clauses while creating table.

**Few useful command to know more about tables –

mysql> SHOW CREATE TABLE cricket \G
*************************** 1. row ***************************
Table: cricket
Create Table: CREATE TABLE `cricket` (
`player_name` varchar(30) DEFAULT NULL,
`country_name` varchar(30) DEFAULT NULL,
`match_count` int(11) DEFAULT NULL,
`run_scored` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (run_scored)
(PARTITION p0 VALUES LESS THAN (26) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (51) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (76) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (101) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.00 sec)

mysql>

** Below statement will give information about all tables in given DB…

mysql> SHOW TABLE STATUS FROM mydata;

Thank you,
Arun Bagul