Partitioned table causes open_files errror
Hello:
We are using MySQL v5.1.21 with a partitioned table using MyISAM engine.
We are getting the following message in the error log.
080220 9:16:55 [ERROR] /usr/libexec/mysqld: Can't open file: './stun_development/Subscribers.frm' (errno: 24)
The client issuing the query gets an error at the same time.
The number of open files is very high as shown by lsof:
#> lsof -u mysql | wc
7771 69944 914577
We have increased the "open_files" variable from 1024->2048->4096 and now 8192.
Each time, the limit is exceeded after some time of the database running.
The number of open files seems to grow unbounded, with spurts during certain query executions.
The number of files in the data directory (including partitions) is < 500.
Any idea why this is happening and how to resolve it?
Here is the definition of partitioned table.
CREATE TABLE `Subscribers` (
`SubscribeID` int(11) NOT NULL AUTO_INCREMENT,
`UserID` int(10) unsigned NOT NULL DEFAULT '0',
`FormID` int(11) NOT NULL DEFAULT '0',
`Lists` varchar(100) NOT NULL DEFAULT '',
`ClientID` int(11) NOT NULL DEFAULT '0',
`CommPreference` varchar(25) NOT NULL DEFAULT '',
`FirstName` varchar(50) NOT NULL DEFAULT '',
`LastName` varchar(50) NOT NULL DEFAULT '',
`Address1` varchar(250) NOT NULL DEFAULT '',
`Address2` varchar(250) NOT NULL DEFAULT '',
...
`UploadID` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`SubscribeID`,`ClientID`),
KEY `ix_ClientID` (`ClientID`,`SubscribeID`),
KEY `ix_HomePhone` (`HomePhone`,`ClientID`),
KEY `ix_FaxNumber` (`FaxNumber`,`ClientID`),
KEY `ix_CellPhoneNumber` (`CellPhoneNumber`,`ClientID`),
KEY `ix_DateCreated` (`DateCreated`,`ClientID`),
KEY `ix_LastModified` (`LastModified`,`ClientID`),
KEY `ix_Status` (`Status`,`ClientID`),
KEY `ix_SubscriptionStatus` (`SubscriptionStatus`,`ClientID`),
KEY `ix_Email` (`Email`,`ClientID`)
) ENGINE=MyISAM AUTO_INCREMENT=62137538 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( ClientID) (PARTITION p0 VALUES LESS THAN (30) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (31) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (100) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (200) ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN (300) ENGINE = MyISAM, PARTITION p5 VALUES LESS THAN (400) ENGINE = MyISAM, PARTITION p6 VALUES LESS THAN (500) ENGINE = MyISAM, PARTITION p7 VALUES LESS THAN (600) ENGINE = MyISAM, PARTITION p8 VALUES LESS THAN (700) ENGINE = MyISAM, PARTITION p9 VALUES LESS THAN (800) ENGINE = MyISAM, PARTITION p10 VALUES LESS THAN (834) ENGINE = MyISAM, PARTITION p11 VALUES LESS THAN (835) ENGINE = MyISAM, PARTITION p12 VALUES LESS THAN (900) ENGINE = MyISAM, PARTITION p13 VALUES LESS THAN (1000) ENGINE = MyISAM, PARTITION p14 VALUES LESS THAN (1100) ENGINE = MyISAM, PARTITION p15 VALUES LESS THAN (1200) ENGINE = MyISAM, PARTITION p16 VALUES LESS THAN (1300) ENGINE = MyISAM, PARTITION p17 VALUES LESS THAN (1313) ENGINE = MyISAM, PARTITION p18 VALUES LESS THAN (1314) ENGINE = MyISAM, PARTITION p19 VALUES LESS THAN (1400) ENGINE = MyISAM, PARTITION p20 VALUES LESS THAN (1437) ENGINE = MyISAM, PARTITION p21 VALUES LESS THAN (1438) ENGINE = MyISAM, PARTITION p22 VALUES LESS THAN (1500) ENGINE = MyISAM, PARTITION p23 VALUES LESS THAN (1600) ENGINE = MyISAM, PARTITION p24 VALUES LESS THAN (1700) ENGINE = MyISAM, PARTITION p25 VALUES LESS THAN (1800) ENGINE = MyISAM, PARTITION p26 VALUES LESS THAN (1900) ENGINE = MyISAM, PARTITION p28 VALUES LESS THAN (2000) ENGINE = MyISAM, PARTITION p29 VALUES LESS THAN (2100) ENGINE = MyISAM, PARTITION p30 VALUES LESS THAN (2200) ENGINE = MyISAM, PARTITION p31 VALUES LESS THAN (2300) ENGINE = MyISAM, PARTITION p32 VALUES LESS THAN (2400) ENGINE = MyISAM, PARTITION p33 VALUES LESS THAN (2500) ENGINE = MyISAM, PARTITION p34 VALUES LESS THAN (2600) ENGINE = MyISAM, PARTITION p35 VALUES LESS THAN (2700) ENGINE = MyISAM, PARTITION p36 VALUES LESS THAN (2800) ENGINE = MyISAM, PARTITION p37 VALUES LESS THAN (2900) ENGINE = MyISAM, PARTITION p38 VALUES LESS THAN (3000) ENGINE = MyISAM, PARTITION p39 VALUES LESS THAN (3100) ENGINE = MyISAM, PARTITION p40 VALUES LESS THAN (3200) ENGINE = MyISAM, PARTITION p41 VALUES LESS THAN (3300) ENGINE = MyISAM, PARTITION p42 VALUES LESS THAN (3400) ENGINE = MyISAM, PARTITION p43 VALUES LESS THAN (3500) ENGINE = MyISAM, PARTITION p44 VALUES LESS THAN (3600) ENGINE = MyISAM, PARTITION p45 VALUES LESS THAN (3700) ENGINE = MyISAM, PARTITION p46 VALUES LESS THAN (3800) ENGINE = MyISAM, PARTITION p47 VALUES LESS THAN (3900) ENGINE = MyISAM, PARTITION p48 VALUES LESS THAN (4000) ENGINE = MyISAM, PARTITION p49 VALUES LESS THAN (4100) ENGINE = MyISAM, PARTITION p50 VALUES LESS THAN (4200) ENGINE = MyISAM, PARTITION pNN VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */