Software Quality and Testing in MySQL
Trim PershadOmer BarNir
MySQL – User Conference and Expo 2009
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Topics
• Overview of QA
• Process
• Testing
• Bug Analysis and Metrics
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Overview of QA
• MySQL products
– Server, Cluster, ET, Developer Tools, Connectors
– We will talk mostly about server
• QA over the years
• QA group structure
• Continuously improving quality
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Overview of QA (cont)
• Internal Processes
– Well defined release criteria
– High quality is the goal
• All releases are tested by QA
• New Tests added all the time
• Build most test tools internally
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Topics
• QA Background in MySQL
• Process
• Testing
• Bug Analysis and Metrics
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Process ..Development Cycle
• Development
– Specifications – Worklogs
– Design reviews
– Code reviews
– Unit tests
– Team Trees and Main Trees
• QA
– Test Plans
– Testing – functional, performance, platforms
– Code coverage
– Platforms
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Process ..Release Cycle
• Build
– Clone off
– Build binaries
– Packaging
• QA
– Run Install Tests
– Run Functional Tests
– System and Performance Testing
• Development
– Bug Fixes
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Process ..Release Cycle
Clone off
Release Binaries
Built
Package
Binaries
Install
Tests
Functional Tests
System /Stress Tests
Build Steps QA Steps Development Steps
Performance Tests
Bug FixBugs Analyzed
Binaries Released
Code Merge
Process
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Topics
• QA Background in MySQL
• Process
• Testing
• Bug Analysis and Metrics
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Testing
• Testing frameworks
• Testing suites
• Cross Product Integration Testing
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Testing - Frameworks
• MTR (mysql-test-run)
– Single user test driver (based on ‘test’ files)
– Used for Functional tests
– Distributed with the MySQL Server
• mysql-stress-test
– Multi connection scenarios (based on ‘test’ files)
– Uses for system/concurrency tests
– Distributed with the MySQL Server (framework only)
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Testing – Frameworks (cont.)
• Systems Test Framework
– Multi connection scenarios (Based on mysql-stress-test)
– Concurrency and Longevity Testing
– Not Distributed
• Random Query Generator
– Test Generator
– Single/multi-user test driver (based on ‘grammar’ files)
– Used for Functional, Compatibility and System tests
– Separate Tree
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Testing – Frameworks (cont.)
• Upgrade-downgrade
– Functional scenarios
– Uses for upgrade, downgrade
– Not Distributed
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Testing – Frameworks, Performance
• Sysbench• C Based application, Multi threaded connections
• Built in scenarios: OLTP, Memory, CPU, threads
• Supports Lua extensions (v0.5.0)
• Public Tree
• MySQLBench• Single user ‘atomic’ test scenarios
• Perl Based
• Included in the mysql distribution
• DBT2 Tests
– Based on TPC-C benchmark
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Testing
• Testing frameworks
• Testing suites
• Cross Product Integration Testing
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Test Suites
• Functional
– Functional Test suite
– NIST compliance tests
– Large table tests
– Random/Complex query testing
– Upgrade/Downgrade Compatibility
• System/Longevity
– Concurrent IUDS scenarios
– Replications
– Crash/Recovery tests
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Upgrade/Downgrade Compatibility
– Start Old Server
– Run test script (init_xxx.test)
– Shut down old server start new one
– Run upgrade process (*)
– Run test script (upgrade_xxx.test)
– Shut down new server, start old one
– Run test script (downgrade_xxx.test)
– Shutdown server
– (*) Different upgrade path
• Live (same database)
• Dump/restore
• Replication (old master new slave)
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Concurrent IUDS scenarios
• Start Server
• Run script initializing tables/users/Procedures etc.
• Launch multiple clients,
– Continuously run until the end of the test
– Each running a random scenario and exists
• Runs tasks
– ‘management’ activities (truncate log tables etc)
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Concurrent IUDS scenarios - Example#
# This test is deleting rows based on a random value
#
--source suite/systems/include/system_1_init.inc
let $NUM_VAL=`SELECT @NUM_VAL`;
USE systest1;
# Setting parameter intormation
--replace_result $NUM_VAL <NUM_VAL>
eval set @f1_nums=$NUM_VAL;
set @tmp_num=int_rand(@f1_nums);
delete from tb0_eng1 where f1=@tmp_num;
#
# This test is inserts rows based on a random value
# using a stored procedure
--source suite/systems/include/system_1_init.inc
let $NUM_VAL=`SELECT @NUM_VAL`;
USE systest1;
# Setting parameter intormation
--replace_result $NUM_VAL <NUM_VAL>
eval SET @f1_nums=$NUM_VAL;
SET @tmp_num=int_rand(@f1_nums);
SET @tmp_word=str_rand(4);
CALL ins_tb0_eng1 (@tmp_num, @tmp_word);
#
# This test is updating rows based on a random value
#--source suite/systems/include/system_1_init.inc
let $NUM_VAL=`SELECT @NUM_VAL`;
use systest1;
# Setting parameter intormation
--replace_result $NUM_VAL <NUM_VAL>
eval set @f1_nums=$NUM_VAL;
set @tmp_num=int_rand(@f1_nums);
set @tmp_word=str_rand(4);
update tb0_eng1
set f2=concat('U:',@tmp_word,'-',@tmp_num), f3=f3+1
where f1=@tmp_num-1;
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
# This procedure scans 'tb0_master' table for rows where f1 = 'num_pr'
# and for each row INSERTs a row in 'tb0_eng1'
#---------------------------------------------------------------------
CREATE PROCEDURE ins_tb0_eng1 (num_pr INT, str_pr CHAR(15))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v3 DECIMAL(5,3);
DECLARE cur1 CURSOR FOR
SELECT f3 FROM tb0_master WHERE f1 = num_pr;
DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' SET done = 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
FETCH cur1 INTO v3;
wl_loop: WHILE NOT done DO
INSERT INTO tb0_eng1 (f1, f2, f3, f4) VALUES
(int_rand(@f1_nums),
CONCAT('I:',str_pr,'-',num_pr),
v3, NOW());
FETCH cur1 INTO v3;
END WHILE wl_loop;
CLOSE cur1;
END//
#
# This test is inserts rows based on a random value
# using a stored procedure
CREATE TRIGGER tb0_eng1_ins AFTER INSERT ON tb0_eng1 FOR EACH ROW
INSERT INTO tb0_logs (dt1, entry_dsc, f4)
VALUES (NOW(), CONCAT('Insert row ', NEW.f1,' ',
NEW.f2, ' ', NEW.f3, ' (tb0_eng1)'), NEW.f1);
CREATE TRIGGER tb0_eng1_upd AFTER UPDATE ON tb0_eng1 FOR EACH ROW
INSERT INTO tb0_logs (dt1, entry_dsc, f4)
VALUES (NOW(), CONCAT('Update row ', OLD.f1,' ', OLD.f2, '->',
NEW.f2, ' ', OLD.f3, '->', NEW.f3, ' (tb0_eng1)'), NEW.f1);
CREATE TRIGGER tb0_eng1_del AFTER DELETE ON tb0_eng1 FOR EACH ROW
INSERT INTO tb0_logs (dt1, entry_dsc, f4)
VALUES (NOW(), CONCAT('Delete row ', OLD.f1,' ', OLD.f2, ' ',
OLD.f3, ' (tb0_eng1)'), OLD.f1);
Concurrent IUDS scenarios – Example (cont.)
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Test Suites (cont.)
• Install Testing
• Load/Stress
– Testing the system under high user load with connect/disconnect and IUDS operations
• Performance
– Performance testing of SQL operations
– Performance trend and changes with changing number of threads
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Test Suites – Performance Charts
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Test Statistics
Test Statistics - Release 5.0 Vs 5.1
0
1000
2000
3000
4000
5000
6000
7000
8000
5.0 5.1
Server Release
# o
f T
ests
7,273
3,604
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Testing
• Testing frameworks
• Testing suites
• Cross Product Integration Testing
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Cross-Product Integration Testing
• Server Testing
– Based on using client tools based on the C-API Interface
• Connector Testing
– Systematic testing of connectors
– Testing using sample applications built with common tools
• Microsoft Access
• Crystal Reports
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Topics
• QA Background in MySQL
• Process
• Testing
• Bug Analysis and Metrics
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Metrics and Bug Analysis
• Bug KPI Tracking
– Used in making Quality decisions
• Bug Analysis
– Bug Analysis
– Prioritization of bugs
• (cross team activity)
v5.1 P1 Inflow/Outflow
4
1 1
2
3
0
2
0
1
0
1
33
1
0
16
0
1
4
3 3
0
4
1 1
2
00
5
10
15
20
9-J
an
16-J
an
23-J
an
30-J
an
6-F
eb
13-F
eb
20-F
eb
27-F
eb
6-M
ar
13-M
ar
20-M
ar
27-M
ar
3-A
pr
10-A
pr
17-A
pr
24-A
pr
1-M
ay
InflowOutflwSeries3
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Bug LifeCycleOpen
Verification
Documenting
RejectedClosed
Triage
In ProgressDev & QA
TestingReview
Approved QA Testing
Before
EngineeringDevelopment QA Resolution
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Metrics and Charts
5.1 Targets
240 241226
242234
284283274276
288306
329 328311 310
0
25
50
75
100
125
150
175
200
225
250
275
300
325
350
375
400
2-J
an
9-J
an
16-J
an
23-J
an
30-J
an
6-F
eb
13-F
eb
20-F
eb
27-F
eb
6-M
ar
13-M
ar
20-M
ar
27-M
ar
3-A
pr
10-A
pr
17-A
pr
24-A
pr
1-M
ay
Server
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Metrics and Charts
v5.1 P1 Inflow/Outflow
4
11
3 3
1
0
1
0
2
0
3
2
1
0
2
11
4
0
33
4
1
0
16
00
5
10
15
20
9-J
an
16-J
an
23-J
an
30-J
an
6-F
eb
13-F
eb
20-F
eb
27-F
eb
6-M
ar
13-M
ar
20-M
ar
27-M
ar
3-A
pr
10-A
pr
17-A
pr
24-A
pr
1-M
ay
Inflow Outflw
MySQL – User Conference and Expo 2009 – Software Quality and Testing in MySQL
Questions?
Contact Information
• Trim Pershad [email protected]
• Omer BarNir [email protected]