One good feature in MySQL 5.1 is the support of partitions which is built into the MySQL server. Lot of applications that I noticed use their own partition schemes by splitting the large table data to multiple tables on a desired key and wrapper table or application logic that identifies what table to use.
Here comes MySQL to rescue all those applications to push the partition logic to MySQL server itself without needing a application specific logic; which maintains partitions underneath the covers (each partition is a sub-table) on a single large table as shown below:
The partition key is specific to table and/or application. If the partition key is account_id or site_id or anything that takes both active writes and reads; it is easy to make the partition table as InnoDB; so that they can really scale well.
If the partitions purpose is to serve reads; then making it MyISAM makes much better sense. Again, MyISAM with partitions does not support KEY CACHE or LOAD INDEX on partitioned table (hopefully in the future versions we can expect), that leaves to use only global key_buffer_size.
By the way, all the tests that I did so far, proves that MyISAM works well with partitions when compared to InnoDB due to stats that InnoDB provides to optimizer on partitions (not always) seems to be wrong especially when you have a partition on a range which is not unique (unless the application logic is forced to use INDEX or have the right join order and use STRAIGHT_JOIN).
The real problem comes when you have a table where active writes happens on a particular range (like current month) and rest of them are used as logging or serving for reports as shown below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DROP TABLE IF EXISTS test_part; CREATE TABLE test_part( date DATE NOT NULL DEFAULT '0000-00-00', comment VARCHAR(20) DEFAULT NULL )ENGINE=MyISAM PARTITION BY RANGE (to_days(date)) ( PARTITION nov08 VALUES LESS THAN(TO_DAYS('2008-12-01')), PARTITION dec08 VALUES LESS THAN(TO_DAYS('2009-01-01')), PARTITION jan09 VALUES LESS THAN(TO_DAYS('2009-02-01')), PARTITION feb09 VALUES LESS THAN(TO_DAYS('2009-03-01')), PARTITION mar09 VALUES LESS THAN(TO_DAYS('2009-04-01')), PARTITION unpart VALUES LESS THAN MAXVALUE ); |
In this case, choosing a right table type is more complex as making it MyISAM blocks the reads on active writes to current month; and choosing InnoDB makes it use more tablespace and memory along with performance penalty.
The one solution for this is to make active writes partition (current month, partition mar09 in the above example) as InnoDB and rest of the partitions as MyISAM. Unfortunately MySQL does not yet support mixing of table types within the partitions.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> mysql> CREATE TABLE test_part( -> date DATE NOT NULL DEFAULT '0000-00-00', -> comment VARCHAR(20) DEFAULT NULL -> )ENGINE=MyISAM -> PARTITION BY RANGE (to_days(date)) -> ( -> PARTITION nov08 VALUES LESS THAN(TO_DAYS('2008-12-01')), -> PARTITION dec08 VALUES LESS THAN(TO_DAYS('2009-01-01')), -> PARTITION jan09 VALUES LESS THAN(TO_DAYS('2009-02-01')), -> PARTITION feb09 VALUES LESS THAN(TO_DAYS('2009-03-01')), -> PARTITION mar09 VALUES LESS THAN(TO_DAYS('2009-04-01')) ENGINE=InnoDB, -> PARTITION unpart VALUES LESS THAN MAXVALUE -> ); ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL mysql> |
So, usage of partitions on date ranges with writes happening in the current month, will have a tough decision to make until MySQL supports mix of table handlers within the partitioned table.
We started using partitions since 6 months in production and we had a table with logging of information for last few years and current month with inserts, updates.. and we left with choosing InnoDB at this time.. but once MySQL supports mix of handles; then old tables will be MyISAM and current active partition will be InnoDB
Don’t hold your breath. Mix of handlers is not in the immediate plans, AFAIK.
If you think it’s a desirable feature, feel free to propose a patch, though.
In the meantime, if you need to deal with short partitions on recent data and long partitions on historical data, you can create partitions of variable length. Yearly partitions for the old data, monthly partitions for the recent data.
Giuseppe
New blog post: http://tinyurl.com/cr5arp – MySQL 5.1 Mix Of Handlers In Partitions
MySQL 5.1 Mix Of Handlers In Partitions: One good feature in MySQL 5.1 is the support of partitions which is bui.. http://tinyurl.com/cr5arp
[…] Venu Anuganti was also on the trail of partitions. He examined MySQL’s 5.1 Mix Of Handlers In Partition. […]
The stats for partitioned tables for all engines were being calculated based on only the first partition. That’s now fixed in 5.1.41 and later:
http://bugs.mysql.com/bug.php?id=44059
For InnoDB there was a bug in the 64 bit server index dive algorithm that caused it not to use truly random dives. Set innodb_use_legacy_cardinality_algorithm = 0 to turn off compatibility and turn on the more truly random fixed method. This bug fix:
http://bugs.mysql.com/bug.php?id=43660
For partitioning in general, get a recent build like 5.1.43 when it’s released soon. Lots of partitioning bug fixes since March 2009.
Hi Is there a way to pass variable instead sending the date directly inside a stored procedure?
FOR Example:
SET TODAY_DATE=curdate();
SET NEXT_YEAR_DATE = (SELECT DATE_ADD(TODAY_DATE, INTERVAL 1 YEAR));
ALTER TABLE MEMBERS ADD PARTITION (PARTITION p8 VALUES LESS THAN (TO_DAYS(NEXT_YEAR_DATE)));
The above command is not working. It says If I try to create the above procedure, the error is thrown like: ERROR 1564 (HY000): This partition function is not allowed .
Where as if pass the date, it is working. Ex: ALTER TABLE MEMBERS ADD PARTITION (PARTITION p8 VALUES LESS THAN (TO_DAYS(‘2012-10-01’)));
Thanks,
Madhu