Re: Help: unable to create 3 tables each having 500 partitions

[Mikael Ronström]

Hi,

Ijaz Rashid Wrote:
-------------------------------------------------------
> ##########
> PROBLEM
> ##########
> My requirements are to create 2,600 tables in one
> DB each having 500 partotions. But I'm unable to
> create even three (ARCHIVE, MYISAM).
>

Eac partitioned table will have 500 * 2 files attached to it
and they will all be opened when the table is open. This is why
the third fails since it will try to increase the number of
open files to 3000. You can avoid this problem by flushing the
tables after creating them but this will only temporarily solve
the problem. So at first I would recommend considering if it is
really necessary to have 500 partitions on each table. It's usually
such that lots of partitions are needed on one big table and not
on all the other small tables around it.

Rgrds Mikael


> Open_file_limit = 2048 (Maximum for windows OS)
> Error is "Out Of Resources when opening
> file'\dbname\t1#P#p4.MYD'(ErrCode:24); "
>
> Windows 2000 SP 4
> MySql 5.1.14-beta-community-nt
> ##########
> EXAMPLE
> ##########
> DROP TABLE IF EXISTS T1;
> CREATE TABLE T1 ( ID smallint , Time timestamp NOT
> NULL) ENGINE=ARCHIVE
> PARTITION BY HASH (ID)
> PARTITIONS 500;
>
> ...
>
> DROP TABLE IF EXISTS T3;
> CREATE TABLE T3 ( ID smallint , Time timestamp NOT
> NULL) ENGINE=ARCHIVE
> PARTITION BY HASH (ID)
> PARTITIONS 500;
>
>
> Thank you.