INFO20003 Database Systems
Dr Renata Borovica-Gajic*
Lecture 18 Database Administration
slides adopted Week 9 from David Eccles
Today’s Lecture…
• FunctionsthatarepartoftheDBArole
– Capacity planning
• Estimating disk space and transaction load
– Backup and recovery
• Types of failures, responses to these, types of backups
INFO20003 Database Systems © University of Melbourne 2
Capacity Planning
Capacity Planning
“Capacity Planning is the process of predicting when future load levels will saturate the system and determining the most cost-effective way of delaying system saturation as much as possible.”
– Menasce and Virgilio (2002) ‘Capacity Planning for Web Services’. Prentice Hall.
• When implementing a database, need to consider:
– disk space requirements
– transaction throughput
– at go-live and throughout the life of the system
• E.g. plan for 7 years can be 20 years
INFO20003 Database Systems © University of Melbourne 4
Estimating Database Usage
INFO20003 Database Systems © University of Melbourne 5
Estimating Database Usage
INFO20003 Database Systems © University of Melbourne 6
Estimating Database Usage
INFO20003 Database Systems © University of Melbourne 7
Capacity Planning in the dev life cycle
INFO20003 Database Systems © University of Melbourne 8
Estimating disk space requirements
• Whichestimationmethodologytouse?
– many vendors sell capacity planning solutions – most have the same ideas at their core
– here we present the core concepts
• Treatdatabasesizeasthesumofalltablesizes
– Table size = number of rows * average row width
INFO20003 Database Systems © University of Melbourne 9
Calculating row widths
• Useinformationaboutstoragesizesofdifferentdata types: https://dev.mysql.com/doc/refman/8.0/en/storage- requirements.html
INFO20003 Database Systems © University of Melbourne 10
Calculating row widths
• ThesesizesareforMySQLandareslightlydifferent for other vendors: https://dev.mysql.com/doc/refman/8.0/en/storage- requirements.html
INFO20003 Database Systems © University of Melbourne 11
Calculating row widths
• https://dev.mysql.com/doc/refman/8.0/en/storage- requirements.html
For VARCHAR/BLOB we use the average size (from catalog)
INFO20003 Database Systems © University of Melbourne 12
Estimate growth of tables
• Howwilltablesgrowovertime?
• Gatherestimatesduringsystemanalysis,e.g.
– “The company sells 1000 products. There are 2,000,000 customers who place, on average, 5 orders each per month. An average order is for 8 different products.”
INFO20003 Database Systems © University of Melbourne 13
Estimate growth of tables
• Usingthissimplifieddatabaseasanexample, assume there are:
– 100 forums
– 1 million users
and assume that:
– users post average 30 times per month
we calculate:
– Post table grows by 1M rows / day – This is 12 inserts per second
INFO20003 Database Systems © University of Melbourne 14
Calculate disk space per table
• useaspreadsheettosimplifycalculationsand enable what-ifs
INFO20003 Database Systems © University of Melbourne 15
Projected total storage requirements
INFO20003 Database Systems © University of Melbourne 16
Estimating transaction load
• Consider each business transaction
– how often will transaction each be run?
– for each transaction, what SQL statements are being run?
• Forexample,considerthisfictitiousbanking application:
INFO20003 Database Systems © University of Melbourne 17
DBs store so much more
• Under the hood there is so much more stored
tab 0, row 1, @0x766
tl: 44 fb: –H-FL– lb: 0x0 cc: 5 ==> Complete row (Head,First,Last)
col 0: [ 4] 52 4f 57 32 ==> VarChar2 data.
col 1:[13] 545241494c494e47204e554c4c
col 2:[7] 77c40101010101==>DATEdata.
col 3: [ 2] c4 02 ==> Number data.
col 4:[10] 312c3030302c30303020
==> ^^^^ Note: Size here is the column length for this PIECE, not the COLUMN.
==> Note there are only five columns – trailing columns are assumed NULL
tab 0, row 2, @0x320
tl: 1020 fb: –H-FL– lb: 0x1 cc: 6 ==> 1020 bytes long, locked, 6 cols
col 0:[4] 524f5733
col 1:[11] 4249472050414444494e47
col 2:[7] 77c40c1f010101
col 3:[2] c102
col 4: [10] 31 20 20 20 20 20 20 20 20 20 ==> CHAR data. Note trailing blanks. col 5: [975]
58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 …. Repeated many times.
• Capacity planning is a rough estimation
INFO20003 Database Systems © University of Melbourne 20
Backup and Recovery
What is a Backup
• Abackupisacopyofyourdata
– however there are several types of backup
• Ifdatabecomescorruptedordeletedorheldto ransom it can be restored from the backup copy
• Abackupandrecoverystrategyisneeded
– To plan how data is backed up – To plan how it will be recovered
INFO20003 Database Systems © University of Melbourne 23
Protect data from …
• human error
– e.g. accidental drop or delete
– example:
http://www.theaustralian.com.au/aus tralian-it/human-error-triggered-nab- software-corruption/story-e6frgakx- 1225962953523
• hardware or software malfunction
– bug in application
– hard drive (failure or corruption) – CPU
– memory
INFO20003 Database Systems © University of Melbourne 24
Must also protect against
• malicious activity
• •
– security compromise
• server, database, application natural or man made disasters
– consider the scale of the damage
government regulation
– historical archiving rules
– Metadata collection (AUS)
– HIPPA, EU data retention regulations – Privacy Rules
INFO20003 Database Systems © University of Melbourne 25
Categories of Failure
Failures can be divided into the following categories:
• Statement failure
– Syntactically incorrect
• User Process failure
– The process doing the work fails (errors, dies)
• Network failure
– Network failure between the user and the database
• User error
– User accidentally drops the rows, table, database
• Memory failure
– Memory fails, becomes corrupt
• Media Failure
– Disk failure, corruption, deletion
INFO20003 Database Systems © University of Melbourne 26
Types of Backups
• PhysicalvsLogical • OnlinevsOffline
• FullvsIncremental • OnsitevsOffsite
INFO20003 Database Systems © University of Melbourne 27
Physical backup
• Physical backup
– raw copies of files and directories
– suitable for large databases that need fast recovery
– database is preferably offline (“cold” backup) when backup occurs
• MySQL Enterprise automatically handles file locking, so database is not wholly off line
– backup = exact copies of the database directories and files
– backup should include logs
– backup is only portable to machines with a similar configuration – to restore
• shut down DBMS
• copy backup over current structure on disk • restart DBMS
INFO20003 Database Systems © University of Melbourne 28
Logical backup
• Logical backup
– backup completed through SQL queries – slower than physical
• SQL Selects rather than OS copy
– output is larger than physical
– doesn’t include log or config files
– machine independent
– server is available during the backup – in MySQL can use the backup using
• Mysqldump
• SELECT … INTO OUTFILE
– to restore
• Use mysqlimport, or LOAD DATA INFILE within the mysql client
INFO20003 Database Systems © University of Melbourne 29
Online vs Offline backup
• Online (or HOT) backup
– backups occur when the database is “live”
– clients don’t realise a backup is in progress
– need to have appropriate locking to ensure integrity of data
• Offline(orCOLD)backup
– backups occur when the database is stopped
– to maximize availability to users take backup from replication server not live server
– simpler to perform
– cold backup is preferable, but not available in all situations e.g. applications without downtime
INFO20003 Database Systems © University of Melbourne 30
Full vs Incremental backup
• Full
– a full backup is where the complete database is backed up
• may be Physical or Logical, Online or Offline
– it includes everything you need to get the database operational in the event of a failure
• Incremental
– only the changes since the last backup are backed up
– for most databases this means only backup log files
– to restore:
• stop the database, copy backed up log files to disk • start the database and tell it to redo the log files
INFO20003 Database Systems © University of Melbourne 31
Create a Backup Policy
• Backup strategy is usually a combination of full and incremental backups
• Forexample:
• weekly full backup, weekday incremental backup
= Delta (only changes since last backup)
Su
Mo
Tu
We
Th
Fr
• Conduct backups when database load is low
• If using replication, use the mirror database for backups to negate any performance concerns with the primary database
• TEST your backup before you NEED your backup (crucial)
Su
INFO20003 Database Systems © University of Melbourne 32
Offsite Backup
• Enables disaster recovery
(because backup is not physically near the disaster site)
• Example solutions:
• backup tapes transported to underground vault
• remote mirror database maintained via replication • backup to Cloud (see figure below)
INFO20003 Database Systems © University of Melbourne 33
What is examinable?
• TherolesofaDBA
– Capacity planning
• Calculating Capacity & Transaction load
– Back up and Recovery
• Types of Failures • Backup Types
INFO20003 Database Systems © University of Melbourne 34
Next lectures
• Database warehousing
INFO20003 Database Systems © University of Melbourne 35