My SQL Storage Engine Overview Dr Charles A

Скачать презентацию My SQL Storage Engine Overview Dr Charles A

c9127a7b7c19480473003b1bc020eeeb.ppt

Количество слайдов: 32

My. SQL AB • • Founded in 1995 Operations in 22 countries 10+ million product installations 50, 000 downloads each day Dramatically reduces TCO of database management Bundled by more than 100 SW and HW companies Sold by partners such as HP, Dell, Novell, and others Copyright 2006 My. SQL AB The World’s Most Popular Open Source Database 3

Chosen by Successful, Modern Companies • Embedded – – ”Batteries included” database in software applications Network elements • High Volume Web Sites – – Dynamic content e. Commerce Gaming & entertainment Scale Out • Enterprise – – – Data Warehousing High-Volume Transactions Departmental Intranet/Extranet Scale Out Copyright 2006 My. SQL AB The World’s Most Popular Open Source Database 4

My. SQL Software Priorities Performance My. SQL, Sun and BEA Web. Logic Set New World Records for Speed & Price/Performance in SPEC Benchmarks Reliability Ease of Use 15 Minute Rule Study found comparatively fewer bugs in My. SQL* Up and running in 15 minutes Lower TCO * Robert Lemos CNET News. com Feb 4, 2005 Copyright 2006 My. SQL AB The World’s Most Popular Open Source Database 5

Second Generation Open Source • My. SQL AB is a profitable company – Develops the software in-house; community helps test it – Owns source code, copyrights and trademarks – Targets the “commoditized” market for databases • “Quid Pro Quo” dual licensing for OEM market – Cost-effective commercial licenses for commercial use – Open source GPL license for open source projects • Annual My. SQL Network subscription for Enterprise, Web and OEM development/testing – Per server annual subscription – Includes support, alert and update advisors, Knowledge Base, Certified/Optimized Binaries • My. SQL Support – Worldwide 24 x 7 support – Training and certification – Consulting Copyright 2006 My. SQL AB “Reasoning's inspection study shows that the code quality of My. SQL was six times better than that of comparable proprietary code. ” The World’s Most Popular Open Source Database 6

Popular Technology Stacks LAMP J 2 EE . NET Java . net / C# Perl My. SQL Apache Linux Copyright 2006 My. SQL AB Apache Tomcat JBoss Linux or Solaris My. SQL IIS Apache JBoss Windows The World’s Most Popular Open Source Database 7

Supported Technology Platforms Programming Languages Perl … Database Web & Application Server Internet Information Server Apache Operating System HP-UX Hardware Storage Copyright 2006 My. SQL AB Tomcat … … The World’s Most Popular Open Source Database 8

Pluggable Storage Engine Architecture • My. SQL supports several storage engines that act as handlers for different table types. • Choose, create, or extend a storage engine that best suits your applications unique requirements. • What is most important to you? - Read Intensive - OLTP - Transactions - Performance - Scalability - Level of Concurrency - Indexes Types - Storage Utilization - High Availability Copyright 2006 My. SQL AB - Replication - Online Backups - Data Warehousing - Foreign Keys - Small Footprint - Row Level Locking - Embedded - Table Level Locking - Clustering The World’s Most Popular Open Source Database 10

Pluggable Storage Engine Architecture • Storage Engines are available on a per table basis • Changing from one storage engine to another can be done via a simple SQL command: ALTER TABLE mytable ENGINE=My. ISAM; Innodb Copyright 2006 My. SQL AB My. ISAM The World’s Most Popular Open Source Database 11

Pluggable Storage Engine Architecture * * In My. SQL 5. 0 transactions are supported, however, the partial rollback of a transaction is not supported. Cluster supports the READ_COMMITTED, REPEATABLE_READ, and SERIALIZABLE transaction isolation levels. Copyright 2006 My. SQL AB The World’s Most Popular Open Source Database 12

