| Bug #19685 | Creating a large number of partitions and subpartitions give storage error. | ||
|---|---|---|---|
| Submitted: | 10 May 2006 14:13 | Modified: | 12 May 2006 21:13 |
| Reporter: | Brett Simpson | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 5.1.9 | OS: | Linux (CentOS 4.3) |
| Assigned to: | CPU Architecture: | Any | |
[10 May 2006 15:55]
Brett Simpson
The following does not give me an error while the second table does. It might have something to do with the number of partitions and subpartitions exceed a certain number. So for the first one below it used 477 partitions/subpartitions while the one that errored used 486.
CREATE TABLE event ( sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
signature INT UNSIGNED NOT NULL,
timestamp DATETIME NOT NULL,
PRIMARY KEY (sid,cid,timestamp),
INDEX sig (signature),
INDEX time (timestamp))
PARTITION BY RANGE(HOUR(timestamp))
SUBPARTITION BY HASH( MINUTE(timestamp) )
SUBPARTITIONS 53 (
PARTITION p1 VALUES LESS THAN (5),
PARTITION p2 VALUES LESS THAN (6),
PARTITION p3 VALUES LESS THAN (7),
PARTITION p4 VALUES LESS THAN (8),
PARTITION p5 VALUES LESS THAN (9),
PARTITION p6 VALUES LESS THAN (10),
PARTITION p7 VALUES LESS THAN (11),
PARTITION p8 VALUES LESS THAN (12),
PARTITION p9 VALUES LESS THAN (13)
);
This one gives an error.
CREATE TABLE event ( sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
signature INT UNSIGNED NOT NULL,
timestamp DATETIME NOT NULL,
PRIMARY KEY (sid,cid,timestamp),
INDEX sig (signature),
INDEX time (timestamp))
PARTITION BY RANGE(HOUR(timestamp))
SUBPARTITION BY HASH( MINUTE(timestamp) )
SUBPARTITIONS 54 (
PARTITION p1 VALUES LESS THAN (5),
PARTITION p2 VALUES LESS THAN (6),
PARTITION p3 VALUES LESS THAN (7),
PARTITION p4 VALUES LESS THAN (8),
PARTITION p5 VALUES LESS THAN (9),
PARTITION p6 VALUES LESS THAN (10),
PARTITION p7 VALUES LESS THAN (11),
PARTITION p8 VALUES LESS THAN (12),
PARTITION p9 VALUES LESS THAN (13)
);
[10 May 2006 16:11]
Hartmut Holzgraefe
For me it fails with a slightly different error message with an "open files" ulimit of 1024: ERROR 29 (HY000): File './test/event#P#p9#SP#p8sp16.MYD' not found (Errcode: 24) when raising the limit to 2100 (2048 was not enough) open files the EXPLAIN statement works fine when further raising it to 2500 then the "Partition: " field in EXPLAIN output suddenly becomes empty
[10 May 2006 16:22]
Hartmut Holzgraefe
The exact cutoff point for the original CREATE statement is at 2070 open files, it failes with ulimit -n 2070 and works with ulimit -n 2071 The empty partitions filed is not related to the ulimit value, it turned out that it is empty on the first request and filled on subsequent EXPLAIN requests for the same statement, i'll probably create a seperate bug report for that
[10 May 2006 17:12]
Brett Simpson
Ah! ulimit. I should have checked for that. I upped my ulimit on open files and was able to create the partitions/subpartitions. Thanks
[12 May 2006 21:13]
Hartmut Holzgraefe
See "Restrictions and Limitations on Partitioning" :
* If, when creating tables with a very large number of partitions,
you encounter an error message such as Got error 24 from
storage engine, you may need to increase the value of the
open_files_limit system variable.
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html
Description: Not sure as to the exact cause for this. How to repeat: CREATE TABLE event ( sid INT UNSIGNED NOT NULL, cid INT UNSIGNED NOT NULL, signature INT UNSIGNED NOT NULL, timestamp DATETIME NOT NULL, PRIMARY KEY (sid,cid,timestamp), INDEX sig (signature), INDEX time (timestamp)) PARTITION BY RANGE(HOUR(timestamp)) SUBPARTITION BY HASH( MINUTE(timestamp) ) SUBPARTITIONS 60 ( PARTITION p1 VALUES LESS THAN (5), PARTITION p2 VALUES LESS THAN (6), PARTITION p3 VALUES LESS THAN (7), PARTITION p4 VALUES LESS THAN (8), PARTITION p5 VALUES LESS THAN (9), PARTITION p6 VALUES LESS THAN (10), PARTITION p7 VALUES LESS THAN (11), PARTITION p8 VALUES LESS THAN (12), PARTITION p9 VALUES LESS THAN (13), PARTITION p10 VALUES LESS THAN (14), PARTITION p11 VALUES LESS THAN (15), PARTITION p12 VALUES LESS THAN (16), PARTITION p13 VALUES LESS THAN (17), PARTITION p14 VALUES LESS THAN (18), PARTITION p15 VALUES LESS THAN (19), PARTITION p16 VALUES LESS THAN (20), PARTITION p17 VALUES LESS THAN (25) ); EXPLAIN PARTITIONS SELECT * FROM event\G ERROR 1030 (HY000): Got error 24 from storage engine Doing an ls -l on /var/lib/mysql/snort shows the partitions... e.g. -rw-rw---- 1 mysql mysql 1024 May 10 10:26 event#P#p9#SP#p8sp5.MYI -rw-rw---- 1 mysql mysql 0 May 10 10:26 event#P#p9#SP#p8sp6.MYD Total event table files are 2042 (MYI and MYD) for 1021 partitions.