Join Firebird!

Join Firebird Foundation to support Firebird SQL development and receive multiple bonuses

Follow Us

Select your media preference

Newsletter

Subscribe to Firebird’s Newsletter to receive the latest news

Developer's Report: QA Stress Testing & Tools Development
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