Storage Engines – My. ISAM Fast Facts • • • Default My. SQL engine No practical limits on data storage Very efficient storage Easily handles high-speed data loads Has B-tree, R-tree, and Full-text Indexes Supported by special index memory caches Offers compressed data option Supports geospatial operations Uses table level locks Does not do transactions Backup/point-in-time recovery supported My. SQL Server Best Use Cases • High-traffic Web sites • Data warehouses Copyright 2006 My. SQL AB My. ISAM The World’s Most Popular Open Source Database 13

Storage Engines – Inno. DB Fast Facts • • • Provides ACID transaction support 64 TB data storage limit per tablespace Higher storage cost Slower data load speed than most other engines Offers MVCC/Snapshot read Has B-tree and clustered indexes Supported by special data & index memory caches Provides foreign key support Does not offer compressed data option Uses row level locks and has custom isolation levels Has crash recovery Backup/point-in-time recovery supported Best Use Cases • Online transaction processing applications Copyright 2006 My. SQL AB My. SQL Server Innodb The World’s Most Popular Open Source Database 14

Storage Engines – Cluster (NDB) Fast Facts • • • Transaction support All data and index reside in main memory Memory limitation removed for tables in 5. 1 High data load speed Offers MVCC/Snapshot read Has B-tree indexes Very fast primary key lookup capabilities Offers 99. 999% uptime Shared nothing architecture Has high-speed API for access as well as SQL API Online backup/point-in-time recovery supported My. SQL Server Best Use Cases • Highly available, always-on/up applications • Fast directory/key lookup applications Copyright 2006 My. SQL AB Cluster (NDB) The World’s Most Popular Open Source Database 15

Storage Engines – Archive Fast Facts • • • New in 5. 0 Provides automatic data compression Offers storage savings up to 80% No practical storage limit Fastest data load speed of any storage engine Offers MVCC/Snapshot read No index support Has special insert buffer for fast insert speed Only supports INSERT and SELECT operations Uses row level locks Backup/point-in-time recovery supported My. SQL Server Best Use Cases • Historical data warehouses • Data archiving applications • Data auditing Copyright 2006 My. SQL AB Archive The World’s Most Popular Open Source Database 16

Storage Engines – Federated Fast Facts • • New in 5. 0 Allows creation of one logical database from many physical Acts as “pointer” from one database to another target object No special middleware needed for remote data access Speed of operations depends on network/misc. factors Actions constrained by target engine object properties Security handled through federated table definition All SQL operations supported (as per target object) My. SQL Server Best Use Cases • Distributed database environments • Data Mart environments Federated Copyright 2006 My. SQL AB The World’s Most Popular Open Source Database 17

Storage Engines – Other Fast Facts • • • Memory tables reside in RAM; data is lost on shutdown Memory tables support both B-tree and hash indexes BDB tables offer transaction support with COMMIT/ROLLBACK Merge tables are collections of underlying My. ISAM tables Merge tables offer one form of data partitioning Custom storage engines may be plugged into My. SQL Server Best Use Cases • • Memory: fast lookups for data objects BDB: Online transaction processing Merge: Large databases with partitioned data Custom: special application situations Memory Merge Copyright 2006 My. SQL AB BDB Custom The World’s Most Popular Open Source Database 18

Storage Engines – Coming Soon New Transactional Storage Engines: • Maria • Falcon • others still… My. SQL Server Memory Merge Copyright 2006 My. SQL AB BDB Custom The World’s Most Popular Open Source Database 19

My. SQL Architecture – Parser/Optimizer Parser Optimizer Query Translation, Object Privilege Access Paths, Statistics • Validates user’s privileges on accessing database objects and executing SQL calls. • Converts all SQL calls to internal database language. • Decides how best to service user’s SQL request. • Supports all storage engines, which means no special coding per storage engine for particular needs. Copyright 2006 My. SQL AB The World’s Most Popular Open Source Database 20

