Article

Adaptive self-tuning memory in DB2

Authors:

Christian Garcia-Arellano,

Sam S. Lightstone,

M. SurendraAuthors Info & Claims

VLDB '06: Proceedings of the 32nd international conference on Very large data bases

Pages 1081 - 1092

Published: 01 September 2006 Publication History

Abstract

DB2 for Linux, UNIX, and Windows Version 9.1 introduces the Self-Tuning Memory Manager (STMM), which provides adaptive self tuning of both database memory heaps and cumulative database memory allocation. This technology provides state-of-the-art memory tuning combining control theory, runtime simulation modeling, cost-benefit analysis, and operating system resource analysis. In particular, the nove use of cost-benefit analysis and control theory techniques makes STMM a breakthrough technology in database memory management. The cost-benefit analysis allows STMM to tune memory between radically different memory consumers such as compiled statement cache, sort, and buffer pools. These methods allow for the fast convergence of memory settings while also providing stability in the presence of system noise. The tuning mode has been found in numerous experiments to tune memory allocation as well as expert human administrators, including OLTP, DSS, and mixed environments. We believe this is the first known use of cost-benefit analysis and control theory in database memory tuning across heterogeneous memory consumers.

References

[1]

{1} K. P. Brown M. J. Carey, and M. Livny, Goa-Oriented Buffer Management Revisited, ACM SIMGOD 1996, Montreal, Canada, 353-364.]]

Digital Library

[2]

{2} K. Brown, M. Carey, and M. Livny, Managing Memory to Meet Multiclass Workload Response Time Goals, VLDB 1993, Dublin, Ireland, 328-341.]]

Digital Library

[3]

{3} R. Christensen, Analysis of Variance, Design, and Regression: Applied Statistica Methods, Chapman & Hall/CRC, 1996.]]

[4]

{4} J. Chung, D. Ferguson, and G. Wang, C. Nikolaou, and J. Teng Goal Oriented Dynamic Buffer Pool Management for Data Base Systems, IEEE ICECCS, 1995, 191-198.]]

Digital Library

[5]

{5} B. Dageville, M. Zaït, SQL Memory Management in Oracle9i. VLDB 2002: 962-973, Hong Kong, China.]]

[6]

{6} D. Davison, G. Graefe, Memory Contention Responsive Hash Join, VLDB, 1994, Santiago, Chile.]]

Digital Library

[7]

{7} DB2 UDB: The autonomic computing advantage, http://www.db2mag.com/epub/autonomic/]]

[8]

{8} Y. Diao, C. Wah Wu, J. L. Hellerstein, A. J. Storm, M. Surendra, S. Lightstone, S. Parekh, C. Garcia-Arellano, M. Carroll, L. Chu, J. Colaco "Comparative Studies of Load Balancing With Control and Optimization Techniques" 24th American Control Conference (ACC), June 8-10, 2005, Portland, Oregon.]]

[9]

{9} Y. Diao, J. Hellerstein, A. Storm, M. Surendra, S. Lightstone, S. Parekh, C. Garcia-Arellano. "Incorporating Cost of Control Into the Design of a Load Balancing Controller", IEEE Real-Time and Embedded Technology and Application Systems Symposium, March 1, 2004.]]

Digital Library

[10]

{10} Y. Diao, J. Hellerstein, A. Storm, M. Surendra, S. Lightstone, S. Parekh, C. Garcia-Arellano "Using MIMO Linear Control for Load Balancing in Computing Systems", American Control Conference, 2004.]]

[11]

{11} K. Dias, M. Ramacher, U. Shaft, V. Venkataramani, G. Wood Automatic Performance Diagnosis and Tuning in Oracle, CIDR 2005.]]

[12]

{12} C. M. Garcia-Arellano, S. Lightstone, G. Lohman, V. Markl, A. Storm, "Autonomic Features of the IBM DB2 Universal Database for Linux, UNIX, and Windows.", IEEE Transactions on Systems Man and Cybernetics special issue on Engineering Autonomic Systems, 2006.]]

Digital Library

[13]

{13} J. L. Hellerstein, Y. Diao, S. Parekh, D.M. Tilbury, Feedback Control of Computing Systems ISBN 0-471-26637-X John Wiley & Sons 2004.]]

