G
George Potemkin
Guest
We run the tests with idxfix and the results exceeded the worst expectations. Online idxfix just for one of the indexes would take from a day to 2 years (TWO YEARS)! Or 6 days for offline index rebuild. It's too much even for the tests. So we got only the estimations of the scan times. Test's scenario: we run a command, wait a few minutes to warm up a buffer pool, collect the promon's statistics during one minute (using 30 2-sec intervals), stop the process and then run a next command for the same index. We tested the following commands: idxblockreport, 'idxfix/scan indexes' with/without record's validation and 'idxfix/scan records'. Database was not restarted between the tests. Scan time was estimated as the number of records in table (reported by dbanalys) divided by the record operations per sec (reported by promon/Activity: Performance Indicators) - multiplied by two seem to be a more accurate estimation but then the numbers would be more scary - and/or as the number of index blocks divided by the index block reads per sec (reported by Activity: I/O Operations by Type). These estimations can be verified by logical reads (a.k.a. db requests) reported by Activity: Buffer Cache. The exception is idxblockreport that almost does not update the logical reads. For a comparison we also have the execution times of ixanalys for each index separately. Sidenote #1: one minute monitoring interval is too short to provide an accurate estimation if a process is going to run the days, months or years, especially for the read operations when large db buffer pool and filesystem cache are used. But, IMHO, it's enough at least for a quick draft estimation. Sidenote #2: idxfix creates the transactions per each index keys. But it's not a problem because the status of these transactions is just "allocated" and it's required just a few db resources (2*MTX+2*LKF locks per Tx). Idxblockreport created one transaction approximately per 2 seconds but it's just a "feature" of the undocumented command. The estimation of run duration of idxfix against a copy of production database (86,44,278,754 records, rec's size: 1.3T, total size of the indexes: 334.6G - from 16.9G to 241.2G individually): The best case for 'idxfix/scan indexes' with record's validation is approximately 20 hours. The worst case - 2 years. The best case for 'idxfix/scan records' - 16 hours. The worst case - 289 days. Ixanalys is incomparably (by 170 times) faster than idxblockreport though both commands do the block level operations. Ixanalys reads the index blocks sequentially while idxblockreport reads them in the order of the index tree. The difference between the times stayed huge even after idxbuild. The 'idxfix/scan indexes' without record's validation aprox 4 times slower than idxblockreport (tested on a small database). Both commands read the same blocks in the same order but additionally idxfix parses all keys from an index block - like 'for each ... use-index' but without real reads of the records. As expected the 'idxfix/scan indexes' with record's validation is significantly slower than without validation especially on the indexes with a bad logical scatter factor. Idxbuild (done with pfactor 80%) remarkably improved the scan times for the "fast" indexes and almost did not affect the scan times for the "slow" ones. Obviously the slowness of an index when running idxfix is mainly the result of its logical scatter factor and its size and depends less from the scatter factor of the index blocks. Logical reads varied between the tests from 300 requests per sec to 400,000. The last number seems to be the best result on the customer's hardware for a single-threaded run of idxfix. To check how the results depends from OS/hardware we run the "calibration" test. We created a database with a table that has one field and one index and the records with field's value from 1 to 10 millions. Db size is 400 GB. We tested only 'idfix/scan records'. I run the same tests on my laptop. The results: Customer's test server: Logical reads: 401,025 blk/sec Record operations: 160,284 rec/sec Estimated scan time: 62 sec Index operations: 80,142 key/sec My laptop (first run after reboot): Logical reads: 578,132 blk/sec Record operations: 230,981 rec/sec Estimated scan time: 48 sec Index operations: 115,491 key/sec Real scan time: 159 sec My laptop (second run): Logical reads: 1,280,805 blk/sec Record operations: 511,720 rec/sec Estimated scan time: 21 sec Index operations: 255,860 key/sec Real scan time: 44 sec Obviously my laptop shows the better results. Maybe because its CPU frequency is higher (2.40 GHz). Maybe because the customer is running on LPAR. But it's not a factor that would help us to scan the indexes in large database in a reasonable period of time (instead of two years). The timings (there are a lot of numbers below, I'm sorry): pre class="brush: abl; light: true; fontsize: 100; first-line: 1; " dbanalys took 23:56:11. ixanalys for all indexes in one area (before idxbuild): 00:39:42 First tests were running before idxbuild, then each index was moved to each own area and the indexes were rebuilt. Then the tests were re-run. The results after idxbuild are specified in the brackets below. Idxbuild was running a bit more than 6 days: [2014/10/16@18:49:35.620+0300] P-9371946 T-1 I : (451) Idxbuild session begin for root on /dev/pts/4. [2014/10/22@20:06:20.194+0300] P-9371946 T-1 I : (334) Idxbuild session end. During the tests the database was running with -B 900000 (6.9 GB). ------------------------------------------------------- Index1 Fields Levels Blocks Size % Util Factor Before idxbuild 6 5 52376577 241.2G 60.6 1.8 After idxbuild 6 5 39988575 240.7G 79.2 1.4 Ixanalys exec time: 00:24:57 Idxblockreport Buffer Hits: 66% Logical reads: 5.7 blk/sec (2.9 blk/sec) Index block reads: 159 blk/sec (165 blk/sec) Estimated scan time: 3.8 days (2.8 days) Idxix / Scan indexes / Not validate recids for index entries Buffer Hits: 99% Logical reads: 51,938 blk/sec (380,254 blk/sec) Index block reads: 166 blk/sec (1,143 blk/sec) Estimated scan time: 3.7 days (09:43:11) Index operations: 50,943 key/sec = 308 key/blk (373,432 key/sec = 2,255 key/blk) Estimated scan time: 2 days (06:25:48) Idxix / Scan indexes / Validate recids for index entries Buffer Hits: 50%-52% Logical reads: 320 blk/sec (280 blk/sec) Data block reads: 156 blk/sec (138 blk/sec) Record operations: 158 rec/sec = 1.01 rec/blk (139 rec/sec = 1.01 rec/blk) Estimated scan time: 1.7 years (2 years) Index block reads: 0.73 blk/sec (0.35 blk/sec) Estimated scan time: 2.3 years (2 years) Index operations: 158 key/sec = 216 key/blk (139 key/sec = 190 key/blk) Idxix / Scan records Buffer Hits: 99% Logical reads: 32,422 blk/sec Data block reads: 0.017 blk/sec Record operations: 9,259 rec/sec Estimated scan time: 11 days Index block reads: 160 blk/sec Index operations: 4,639 key/sec = 29 key/blk ------------------------------------------------------- Index2 Fields Levels Blocks Size % Util Factor Before idxbuild 2 4 13891121 56.6G 53.6 1.9 After idxbuild 2 4 9366784 56.5G 79.3 1.4 Ixanalys exec time: 00:09:05 Idxblockreport Buffer Hits: 66% Logical reads: 1.5 blk/sec (1.5 blk/sec) Index block reads: 140 blk/sec (170 blk/sec) Estimated scan time: 1 day (15:20:35) Idxix / Scan indexes / Not validate recids for index entries Buffer Hits: 99% Logical reads: 42,151 blk/sec (365,137 blk/sec) Index block reads: 229 blk/sec (776 blk/sec) Estimated scan time: 16:49:01 (03:21:12) Index operations: 41,004 key/sec = 179 key/blk (361,292 key/sec = 466 key/blk) Estimated scan time: 2.4 days (06:38:46) Idxix / Scan indexes / Validate recids for index entries Buffer Hits: 63%-94% Logical reads: 10,253 blk/sec (14,304 blk/sec) Data block reads: 1,530 blk/sec (1,908 blk/sec) Record operations: 5,079 rec/sec = 3.31 rec/blk (7,127.90 rec/sec = 3.74 rec/blk) Estimated scan time: 20 days (14 days) Index block reads: 20 blk/sec (10 blk/sec) Estimated scan time: 8 days (11 days) Index operations: 5,079 key/sec = 254 key/blk (7,128 key/sec = 704 key/blk) Idxix / Scan records Buffer Hits: 82%-90% Logical reads: 1,042 blk/sec Data block reads: 0 blk/sec Record operations: 346 rec/sec Estimated scan time: 289 days Index block reads: 137 blk/sec Index operations: 173 key/sec = 1.3 key/blk ------------------------------------------------------- Index3 Fields Levels Blocks Size % Util Factor Before idxbuild 3 4 8335147 19.9G 31.4 2.4 After idxbuild 3 4 3276506 19.7G 79.3 1.4 Ixanalys exec time: 00:04:08 Idxblockreport Buffer Hits: 66% Logical reads: 5.4 blk/sec Index block reads: 728 blk/sec (584 blk/sec) Estimated scan time: 03:10:47 (01:33:27) Idxix / Scan indexes / Not validate recids for index entries Buffer Hits: 99% Logical reads: 231,856 blk/sec (282,018 blk/sec) Index block reads: 365 blk/sec (264 blk/sec) Estimated scan time: 06:20:07 (03:26:36) Index operations: 230,063 key/sec = 629 key/blk (280,720 key/sec = 1062 key/blk) Estimated scan time: 10:26:14 (08:33:13) Idxix / Scan indexes / Validate recids for index entries Buffer Hits: 99% Logical reads: 225,061 blk/sec Data block reads: 1,864 blk/sec (1,900 blk/sec) Record operations: 112,203 rec/sec = 60 rec/blk (112,667 rec/sec = 59 rec/blk) Estimated scan time: 21:24:01 (21:18:44) Index block reads: 136 blk/sec (88 blk/sec) Estimated scan time: 17:01:50 (10:21:45) Index operations: 112,203 key/sec = 825 key/blk (112,667 key/sec = 1,283 key/blk) Idxix / Scan records Buffer Hits: 99% Logical reads: 383,050 blk/sec Data block reads: 995 blk/sec Record operations: 127,363 rec/sec = 128 rec/blk Estimated scan time: 18:51:11 Index block reads: 123 blk/sec Index operations: 63,682 key/sec = 519 key/blk ------------------------------------------------------- Index4 Fields Levels Blocks Size % Util Factor Before idxbuild 3 4 3982408 16.9G 55.7 1.9 After idxbuild 3 4 2792427 16.8G 79.3 1.4 Ixanalys exec time: 00:03:31 Idxblockreport Buffer Hits: 66% Logical reads: 1.8 blk/sec (124 blk/sec) Index block reads: 139 blk/sec Estimated scan time: 07:57:35 Idxix / Scan indexes / Not validate recids for index entries Buffer Hits: 99% Logical reads: 372,976 blk/sec (402,505 blk/sec) Index block reads: 208 blk/sec (128 blk/sec) Estimated scan time: 05:19:03 (06:02:40) Index operations: 371,969 key/sec = 1,788 key/blk (401,909 key/sec = 3,132 key/blk) Estimated scan time: 06:27:19 (05:58:28) Idxix / Scan indexes / Validate recids for index entries Buffer Hits: 50%-58% Logical reads: 697 blk/sec (637 blk/sec) Data block reads: 320 blk/sec (299 blk/sec) Record operations: 347 rec/sec = 1.08 rec/blk (318 rec/sec = 1.06 rec/blk) Estimated scan time: 288.5 days (315 days) Index block reads: 0.75 blk/sec (0.40 blk/sec) Estimated scan time: 62 days (81 days) Index operations: 347 key/sec = 462 key/blk (318 key/sec = 794 key/blk) Idxix / Scan records Buffer Hits: 99% Logical reads: 444,789 blk/sec Data block reads: 0 blk/sec Record operations: 147,891 rec/sec Estimated scan time: 16:14:10 Index block reads: 10 blk/sec Index operations: 73,945 key/sec = 7447 key/blk ------------------------------------------------------- /pre
Continue reading...
Continue reading...