A Framework for the Automatic Physical Configuration and Tuning of a Mysql Community Server

Manual physical configuration and tuning of database servers, is a complicated task requiring a high level of expertise. Database administrators must consider numerous possibilities, to determine a candidate configuration for implementation. In recent times database vendors have responded to this problem, providing solutions which can automatically configure and tune their products. Poor configuration choices, resulting in performance degradation commonplace in manual configurations, have been significantly reduced in these solutions. However, no such solution exists for MySQL Community Server. This thesis, proposes a novel framework for automatically tuning a MySQL Community Server. A first iteration of the framework has been built and is presented in this paper together with its performance measurements

Development of novel software tools and methods for investigating the significance of overlapping transcription factor genomic interactions

Identifying overlapping DNA binding patterns of different transcription factors is a major objective of genomic studies, but existing methods to archive large numbers of datasets in a personalised database lack sophistication and utility. To address this need, various database systems were benchmarked and a tool BiSA (Binding Sites Analyser) was developed for archiving of genomic regions and easy identification of overlap with or proximity to other regions of interest. BiSA can also calculate statistical significance of overlapping regions and can also identify genes located near binding regions of interest or genomic features near a gene or locus of interest. BiSA was populated with >1000 datasets from previously published genomic studies describing transcription factor binding sites and histone modifications. Using BiSA, the relationships between binding sites for a range of transcription factors were analysed and a number of statistically significant relationships were identified. This included an extensive comparison of estrogen receptor alpha (ERα) and progesterone receptor (PR) in breast cancer cells, which revealed a statistically significant functional relationship at a subset of sites. In summary, the BiSA comprehensive knowledge base contains publicly available datasets describing transcription factor binding sites and epigenetic modification and provides an easy graphical interface to biologists for advanced analysis of genomic interactions

Physical tuning techniques in two DBMSs

