"Can't create table" when having to many partitions
- by Chris
I am currently having a problem I dont understand. Wherever I look it says mySQL (5.5) / InnoDB doesnt have a table limit.
I wanted to test the InnoDB compression and was about to create an empty copy of an existing table and ran into the following problem.
this one works:
CREATE TABLE `hsc` (
LOTS OF STUFF
) ENGINE=InnoDB CHARSET=utf8
PARTITION BY RANGE (pid)
SUBPARTITION BY HASH (cons)
SUBPARTITIONS 2
(PARTITION hsc_p0 VALUES LESS THAN (10000) ,
PARTITION hsc_p1 VALUES LESS THAN (20000) ,
PARTITION hsc_p2 VALUES LESS THAN (30000) ,
PARTITION hsc_p3 VALUES LESS THAN (40000) ,
PARTITION hsc_p4 VALUES LESS THAN (50000) ,
PARTITION hsc_p40 VALUES LESS THAN (4000000) );
this one doesn't:
CREATE TABLE `hsc` (
LOTS OF STUFF
) ENGINE=InnoDB CHARSET=utf8
PARTITION BY RANGE (pid)
SUBPARTITION BY HASH (cons)
SUBPARTITIONS 2
(PARTITION hsc_p0 VALUES LESS THAN (10000) ,
PARTITION hsc_p1 VALUES LESS THAN (20000) ,
PARTITION hsc_p2 VALUES LESS THAN (30000) ,
PARTITION hsc_p3 VALUES LESS THAN (40000) ,
PARTITION hsc_p4 VALUES LESS THAN (50000) ,
PARTITION hsc_p5 VALUES LESS THAN (75000) ,
PARTITION hsc_p6 VALUES LESS THAN (100000) ,
PARTITION hsc_p7 VALUES LESS THAN (125000) ,
PARTITION hsc_p8 VALUES LESS THAN (150000) ,
PARTITION hsc_p9 VALUES LESS THAN (175000) ,
PARTITION hsc_p40 VALUES LESS THAN (4000000) );
ERROR 1005 (HY000): Can't create table 'hsc' (errno: 1)
Its reproducable by removing the number of partitions and adding them again. it does not have to do anything with the name of the table as i tried various names. there is also enough empty space on the HDD.
/dev/simfs 230G 26G 192G 12% /var/lib/mysql.mnt
There should be no limit on the partitions
http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations.html
Maximum number of partitions. The maximum possible number of partitions for a given table (that does not use the NDB storage engine) is 1024. This number includes subpartitions.
i have increased both open_files
show variables where variable_name LIKE '%open_files%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| innodb_open_files | 512 |
| open_files_limit | 1536 |
+-------------------+-------+
No change. Any clues where should I start looking?
UPDATE: the whole thing is running in an openvz environment. i saw in users_beancounters that the numflock was a problem, so i increased it. but the problem still persists.
maybe this helps:
ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 515011
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 515011
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
cat /proc/user_beancounters
Version: 2.5
uid resource held maxheld barrier limit failcnt
200: kmemsize 9309653 13357056 14372700 14790164 0
lockedpages 0 1008 2048 2048 0
privvmpages 675424 686528 1048576 1572864 0
shmpages 33 673 21504 21504 0
dummy 0 0 9223372036854775807 9223372036854775807 0
numproc 49 90 240 240 0
physpages 243761 246945 0 9223372036854775807 0
vmguarpages 0 0 1048576 1048576 0
oomguarpages 81672 83305 1048576 1048576 0
numtcpsock 6 8 360 360 0
numflock 175 188 512 512 8
numpty 1 9 16 16 0
numsiginfo 0 48 256 256 0
tcpsndbuf 104640 263912 1720320 2703360 0
tcprcvbuf 98304 131072 1720320 2703360 0
othersockbuf 32368 89304 1126080 2097152 0
dgramrcvbuf 0 2312 262144 262144 0
numothersock 19 28 360 360 0
dcachesize 2285052 3624426 3409920 3624960 0
numfile 616 870 9312 9312 0
dummy 0 0 9223372036854775807 9223372036854775807 0
dummy 0 0 9223372036854775807 9223372036854775807 0
dummy 0 0 9223372036854775807 9223372036854775807 0
numiptent 24 24 128 128 0
