臺灣博碩士論文加值系統

隨著電腦資訊系統使用在各行各業中日益廣泛，越來越多的新增資料、日積月累的陳年資料或大量的資料分析運算需求，讓資料庫系統在整體資訊系統中扮演極重要的角色。資料庫系統處理資料的需求能力不斷的被提昇，單一昂貴伺服器處理所有資料量的時代也式微，改成眾多機器平均承受負載，整體伺服器群能快速的增加或降低資料處理能量以應付尖峰負載或省電節能。

多數企業在選擇使用資料庫時大多會考量營運成本價格，加上目前開放原始碼軟體效能佳、使用者眾多，故本研究針對開放原始碼 MySQL 資料庫在多種同步架構下之實作及效能改善分析。此研究達到讓目前及未來使用 MySQL 資料庫系統之管理者或系統規劃者了解各種MySQL資料庫的同步架構(Replication topologies)下之差異，及不同條件下之解決配套方案，進而有效地協助系統建構時的決策制定。在實作上我們將樹狀同步架構與鏈狀同步架構做了網頁連線完成時間及資料庫同步完成時間的比較，以證明樹狀架構適合於大量查詢語法負載、鏈狀架構適合於瞬間大量新增資料語法負載。兩者之結合也可以經由本研究提出之實測方式觀察其表現而調整出適合各企業的客製化需求。使用MySQL同步架構的高效能資料庫可以讓組織企業依照各單位地承載需求，風險忍受程度及可運用之經費等，種種條件下，讓建構出的資料庫系統能以最少的代價而得到系統最大之效能，其中包括：高有效性(High availability)、高延展性(Scalability)及可控制性(Administration)。

Computer Information System is fully adapted in all business those days. There is plenty of analysis being necessary for great amount of data. Such an environment made databases system become most important part in the whole Computer Information System. Then the more processing power is needed for database system increasing dramatically. A single super server to handle everything has been replaced by a group of servers working together to share the load. The benefits of using a group of computer working together with load balancing solution are based on adding servers quickly to handle the peak time load or removing the servers when the loading is not heavy.
Most business sometimes will evaluate the operation cost before they choose the database system. In these days, the open source software becomes very popular and powerful. We then use Open Source MySQL database System for performance implementation and evaluation. This research will help people understand the solutions of how to use MySQL database at different replication topologies in many situations. Our proposals will reduce the time and budget cost for Enterprise to set up the suitable replication topologies for their Computer Information System. We will evaluate two basic Replication topologies: Tree Replication Topology and Daisy Chain Replication Topology. We compare web page reading completion time cost and database replication completion time cost. We have found out Tree Replication Topology is best for heavy data query load, Daisy Chain Replication Topology is perfect for none-persist peak time extremely heavy data insertion load. The combination of two topologies will also good for most situation when heavy data query load and Heavy data insertion load. Understand MySQL database replication topologies will help business to evaluate their budget and tolerance of system downtime to implement the own custom database system. Based our evaluation, the MySQL database replication topologies can reduce business operation cost, but also with most high performance database system under high availability, good scalability and easy administration.

中文摘要 II
英文摘要 III
誌謝 IV
目錄 V
表目錄 VII
圖目錄 VIII

第一章 緒論 1
1.1 研究背景與動機 2
1.2 研究目的 2
1.3 研究方法 3
1.4 研究流程 3
1.5 研究範圍與限制 4
1.6 章節結構 5

第二章 自由軟體 MySQL 資料庫介紹 7
2.1 開放原使碼 MySQL 資料庫介紹 7
2.2 開放原使碼 MySQL 資料庫儲存引擎種類介紹 9
2.3 開放原使碼 MySQL 資料庫年度會議(Conference) 13

第三章 MySQL 資料庫架構分析及效能提昇之相關配合工具 14
3.1 同步架構(Replication topology)與叢集架構(Cluster)比較 14
3.2 開放原使碼 MySQL 資料庫相關工具程式 18
3.3 連接應用代理伺服器(Proxy)及負載平衡(Loading balance)在同步架構
下的應用 30

第四章 效能測試架構及測試方法 38
4.1 效能測試硬體架構說明 38
4.2 效能測試軟體架構及六種虛擬機器實作測試架構圖 39
4.3 效能測試程式及監控流程說明 43
4.4 效測及監控架構應用說明 46
4.5 影響變數取樣及誤差值 48
4.6 壓測過程遵守原則及注意事項 49

第五章 各種同步架構及組合方式評比 51
5.1 架構 A，單一MySQL資料庫伺服器 52
5.2 架構 B，單一主資料庫加單一副資料庫架構 53
5.3 架構 C，樹狀同步、單一主資料庫加多台副資料庫架構 54
5.4 架構 D，樹狀同步、主資料庫加多台副資料庫分佈於多階層架構 55
5.5 架構 E，鏈狀同步、雙主資料庫架構 56
5.6 架構 F，鏈狀同步：兩台以上多台主資料庫同步架構 57
5.7 其他混合架構之組合及注意事項 61

第六章 研究成果 68
6.1 虛擬機器解決方案 68
6.2 MySQL資料庫之同步架構說明及分析 68
6.3 MySQL資料庫之樹狀同步測試實作 68
6.4 MySQL資料庫之鏈狀同步測試實作 69

