Table keeps crashing
Hi,
An acquired package (2 years ago) included MySQL 4.0.7g as database. The last few months, there is one table that keeps crashing. After a REPAIR it is available again, but only for say 2 weeks. Repairing takes approximately 3.5 hours, so it is not really something for every day.
The check table returns with different error messages: the last time it reported that the actual amount of data found was smaller than the expected amount.
A lot(?) of inserts are done on the table, evenly distributed in a timeframe from 9AM to 5PM.
Some figures:
- 10,000 records are added each working day (not that much seems to me)
- always about 3-4 users are looking up information concurrently
This is the structure of the table (I changed the name, do not want any troubles with the ISV)
----------
CREATE TABLE `datatable` (
`ValueKey` varchar(36) NOT NULL default '',
`CtntKey` varchar(36) NOT NULL default '',
`FieldKey` varchar(36) NOT NULL default '',
`FieldIndex` int(11) default NULL,
`IntVal` int(11) default NULL,
`LongVal` bigint(20) default NULL,
`FloatVal` float default NULL,
`DoubleVal` double default NULL,
`StringVal` mediumtext,
`ObjVal` mediumblob,
PRIMARY KEY (`ValueKey`),
KEY `CtntKey` (`CtntKey`),
KEY `IntField` (`FieldKey`,`IntVal`),
KEY `LongField` (`FieldKey`,`LongVal`),
KEY `FloatField` (`FieldKey`,`FloatVal`),
KEY `DoubleField` (`FieldKey`,`DoubleVal`),
KEY `StringField` (`FieldKey`,`StringVal`(32))
TYPE=MyISAM MAX_ROWS=500000 AVG_ROW_LENGTH=50000
----------
Currently the file is 1.3GB in size and the index 1.7GB. The file contains over 9.7 million rows. We only use the IntVal and StringVal fields to store data and the StringVal only stores company names, so the AVG_ROW_LENGTH is way below the expected average. The xKey fields contain UUID() values.
The table sizes look OK to me as MAX_ROWS * AVG_ROW_LENGTH results in an expected table size of about 23GB.
The table status
-------------------
Type: MyISAM
Row_format: Dynamic
Rows: 9764714
Avg_row_length: 141
Data_length: 1378428800
Max_data_length: 1099511627775
Index_length: 1745070080
Data_free: 0
Auto_increment: NULL
Create_time: 2004-07-22 14:53:54
Update_time: 2006-06-09 09:32:46
Check_time: 2006-06-09 01:11:05
Create_options: max_rows=500000 avg_row_length=50000
-------------------
Could the MAX_ROWS=500000 be the cause of our problems (>9Mi actual rows)?
I also found in the forums there are issues with the use of VARCHAR. Probably that is the culprit?
Thanks for helping me out,
Olivier
Edited 1 time(s). Last edit at 06/09/2006 01:53AM by Olivier Maurice.