Re: Often corrupt table indexes
Posted by:
oliver
Date: July 20, 2005 01:20AM
Hi Ingo,
actually I do have a very repeatable szenario in our Database System. Let me outline to you what we do: We collect Log-Information from our pc's (about 20.00) and put them into a table with the following structure:
CREATE TABLE `log_05_02` (
`LOG_ID` varchar(18) NOT NULL,
`LOG_LIEFERDAT` date NOT NULL,
`LOG_MANDANT` varchar(10) default NULL,
`LOG_PORTAL` varchar(20) default NULL,
`LOG_BENGR` varchar(5) default NULL,
`LOG_BENF` varchar(10) default NULL,
`LOG_ZN` char(2) default NULL,
`LOG_GS` char(3) default NULL,
`LOG_AGENTUR` varchar(4) default NULL,
`LOG_ARBEITSPLATZ` varchar(12) default NULL,
`LOG_VISSAMMNR` varchar(7) default NULL,
`LOG_TARIF` varchar(16) NOT NULL,
`LOG_HANDLE` varchar(8) NOT NULL,
`LOG_VORGANG` varchar(10) NOT NULL,
`LOG_DATUM` date NOT NULL,
`LOG_UHRZEIT` time NOT NULL,
`LOG_TARIFINFO` varchar(10) default NULL,
`LOG_DRUCKAUSWAHL` varchar(12) default NULL,
`LOG_DRUCKSTUECK` varchar(255) default NULL,
PRIMARY KEY (`LOG_ID`,`LOG_DATUM`,`LOG_HANDLE`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Log-Einträge aus 02/2005'
Every night we receive about 1.8 million lines which are inserted into the database. Each log-tables has an average size of about 21 to 26 million lines.
Since it takes a couple of months to receive all log-information from every pc the received lines are spread over at least 6 different log-tables. In the next step all log-tables are aggregated into an analysis table to speed up further selects, where first all lines from a month are deleted via
delete from auswertung_05 where log_datum like '2005-02%'
and then are freshly aggregated via
INSERT INTO auswertung_05 (LOG_TARIF, LOG_VORGANG, LOG_DATUM, LOG_TARIFINFO, LOG_DRUCKAUSWAHL, LOG_DRUCKSTUECK, LOG_ANZAHL )
SELECT LOG_TARIF, LOG_VORGANG, LOG_DATUM, LOG_TARIFINFO, LOG_DRUCKAUSWAHL, LOG_DRUCKSTUECK, count(LOG_ID) FROM log_05_02
GROUP BY LOG_TARIF, LOG_VORGANG, LOG_DATUM, LOG_TARIFINFO, LOG_DRUCKAUSWAHL, LOG_DRUCKSTUECK
the size of the resultset is on average 60.000 lines.
During this process currently all months since January 2005 are recreated every night, but we receive Index errors at almost every month that should be aggregated even if we repair the table using REPAIR TABLE auswertung_05 EXTENDED only one month will work and the next will crash the index table again.
If we can help you to isolate and fix the problem, you're welcome.
Regards, Oliver