第七章 未來展望 71
7.1 本文相關實作未包含但後續尚可改進的部份 71
7.2 已知MySQL 研發團隊努力方向 71
7.3 其它 72

參考文獻 73


附錄1 MySQL 資料庫同步細節說明 75
1.1 開放原始碼 MySQL 資料庫同步過程步驟說明及版本演化改進
1.2 MySQL 同步的設定步驟及設定檔相關內容
1.3 同步的三個程序，運作中顯示狀態的說明
1.4 同步架構運作需注意事項
1.5 MySQL 樹狀同步架構中 auto_increment 的設定

附錄2 同步架構中前端程式之配合事項 110
2.1 手動設定程式的IP連線設定檔區分資料庫為主資料庫或副資料庫
2.2 用程式區分資料庫為主資料庫或副資料庫
2.3 將程式以功能導向區分

附錄3 實作同步架構測試之程式碼架構 114

[01] 2008 Web Server Survey, Netcraft Ltd. http://news.netcraft.com/
[02] Alexander,MySQL Enterprise Solutions, page 204, Wiley, 2003
[03] Businesswire.com, http://www.businesswire.com/portal/site/google/index.jsp?ndmViewId=news_view&newsId=20080116005349&newsLang=en
[04] Chester Gnull and Laverta Voyd, Tech Tips with Gnull and Voyd, February 1st, 2007 Linux Journal, Recover a dropped MySQL table and save partition images.
http://www.linuxjournal.com/article/9488
[05] Comparison of Statement-Based and Row-Based Replication, http://dev.mysql.com/doc/refman/6.0/en/replication-sbr-rbr.html
[06] David Axmark, http://en.wikipedia.org/wiki/David_Axmark
[07] Glyn Moody, Linux Journal, An Interview with Marten Mickos of MySQL, January 1st, 2007 , http://www.linuxjournal.com/article/9224
[08] Implementation of Multi-Versioning, http://dev.mysql.com/doc/refman/5.1-maria/en/innodb-multi-versioning.html
[09] Jeremy Zawodny, High Performance MySQL,Page 140, 141, O''Reilly, 2004
[10] M. D. Giacomo , D. Chote, G. Harrison, Lessons Learned in Building a Highly Scalable MySQL Database Presentation,
http://en.oreilly.com/mysql2008/public/schedule/detail/625
[11] Michael Kofler,Apress.The Definitive Guide to MySQL 5, Page 382, Apress 2006
[12] M. Kruckenberg and J. Pipes,Pro MySQL, page 614, Apress 2005
[13] MySQL AB, MySQL® Administrator''s Guide and Language Reference, Chapter 5.4. Replication Implementation Details, MySQL Press, 2006
[14] MySQL on Amazon EC2, http://blog.apokalyptik.com/2006/09/05/mysql-on-amazon-ec2-my-thoughts/
[15] MyTOP in details,
http://www.opensourcetutorials.com/tutorials/Server-Side-Coding/Administration/mytop/page1.html
[16] Phil Hildebrand , Applied Partitioning and Scaling Your Database System Presentation, Santa Clara Convention Center in Santa Clara, California. MySQL conference, 2008
[17] P. McCullagh, Scalable BLOB Streaming Infrastructure for MySQL
http://pbxt.blogspot.com/ , http://blobstreaming.org/
[18] PrimeBase technologies, PrimeBase XT Storage Engine for MySQL
http://www.primebase.org/
[19] Robert Hodges, CTO of Continuent, Continuent Tungsten: Proxies on Steroids for HA and Performance!, http://en.oreilly.com/mysql2008/public/schedule/detail/2582，http://www.continuent.org/HomePage。
[20] Robin Schumacher, Director of Product Management MySQL AB,
Using MySQL Replication for Scale-Out and High Availability(white paper).
http://www.mysql.com/news-and-events/on-demand-webinars/replication-20070208.php。
[21] rsync, Fast incremental file transfer tool。
http://samba.anu.edu.au/rsync/。
[22] Sasha Pachev, Understanding MySQL Internals, page214, O’Reilly, 2007
[23] S Drake, W Hu, D. M. McInnis, M. Skold, L. Thalmann, M. Tikkanen, O. Torbjornsen, and A. Wolski, Architecture of Highly Available Databases, ISAS 2004, LNCS 3335,© Springer-Verlag Berlin Heidelberg 2005。
[24] S. Sahri,Design of a Scalable Distributed Database System:SD-SQL Server,0-7803-9521-2/06, IEEE。
[25] T. Groothuyse, S. Sivasubramanian, G. Pierre, Globetp: template-based database replication for scalable web applications May 2007 ,WWW ''07: Proceedings of the 16th international conference on World Wide Web , Publisher: ACM。
[26] The BLOB and TEXT Types, MySQL AB,
http://dev.mysql.com/doc/refman/5.0/en/blob.html。
[27] Unison, File-synchronization (bi-directional reconciliation) tool。
http://www.myoops.org/twocw/mit/Electrical-Engineering-and-Computer-Science/6-033Spring-2005/Assignments/detail/pset34.htm
　http://www.cis.upenn.edu/~bcpierce/unison/。
[28] Y. Lin, B. Kemme,P. M. Marta, J. P. Ricardo , Applying database replication to multi-player online games ,October 2006 NetGames ''06: Proceedings of 5th ACM SIGCOMM workshop on Network and system support for games ,Publisher: ACM。