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