MySQL has a number of global buffers, i.e. your SGA. There are also a number of per session/thread buffers that combined with other memory usage constitutes an unbounded PGA. One of the most common errors in mis-configured MySQL environments is the setting of the 4 primary per session buffers thinking they are global buffers.

Global buffers include:

• • key_buffer_size – For MyISAM Indexes (note you can define multiple key_buffer’s The MyISAM Key Cache)
  • innodb_buffer_pool_size – For Innodb …

MySQL by default is a NON transactional database. For the hobbyist (See The Hobbyist and the Professional), startup entrepreneur and website developer this may not appear foreign, however to the seasoned Oracle DBA who has only used Oracle the concept is very foreign.

In MySQL you have to be concerned with two situations that will catch the unprepared out. The first is the default autocommit mode. This is TRUE, i.e. all statements are automatically committed on completion.

mysql> SELECT @@autocommit,TRUE;
+--------------+------+
| @@autocommit | TRUE |
+--------------+------+
|            1 |    1 |
+--------------+------+
1 row in set (0.00 sec)

The second is the storage engine used. Again a foreign term for Oracle DBA’s, a storage engine is a technology that stores and retrieves the underlying data from the …

In Oracle the default transaction isolation is READ_COMMITTED. In MySQL the default is REPEATABLE_READ. Because MySQL also has READ_COMMITTED I have seen in more then one production MySQL environment a transaction isolation of READ_COMMITTED. The explanation and ultimately incorrect assumption is the default in Oracle is READ_COMMITTED so we made that the default in MySQL.

I’m not going to discuss the specific differences of these isolation levels (see reference lines below) except to say it that READ_COMMITTED in Oracle more closely relates to the MySQL default of REPEATABLE_READ and not READ_COMMITTED. Just because the same term for a common feature exists, don’t assume the underlying functionality is the same or that either or both actually conform to the SQL ANSI standard.

While switching your MySQL environment to READ_COMMITTED is possible, there is still conjucture if this actually provides any performance improvement. …

As part of my MySQLCamp for the Oracle DBA series of talks to help the Oracle DBA understand, use and appreciate MySQL I have also developed a series of short interesting posts I have termed “Don’t Assume”. Many of these are re-occurring points during my consulting experiences as I observe Oracle DBA’s using MySQL. I am putting the finishing touches to my MySQL for the Oracle DBA series of talks and I’m excited to highlight some of the subtle differences and unique characteristics of MySQL RDBMS in comparison to Oracle and some extent other products including SQL Server.

Stay tuned for more soon.

I will be presenting at the MySQL Users Conference 2010 in Santa Clara, April 2010 two presentations from this series, …