基于Linux平台的Oracle RAC集群系统的性能调整与优化研究
|
摘要
数据库系统的性能是影响生产系统效率的重要因数。许多使用到数据库的系统在进入到生产阶段一段时间以后都会产生一定的性能问题,和服务器硬件、数据库的内存设置、操作系统、数据库初始参数设置都有可能有关系。因此,数据库的性能调整与优化对于整个生产系统的正常运行起着至关重要的作用。随着企业数据的不断扩展,单实例数据库已经不能满足要求,于是产生了数据库高可用性集群技术。从而,对于如何使用数据库集群技术,如何保障数据库高可用性集群技术在生产系统上面有很好的应用就成为了数据库研究的一个重要课题。
本文是在基于Linux操作系统基础上,研究针对Oracle RAC系统行之有效的优化方法,能够提升系统的性能。本文首先对Linux系统的内核参数和OracleRAC技术进行了研究。和Oracle单实例数据库相比,RAC集群无论在逻辑结构还是物理结构上都有了很大的变化。通过分析RAC集群Cache Fusion这一机制,设计了以Service的质量为要求并且根据并发访问量为前提条件,对Service进行分组这一优化方法,分散了工作负载,从而使得集群中不同节点之间的数据能够同步,不会产生冗余。
通过对Oracle RAC的Shared Pool、Buffer Cache、Cache Fusion的分析,研究了针对Oracle单实例数据库优化的方法在RAC中优化的可行性。在Oracle RAC系统中,对于Shared Pool以及Buffer Cache的优化在一定程度上也可以提高集群系统的性能。
最后通过VMware虚拟机搭建了一个RAC集群实验平台,并设计了一个图书管理系统,进行了三组实验,对在Oracle单实例数据库以及RAC集群上使用不同的优化方法以后的性能结果作了分析比较。最后一个实验应用了本文设计的Service优化分组方案,对Oracle RAC系统进行了优化。通过实验可以知道,经过优化以后的RAC集群系统的性能要比经过优化的Oracle单实例数据库的性能要好,具有比较重要的现实意义。
The performance of Database system is an important factor of production systems'efficiency. After a period of time, many production systems will have some performance issues, which are likely to have relationships with the server hardware, data block memory settings, operating system, the initial parameters of database and so on. Therefore, the optimization of database performance for the normal operation of the entire production system plays a vital role. Moreover, with the continuous expansion of enterprise data, single-instance database can no longer meet the requirements, so there is the database high-availability clustering technology. Therefore, it has become an important research topic about database how to well use the database clustering technology and how to use high-availability database clustering technology in the production system.
Based on the Linux operating system, this article researches the effective optimization method, which can increase system performance. Firstly, the article studies Linux system kernel parameters and Oracle RAC technology. Compared to Oracle single-instance database, in terms of logical structure or physical structure, RAC cluster has a lot of changes. By analyzing the Cache Fusion mechanism, it has designed an optimization schema using service for the requirements of the quality of the Service and the concurrent access. This schema has distributed the work load and makes the data between different nodes synchronized and no redundancy.
Secondly, by analyzing the shared pool, buffer cache, cache fusion in Oracle RAC, it researches the feasibility of tuning Oracle RAC. To some extent, optimizing the shared pool and buffer cache can also improve the performance of cluster system.
Finally, it has built a RAC cluster experimental platform using VMware virtual machines. Also it has designed a library management system and has carried out three experiments in single-instance and Oracle RAC cluster database. There are different optimization methods on the platform. After that, the results are analyzed and compared. The last experiment optimizes the Oracle RAC using service tuning schema. Through these experiments we can know, after the tuning process, the performance of Oracle RAC system is better than the performance of Oracle single-instance. So it has more important practical significance.
本文是在基于Linux操作系统基础上,研究针对Oracle RAC系统行之有效的优化方法,能够提升系统的性能。本文首先对Linux系统的内核参数和OracleRAC技术进行了研究。和Oracle单实例数据库相比,RAC集群无论在逻辑结构还是物理结构上都有了很大的变化。通过分析RAC集群Cache Fusion这一机制,设计了以Service的质量为要求并且根据并发访问量为前提条件,对Service进行分组这一优化方法,分散了工作负载,从而使得集群中不同节点之间的数据能够同步,不会产生冗余。
通过对Oracle RAC的Shared Pool、Buffer Cache、Cache Fusion的分析,研究了针对Oracle单实例数据库优化的方法在RAC中优化的可行性。在Oracle RAC系统中,对于Shared Pool以及Buffer Cache的优化在一定程度上也可以提高集群系统的性能。
最后通过VMware虚拟机搭建了一个RAC集群实验平台,并设计了一个图书管理系统,进行了三组实验,对在Oracle单实例数据库以及RAC集群上使用不同的优化方法以后的性能结果作了分析比较。最后一个实验应用了本文设计的Service优化分组方案,对Oracle RAC系统进行了优化。通过实验可以知道,经过优化以后的RAC集群系统的性能要比经过优化的Oracle单实例数据库的性能要好,具有比较重要的现实意义。
The performance of Database system is an important factor of production systems'efficiency. After a period of time, many production systems will have some performance issues, which are likely to have relationships with the server hardware, data block memory settings, operating system, the initial parameters of database and so on. Therefore, the optimization of database performance for the normal operation of the entire production system plays a vital role. Moreover, with the continuous expansion of enterprise data, single-instance database can no longer meet the requirements, so there is the database high-availability clustering technology. Therefore, it has become an important research topic about database how to well use the database clustering technology and how to use high-availability database clustering technology in the production system.
Based on the Linux operating system, this article researches the effective optimization method, which can increase system performance. Firstly, the article studies Linux system kernel parameters and Oracle RAC technology. Compared to Oracle single-instance database, in terms of logical structure or physical structure, RAC cluster has a lot of changes. By analyzing the Cache Fusion mechanism, it has designed an optimization schema using service for the requirements of the quality of the Service and the concurrent access. This schema has distributed the work load and makes the data between different nodes synchronized and no redundancy.
Secondly, by analyzing the shared pool, buffer cache, cache fusion in Oracle RAC, it researches the feasibility of tuning Oracle RAC. To some extent, optimizing the shared pool and buffer cache can also improve the performance of cluster system.
Finally, it has built a RAC cluster experimental platform using VMware virtual machines. Also it has designed a library management system and has carried out three experiments in single-instance and Oracle RAC cluster database. There are different optimization methods on the platform. After that, the results are analyzed and compared. The last experiment optimizes the Oracle RAC using service tuning schema. Through these experiments we can know, after the tuning process, the performance of Oracle RAC system is better than the performance of Oracle single-instance. So it has more important practical significance.
引文
[1]James L. Johnson编著.李天柱,肖艳芹,杨文柱等译.数据库——模型、语言与设计[M].北京：电子工业出版社,2004：2-12.
[2]王珊,萨师煊编著.数据库系统概论(第4版)[M].北京：高等教育出版社,2006：5-20.
[3]陈吉平编著,构建Oracle高可用环境——企业级高可用数据库架构、实战与经验总结[M].北京：电子工业出版社,2008：151-188.
[4]Julian Dyke, Steve Shaw编著Pro Oracle Database lOg RAC on Linux: Installation, Administration, and Performance [M]美国,Apress,2006:21-30
[5]Benoit Dageville, Karl Dias. Oracle's Self-Tuning Architecture and Solutions [J]. Bulletin of the IEEE Computer Society Technical Committee on Data Engineering, 2006,29(3):24-31.
[6]Qun Li, Honglin Xu, Guo Yan. Research on Performance Optimization and Implementation of Oracle Database[J]. Intelligent Information Technology Application,2009:520-523.
[7]金震江.针对Oracle服务器的Linux内核的研究与定制[D].苏州大学优秀硕士学位论文,2006.
[8]Benoit Dageville, Dinesh Das, Karl Dias, Khnled Yagoub, Mohamed Zait, Mohamed Ziauddin. Automatic SQL Tuning in Oracle 10g[J]. Proceedings of the 30th VLDB Conference,2004(30):1098-1109.
[9]Philip A. Adams, John C. Hax. A Case for Database Filesystems[J]. Proceedings of the Third IEEE International Conference on Space Mission Challenges for Information Technology,2009:173-179.
[10]Dandan Li, Lu Han, Yi Ding. SQL Query Optimization Methods of Relational Database System[J]. Computer Engineering and Applications,2010(1):557-560.
[11]刘同.负载均衡技术在数据库集群系统中的应用与实现[D].国防科学技术大学优秀硕士学位论文,2009.
[12]Benoit Dageville, Mohamed Zait. SQL Memory Management in Oracle9i[J]. Proceedings of the 28th VLDB Conference,2002:962-973.
[13]刘冉冉.基于任务分配的数据库集群模型研究[D].电子科技大学优秀硕士学位论文,2007.
[14]Fabien De Marchi. Mohand-Said Hacid. Jean-Marc Petit. Some remarks on self-tuning logical database design [J]. Proceedings of the 21st International Conference on Data Engineering,2005:1219-1225.
[15]Pete Belknap, Benoit Dageville, Karl Dias, Khaled Yagoub. Self-Tuning for SQL Performance in Oracle Database 11g [J]. Proceedings of the 25th International Conference on Data Engineering,2009:1694-1700.
[16]梁勇.数据库集群故障切换技术的研究与实现[D].国防科学技术大学优秀硕士学位论文,2010.
[17]Gerhard Weikum, Axel Moenkeberg, Christof Hasse, Peter Zabback. Self-tuning Database Technology and Information Services:From Wishful Thinking to Viable Engineering [J].28th International Conference on Very Large Databases,2002:20-31.
[18]Surajit Chaudhuri, Vivek Narasayya. Self-Tuning Database Systems:A Decade Of Progress [J].33rd International Conference on Very Large Database,2007:3-14.
[19]Jeong Seok Oh, Sang Ho Lee. Resource Selection for Autonomic Database Tuning [J]. Proceedings of the 21st International Conference on Data Engineering, 2005:1218-1225.
[20]郭珉ORACLE数据库SQL优化原则[J].计算机系统应用2010(04)：170-173.
[21]杜庆峰,张卫山Oracle的中大型应用系统性能优化分析[J].计算机工程,2005,31(14)：91-93.
[22]杨小艳,尹明,戴学丰Oracle数据库查询优化方法研究[J].计算机与现代化,2008(4)：4-7.
[23]刘英霞,刘一臻Oracle9i数据库性能调优技术应用研究[J].辽宁科技学院学报,2009,11(1)：16-18.
[24]Karl Dias, Mark Ramacher, Uri Shaft, Venkateshwaran Venkataramani, Graham Wood. Automatic Performance Diagnosis and Tuning in Oracle[J]. Proceedings of the CIDR Conference,2005:162-172.
r25] Darcy G. Benoit. Automatic Diagnosis of Performance Problems in Database Management Systems [J]. Autonomic Computing,2005:326-338.
[26]Herodotos Herodotou, Shivnath Babu. Automated SQL tuning through trial and (sometimes) error[J]. Proceedings of the Second International Workshop on Testing Database Systems,2009:76-81.
[27]万春.基于Linux的数据库集群系统的研究[D].华中科技大学优秀硕士学位论文,2004.
[28]刘增军.高可用性数据库系统研究、应用与性能优化[D].国防科学技术大学优秀硕士学位论文,2006.
[29]姜维智Oracle数据库性能优化[D].电子科技大学优秀硕士学位论文,2007.
[30]Sam R.Alapati. Expert Oracle Database 11g Administration[M]. Apress Press, 2010:289-340.
[31]邹俊.基于Oracle数据库系统性能调整与优化研究[D].江西财经大学优秀硕士学位论文,2006.
[32]周晓丹.基于ORACLE RAC平台的海量数据DML处理性能的研究[D].厦门大学优秀硕士学位论文,2007.
[33]谭磊.基于等待事件的Oracle数据库调优与实时监控研究[D].成都理工大学优秀硕士学位论文,2010.
[34]Richmond Shee, Kirtikumar Deshpande, K Gopalakrishnan. Oracle Wait Interface:A Practical Guide to Performance Diagnostics & Tuning[M]. McGraw-Hill/Osborne Press,2004:167-182.
[35]龚卫华.数据库集群系统的关键技术研究[D].华中科技大学优秀博士学位论文,2006.
[36]袁爱梅Oracle数据库性能优化研究[D].华东师范大学优秀硕士学位论文,2007.
[37]姜召凤Oracle RAC数据库缓存优化方法研究[D].大连海事大学优秀硕士学位论文,2009.
[.38]周晓丹,冯少荣,薛永生Oracle 10g RAC核心技术研究与分析[J].计算机工程,2007,33(7)：53-55.
[39]董金梅,原有进,郑建华.访问Oracle大对象数据的技术及实现[J].计算机 应用研究,2005(1)：177-178.
[40]王意洁,肖侬,任浩.卢锡城数据网格及其关键技术研究[J].计算机研究与发展,2002,39(8)：943-947.
[41]数据仓库数据更新的研究及基于Oracle数据库的开发与应用[J].计算机工程与应用,2004,26(11)：168-171.
[42]Matthew Hart, Scott Jesse. Oracle Database 10g:High Availablity with RAC Flashback & Data Guard [M]. McGraw-Hill/Osborne Press,2004:152-191.
[43]Murali Vallath. Oracle Real Application Clusters [M]. Digital Press, 2004:311-367.
[44]Thomas Kyte. Expert Oracle Database Architecture 9i and lOg Programming Techniques and Solutions[M]. Apress Press,2005:190-312.
[45]Richard J.Niemiec. Oracle Database lOg Performance Tuning Tips and Techniques [M]. McGraw-Hill/Osborne Press,2007:189-219.
[46]Jonathan Lewis. Cost Based Oracle Fundamentals[M]. Apress Press, 2004:165-198.
[47]张晓明.大话Oracle RAC集群、高可用性、备份与恢复[M].北京,人民邮电出版社,2009：96-134.
[48]Edward Whalen. Oracle Database lOg Linux Administration:Deploy and Optimize Oracle Database 10g in a Linux Environment[M]. McGraw-Hill/Osborne Press,2007:119-198.
[49]李爱武.基于Linux的Oracle数据库管理[M].北京,北京邮电大学出版社,2008：35-124.
[50]Donald K.Burleson. Oracle High-Performance SQL Tuning[M]. McGraw-Hill/Osborne Press,2002:87-125.
[51]鸟哥.鸟哥的Linux私房菜[M].北京,人民邮电出版社,2002：21-97.
[52]Nitin Vengurlekar, Murali Vallath, Rich Long. Oracle Automatic Storage Management[M]. McGraw-Hill/Osborne Press,2007:89-135.
[53]Barb Lundhild. Automatic Workload Management with Oracle Real Application Clusters[J]. An Oracle Technical Whiter Paper,2008.
[54]Baos Qoa. Oracle Real Application Clusters in Oracle VM Environments[M]. An Oracle Technical Whiter Paper,2009.
[2]王珊,萨师煊编著.数据库系统概论(第4版)[M].北京：高等教育出版社,2006：5-20.
[3]陈吉平编著,构建Oracle高可用环境——企业级高可用数据库架构、实战与经验总结[M].北京：电子工业出版社,2008：151-188.
[4]Julian Dyke, Steve Shaw编著Pro Oracle Database lOg RAC on Linux: Installation, Administration, and Performance [M]美国,Apress,2006:21-30
[5]Benoit Dageville, Karl Dias. Oracle's Self-Tuning Architecture and Solutions [J]. Bulletin of the IEEE Computer Society Technical Committee on Data Engineering, 2006,29(3):24-31.
[6]Qun Li, Honglin Xu, Guo Yan. Research on Performance Optimization and Implementation of Oracle Database[J]. Intelligent Information Technology Application,2009:520-523.
[7]金震江.针对Oracle服务器的Linux内核的研究与定制[D].苏州大学优秀硕士学位论文,2006.
[8]Benoit Dageville, Dinesh Das, Karl Dias, Khnled Yagoub, Mohamed Zait, Mohamed Ziauddin. Automatic SQL Tuning in Oracle 10g[J]. Proceedings of the 30th VLDB Conference,2004(30):1098-1109.
[9]Philip A. Adams, John C. Hax. A Case for Database Filesystems[J]. Proceedings of the Third IEEE International Conference on Space Mission Challenges for Information Technology,2009:173-179.
[10]Dandan Li, Lu Han, Yi Ding. SQL Query Optimization Methods of Relational Database System[J]. Computer Engineering and Applications,2010(1):557-560.
[11]刘同.负载均衡技术在数据库集群系统中的应用与实现[D].国防科学技术大学优秀硕士学位论文,2009.
[12]Benoit Dageville, Mohamed Zait. SQL Memory Management in Oracle9i[J]. Proceedings of the 28th VLDB Conference,2002:962-973.
[13]刘冉冉.基于任务分配的数据库集群模型研究[D].电子科技大学优秀硕士学位论文,2007.
[14]Fabien De Marchi. Mohand-Said Hacid. Jean-Marc Petit. Some remarks on self-tuning logical database design [J]. Proceedings of the 21st International Conference on Data Engineering,2005:1219-1225.
[15]Pete Belknap, Benoit Dageville, Karl Dias, Khaled Yagoub. Self-Tuning for SQL Performance in Oracle Database 11g [J]. Proceedings of the 25th International Conference on Data Engineering,2009:1694-1700.
[16]梁勇.数据库集群故障切换技术的研究与实现[D].国防科学技术大学优秀硕士学位论文,2010.
[17]Gerhard Weikum, Axel Moenkeberg, Christof Hasse, Peter Zabback. Self-tuning Database Technology and Information Services:From Wishful Thinking to Viable Engineering [J].28th International Conference on Very Large Databases,2002:20-31.
[18]Surajit Chaudhuri, Vivek Narasayya. Self-Tuning Database Systems:A Decade Of Progress [J].33rd International Conference on Very Large Database,2007:3-14.
[19]Jeong Seok Oh, Sang Ho Lee. Resource Selection for Autonomic Database Tuning [J]. Proceedings of the 21st International Conference on Data Engineering, 2005:1218-1225.
[20]郭珉ORACLE数据库SQL优化原则[J].计算机系统应用2010(04)：170-173.
[21]杜庆峰,张卫山Oracle的中大型应用系统性能优化分析[J].计算机工程,2005,31(14)：91-93.
[22]杨小艳,尹明,戴学丰Oracle数据库查询优化方法研究[J].计算机与现代化,2008(4)：4-7.
[23]刘英霞,刘一臻Oracle9i数据库性能调优技术应用研究[J].辽宁科技学院学报,2009,11(1)：16-18.
[24]Karl Dias, Mark Ramacher, Uri Shaft, Venkateshwaran Venkataramani, Graham Wood. Automatic Performance Diagnosis and Tuning in Oracle[J]. Proceedings of the CIDR Conference,2005:162-172.
r25] Darcy G. Benoit. Automatic Diagnosis of Performance Problems in Database Management Systems [J]. Autonomic Computing,2005:326-338.
[26]Herodotos Herodotou, Shivnath Babu. Automated SQL tuning through trial and (sometimes) error[J]. Proceedings of the Second International Workshop on Testing Database Systems,2009:76-81.
[27]万春.基于Linux的数据库集群系统的研究[D].华中科技大学优秀硕士学位论文,2004.
[28]刘增军.高可用性数据库系统研究、应用与性能优化[D].国防科学技术大学优秀硕士学位论文,2006.
[29]姜维智Oracle数据库性能优化[D].电子科技大学优秀硕士学位论文,2007.
[30]Sam R.Alapati. Expert Oracle Database 11g Administration[M]. Apress Press, 2010:289-340.
[31]邹俊.基于Oracle数据库系统性能调整与优化研究[D].江西财经大学优秀硕士学位论文,2006.
[32]周晓丹.基于ORACLE RAC平台的海量数据DML处理性能的研究[D].厦门大学优秀硕士学位论文,2007.
[33]谭磊.基于等待事件的Oracle数据库调优与实时监控研究[D].成都理工大学优秀硕士学位论文,2010.
[34]Richmond Shee, Kirtikumar Deshpande, K Gopalakrishnan. Oracle Wait Interface:A Practical Guide to Performance Diagnostics & Tuning[M]. McGraw-Hill/Osborne Press,2004:167-182.
[35]龚卫华.数据库集群系统的关键技术研究[D].华中科技大学优秀博士学位论文,2006.
[36]袁爱梅Oracle数据库性能优化研究[D].华东师范大学优秀硕士学位论文,2007.
[37]姜召凤Oracle RAC数据库缓存优化方法研究[D].大连海事大学优秀硕士学位论文,2009.
[.38]周晓丹,冯少荣,薛永生Oracle 10g RAC核心技术研究与分析[J].计算机工程,2007,33(7)：53-55.
[39]董金梅,原有进,郑建华.访问Oracle大对象数据的技术及实现[J].计算机 应用研究,2005(1)：177-178.
[40]王意洁,肖侬,任浩.卢锡城数据网格及其关键技术研究[J].计算机研究与发展,2002,39(8)：943-947.
[41]数据仓库数据更新的研究及基于Oracle数据库的开发与应用[J].计算机工程与应用,2004,26(11)：168-171.
[42]Matthew Hart, Scott Jesse. Oracle Database 10g:High Availablity with RAC Flashback & Data Guard [M]. McGraw-Hill/Osborne Press,2004:152-191.
[43]Murali Vallath. Oracle Real Application Clusters [M]. Digital Press, 2004:311-367.
[44]Thomas Kyte. Expert Oracle Database Architecture 9i and lOg Programming Techniques and Solutions[M]. Apress Press,2005:190-312.
[45]Richard J.Niemiec. Oracle Database lOg Performance Tuning Tips and Techniques [M]. McGraw-Hill/Osborne Press,2007:189-219.
[46]Jonathan Lewis. Cost Based Oracle Fundamentals[M]. Apress Press, 2004:165-198.
[47]张晓明.大话Oracle RAC集群、高可用性、备份与恢复[M].北京,人民邮电出版社,2009：96-134.
[48]Edward Whalen. Oracle Database lOg Linux Administration:Deploy and Optimize Oracle Database 10g in a Linux Environment[M]. McGraw-Hill/Osborne Press,2007:119-198.
[49]李爱武.基于Linux的Oracle数据库管理[M].北京,北京邮电大学出版社,2008：35-124.
[50]Donald K.Burleson. Oracle High-Performance SQL Tuning[M]. McGraw-Hill/Osborne Press,2002:87-125.
[51]鸟哥.鸟哥的Linux私房菜[M].北京,人民邮电出版社,2002：21-97.
[52]Nitin Vengurlekar, Murali Vallath, Rich Long. Oracle Automatic Storage Management[M]. McGraw-Hill/Osborne Press,2007:89-135.
[53]Barb Lundhild. Automatic Workload Management with Oracle Real Application Clusters[J]. An Oracle Technical Whiter Paper,2008.
[54]Baos Qoa. Oracle Real Application Clusters in Oracle VM Environments[M]. An Oracle Technical Whiter Paper,2009.