Bug #47492 extend limit beyond 1024 partitions
Submitted: 21 Sep 2009 18:40 Modified: 6 Sep 2012 11:51
Reporter: Scott Noyes Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:5.1+ OS:Any
Assigned to: CPU Architecture:Any

[21 Sep 2009 18:40] Scott Noyes
Description:
The maximum number of partitions possible for a given table is 1024.

How to repeat:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html

Suggested fix:
Request is to allow more than 1024.
[21 Sep 2009 18:48] Eyal Sorek
Hi,

There are tables which suppose to hold lots of archive and/or billing information which needs to preserve more than ~2.8 years (1024 / 365 days).

And instead of doing all kind redundant heavy administration on large tables with big partitions, like: large insert..select or rename tables.., it would be much  cleaner and easier not to have this low limitation....

Thanks,
Eyal Sorek
[12 Jun 2012 10:29] Mattias Jonsson
Would 4096 partitions be sufficient? That would allow more than 11 years of 1 partition per day.
[12 Jun 2012 10:33] Tim Jennings
That would help. 

Is there a good reason to have a limit at all? I'm guessing it's something to do with open file handles.
[12 Jun 2012 10:58] Mattias Jonsson
Each partition can have its own resources (memory, files, etc.) and there is no partition cache in the table cache, it includes all partitions. So currently the limit is there to avoid having too many open files, or use too much memory. I need to test a bit more what a reasonable limit would be, but 4096 should be OK in 5.6, perhaps even 8K if needed, so any input on the use of thousands partitions is appreciated!

The fix itself is only to modify the define of MAX_PARTITIONS in sql_const.h.
[6 Sep 2012 11:51] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html
[6 Sep 2012 11:52] Jon Stephens
Fixed in 5.6.7. (The new limit is 8192 total partitions + subpartitions.)