MySQL Forums
Forum List  »  MyISAM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
8032
June 11, 2005 01:54PM
3333
June 13, 2005 02:41AM
Re: Often corrupt table indexes
3230
July 20, 2005 01:20AM
2853
July 20, 2005 05:25AM
2814
July 20, 2005 07:40AM
2527
July 20, 2005 09:03AM
3142
July 20, 2005 12:13PM
2467
July 21, 2005 04:16AM
2481
July 22, 2005 03:05PM
2448
July 25, 2005 02:26AM
2525
August 01, 2005 02:26AM
2448
August 02, 2005 03:32AM
2557
August 02, 2005 09:00AM
2758
August 16, 2005 12:04PM


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.