CS 5226 2002 Operating System & Database Performance Tuning Xiaofang Zhou School of Computing, NUS Office: S 16 -08 -20 Email: zhouxf@comp. nus. edu. sg URL: www. itee. uq. edu. au/~zxf
Outline n Part 1: Operating systems and DBMS n 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 n Process vs thread n Scheduling based on time-slicing, IO, priority etc n n The cost of content switching n n Different from transaction scheduling When switch is desirable? And when is not? The administrator can set priorities to processes/threads n n n 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 n Let priorities T 1 > T 2 s > T 3 Request X T 1 T 2 s T 3 Lock x … a solution: priority inheritance 5
Database Buffers • An application can have its own inmemory 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; Application buffers 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 support the virtual memory the DBMS buffer is built on. OS buffers 6
Database Buffer Size DATABASE PROCESSES n Buffer too small, then hit ratio too small hit ratio = (logical acc. - physical acc. ) / (logical acc. ) DATABASE BUFFER RAM n n Paging Disk LOG DATA Buffer too large, paging Recommended strategy: monitor hit ratio and increase buffer size until hit ratio flattens out. If there is still paging, then buy memory. 7
Buffer Size - Data Settings: employees(ssnum, name, lat, long, hundreds 1, hundreds 2); clustered index c on employees(lat); (unused) n n 10 distinct values of lat and long, 100 distinct values of hundreds 1 and hundreds 2 20000000 rows (630 Mb); Warm Buffer Dual Xeon (550 MHz, 512 Kb), 1 Gb RAM, Internal RAID controller from Adaptec (80 Mb), 4 x 18 Gb drives (10000 RPM), Windows 2000. 8
Buffer Size - Queries: n Scan Query select sum(long) from employees; n Multipoint query select * from employees where lat = ? ; 9
Database Buffer Size n n SQL Server 7 on Windows 2000 Scan query: n n LRU (least recently used) does badly when table spills to disk as Stonebraker observed 20 years ago. Multipoint query: n Throughput increases with buffer size until all data is accessed from RAM. 10
It’s All About $$$ n Buffering is about a trade-off between speed and cost n n n 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? n n n Yes: cost C/1024*B = $0. 0039 for 8 KB RAM No: cost A/I = $0. 0088 So, p is in memory until its access interval reaches ? ? ? s 11
Multiprogramming Levels n More concurrent users n n Better utilization of CPU cycles (and other system resources) Risk of excessive page swapping More lock conflicts So how many exactly n Depends on transaction profiles n n Experiments to find the best value And this parameter may change when application patterns change 12
Disk Layout and Access n Larger disk allocation chunks improves write performance n n Setting disk usage factor n n n At the cost of disk utilisation Low when expecting updates/inserts Higher for scan-type of queries Using prefetching n 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 ); n n 600 000 rows Lineitem tuples are ~ 160 bytes long Cold Buffer Dual Xeon (550 MHz, 512 Kb), 1 Gb RAM, Internal RAID controller from Adaptec (80 Mb), 4 x 18 Gb drives (10000 RPM), Windows 2000. 14
Scan Performance - Queries: select avg(l_discount) from lineitem; 15
Usage Factor n n n DB 2 UDB v 7. 1 on Windows 2000 Usage factor is the percentage of the page used by tuples and auxiliary data structures (the rest is reserved for future) Scan throughput increases with usage factor. 16
Prefetching n n DB 2 UDB v 7. 1 on Windows 2000 Throughput increases up to a certain point when prefetching size increases. 17
Summary n In this module, we have covered: n n A review of OS from the DBMS perspective How to optimise OS-related parameters and options n n Thread Buffer, and File system Next: tuning the hardware 18
Скачать презентацию CS 5226 2002 Operating System Database Performance
6142c0911d73338f34f31a2f0373970d.ppt