Sunday, September 19, 2010

JavaOne 2010/MySQL Sunday: You Know Databases, So How Hard Can MySQL Be?

Sarah Novotny presented You Know Databases, So How Hard Can MySQL Be? at MySQL Sunday.  This is my summary of that presentation.  As I stated in my summary of Ronald Bradford's MySQL Sunday presentation MySQL Idiosyncrasies that Bite, I'm far from a MySQL expert and any errors in this summary are likely mine in interpretation.

There are terminology differences between Oracle and MySQL that can cause confusion.  There are not always direct translations between Oracle and MySQL terms and concepts.  For example, Oracle has a database, instance, and schema, but MySQL treats all three of these concepts as database.  Notvotny described approximate MySQL equivalents to Oracle's System Global Area (SGA) and User Global Area (UGA).

Novotny pointed out several tips to make using MySQL easier. She said to flush privileges and use minimal privileges. Like Ronald Bradford, Sarah Novotny generally recommended using the InnoDB storage engine rather than the MyISAM storage engine because InnoDB is more likely to do what people familiar with Oracle expect. Novotny stated that InnoDB employs row-level locking while MyISAM employs table-level locking.  An interesting read related to these two storage engines is Should you move from MyISAM to InnoDB?

Novotny inserted several "short diversions" into her presentation.  One of these was to recommend the Second Edition of High Performance MySQL. She emphasized that the first edition should now be avoided because it is very out of date unless using MySQL 4.  I also enjoyed the fact that a developer was used as an example of accidental exploitation of MySQL vulnerabilities when a DBA doesn't set up the database security/privileges/permissions appropriately.  That theme was 2 for 2 in the MySQL presentations I attended today.

Novotny recommended several free tools including Innotop, Maatkit, MySQL proxy, and cacti templates.  Her recommended additional resources include irc.freenode.org (#mysql and #maatkit), mysql.com, and the previously mentioned book High Performance MySQL (Second Edition). The slides for this presentation are available online at http://www.slideshare.net/sarahnovotny/you-know-databases-how-hard-can-mysql-be.

No comments: