August/September 2013
August
- Check CORE-3013 A data source for JOIN that is result of aggregating must be used only one time: Improvement works correctly
- Regress check CORE-3137 (Partial rollback is possible for a selectable procedure modifying data): works OK, no regression found
- Regress check CORE-3312 (Sub-optimal join plan when the slave table depends on the master one via the OR predicate): works OK, no regression found
- Regress check CORE-3340 (Error in autonomous transaction with empty exception handler: can insert duplicate values into PK/UK column (leads to unrestorable backup): works OK, no regression found
- Comparison of INSERT performance in GTTs (on commit delete | preserve rows) 2.5 vs 3.0: FB 3.0 loses, ratio ~ 5/6
- Validation of 'true SMP support for Superserver' in Fb3.0 (run 10 isql, each count rows from big data source; watch for load volumes of CPU in `top` utility): Confirmed, all CPU cores are loaded
- Regress check whether *all* bitmaps are used for each separate index when search condition is like: WHERE field_a = … and field_b starting with … and field_c starting with … (all fields: _a, _b, _c - have indexes); works OK, no regression found
- Comparison of JOIN performance, data sources: 10 rows and 10E+07 rows (indexed) 2.5 vs 3.0: FB 3.0 was better: 5.5 vs 8.4 in earlier tests but now it is NOT true! Time is equal)
- Check issue about unnecessary join when condition contains expression that similar to be resolved by compiler as FALSE (like join on 1=0) Fb 3.0: Poor. Firebird still included table that should not ever be processed in plan and under some conditions fetches from that table
- Compare memory usage on simple join of two data sources, 3.0 vs 2.5: discovered minor bug in mon$memory_usage output; created CORE-4159
- Check new feature of 3.0: int column generated by default as identity: Troubles when insert by non-privileged user (no usage rights for `rdb$1`); created CORE-4161
- Compare performance of insex scan vs natural scan, in case of very bad index selectivity (almost 1) 2.5 vs 3.0 3.0 wins!
- Check performance of simple joins (“count lucky tickets”) and sort that occurs in union DISTINCT (the same task) 2.5 vs 3.0; see also: CORE-4165 (dimitr: Replace the hierarchical union execution with the plain one): BAD performance of 2.5 when sort occurs. FB 3.x better by ~3 times
- Check issues about fetches when insert data in child tables that violates FK constraint, 2.5 vs 3: same for both 2.5 and 3.0
- Check again: how long DML can ignore request in MON$-table to be stopped (or disconnect attachment); too long wait when FB performs rollbacks after cancelling bulk DML, 2.5 vs 3.0: same for both 2.5 and 3.0
- Compare index building time, table of 10E6 rows, 2.5 vs 3.0 3.0 loses!
- Check select n01,count(*) cnt from tbig where n01 in(741,258,963) group by n01 for table tbig=10’000’000 rows (index on n01 present), increased page buffer up to maximum in 32bit: 128K pages, 2.5 vs 3.0: no changes in CPU time and statistics between 2.5 and 3.0
- Compare performance of hash join (new in FB 3) vs merge join, table 3'000'000 rows, 2.5 vs 3.0: hash join LOSES, 20%
- Check again Fb3 performance of hash join when buf=128K and FileSysThreshold=512K (TOO SLOW), (add gathering info from mon$io + mon$rec: too low values!): Bad performance! Values from mon$io and mon$rec almost not changed in time: fetched only 113'000 pages over 3 hours
- Check whether Fb3 protection from big natural scans that forced out cached pages was really fixed: Yes, fixed.
- firebird.conf parameter 'AuthClient' should be exactly as this: AuthClient =Legacy_Auth, Srp, Win_Sspi; removing Win_Scpi (i.e. AuthClient = Srp, Legacy_Auth) leads to blocking of trace start connecting from 2.5 to 3.0
- Test performance of windowed functions: lead(. . .)over(order by ) in comparison with old style: select a.id id_curr, (select first 1 id id_next from tr b where b.id>a.id order by id) from tr a order by id: Fb3 performs badly. Also performs badly in comparison with Oracle 10/11.
- Check CORE-3362 (cursor stability - integral ticket of samples)
- Check again new single sort when union hierarchy present, since 05-08-2013
- Check again merge when more than one rows in join cond: CORE-2274: NOT FIXED YET
- Check again issues about visibility of data for two transactions in the same connect (using GTT on commit preserve rows): OK when TIL = read_only + read_committed
- Check again issue about performance of RDB$GET_CONTEXT and RDB$SET_CONTEXT, 2.5 vs 3.0: 3.0 better
- Check for regression: CORE-3650 (recreation of collation for utf8 from unicode with option 'NUMERIC-SORT=1' leads to FB death)
- Check for regression: CORE-3683 (Wrong results if the recursive query contains an embedded GROUP BY clause): OK (no regression)
- Check performance of misc. mon$ queries, 2.5 vs 3.0: 3.0 same as 2.5 at time; since refactored now by dimitr, I believe
- Check again performance of SIMILAR TO vs LIKE, 2.5 vs 3.0: both still VERY bad. Time for LIKE = 1, time for SIMILAR TO = 22
- Check issue about cursor stability: OK in 3.0
- Check ‘strange results’ of merge that were on 2.5: OK in 3.0
- Check cursor stability sample: OK in 3.0
- Performance test of new FB-3 features related to obtaining constant values in Fb 3 (sample from dimitr scalar functions)
- Compare performance of "search holes in sequences", 2.5 vs 3.0: Fb 3.0 wins: 0.25 vs 7 in 2.5 (!)
- Check performance of inserts 20’000 blobs vs varchars : same as in 2.5: (ins + commit) 16.65” + 10.05” for varchars vs 41.31” + 0.81” for blobs
- Check again CORE-3340 (could insert duplicate in PK in autonom TX, leading to unrecoverable .fbk), 2.5 vs 3.0: OK on both FB 2.5 and 3.0
- Check CORE-3977 (DELETE FROM MON$STATEMENTS does not interrupt a longish fetch in Fb 3.0: NOT yet solved on table with 10E9 rows. Made a new test for this issue and opened a new tracker ticket (CORE-4179: mon$tables cannot be queried while a few ISQLs are doing bulk updates of huge table)
- Get strange ability to create collation for WIN1251 from UNICODE case insensitive, which does not work properly (Fb 3 – see core-4180)
- Invalid number of indexed pages in report of validation (hundred of trillions)
- Run gbak –b t10e9 and found ZERO activity of FB
- Check validation report about bad index pages numbers, Fb 3 on Linux: OK on snapshot LI-T3.0.0.30590
- Run again gbak –b t10e9, with logging via querying mon$-tables .fdb page size=4K: trouble with reading $mon-tables
- Test again case when few table records are stored in one database page
- Test on connect + disconnect of hundred ISQL, inifinite times (purpose: check whether memory leak will be detected in such case). Using WI-T3.0.0.30590, SS, Fatal lock manager error: invalid lock id (nnnn), errno: 0; dumps of LT. MEMORY LEAK DETECTED
- Test again bulk DML on table with 10E9 rows,new page size = 16K (Fb 3): Bulk updates causes FB perform very long rollbacks that continue even after db shutdown (have sample with 60 minutes!)
- Fb3 can not correctly be stopped (via /etc/init.d/fb30xx stop) if it is doing rollbacks at that moment
- Implement and run test for watching Fb3 memory consumption when bulk connect/disconnect occurs
- Continued investigation of test to compare on table 10E9: creation of two indexes "in parallel" vs sequential and comparing "pseudo-parallel" indexes for the same table
- Finished investigation of Fb3 crashing when bulk ISQLs are killed from database with locked record in some table (CORE-4185)
- Check again CORE-4036 (Bugcheck or database corruption under Fb3 when attempting to store long incompressible data into a table): Ok (no error)
- Test issue from doc/readme.transactions: no auto undo when doing bulk UPDATES (rather than inserts)
- Check slow QUIT after update 400,000 rows and rollback: more than 20 seconds
- Check test of FB crash on SuperClassic, seems to be linked to CORE-4185 (discuss with Alex)
- Rebuild Fb3 with patch from Alex (lck.cpp), retry test on bulk connect/disconnect isqls: OK, no more crashes
- Run several tests from Russian forum concerning CORE-4194 and other reports that infinite ES EDS with AUTONOMOUS transaction leads to memory leak and error 335544333: internal consistency check (cannot start cache writer thread)) in Fb 3
September
- Repeated testing of bulk connects/disconnects on successive Fb 2.5.3 builds
- Repeated testing related to CORE-4185 crash scenario on successive Fb 2.5.3 builds/patches, in concert with Alex
- Fixed error in 2.5.3 and 3.0 that led FB 2.5.3 to crash (CORE-4211)
- Investigation of server crashes when attempting to trace activity on a database having a db-level trigger
- Adapt and run test for connect establishing speed to FB 3.0 vs FB 2.5, buffers=65000 in both instances (CORE-4225 4)
- Discussion with Alex and implementation of improvements for connect calibration test
- Run test with for connect speed calibration with applying dumpTimer.patch (from Alex), get stacktraces, LT snapshots at the
- moments when connect was more 4 seconds. Sent them to Alex along with core dumps.
- Run multiple variants of test on huge table (10E9 rows) under Fb 3 retesting issues from CORE-4179: querying mon$-tables after forced shutdown database now faster.
- Run test: gfix –shut full when bulk DMLs working. Check whether gfix is still async even when FW = ON: Hooray! gfix -shut now becomes SYNCHRONOUS and does not return to command prompt untill all rollbacks of DML are done.
- Rerun IDX_UNDER_LOAD test with client 3.0, default firebird.conf and without calibration test (300 connects) – Alex’s suggestion for addressing the slow connect problem also seen in 2.5
- Multiple rebuilds of Fb3 and runs with gdb to get stack traces for server hangs (perhaps from querying mon$-tables) and discussion about this with Alex and Vlad. All test cases have been sent to Vlad. No solution yet.
Pavel Zotov
Moscow, Russian Federation
August/September 2013
August
- Check CORE-3013 A data source for JOIN that is result of aggregating must be used only one time: Improvement works correctly
- Regress check CORE-3137 (Partial rollback is possible for a selectable procedure modifying data): works OK, no regression found
- Regress check CORE-3312 (Sub-optimal join plan when the slave table depends on the master one via the OR predicate): works OK, no regression found
- Regress check CORE-3340 (Error in autonomous transaction with empty exception handler: can insert duplicate values into PK/UK column (leads to unrestorable backup): works OK, no regression found
- Comparison of INSERT performance in GTTs (on commit delete | preserve rows) 2.5 vs 3.0: FB 3.0 loses, ratio ~ 5/6
- Validation of 'true SMP support for Superserver' in Fb3.0 (run 10 isql, each count rows from big data source; watch for load volumes of CPU in `top` utility): Confirmed, all CPU cores are loaded
- Regress check whether *all* bitmaps are used for each separate index when search condition is like: WHERE field_a = … and field_b starting with … and field_c starting with … (all fields: _a, _b, _c - have indexes); works OK, no regression found
- Comparison of JOIN performance, data sources: 10 rows and 10E+07 rows (indexed) 2.5 vs 3.0: FB 3.0 was better: 5.5 vs 8.4 in earlier tests but now it is NOT true! Time is equal)
- Check issue about unnecessary join when condition contains expression that similar to be resolved by compiler as FALSE (like join on 1=0) Fb 3.0: Poor. Firebird still included table that should not ever be processed in plan and under some conditions fetches from that table
- Compare memory usage on simple join of two data sources, 3.0 vs 2.5: discovered minor bug in mon$memory_usage output; created CORE-4159
- Check new feature of 3.0: int column generated by default as identity: Troubles when insert by non-privileged user (no usage rights for `rdb$1`); created CORE-4161
- Compare performance of insex scan vs natural scan, in case of very bad index selectivity (almost 1) 2.5 vs 3.0 3.0 wins!
- Check performance of simple joins (“count lucky tickets”) and sort that occurs in union DISTINCT (the same task) 2.5 vs 3.0; see also: CORE-4165 (dimitr: Replace the hierarchical union execution with the plain one): BAD performance of 2.5 when sort occurs. FB 3.x better by ~3 times
- Check issues about fetches when insert data in child tables that violates FK constraint, 2.5 vs 3: same for both 2.5 and 3.0
- Check again: how long DML can ignore request in MON$-table to be stopped (or disconnect attachment); too long wait when FB performs rollbacks after cancelling bulk DML, 2.5 vs 3.0: same for both 2.5 and 3.0
- Compare index building time, table of 10E6 rows, 2.5 vs 3.0 3.0 loses!
- Check select n01,count(*) cnt from tbig where n01 in(741,258,963) group by n01 for table tbig=10’000’000 rows (index on n01 present), increased page buffer up to maximum in 32bit: 128K pages, 2.5 vs 3.0: no changes in CPU time and statistics between 2.5 and 3.0
- Compare performance of hash join (new in FB 3) vs merge join, table 3'000'000 rows, 2.5 vs 3.0: hash join LOSES, 20%
- Check again Fb3 performance of hash join when buf=128K and FileSysThreshold=512K (TOO SLOW), (add gathering info from mon$io + mon$rec: too low values!): Bad performance! Values from mon$io and mon$rec almost not changed in time: fetched only 113'000 pages over 3 hours
- Check whether Fb3 protection from big natural scans that forced out cached pages was really fixed: Yes, fixed.
- firebird.conf parameter 'AuthClient' should be exactly as this: AuthClient =Legacy_Auth, Srp, Win_Sspi; removing Win_Scpi (i.e. AuthClient = Srp, Legacy_Auth) leads to blocking of trace start connecting from 2.5 to 3.0
- Test performance of windowed functions: lead(. . .)over(order by ) in comparison with old style: select a.id id_curr, (select first 1 id id_next from tr b where b.id>a.id order by id) from tr a order by id: Fb3 performs badly. Also performs badly in comparison with Oracle 10/11.
- Check CORE-3362 (cursor stability - integral ticket of samples)
- Check again new single sort when union hierarchy present, since 05-08-2013
- Check again merge when more than one rows in join cond: CORE-2274: NOT FIXED YET
- Check again issues about visibility of data for two transactions in the same connect (using GTT on commit preserve rows): OK when TIL = read_only + read_committed
- Check again issue about performance of RDB$GET_CONTEXT and RDB$SET_CONTEXT, 2.5 vs 3.0: 3.0 better
- Check for regression: CORE-3650 (recreation of collation for utf8 from unicode with option 'NUMERIC-SORT=1' leads to FB death)
- Check for regression: CORE-3683 (Wrong results if the recursive query contains an embedded GROUP BY clause): OK (no regression)
- Check performance of misc. mon$ queries, 2.5 vs 3.0: 3.0 same as 2.5 at time; since refactored now by dimitr, I believe
- Check again performance of SIMILAR TO vs LIKE, 2.5 vs 3.0: both still VERY bad. Time for LIKE = 1, time for SIMILAR TO = 22
- Check issue about cursor stability: OK in 3.0
- Check ‘strange results’ of merge that were on 2.5: OK in 3.0
- Check cursor stability sample: OK in 3.0
- Performance test of new FB-3 features related to obtaining constant values in Fb 3 (sample from dimitr scalar functions)
- Compare performance of "search holes in sequences", 2.5 vs 3.0: Fb 3.0 wins: 0.25 vs 7 in 2.5 (!)
- Check performance of inserts 20’000 blobs vs varchars : same as in 2.5: (ins + commit) 16.65” + 10.05” for varchars vs 41.31” + 0.81” for blobs
- Check again CORE-3340 (could insert duplicate in PK in autonom TX, leading to unrecoverable .fbk), 2.5 vs 3.0: OK on both FB 2.5 and 3.0
- Check CORE-3977 (DELETE FROM MON$STATEMENTS does not interrupt a longish fetch in Fb 3.0: NOT yet solved on table with 10E9 rows. Made a new test for this issue and opened a new tracker ticket (CORE-4179: mon$tables cannot be queried while a few ISQLs are doing bulk updates of huge table)
- Get strange ability to create collation for WIN1251 from UNICODE case insensitive, which does not work properly (Fb 3 – see core-4180)
- Invalid number of indexed pages in report of validation (hundred of trillions)
- Run gbak –b t10e9 and found ZERO activity of FB
- Check validation report about bad index pages numbers, Fb 3 on Linux: OK on snapshot LI-T3.0.0.30590
- Run again gbak –b t10e9, with logging via querying mon$-tables .fdb page size=4K: trouble with reading $mon-tables
- Test again case when few table records are stored in one database page
- Test on connect + disconnect of hundred ISQL, inifinite times (purpose: check whether memory leak will be detected in such case). Using WI-T3.0.0.30590, SS, Fatal lock manager error: invalid lock id (nnnn), errno: 0; dumps of LT. MEMORY LEAK DETECTED
- Test again bulk DML on table with 10E9 rows,new page size = 16K (Fb 3): Bulk updates causes FB perform very long rollbacks that continue even after db shutdown (have sample with 60 minutes!)
- Fb3 can not correctly be stopped (via /etc/init.d/fb30xx stop) if it is doing rollbacks at that moment
- Implement and run test for watching Fb3 memory consumption when bulk connect/disconnect occurs
- Continued investigation of test to compare on table 10E9: creation of two indexes "in parallel" vs sequential and comparing "pseudo-parallel" indexes for the same table
- Finished investigation of Fb3 crashing when bulk ISQLs are killed from database with locked record in some table (CORE-4185)
- Check again CORE-4036 (Bugcheck or database corruption under Fb3 when attempting to store long incompressible data into a table): Ok (no error)
- Test issue from doc/readme.transactions: no auto undo when doing bulk UPDATES (rather than inserts)
- Check slow QUIT after update 400,000 rows and rollback: more than 20 seconds
- Check test of FB crash on SuperClassic, seems to be linked to CORE-4185 (discuss with Alex)
- Rebuild Fb3 with patch from Alex (lck.cpp), retry test on bulk connect/disconnect isqls: OK, no more crashes
- Run several tests from Russian forum concerning CORE-4194 and other reports that infinite ES EDS with AUTONOMOUS transaction leads to memory leak and error 335544333: internal consistency check (cannot start cache writer thread)) in Fb 3
September
- Repeated testing of bulk connects/disconnects on successive Fb 2.5.3 builds
- Repeated testing related to CORE-4185 crash scenario on successive Fb 2.5.3 builds/patches, in concert with Alex
- Fixed error in 2.5.3 and 3.0 that led FB 2.5.3 to crash (CORE-4211)
- Investigation of server crashes when attempting to trace activity on a database having a db-level trigger
- Adapt and run test for connect establishing speed to FB 3.0 vs FB 2.5, buffers=65000 in both instances (CORE-4225 4)
- Discussion with Alex and implementation of improvements for connect calibration test
- Run test with for connect speed calibration with applying dumpTimer.patch (from Alex), get stacktraces, LT snapshots at the
- moments when connect was more 4 seconds. Sent them to Alex along with core dumps.
- Run multiple variants of test on huge table (10E9 rows) under Fb 3 retesting issues from CORE-4179: querying mon$-tables after forced shutdown database now faster.
- Run test: gfix –shut full when bulk DMLs working. Check whether gfix is still async even when FW = ON: Hooray! gfix -shut now becomes SYNCHRONOUS and does not return to command prompt untill all rollbacks of DML are done.
- Rerun IDX_UNDER_LOAD test with client 3.0, default firebird.conf and without calibration test (300 connects) – Alex’s suggestion for addressing the slow connect problem also seen in 2.5
- Multiple rebuilds of Fb3 and runs with gdb to get stack traces for server hangs (perhaps from querying mon$-tables) and discussion about this with Alex and Vlad. All test cases have been sent to Vlad. No solution yet.
Pavel Zotov
Moscow, Russian Federation