Are there any differences in the performance between tuning techniques in MySQL and PostgreSQL? What tuning techniques do they support? Do the tuning techniques actually improve the performance? It should be easy to answer these questions, but the fact is that it is not. There is a wide range of benchmark tests available that compare DBMSs, but the focus tends to be more on the general overall performance than on the specific performance of tuning techniques that can be implemented on these databases. The goal of this study was to compare different tuning techniques between Open Source Databases and also investigate if there were any differences between the databases in the way they managed Binary Large Object data. The research was limited to comparing the two Open Source Databases MySQL and PostgreSQL against each other. The research problem was: "are there any significant differences in the untuned and tuned performance of queries between MySQL and PostgreSQL?" The results showed that there was a significant difference between MySQL and PostgreSQL with regards to indexes, BLOB management and denormalisation. Looking at the overall performance of the two DBMSs, PostgreSQL was also significantly faster than MySQL.UnpublishedBalmin, A. et al. 2005. Storing and querying XML data using denormalized relational databases, The International Journal on Very Large Data Bases, Volume 14, Issue 1, Pages: 30 - 49. Bayer, R. 1971. Binary B-Trees for Virtual Memory, ACM-SIGFIDET Workshop 1971, San Diego, California, Session 5B, p. 219-235. Bayer, R. et al. 1972. Organization and Maintenance of Large Ordered Indexes. Acta Informatica 1, 173-189. Bock, D. et al. 2002. Denormalization Guidelines for Base and Transaction Tables, ACM SIGCSE Bulletin, Volume 34, Issue 4, Pages: 129 - 133. Codd, E. F. 1970. A Relational Model of Data for Large Shared Data Banks, CACM 13, No. 6, p.377-387. Corner, D. 1979. The Ubiquitous B-Tree, Computer Surveys, Volume 11, No. 2. Cooper, M. 2005. An in-depth exploration of the art of shell scripting, Advanced Bash-Scripting Guide. http://www.tldp.org/LDP/abs/html/ Last accessed: 30 September 2005. Database Journal. The Knowledge Center for Database Professionals. 2003. PostgreSQL vs MySQL: Which is better? http://www.databasejournal.com/fea ures/mysql/article.php/3288951 Last accessed: 9 May 2005. Date, C. J. 2004. An Introduction to Database Systems, Eighth Edition. Pearson Education. Date, C. J. 2003. Edgar F. Codd: a tribute and personal memoir, ACM SIGMOD Record, Volume 32, Issue 4, Pages: 4 - 13. Date, C. J. 1998. The Birth of the Relational Model - Thirty Years of Relational, Intelligent Enterprise Magazine, Volume 1, No. 1. DevX.com. 2004, PostgreSQL vs. MySQL vs. Commercial Databases: Its All About What You Need. http://www.devx.com/dbzone/Article/20743 Last accessed: 9 May 2005. Fedora Project. http://fedora.redhat.com/ Last accessed: 30 September 2005. Gillette et al. 1995. Physical Database Design for Sybase SQL Server, Englewood Cliffs, NJ: Prentice-Hall Publishing. King, T. et al. 2002. Managing and Using MySQL, 2nd Edition. O'Reilly and Associates, Inc. Leedy, P. D. et al. 2001. Practical Research: Planning and Design, Eighth Edition. Upper Saddle River, N.J. Merrill Prentice Hall. Lehman, T. J. et al. 1986. A Study of Index Structures for Main Memory Database Management Systems, Proceedings of the Twelfth International Conference on Very Large Data Bases, Pages: 294 - 303. LinuxCommand.org. Man Pages. Time. http://www.linuxcommand.org/man_pages/ timel.html Last accessed: 30 September 2005. MySQL website. http://www.mysql.co Last accessed: 9 May 2005. MySQL Reference Manual. http://dev.mysql.com/doc/mysql/en/ Last accessed: 3 October 2005. Ooi, B. C. et al. 2001. B-trees: Bearing Fruits of All Kinds, ACM International Conference Proceeding Series, Proceedings of the thirteenth Australasian conference on Database technologies - Volume 5, Pages: 13 - 20. Gennick, J. ANSI Standard SQL Joins, Oracle.com. http://www.oracle.com/oramag/oracle/01-nov/o61sql.html Last accessed: 30 September 2005. Paulson, L. D. 2004. Open Source Databases Move into the Marketplace. IEEE Computer, Volume 37, Issue 7, p. 13 - 15 PostgreSQL website. 2005. http://www.postgresqlorg Last accessed: 3 October 2005. PostgreSQL Documentation. 2005. http://www.postgresql.org/docs/ Last accessed: 3 October 2005. PostgreSQL Documentation - References - I. SQL Commands. Create Table. http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html Last accessed: 30 September 2005. Sanders, G. L. et al. 2001. Denormalization Effects on Performance of RDBMS. Proceedings of the 34th Hawaii International Conference on System Sciences, Volume 3, p.3013, January 03-06. Schkolnick, M. et al. 1982. The Effect of Denormalisation on Database Performance. The Australian Computer Journal, Volume 14, No. 1. Severance, D. et al. 1972. Performance evaluation of file organizations through modelling, Proceedings of the ACM Annual Conference, pp. 1061-1072. Silberschatz, A. et al. 2002. Database System Concepts, 4th edition, International Edition. McGraw-Hill. SPSS Inc. http://www.spss.com/ Last accessed: 1 October 2005. The Dada Engine http://dev.null.org/dadaengine/ Last accessed: 30 September 2005. Transaction Processing Performance Council website. http://www.tcp.org Last accessed: 9 May 2005. Transaction Processing Performance Council. 2005. TPC BENCHMARKtrn C, Standard Specification, Revision 5.4. No author listed. www.tpc.org/tpcc/spec/tpcc_current.pdf Last accessed: 14 June 2005. Widenius, M. et al. 1999. MySQL Introduction. Linux Journal, Volume 1999, Issue 67, Article No. 5. Wikipedia, the free encyclopedia. http://en.wikipedia.org/ Last accessed: 9 May 2005. Wikipedia, the free encyclopedia. The Program SPSS. http://en.wikipedia.org/wiki/SPSS Last accessed: 1 October 2005