Advanced Topics in Databases

1. Advanced Topics in Databases Hadi Amiri Abolfazl AleAhmad Summer 1385

2. Content • Why Main Memory Databases? • Performance Evaluation of MMDB: • PERST DB • TPC Performance Benchmark • Implementation • Experimental Results • MARS MMDBMS • Conclusion • References

3. About Main Memory Databases • Main Memory Databases (MMDB) =The idea of having entire databases reside in main memory. •  In a Main Memory DBMS (MMDBMS) there is no central role for I/O management. •  MMDB will use physical memory as primary storage and a disk subsystem for backup. • Motivations: • Requirement of short Access/Response time • Telecommunication applications • Applications handling high traffic of data, e.g. Router. • Real-Time applications

4. About Main Memory Databases (Cont.) • Traditional database systems : rely on the disk subsystem to retrieve and update data and use an offline storage device such as magnetic tape for backup • Advantages to the use of MMDBs: • The memory resident databases can achieve significant performance improvements over conventional database systems • by eliminating the need for I/O to perform database applications. • processing time and throughput rates should improve due to the elimination of I/O overhead.

5. Our Project: Performance Evaluation of MMDB: • PERST DB: • a highly efficient main memory database system with realtime capabilities and convenient C# interface • optimized for applications with dominated read access pattern • supports transactions, online backup and automatic recovery after system crash • it is also possible to use it with databases, the size of which exceeds the size of the physical memory in the system

6. Our Project: Performance Evaluation of MMDB: • PERST (Cont.): • Database tables are constructed using information about application classes • A SQL-like query language is used to specify queries • Table rows are considered as object instances and the table is the class of these objects

7. Our Project: Performance Evaluation of MMDB: • TPC Performance Benchmark: • defined in 1989… • The benchmark simulates a typical bank application by a single type of transaction that models cash withdrawal and deposit at a bank teller • The transaction updates several relations such as the bank balance and the customer’s balance • The benchmark also incorporates communication with terminals • to model the end to end performance of the system realistically.

8. Our Project: Performance Evaluation of MMDB: • Implementation: • We implemented another module in conjunction with PERST modules, this module • first creates the both data bases, • then allows user to fill up them, • and then to run some “manipulations”, • Visual C# .NET interface in conjunction with PERST • Evaluation queries including select, insert, update and delete for variable number of table records and updates both for main memory and MS-SQL

9. Our Project: Performance Evaluation of MMDB: • Implementation (Cont.) : • recorded time is calculated from the beginning of an “existing check”–which checks whether the produced random source/destination account/branch exists or not -to the end of the query execution • Selective query is designed so that it selects for half of the branches • Performed on • a Pentium 2.8 GHz system • with Windows XP as operating system • and 512 MB of RAM

10. Experimental Results • we implemented to create the following result diagrams : • Insert time measurements • Update time measurements • Query time measurement

11. Experimental Results :Insert time measurements • PERST insertion time is about 10000 times less than MS-SQL’s

12. Experimental Results :Update time measurements • PERST has approximately a steady response time • although MS-SQL reaches a final steady status too, but its performance time is much longer than PERST’s

13. Experimental Results :Query time measurements • PERST has a more steady state than MS-SQL, and the main important difference is the noticeable ratio of the performance times

14. PerstDB Performance

15. FastDB Performance

16. مقایسه میزان استفاده از پردازنده در FastDBو PERST PERST Scale60 FastDB Scale30

17. Conclusion • Our results of the evaluation tests match completely with what we expected. • PERST is an object-oriented database, and is optimized for an application with dominated read access pattern. • Our performance results indicate that : • elimination of the overhead time caused by transferring database files to the buffer pool and vice versa, makes PERST work significantly faster than a traditional database,

18. Conclusion (Cont.) • MMDB fits more efficiently to the requirements of today’s real time applications • Main Memory has short response time, and its decreasing cost makes it affordable and suitable for real-time applications. • Main Memory DBMS manages in-memory data and ensures ACID properties on it.

19. Case Study MARS MMDB System

