One of the convenient features of Hibernate that is inherited by Grails, is that once you have defined your data model, it can create the tables for you automatically on start up of the application. The nice thing about this is since Hibernate/GORM is database agnostic, it means you don't need to know how to define the tables in different relational databases. Be it Oracle, DB2, or MySQL. If you change your connection property, and load the appropriate JDBC driver, it will know how to talk to the database and create the correct tables.
MySQL is a popular choice of database for many developers. It's open source, easy to install, stable, and very fast. However, MySQL comes with several storage engine and you need to decide which one to choose. The two most popular are:
MyISAM - this is the default storage engine for MySQL prior to version 5.5. I believe this is the fastest storage engine of all, but the drawside is it does not support transactions.
InnoDB - this is not as fast as MyISAM, but it support transactions. This is the most popular among Java/Grails developers.
During development, I recommend using a standalone database server like MySQL, instead of using the default HSQLDB or H2 database. Because you can open a client and inspect the table structure created, and also perform queries outside the Grails application.
To configure MySQL in your datasource, first download the driver from: http://dev.mysql.com/downloads/connector/j/
The default configuration of MySQL is not very fast. They made it generic so that it can run on servers with small memory (For example, a VPS with 256Mb RAM). But desktop nowadays have so much memory and disk space that we can trade off for faster performance.
Performance is important because it affects your productivity. I have experimented with many configurations, but below is the simplest that I came up with that gave me good results.