| Subject | Index corruption in 1.5.6 |
|---|---|
| Author | matt_midge_clark |
| Post date | 2010-04-26T19:08:51Z |
Hello all,
I've a short question and a long discussion.
As per the subject really. Is there known index corruption in 1.5.6? and if so, I guess it's not likely to be 'fixed'?
The long version, in case any one can help me eliminate the corruption-
We have a 90gb transactional database, talking to around 200 stores on a daily basis. It's also doing a lot of reporting and interfacing to other systems so we're really dependant on reliable indexes.
Recently I removed a number of multi-key indexes, which take forever to restore, and replaced them with single key indexes. I've resisted doing that for a while because I believe I read 1.5 has problems combining keys but the time taken to restore the database is getting silly. Following this action, one of our export programs ran very slowly so I guessed it was suffering from bad optimisation and I tweaked the selects to remove order by clauses and give the optimiser a better chance. That's when it started to use the new 'better' indexes and when finance started to notice the numbers it's returning don't add up.
I've finally got to the point where I can, after inactivating and activating the indexes to 'rebuild' them, import a days transactions (20-40k rows into 60m in a problem table) and then get two different numbers returned by a select depending on the field (index) I sort by.
I had hoped the corruption in [CORE-1830], fixed in 1.5.6 matched my problem so I upgraded last week (from 1.5.5) but the problem continues , though I no longer get the corruption described in the bug's test case so this seems to be a different problem
This is clearly not acceptable but I'm between a rock and a hard place. I don't want to just put the old indexes back because now I've got such a simple test case of corruption I'm not confident the old indexes will be any better. They're just harder to test (and rebuild). Upgrading to 2.0, 2.1 is not practical as we don't have the time required to debug every application (over 100 written in Kylix 3) right now. I tried it previously and hit enough problems I had to roll back. I can't just 'fix' the affected program because without knowing that the corruption is fixed I risk being left in exactly the same position, what ever I do and finding a days worth of errors is several hours work for someone in finance.
Apologies for the long post but I really need some help so I'm going with more info than less. Schemas etc will be available on request but I'll have to consider pruning anything company specific.
regards,
Matt
I've a short question and a long discussion.
As per the subject really. Is there known index corruption in 1.5.6? and if so, I guess it's not likely to be 'fixed'?
The long version, in case any one can help me eliminate the corruption-
We have a 90gb transactional database, talking to around 200 stores on a daily basis. It's also doing a lot of reporting and interfacing to other systems so we're really dependant on reliable indexes.
Recently I removed a number of multi-key indexes, which take forever to restore, and replaced them with single key indexes. I've resisted doing that for a while because I believe I read 1.5 has problems combining keys but the time taken to restore the database is getting silly. Following this action, one of our export programs ran very slowly so I guessed it was suffering from bad optimisation and I tweaked the selects to remove order by clauses and give the optimiser a better chance. That's when it started to use the new 'better' indexes and when finance started to notice the numbers it's returning don't add up.
I've finally got to the point where I can, after inactivating and activating the indexes to 'rebuild' them, import a days transactions (20-40k rows into 60m in a problem table) and then get two different numbers returned by a select depending on the field (index) I sort by.
I had hoped the corruption in [CORE-1830], fixed in 1.5.6 matched my problem so I upgraded last week (from 1.5.5) but the problem continues , though I no longer get the corruption described in the bug's test case so this seems to be a different problem
This is clearly not acceptable but I'm between a rock and a hard place. I don't want to just put the old indexes back because now I've got such a simple test case of corruption I'm not confident the old indexes will be any better. They're just harder to test (and rebuild). Upgrading to 2.0, 2.1 is not practical as we don't have the time required to debug every application (over 100 written in Kylix 3) right now. I tried it previously and hit enough problems I had to roll back. I can't just 'fix' the affected program because without knowing that the corruption is fixed I risk being left in exactly the same position, what ever I do and finding a days worth of errors is several hours work for someone in finance.
Apologies for the long post but I really need some help so I'm going with more info than less. Schemas etc will be available on request but I'll have to consider pruning anything company specific.
regards,
Matt