Digital Library

[14]

{14} P. Larson, G. Graefe, Memory Management During Run Generation in Externa Sorting SIGMOD, 1998, Seattle, Washington, U.S.A.]]

Digital Library

[15]

{15} S. Lightstone, A. Storm, C. Garcia-Arellano, M. Carroll, J. Colaco, Y. Diao, M. Surendra "Self tuning memory management in a relational database system" Fourth Annual Workshop on Systems and Storage Technology, December 11, 2005, IBM Research Lab Haifa University campus, Mount Carmel, Haifa, Israel.]]

[16]

{16} P. Martin, H. Li, M. Zheng, K. Romanufa, and W. Powley, Dynamic Reconfiguration Algorithm: Dynamically Tuning Multiple Buffer Pools, DEXA 2000, 92-101.]]

Digital Library

[17]

{17} M. Mehta, D. DeWitt, Dynamic Memory Allocation For Multiple-Query Workloads, VLDB, 1993, Dublin, Ireland.]]

Digital Library

[18]

{18} Oracle 9i Memory Management http://www.oracle.com/technology/products/oracle9i/daily/ap r15.html]]

[19]

{19} H. Pang, M. Carey, M. Livny, Partially Preemptible Hash Joins, SIGMOD, 1993, Washington, D.C., U.S.A.]]

Digital Library

[20]

{20} J. Rao, S. Lightstone, G. Lohman, D. Zilio, A. Storm, C. Garcia-Arellano, S. Fadden. "DB2 Design Advisor: integrated automated physica database design" VLDB 2004, Toronto, Canada.]]

[21]

{21} The Self Managing Database: Automatic SGA Memory Management. Oracle White Paper November 2003. http://www.oracle.com/technology/products/manageability/d atabase/pdf/twp03/TWP_manage_self_managing_database.p df]]

[22]

{22} M. Sinnwell, and A. C. Konig, Managing Distributed Memory to Meet Multiclass Workload Response Time Goals, ICDE 1995, 87-94.]]

Digital Library

[23]

{23} SQL Server Architecture: Memory Architecture http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_4rc5.asp]]

[24]

{24} W. Tian, W. Powley and P. Martin. Techniques for Automatically Sizing Multiple Buffer Pools in DB2. CASCON 2003, Toronto, Canada.]]

Digital Library

[25]

{25} "Transaction Processing Performance Council" http://www.tpc.org.]]

[26]

{26} W. Zhang P. Larson, Dynamic Memory Adjustment for External Merge Sort, VLDB, 1997, Athens, Greece.]]

Digital Library

Cited By

Lee BAn MLee S(2023)LRU-C: Parallelizing Database I/Os for Flash SSDsProceedings of the VLDB Endowment10.14778/3598581.359860516:9(2364-2376)Online publication date: 10-Jul-2023
https://dl.acm.org/doi/10.14778/3598581.3598605
An MSong ISong YLee SIves ZBonifati AEl Abbadi A(2022)Avoiding Read Stalls on Flash StorageProceedings of the 2022 International Conference on Management of Data10.1145/3514221.3526126(1404-1417)Online publication date: 10-Jun-2022
https://dl.acm.org/doi/10.1145/3514221.3526126
Pavlo AButrovich MMa LMenon PLim WVan Aken DZhang W(2021)Make your database system dream of electric sheepProceedings of the VLDB Endowment10.14778/3476311.347641114:12(3211-3221)Online publication date: 28-Oct-2021
https://dl.acm.org/doi/10.14778/3476311.3476411
Show More Cited By

Index Terms

Adaptive self-tuning memory in DB2
1. Information systems
2. Software and its engineering
  1. Software organization and properties
    1. Contextual software domains
      1. Operating systems
        File systems management
        Memory management

Recommendations

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences

VLDB '06: Proceedings of the 32nd international conference on Very large data bases

September 2006

1269 pages

Sponsors