Query Trees Alternative‘query tree’ for the query to list all the managers that work in the sales department: (job = ‘Manager’) Ù (name=‘Sales’) (EMP emp. deptno = deptno DEPT) (job = ‘Manager’) Ù (name=‘Sales’) emp. deptno = deptno EMP Copyright 2006 My. SQL AB DEPT The World’s Most Popular Open Source Database 23

Query Trees • Tree nodes are atomic operations – – Project Restrict Join Sort • Each node has 0 -2 inputs – From 0 -2 relations directly – From 0 -2 children Copyright 2006 My. SQL AB The World’s Most Popular Open Source Database 24

Optimization Techniques • Cost-Based – Statistics, statistics • Manual offline computation • Piggy back – Strategies • Even distribution • Heuristic – Knowledge of operations – “Works Best” • Semantic – Knowledge of schema – Decisions based on behavior/relationships Copyright 2006 My. SQL AB The World’s Most Popular Open Source Database 26

Heuristic Optimization Example s(job = ‘Manager’) (name=‘Sales’) (emp. deptno = deptno) s(job = ‘Manager’) (name=‘Sales’) emp. deptno = deptno X EMP DEPT emp. deptno = deptno s(job = ‘Manager’) EMP Copyright 2006 My. SQL AB s(name=‘Sales’) Optimised Canonical Query DEPT The World’s Most Popular Open Source Database 27

Heuristic Algorithm void Query. Tree: : HOptimization() { Split. Restrict. With. Join(Root); Split. Project. With. Join(Root); Split. Restrict. With. Project(Root); p. Node = Find. Restriction(Root); while (p. Node != 0) { Push. Restrictions(Root, p. Node); n. Node = Find. Restriction(Root); } p. Node = Find. Projection(Root); while (p. Node != 0) { Push. Projections(Root, p. Node); n. Node = Find. Projection(Root); } p. Node = Find. Natural. Join(Root); while (p. Node != 0) { Push. Natural. Joins(Root, p. Node); n. Node = Find. Natural. Join(Root); } Balance. Joins(Root); Prune. Tree(0, Root); } Copyright 2006 My. SQL AB The World’s Most Popular Open Source Database 28

Optimization Using Query Trees SELECT P. Name FROM Professor P, Teaching T WHERE P. Id = T. Prof. Id AND P. Dept. Id = ‘CS’ AND T. Semester = ‘F 1994’ Name( Dept. Id=‘CS’ Semester=‘F 1994’(Professor Id=Prof. Id Teaching)) Teaching Name Dept. Id=‘CS’ Semester=‘F 1994’ Id=Prof. Id Professor Copyright 2006 My. SQL AB Teaching The World’s Most Popular Open Source Database 29

Query Optimizer • Heuristic optimization eliminates most inefficiencies and generates near optimal queries which can be executed directly from the internal representation (query tree) without modification. • Test – Replace SELECT-PROJECT-JOIN optimizer in My. SQL with Heuristic Query Optimizer. – To find out the results you need to… Copyright 2006 My. SQL AB The World’s Most Popular Open Source Database 30

Buy my book! Expert My. SQL Since the enormous number of new features made available with My. SQL release 5. 0, My. SQL has been gaining steam as a viable alternative to database behemoths like Oracle and IBM DB 2. My. SQL users now have the ability to extend My. SQL with new SQL commands, optimize query execution, and embed My. SQL within low-resource environments like embedded devices and kiosks. Expert My. SQL, by Dr. Charles A. Bell, is the first book to examine these opportunities in detail, showing you how to wield maximum control over this powerful open source database. You’ll learn how to create your own custom storage handlers, ensuring maximum flexibility and speed within your specialized applications. You’ll also gain valuable insight into My. SQL’s architecture and learn how to tweak its behavior through custom changes to the source code. Copyright 2006 My. SQL AB The World’s Most Popular Open Source Database 31