by ronald
I recently gave a webinar to the LAOUC and NZOUG user groups on MySQL Idiosyncrasies that BITE.
For the benefit of many viewers that do not use English as a first language my slides include some additional information from my ODTUG Kaleidoscope presentation in June.
Thanks to Francisco Munoz Alvarez for organizing.
by ronald
I have uploaded my three presentations from the 2010 MySQL Users Conference in Santa Clara, California which was my 5th consecutive year appearing as a speaker.
A full history of my MySQL presentations can be found on the Presenting page.
by ronald
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:
The four important per session buffers are:
I have seen people see these values > 5M. The defaults range from 128K to 256K. My advice for any values above 256K is simple. What proof do you have this works better? When nothing is forthcoming, the first move is to revert to defaults or a maximum of 256K for some benchmarkable results. The primary reason for this is MySQL internally as quoted by Monty Taylor – for values > 256K, it uses mmap() instead of malloc() for memory allocation.
These are not all the per session buffers you need to be aware of. Others include thread_stack, max_allowed_packet,binlog_cache_size and most importantly max_connections.
MySQL also uses memory in other areas most noticeably in internal temporary tables and MEMORY based tables.
As I mentioned, there is no bound for the total process memory allocation for MySQL, so some incorrectly configured variables can easily blow your memory usage.
“Don’t Assume” is a series of posts to help the Oracle DBA understand, use and appreciate the subtle differences and unique characteristics of the MySQL RDBMS in comparison to Oracle. These points as essential to operate MySQL effectively in a production environment and avoid any loss of data or availability.
For more posts in this series be sure to follow the mysql4oracledba tag and also watch out for MySQL for Oracle DBA presentations.
The MySQLCamp for the Oracle DBA is a series of educational talks all Oracle DBA resources should attend. Two presentations from this series IGNITION and LIFTOFF will be presented at the MySQL Users Conference 2010 in Santa Clara, April 2010 This series also includes JUMPSTART and VELOCITY. If you would like to here these presentations in your area, please contact me.
by ronald
MySQL has the same level of data integrity for numbers and strings as Oracle; when MySQL is correctly configured. By default (a reason I wish I knew why it is still the default), MySQL performs silent conversions on boundary conditions of data that will result in your data not always being what is specified. Let’s look at the following examples to demonstrate default behavior.
mysql> DROP TABLE IF EXISTS example; mysql> CREATE TABLE example(i1 TINYINT, i2 TINYINT UNSIGNED, c1 VARCHAR(5)); mysql> INSERT INTO example (i1) VALUES (1), (-1), (100), (500); Query OK, 4 rows affected, 1 warning (0.08 sec) mysql> SELECT * FROM example; +------+------+------+ | i1 | i2 | c1 | +------+------+------+ | 1 | NULL | NULL | | -1 | NULL | NULL | | 100 | NULL | NULL | | 127 | NULL | NULL | +------+------+------+ 4 rows in set (0.00 sec)
As you can see for one value we inserted 500, yet the value of 127 is stored? For this example I have used the TINYINT numeric data type to demonstrate truncation. TINYINT is a 1 byte integer that stores values from -128 to +127. Unlike Oracle, MySQL has 9 different data types for numeric columns, and using these wisely can improve your database disk footprint, for example BIGINT v INT. Is there a big deal?.
MySQL also has a nice feature for numeric data types, the UNSIGNED attribute that ensures only a positive integer or 0 value. Let’s see what happens with this column.
mysql> TRUNCATE TABLE example; mysql> INSERT INTO example (i2) VALUES (1), (-1), (100), (500); Query OK, 4 rows affected, 2 warnings (0.00 sec) mysql> SELECT * FROM example; +------+------+------+ | i1 | i2 | c1 | +------+------+------+ | NULL | 1 | NULL | | NULL | 0 | NULL | | NULL | 100 | NULL | | NULL | 255 | NULL | +------+------+------+ 4 rows in set (0.00 sec)
Now you see that -1 and 500 are now not the expected values, and before while 500 was silently truncated to 127, now it’s truncated to 255.
As you can now assume, the following also occurs for strings.
mysql> TRUNCATE TABLE example;
mysql> INSERT INTO example (c1) VALUES (NULL),('a'),('abcde'),('xyz12345');
Query OK, 4 rows affected, 1 warning (0.00 sec)
mysql> SELECT * FROM example;
+------+------+-------+
| i1 | i2 | c1 |
+------+------+-------+
| NULL | NULL | NULL |
| NULL | NULL | a |
| NULL | NULL | abcde |
| NULL | NULL | xyz12 |
+------+------+-------+
4 rows in set (0.00 sec)
As you can see here, the mysql client shows that warnings occurred, but if you don’t review the warning you would never know, a situation that is rarely reviewed with development in richer programming languages. Let us look at these actual warnings more closely.
mysql> INSERT INTO example (i1) VALUES (1), (-1), (100), (500);
Query OK, 4 rows affected, 1 warning (0.08 sec)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'i1' at row 4 |
+---------+------+---------------------------------------------+
mysql> INSERT INTO example (i2) VALUES (1), (-1), (100), (500);
Query OK, 4 rows affected, 2 warnings (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'i2' at row 2 |
| Warning | 1264 | Out of range value for column 'i2' at row 4 |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO example (c1) VALUES (NULL),('a'),('abcde'),('xyz12345');
Query OK, 4 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'c1' at row 4 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)
The solution is the sql_mode configuration variable and at minimum the value of STRICT_ALL_TABLES defined. We can demonstrate the expected behavior with the following syntax.
mysql> set SESSION sql_mode=STRICT_ALL_TABLES; Query OK, 0 rows affected (0.00 sec) mysql> TRUNCATE TABLE example; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO example (i1) VALUES (1), (-1), (100), (500); ERROR 1264 (22003): Out of range value for column 'i1' at row 4 mysql> SELECT * FROM example; +------+------+------+ | i1 | i2 | c1 | +------+------+------+ | 1 | NULL | NULL | | -1 | NULL | NULL | | 100 | NULL | NULL | +------+------+------+ 3 rows in set (0.00 sec)
As you can see, even with an error for a single INSERT statement, some data was actually stored. You should read Don’t Assume – Transactions for some insights here.
When it comes to dates, there is greater complexity and this is grounds for another entry of this series.
“Don’t Assume” is a series of posts to help the Oracle DBA understand, use and appreciate the subtle differences and unique characteristics of the MySQL RDBMS in comparison to Oracle. These points as essential to operate MySQL effectively in a production environment and avoid any loss of data or availability.
For more posts in this series be sure to follow the mysql4oracledba tag and also watch out for MySQL for Oracle DBA presentations.
The MySQLCamp for the Oracle DBA is a series of educational talks all Oracle DBA resources should attend. Two presentations from this series IGNITION and LIFTOFF will be presented at the MySQL Users Conference 2010 in Santa Clara, April 2010 This series also includes JUMPSTART and VELOCITY. If you would like to here these presentations in your area, please contact me.
by ronald
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 MySQL database. MySQL has many different storage engines, each with relative strengths and weaknesses and different features. For the purpose of this discussion it is important to know that engines are either non-transactional or transactional. The default storage engine MyISAM is NON transactional. MySQL provides by default the InnoDB storage engine which is transactional. There are distinct advantages of a non transactional environment which I will not go into at this time.
Having recently written about this in my upcoiming book Expert PHP and MySQL I will demonstrate what happens with both MyISAM and InnoDB.
To show the difference, Listing 6-7 demonstrates that atomicity is not possible with non-transactional tables. The following tables are used in this example.
DROP TABLE IF EXISTS non_trans_parent; CREATE TABLE non_trans_parent ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, val VARCHAR(10) NOT NULL, PRIMARY KEY (id), UNIQUE KEY (val) ) ENGINE=MyISAM DEFAULT CHARSET latin1; DROP TABLE IF EXISTS non_trans_child; CREATE TABLE non_trans_child ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, parent_id INT UNSIGNED NOT NULL, created TIMESTAMP NOT NULL, PRIMARY KEY (id), INDEX (parent_id) ) ENGINE=MyISAM DEFAULT CHARSET latin1;
To test things out, perform a sample transaction that inserts records into these two tables:
START TRANSACTION; INSERT INTO non_trans_parent(val) VALUES(‘a’); INSERT INTO non_trans_child(parent_id,created) VALUES(LAST_INSERT_ID(),NOW()); INSERT INTO non_trans_parent (val) VALUES(‘a’); ERROR 1062 (23000): Duplicate entry ‘a’ for key ‘val’ ROLLBACK; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +--------+------+-------------------------------------------------------------- | Level | Code | Message +--------+------+-------------------------------------------------------------- | Warning| 1196 | Some non-transactional changed tables couldn’t be rolled back +--------+------+-------------------------------------------------------------- SELECT * FROM non_trans_parent; +----+-----+ | id | val | +----+-----+ | 1 | a | +----+-----+ SELECT * FROM non_trans_child; +----+-----------+---------------------+ | id | parent_id | created | +----+-----------+---------------------+ | 1 | 1 | 2009–09–21 23:44:25 | +----+-----------+---------------------+
As you can see, data that you would have expected to not exist from the transaction is present.
Repeat these SQL statements using the transactional storage engine InnoDB; you will observe the difference between transactional and non transactional processing. The following tables, shown in Listing 6-8, are used in this example.
DROP TABLE IF EXISTS trans_parent; CREATE TABLE trans_parent ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, val VARCHAR(10) NOT NULL, PRIMARY KEY (id), UNIQUE KEY (val) ) ENGINE=InnoDB DEFAULT CHARSET latin1; DROP TABLE IF EXISTS trans_child; CREATE TABLE trans_child ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, parent_id INT UNSIGNED NOT NULL, created TIMESTAMP NOT NULL, PRIMARY KEY (id), INDEX (parent_id) ) ENGINE=InnoDB DEFAULT CHARSET latin1;
Perform a sample transaction that inserts records into these two tables:
START TRANSACTION; INSERT INTO trans_parent (val) VALUES(‘a’); INSERT INTO trans_child (parent_id,created) VALUES(LAST_INSERT_ID(),NOW()); INSERT INTO trans_parent (val) VALUES(‘a’); ERROR 1062 (23000): Duplicate entry ‘a’ for key ‘val’ ROLLBACK; Query OK, 0 rows affected (0.01 sec) SELECT * FROM trans_parent; Empty set (0.00 sec) SELECT * FROM trans_child; Empty set (0.00 sec)
As you can see, no data has been recorded as part of the failing transaction.
“Don’t Assume” is a series of posts to help the Oracle DBA understand, use and appreciate the subtle differences and unique characteristics of the MySQL RDBMS in comparison to Oracle. These points as essential to operate MySQL effectively in a production environment and avoid any loss of data or availability.
For more posts in this series be sure to follow the mysql4oracledba tag and also watch out for MySQL for Oracle DBA presentations.
The MySQLCamp for the Oracle DBA is a series of educational talks all Oracle DBA resources should attend. Two presentations from this series IGNITION and LIFTOFF will be presented at the MySQL Users Conference 2010 in Santa Clara, April 2010 This series also includes JUMPSTART and VELOCITY. If you would like to here these presentations in your area, please contact me.
by ronald
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. There are different cases of improving locking contention, in one case Heikki Tuuri the creator of InnoDB suggests READ_COMMITTED may overcome an adjacent range gap locking contention problem while in a tpcc-like benchmark a far greater number of deadlocks were detected.
I will close by stating two facts. When changing the MySQL transaction isolation from the default of REPEATABLE_READ you are using a code path that is less tested and not used as frequently to the millions of default MySQL installations, and you are also required to change the default replication format, again a code path less tested and potential a significant increase in I/O load.
“Don’t Assume” is a series of posts to help the Oracle DBA understand, use and appreciate the subtle differences and unique characteristics of the MySQL RDBMS in comparison to Oracle. These points as essential to operate MySQL effectively in a production environment and avoid any loss of data or availability.
For more posts in this series be sure to follow the mysql4oracledba tag and also watch out for MySQL for Oracle DBA presentations.
The MySQLCamp for the Oracle DBA is a series of educational talks all Oracle DBA resources should attend. Two presentations from this series IGNITION and LIFTOFF will be presented at the MySQL Users Conference 2010 in Santa Clara, April 2010 This series also includes JUMPSTART and VELOCITY. If you would like to here these presentations in your area, please contact me.
by ronald
MySQL system variables and status variables have two scopes. These are GLOBAL and SESSION which are self explanatory.
This is important to realize when altering system variables dynamically. The following example does not produce the expected results.
mysql> USE test;
Database changed
mysql> CREATE TABLE example1(
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> col1 VARCHAR(10) NOT NULL,
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.29 sec)
mysql> SHOW CREATE TABLE example1G
*************************** 1. row ***************************
Table: example1
Create Table: CREATE TABLE `example1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col1` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
We see that the table has a default CHARACTER SET of latin1. If you wanted to ensure all tables are created as utf8 you change the appropriate system variable. For example, we change the GLOBAL system variable and re-create the table.
mysql> SHOW GLOBAL VARIABLES like 'char%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /Users/rbradfor/mysql/mysql-5.1.39-osx10.5-x86/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> SET GLOBAL character_set_server=utf8;
Query OK, 0 rows affected (0.10 sec)
mysql> DROP TABLE example1;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE example1( id INT UNSIGNED NOT NULL AUTO_INCREMENT, col1 VARCHAR(10) NOT NULL, PRIMARY KEY(id) );
Query OK, 0 rows affected (0.12 sec)
mysql> SHOW CREATE TABLE example1G
*************************** 1. row ***************************
Table: example1
Create Table: CREATE TABLE `example1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col1` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)
The table still is latin1. This is because now we have a SESSION scope that differs from the GLOBAL scope as seen in this output.
mysql> SELECT @@GLOBAL.character_set_server,@@SESSION.character_set_server; +-------------------------------+--------------------------------+ | @@GLOBAL.character_set_server | @@SESSION.character_set_server | +-------------------------------+--------------------------------+ | utf8 | latin1 | +-------------------------------+--------------------------------+ 1 row in set (0.00 sec)
The solution is easy however the trap can be easily overlooked and especially when changing other MySQL system variables.
mysql> SET SESSION character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE example1;Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE example1( id INT UNSIGNED NOT NULL AUTO_INCREMENT, col1 VARCHAR(10) NOT NULL, PRIMARY KEY(id) );
Query OK, 0 rows affected (0.09 sec)
mysql> SHOW CREATE TABLE example1G
*************************** 1. row ***************************
Table: example1
Create Table: CREATE TABLE `example1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col1` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
By default, and when not specified in SHOW and SET commands the default scope is GLOBAL, however prior to MySQL 5.0.2 the default was SESSION. A note you will find in the 5.0 Reference Manual but not the current GA version 5.1 Reference Manual. See also SHOW STATUS Gotcha written in August 2006.
There are also other gotchas with scope that we will discuss at some other time.
“Don’t Assume” is a series of posts to help the Oracle DBA understand, use and appreciate the subtle differences and unique characteristics of the MySQL RDBMS in comparison to Oracle. These points as essential to operate MySQL effectively in a production environment and avoid any loss of data or availability.
For more posts in this series be sure to follow the mysql4oracledba tag and also watch out for other MySQL for Oracle DBA presentations.
The MySQLCamp for the Oracle DBA is a series of educational talks all Oracle DBA resources should attend. Two presentations from this series IGNITION and LIFTOFF will be presented at the MySQL Users Conference 2010 in Santa Clara, April 2010 This series also includes JUMPSTART and VELOCITY. If you would like to here these presentations in your area, please contact me.
by ronald
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, IGNITION and LIFTOFF. This series also includes JUMPSTART and VELOCITY.