G
George Potemkin
Guest
Rob: The customer is planning to use Table Partitioning but, IMHO, it will only slightly help with repairing the indexes. Brian: I agree that not all customer requirements are the same. But the large customers are the vanguard. They are facing the issues that other customers would face in the future. Including the issues that come from large db sizes. I remember the days when 2 GB databases were treated as very large ones. I would not surprise if in the foreseeable future 2 TB databases will become a common case. Probably in the messages above I should have avoided the words "enhancement requests". I'm looking just for a solution of the customer's issue. The customer is occasionally getting the corrupted indexes in their databases. It may happen with any customer. Fortunately for us the previous corruptions happened for more or less small tables. Nevertheless in some cases we were forced to use the tricks to fix the indexes. For example, for the bug PSC00262763 when a client transaction hangs in an infinite loop reading the same index blocks. Idxfix hung as well. Progress development did a fantastic job to identify the root case of the corruption and to fix it. But I would like to get more - namely an official tool that could quickly fix the corrupted index blocks in the future cases. According to the Progress development the trick that we used was unreliably but we did not have any other working option to fix the corruption in index block. Recently the customer got the index corruption in very large table (2 TB) and now we again see that the standard tools do not work well in such case. Fortunately it happened in the database that was used only for the tests. But I would like to use this incident to create the standard instruction: what the customer should do in the case of index corruption in production environment. I don't like to mock up with every new case. Business would not like to wait two months while idxfix will scan the indexes of large table. What can I do right now to be ready for the similar incidents in production environment? TheMadDBA: thanks for the idea with "warming up" the filesystem cache. Though I'm going to try a slightly different approach. Our particular database has only one table. So we can assign all its indexes to an alternative buffer pool. The size of all indexes is 335 GB (20% of db size). It may be too big for buffer pool but we can use the less memory while scanning one index at time. There are a few ways to warm up the buffer pool. We can run ixanalys for the index area linked to the alternative buffer pool. Total dbanalys for this database took almost 24 hours. I think ixanalys will be completed in 5 hours (20% of dbanalys time). But we could load in memory the blocks for a selected index only. For example, using an undocumented 'idxblockreport' option of proutil. Or by running 'idxfix/scan indexes' /without/ record's validation. We did some tests and the surprise: idxfix was working faster (by 50%!) than idxblockreport though the last command supposed to be block-oriented while idxfix is checking all index keys in every block. Another interesting result of the tests: the low disk activity when these utilities were walking through an index tree (~ 250 8K-blocks per sec or 2 MB/sec or 2 days to read all index blocks). The 'vmstat' command shows high disk IO waits. The same 'idxfix/scan indexes' option but with record's validation reads 10 times more blocks from disks per second. Of course, it reads mostly the blocks with records. This means, IMHO, that we need to take into account the scatter factor for index blocks (not to be confused with a logical scatter factor of the indexes). Unfortunately Progress tools do not report such metric. I guess the idxbuild will significantly improve the scatter factor of the index blocks. It's another thing to test. And new ideas for the enhancement requests (it's not me but the customer's environment is fruitful for new ideas ;-) 1) an option of 'idxfix/scan indexes' to read the index blocks sequentially rather than from a root block down to the leaf blocks (in our case it will speed up a single-threaded 'idxfix/scan indexes' by 10 times); 2) online de-scattering the index blocks without rebuilding the indexes.
Continue reading...
Continue reading...