CS5226 2002
Operating System &
Database Performance Tuning
Xiaofang Zhou
School of Computing, NUS
Office: S16-08-20
Email: zhouxf@comp.nus.edu.sg
URL: www.itee.uq.edu.au/~zxf
Outline
Part 1: Operating systems and DBMS
Part 2: OS-related tuning
2
Operating System
• Operating system is an
interface between hardware
and other software,
supporting:
• Processes and threads;
hardware
• Paging, buffering and IO
scheduling
• Multi-tasking
• File system
• Other utilities such as timing,
networking and performing
monitoring
3
Scheduling
Process vs thread
Scheduling based on time-slicing, IO, priority etc
Different from transaction scheduling
The cost of content switching
When switch is desirable? And when is not?
The administrator can set priorities to
processes/threads
Case 1: the DBMS runs at a lower priority
Case 2: different transactions run at different priority
Case 3: online transactions with higher priority than offline
transactions
4
Priority Inversion
Let priorities T1 > T2s > T3
Request X
T1
T2s
T3
Lock x
… a solution: priority inheritance
5
Database Buffers
•An application can have its own in-
Application buffers
memory buffers (e.g., variables in the
program; cursors);
•A logical read/write will be issued to
the DBMS if the data needs to be
read/written to the DBMS;
DBMS buffers
•A physical read/write is issued by the
DBMS using its systematic page
replacement algorithm. And such a
request is passed to the OS.
•OS may initiate IO operations to OS buffers
support the virtual memory the DBMS
buffer is built on. 6
Database Buffer Size
DATABASE PROCESSES Buffer too small, then hit
ratio too small
hit ratio =
(logical acc. - physical acc.) /
(logical acc.)
Buffer too large, paging
DATABASE RAM
BUFFER
Recommended strategy:
monitor hit ratio and
increase buffer size until
Paging
hit ratio flattens out. If
Disk
LOG DATA DATA there is still paging, then
buy memory.
7
Buffer Size - Data
Settings:
employees(ssnum, name, lat, long, hundreds1,
hundreds2);
clustered index c on employees(lat); (unused)
10 distinct values of lat and long, 100 distinct values of
hundreds1 and hundreds2
20000000 rows (630 Mb);
Warm Buffer
Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID
controller from Adaptec (80Mb), 4x18Gb drives (10000
RPM), Windows 2000.
8
Buffer Size - Queries
Queries:
Scan Query
select sum(long) from employees;
Multipoint query
select * from employees where lat = ?;
9
Database Buffer Size
Scan Query
0.1
SQL Server 7 on
0.08
Windows 2000
(Queries/sec)
Throughput
0.06
0.04
0.02
Scan query:
0 LRU (least recently used)
does badly when table spills
0 200 400 600 800 1000
Buffer Size (Mb)
to disk as Stonebraker
Multipoint Query observed 20 years ago.
160 Multipoint query:
(Queries/sec)
Throughput increases with
Throughput
120
80
buffer size until all data is
40
accessed from RAM.
0
0 200 400 600 800 1000
Buffer Size (Mb)
10
It’s All About $$$
Buffering is about a trade-off between speed
and cost
A (18 GB) disk offers 170 random access for $300
the access cost A=$1.76 per access per second
RAM C=$0.5/MB
Page size B = 8 KB
Page p is accessed every I=200 s
Keep page p in memory?
Yes: cost C/1024*B = $0.0039 for 8KB RAM
No: cost A/I = $0.0088
So, p is in memory until its access interval reaches ??? s
11
Multiprogramming Levels
More concurrent users
Better utilization of CPU cycles (and other system
resources)
Risk of excessive page swapping
More lock conflicts
So how many exactly
Depends on transaction profiles
Experiments to find the best value
And this parameter may change when application
patterns change
12
Disk Layout and Access
Larger disk allocation chunks improves
write performance
At the cost of disk utilisation
Setting disk usage factor
Low when expecting updates/inserts
Higher for scan-type of queries
Using prefetching
For non-random accesses
13
Scan Performance - Data
Settings:
lineitem ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY,
L_LINENUMBER , L_QUANTITY, L_EXTENDEDPRICE ,
L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS ,
L_SHIPDATE, L_COMMITDATE,
L_RECEIPTDATE, L_SHIPINSTRUCT ,
L_SHIPMODE , L_COMMENT );
600 000 rows
Lineitem tuples are ~ 160 bytes long
Cold Buffer
Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID
controller from Adaptec (80Mb), 4x18Gb drives (10000RPM),
Windows 2000.
14
Scan Performance - Queries
Queries:
select avg(l_discount) from lineitem;
15
Usage Factor
DB2 UDB v7.1 on
Windows 2000
0.2
Usage factor is the
Throughput (Trans/sec)
0.15 percentage of the page
0.1 used by tuples and
0.05
scan auxiliary data structures
(the rest is reserved for
0
70 80 90 100
future)
Usage Factor (%) Scan throughput
increases with usage
factor.
16
Prefetching
DB2 UDB v7.1 on
0.2
Windows 2000
Throughput (Trans/sec)
0.15
0.1 Throughput
0.05
scan
increases up to a
0
32Kb 64Kb 128Kb 256Kb
certain point when
Prefetching prefetching size
increases.
17
Summary
In this module, we have covered:
A review of OS from the DBMS perspective
How to optimise OS-related parameters
and options
Thread
Buffer, and
File system
Next: tuning the hardware
18