SIGMOD: ACM Special Interest Group on Management of Data
K.I.S.S. SIG on Databases
AJU Information Technology Co., Ltd
US Army ITC-PAC Asian Research Office
Google Inc.
The Database Society of Japan
Samsung SOS
Advanced Information Technology Research Center
Naver
Microsoft: Microsoft
Korea Info Sci Society: Korea Information Science Society
SK telecom
Systems Applications Products
ORACLE: ORACLE
International Business Management
Air Force Office of Scientific Research/Asian Office of Aerospace R&D
Kosef
Kaist
LG Electronics
CCF-DBS

Publisher

VLDB Endowment

Publication History

Published: 01 September 2006

Check for updates

Qualifiers

Article

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

39
Total Citations
View Citations
844
Total Downloads

Downloads (Last 12 months)14
Downloads (Last 6 weeks)1

Reflects downloads up to 03 Oct 2024

Other Metrics

View Author Metrics

Citations

Cited By

Lee BAn MLee S(2023)LRU-C: Parallelizing Database I/Os for Flash SSDsProceedings of the VLDB Endowment10.14778/3598581.359860516:9(2364-2376)Online publication date: 10-Jul-2023
https://dl.acm.org/doi/10.14778/3598581.3598605
An MSong ISong YLee SIves ZBonifati AEl Abbadi A(2022)Avoiding Read Stalls on Flash StorageProceedings of the 2022 International Conference on Management of Data10.1145/3514221.3526126(1404-1417)Online publication date: 10-Jun-2022
https://dl.acm.org/doi/10.1145/3514221.3526126
Pavlo AButrovich MMa LMenon PLim WVan Aken DZhang W(2021)Make your database system dream of electric sheepProceedings of the VLDB Endowment10.14778/3476311.347641114:12(3211-3221)Online publication date: 28-Oct-2021
https://dl.acm.org/doi/10.14778/3476311.3476411
Luo CCarey M(2021)Breaking down memory wallsProceedings of the VLDB Endowment10.14778/3430915.343091614:3(241-254)Online publication date: 9-Dec-2021
https://dl.acm.org/doi/10.14778/3430915.3430916
Kanellis KAlagappan RVenkataraman SBadam AChidambaram V(2020)Too many knobs to tune? towards faster database tuning by pre-selecting important knobsProceedings of the 12th USENIX Conference on Hot Topics in Storage and File Systems10.5555/3488733.3488749(16-16)Online publication date: 13-Jul-2020
https://dl.acm.org/doi/10.5555/3488733.3488749
Lu JChen YHerodotou HBabu S(2019)Speedup your analyticsProceedings of the VLDB Endowment10.14778/3352063.335211212:12(1970-1973)Online publication date: 1-Aug-2019
https://dl.acm.org/doi/10.14778/3352063.3352112
Tan JZhang TLi FChen JZheng QZhang PQiao HShi YCao WZhang R(2019)iBTuneProceedings of the VLDB Endowment10.14778/3339490.333950312:10(1221-1234)Online publication date: 1-Jun-2019
https://dl.acm.org/doi/10.14778/3339490.3339503
Ding BDas SMarcus RWu WChaudhuri SNarasayya VBoncz PManegold SAilamaki ADeshpande AKraska T(2019)AI Meets AIProceedings of the 2019 International Conference on Management of Data10.1145/3299869.3324957(1241-1258)Online publication date: 25-Jun-2019
https://dl.acm.org/doi/10.1145/3299869.3324957
Zhang JLiu YZhou KLi GXiao ZCheng BXing JWang YCheng TLiu LRan MLi ZBoncz PManegold SAilamaki ADeshpande AKraska T(2019)An End-to-End Automatic Cloud Database Tuning System Using Deep Reinforcement LearningProceedings of the 2019 International Conference on Management of Data10.1145/3299869.3300085(415-432)Online publication date: 25-Jun-2019
https://dl.acm.org/doi/10.1145/3299869.3300085
Van Aken DPavlo AGordon GZhang BChirkova RYang JSuciu D(2017)Automatic Database Management System Tuning Through Large-scale Machine LearningProceedings of the 2017 ACM International Conference on Management of Data10.1145/3035918.3064029(1009-1024)Online publication date: 9-May-2017
https://dl.acm.org/doi/10.1145/3035918.3064029
Show More Cited By

View Options

Get Access

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Table of Contents