20. MARS MMDB SYSTEM • MARS (MAin memory Recoverable database with Stable log) • is the main memory database system that. • It assumes that the entire database resides in a volatile main memory (MM) • its backup copy is kept in an archive memory (AM) residing on secondary storage.

21. MARS MMDB SYSTEM • At least 20,000 transactions are executed The size of a transaction is determined by the numberof operations it executes, which is distributed uniformly between 5 and 30concurrency locking: 2phase locking on the memory pages

22. Immediate و Deferred Update • With immediate update, modified pages may be propagated to the primary database at any time. Hence, we need to make failure recovery possible. • With deferred update, modified data is kept in the log until a successful completion of the transaction performing the updates is assured. Since no dirty pages are propagated to disks, only AFIMs need to be logged for REDO purposes

23. System Resource DYNAMIC PARAMETERS

24. System ResourceSTATIC PARAMETERS AND etc ….

25. Before CrashTransaction arrival rate vs. transaction response time

26. Recovery Overhead

27. Abort Situation

28. References (Cont.) • Tobin J. Lehman, Michael J. Carey, “A Study of Index Structures for Main Memory Database Management Systems”, Proceedings of the Twelfth International Conference on Very Large Data Bases, Kyoto, August, 1986 • Rajeev Rastogi, S. Seshadri, Philip Bohannon, Dennis Leinbaugh, “Logical and Physical Versioning in Main Memory Databases”, Proceedings of the 23rd VLDB Conference, Athens, Greece, 1997 • Hector Garcia-Molina, Kenneth Salem, “Main Memory Database Systems: An overview”, IEEE 1992 • Piyush Burte, Boanerges Aleman-Meza, D. Brent Weatherly, Rong Wu, “Transaction Management for a Main-Memory Database” • Tobin J. Lehman, Michael J. Carey, “Query Processing in Main Memory Database Management Systems”, ACM, 1986 • Margaret H. Eich, “Main Memory Database Recovery”, IEEE 1986 - J. Baulier, P. Bohannon, S. Gogate, C. Gupta, S. Haldar, S. Joshi, A. Khivesera, H. F. Korth, P. McIlroy, J. Miller, P. P. S. Narayan, M. Nemeth, R. Rastogi, S. Seshadri, A. Silberschatz, S. Sudarshan, M. Wilder, and C. Wei. “DataBlitz Storage Manager: Main-Memory Database Performance for Critical applications”, In Proceedings of theACM SIGMOD International Conference on Management of Data (SIGMOD), pages 519–520, Philadephia, PA, USA, June 1999.

29. References • PERST: a main-memory database object-orientd database system, http://www.mcobject.com/ , august 2006. • Inseon Lee, Heon Y.Yeon, Taesoon Park, “A New Approach for Distributed Main Memory Database Systems: A Causal Commit Protocol”, IEICE TRANS. INF. & SYST., VOL.ES7, NO.1 JANUARY 2004 • Nicholas Carriero, Michael V. Osier, Kei-Hoi Cheung, Peter Masiar, Perry L. Miller, Kevin White, Martin Schultz, “ Exploring the Use of Main Memory Database (MMDB) Technology for the Analysis of Gene Expression Microarray Data”, Technical report, April 2004 • Stefan Manegold, “Understanding, Modeling, and Improving Main-Memory Database Performance”, November 2002 • Philip Bohannon, Peter McIlroy, Rajeev Rastogi,”Main Memory Index Structures with FixedSize Partial Keys”, ACM SIGMOD 2001 May 2124,Santa Barbara, California, USA • S. Manegold, P. A. Boncz, and M. L. Kersten. “Optimizing Main-Memory Join on Modern Hardware”, IEEE Transactions on Knowledge and Data Engineering (TKDE), 14(4):709–730, July 2002. - J. Rao and K. A. Ross. “Making B+-Trees Cache Conscious in Main Memory”, In Proceedings of the ACM SIGMOD International Conference on Management of Data (SIGMOD), pages 475–486, Dallas, TX, USA, May 2000.