Databases in Context

1. Databases in Context Wendy Moncur Department of Computing Science, University of Aberdeen

2. Databases in Context • Database design in a major bank • Database management • 6000-table Personnel database

3. Who am I ? • Wendy Moncur • DataBase Administrator (DBA) at one of UK’s largest banks. • Designed databases for high performance & availability. • 19 years industry experience. • Platform: DB2 & SQL • Largest database: 6000 tables

4. Why listen? • DBA Average Minimum Salary £41,896 • DBA Average Maximum Salary £47,147 • Source: http://www.itjobswatch.co.uk

5. What does a DBA do? • Database design & optimisation • Quality assurance of SQL • Performance management • Database administration

6. Case study: the monster database

7. Case study: the monster database • 6000+ tables • 18000+ indexes

8. Part1: Challenges • “One size fits all” • External supplier • 6000+ tables • 18000+ indexes • 1 tablespace • Short timescale

9. Challenges: “one size fits all”? • One size does not fit all. • Performance of SQL statements dependent on: • Database design • Index design • The DATA

10. Challenges: “one size fits all”? • Every company has different requirements. • Customers demand high performance... and control the budget. • Service Level Agreements (SLAs) dictate … • Minimum transaction speed • Number of concurrent users • Number of remote locations • Daily system availability • Database must be tailored to achieve site-specific SLAs.

11. Challenges: external supplier • Software package & database from external supplier. • Cannot change this.

12. Challenges: 6,000+ tables • Cannot change tables: no denormalisation allowed. • Supplied program code demands these tables exist. • Cannot change supplied program code unless essential.

13. Challenges: 18,000+ indexes • Can change indexes: • Unique indexes • Clustering indexes • Secondary indexes

14. Unique index • Defines what makes a row unique. • Components of the index cannotbe changed. • Order of componentscanbe changed.

15. Unique index E.g. – for Table “EMPLOYEE” Unique index =DateOfBirth, Firstname, Surname. Most queries ask for data where only Surname, Firstnameare known. SELECT Surname, Firstname, DateOfBirth From Employee Where Surname= “Jenkins” AndFirstname= “Malcolm” ; Recommendation: Change order of unique index to Surname, Firstname, DateOfBirth.

16. Clustering indexes • Defines the physical order in which rows of data should be stored. • Components of the index can be changed. • Order of components can be changed.

17. Clustering indexes E.g. – Table “EMPLOYEE” Clustering index = DateOfBirth Yet most queries order by EmploymentStartDate SELECT EmploymentStartDate, Surname, Firstname From Employee Where Surname = “Jenkins” And Firstname = “Malcolm” ; Order by EmploymentStartDate; Recommendation: Change clustering index to use EmploymentStartDate.

18. Secondary indexes • Not unique. • Do not dictate how the data is to be held. • Created to improve performance of queries and updates. • Increases cost of insert and update, as must be created and maintained along with the table. Recommendation: Drop superfluous secondary indexes.

19. Challenges: One tablespace

20. Many tablespaces • Create many new tablespaces. • Split the tables between them, according to table function.

21. Challenge: Short timescale • At least 4 test environments: • 96,000 objects! ((6,000 tables + 18,000 indexes) * 4 environments) • 3 months Vanilla Unit test System test • Pre-live

22. Tools • Use tools to… • Check performance of each SQL statement • Manage change process

23. Check performance • “EXPLAIN” • Evaluates route to data for every SQL statement. • Identifies what indexes are used • Doesn’t identify redundant indexes • Doesn’t identify indexes that need to be changed.

24. Manage change process • Rigorous control needed • Achieved through… • Consistent naming standards • Detailed record of every change • Consistent route through environments, no short cuts • DBA tools

25. Can’t change: “One size fits all” External supplier 6000+ tables Can change: 18000+ indexes 1 tablespace Short timescale Part1: Recap of challenges

26. Part2: The Production Database • Does it perform? • Can the right people use it? • If disaster strikes, can the data be recovered?

27. Does the database perform? • Database performance monitored against Service Level Agreements (SLAs). • Regular health checks carried out: • Data stored in sequence? • Enough space? • If sub-standard performance, further database design work done.

28. Can the right people access the data? PERSONNEL database

29. Can the right people access the data? PERSONNEL database Personnel team Query & update data at individual or regional level

30. Can the right people access the data? DBA Backup/ restore data Reorganise data Change database definitions Update statistics on data PERSONNEL database Personnel team Query & update data at individual or regional level

31. Can the right people access the data? DBA Backup/ restore data Reorganise data Change database definitions Update statistics on data Chief executive Employee statistics PERSONNEL database Personnel team Query & update data at individual or regional level

32. Can the right people access the data? DBA Backup/ restore data Reorganise data Change database definitions Update statistics on data Chief executive Employee statistics PERSONNEL database Staff member Their own data Personnel team Query & update data at individual or regional level

33. Can the right people use the database? • Different people, different information needs. • Sensitive data – salary, health, discipline… • Solution • VIEWS • Transaction Management

34. If disaster strikes, can the data be recovered? • Robust backup & recovery strategies for: • Hardware failure • Software failure

36. Part2: Recap of Production Database issues • Database must perform to acceptable level. • Only the right people should have access to any data item. • No matter what, the data must be recoverable.

37. Questions?