MySQL Forums
Forum List  »  Partitioning

Re: UNABLE TO INSERT DATA IN ARCHIVE ENGINE HAVING TABLE WITH 1015 PARTITIONS
Posted by: Mikael Ronström
Date: January 17, 2007 05:18PM

Hi,

Ijaz Rashid Wrote:
-------------------------------------------------------
> ##################
> PROBLEM
> ##################
> I've created a table using system variables and
> schema below successfully. But when i started
> inserting records in table, I recieved table
> crashed error below.
> If i use less number of partitions, every thing
> works fine. PROBLEM occcurs when partition number
> exceeds 500 partitions.
>
> ERROR : "Table is crashed and need's to be
> repaired"
> mysql> CHECK TABLE AR;
> (0 error, 1001 Warnings)
> mysql> SHOW WARNINGS;
> Out of resources when opening file
> 'C:\...\TEMP\p_2007011600#sp01' (Errcode: 24)
>

perror 24 shows Too many open files, you need to extend even
beyond 2048 that you've set below.

Rgrds Mikael


>
>
> ##################
> System Variables
> ##################
> OPEN_FILES_LIMIT=2048
> TABLE_CACHE=3000
> THREAD_CACHE=80
> BULK_INSERT_BUFFER_SIZE=32MB.
>
> ##################
> Table Schema
> ##################
> DROP TABLE IF EXISTS AR;
> CREATE TABLE AR (TagID smallint NOT NULL, Value
> smallint NOT NULL, Time timestamp NOT NULL)
> ENGINE=ARCHIVE
> PARTITION BY RANGE (unix_timestamp(time))
> SUBPARTITION BY HASH(TagID)
> SUBPARTITIONS 202
> (
> PARTITION p_2007011600 VALUES LESS THAN
> (unix_timestamp('2007-01-16 00:00:00')),
> PARTITION p_2007011700 VALUES LESS THAN
> (unix_timestamp('2007-01-17 00:00:00')),
> PARTITION p_2007011800 VALUES LESS THAN
> (unix_timestamp('2007-01-18 00:00:00')),
> PARTITION p_2007011900 VALUES LESS THAN
> (unix_timestamp('2007-01-19 00:00:00')),
> PARTITION p_2007012000 VALUES LESS THAN MAXVALUE
> );
>
> ##################
> INSERT STMT
> ##################
> insert into AR values (1,1,'07-01-02 00:00:01'),
> (2,2,'07-01-02 00:00:01'),
> .....
> (35000,1,'07-01-02 00:00:01')
>
> Thank you for help,
> IR

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.