Thursday, December 16, 2010

Some hidden goods in MySQL 5.5

5.5 GA The announcement of MySQL 5.5 released as GA has outlined the improvements in this version, which indeed has enough good new features to excite most any user.
There are two additions, though, that were lost in the noise of the bigger features, and I would like to spend a few words for each of them.
The first addition is something that users of stored routines have been waiting for since MySQL 5.0. No, it is not SIGNAL and its close associate RESIGNAL, which have been publicized enough. I am talking about the stored routine parameters, for which now there is a dedicated table in the information_schema.
Let's see an example, with a simple procedure that uses three parameters.

drop procedure if exists add_to_date ;
create procedure add_to_date(in d date, in i int, out nd date)
    set nd = d + interval i day;
This works as expected in both 5.1 and 5.5. (Never mind that it's redundant. I know it. It's only for the sake of keeping the example short).

 call add_to_date('2010-12-15',10,@new_date);
Query OK, 0 rows affected (0.00 sec)

 select @new_date;
| @new_date  |
| 2010-12-25 |
1 row in set (0.00 sec)
The difference starts to show when you want to deal with this procedure programmatically. If you need to find out which parameters are expected by this procedure, your only option in MySQL 5.1 is parsing the result of SHOW CREATE PROCEDURE add_to_date. Not terribly difficult in any scripting language, but a hassle in SQL.
In MySQL 5.5, instead, you can easily get the routine parameters with a simple query:

 select parameter_name, parameter_mode,data_type from information_schema. parameters where specific_schema='test' and specific_name= 'add_to_date' order by ordinal_position;
| parameter_name | parameter_mode | data_type |
| d              | IN             | date      |
| i              | IN             | int       |
| nd             | OUT            | date      |
3 rows in set (0.00 sec)

Speaking of the information_Schema, there are more goodies that were not emphasized enough. The Innodb engine that you find in the server is the evolution of the InnoDB plugin that ships with MySQL 5.1. Only that it is now built-in. What many people forget to mention is that the plugin (and thus the current InnoDB engine in 5.5) comes provided with its own InnoDB-specific instrumentation tables in the information_schema.

show tables like 'innodb%';
| Tables_in_information_schema (innodb%) |
| INNODB_CMP_RESET                       |
| INNODB_TRX                             |
| INNODB_CMPMEM_RESET                    |
| INNODB_LOCK_WAITS                      |
| INNODB_CMPMEM                          |
| INNODB_CMP                             |
| INNODB_LOCKS                           |
7 rows in set (0.00 sec)
This is the same set of tables that you may have seen if you have worked with the InnoDB plugin in 5.1. In short, you can get a lot of the info that you used to look at in the output of SHOW ENGINE INNODB STATUS. For more information, you should look at what the InnoDB plugin manual says on this topic.
I don't know if the tables can replace the SHOW ENGINE INNODB STATUS. Perhaps someone can comment on this issue and provide more information?


Mark Leith said...

Hey Giuseppe!

"I don't know if the tables can replace the SHOW ENGINE INNODB STATUS. Perhaps someone can comment on this issue and provide more information?"

Yes and No.. :)

The INNODB_TRX table now lists everything available in the TRANSACTIONS section of SHOW ENGINE INNODB STATUS (and more), so that can effectively replace it if you are using it for transaction information.

However, the rest of the "static" output has yet to be exposed in a structured format - in a GA release anyway.

The next release "should" (caveats apply et al) have some incarnation of the INNODB_METRICS table, examples of which were given at the UC this year:

That would leave foreign key and deadlock errors left as use cases..

Giuseppe Maxia said...

Hi Mark,
Thanks for the explanation. This is exactly what I was looking for.
Let's hope that the information_schema (or performance_schema) tables catch up with the SHOW ENGINE INNODB STATUS soon.

Matthew Montgomery said...

Hey Giuseppe!

One of my favorite under publicised feature enhancements is mysqldump --dump-slave=1|2

mysqldump now gives you the ability to set up a new replication slave by cloning an existing one. Locking the master or taking a file system snapshot from the slave are no longer needed.

Giuseppe Maxia said...

Thanks for this tip. I forgot about this option!