Embedded Database Sizing

The initially installed Embedded Database is a relatively small instance--possibly too small for your application. This is important to note because errors occur when you reach the size limit of the database. Therefore, after installing, you may want to resize the Embedded Databases to fit your application. See Modifying the MySQL File Systems for instructions about modifying an existing, installed system.

Table and User limits for the embedded database make Oracle the preferred database for large installations.

Follow these steps to resize your database

For Windows,

1. Shut down all applications and application server. And also disconnect any other process to MySQL.

2. Stop the MySQL service on Windows with the command line: net stop mysql.

3. Edit the text file innodb_data_file_path in my.ini.

4. Add the new file path to innodb_data_file_path, for example:

Original:

innodb_data_file_path =d:/data/ibdata/ibdata1:600M:autoextend:max:2000M

To add the path f:/data/ibdata/ibdata2:200M, the new entry should be:

innodb_data_file_path = d:/data/ibdata/ibdata1:600M;f:/data/ibdata/ibdata2:200M:autoextend:max:2000M

Case sensitivity is important here. Be sure to use uppercase “M's”. Omitting this prevents your database from restarting

You must remove the initial, max and autoextend parameters from the initial line that includes ibdata1. You must also set the file size to what it really is. To find the size of ibdata1 run this command (in an command shell where you have already run oware in Windows):

ls -lh /opt/dorado/..../ibdata1

In my.cnf, using “1G” is acceptable. The line following this description of the original volume describes the additional volume:

/opt/dorado/oware3rd/.../ibdata1:1G;
/opt/dorado/..../ibdata2:1024M:autoextend<optional params>

The MySQL Reference Manual for adding a data volume to a MySQL database is http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html

5. Save my.ini.

6. Start MySQL service on Windows by running net start mysql'. You should see the following messages,

The MySql service is starting.

The MySql service was started successfully.

7. Check the file f:/data/ibdata/ibdata2 appears, and is the right size.

For Solaris,

1. Shut down all applications and application server. And also disconnect any other process connected to MySQL.

2. Stop the MySQL server process by running the following command:

mysqladmin -h <DBServerName> --user=root --password=<RootUserPassword> shutdown'

For example, if DBServerName is nova, RootUserPassword is dorado:

mysqladmin -h nova --user=root --password=dorado shutdown'

If that command fails, run the following command:

$MYSQL_ROOT/support-files/mysql.server stop

3. Log on as the root user and use a text editor like vi to open /ect/my.cnf.

4. Locate the entry innodb_data_file_path in my.cnf.

5. Add the new file path to innodb_data_file_path, for example,

Original:

innodb_data_file_path = /data1/ibdata/ibdata1:600M:autoextend:max:2000M

To add the path /data2/ibdata/ibdata2:200M, the new entry should be:

innodb_data_file_path = /data1/ibdata/ibdata1:600M;/data2/ibdata/ibdata2:200M:autoextend:max:2000M

Remove the remove the initial, max and autoextend parameters from the initial line that includes ibdata1, as described in the Tip for Windows.

6. Save my.cnf

7. Log on as an authorized user to start MySQL server process as mysqld_safe.

You may need to specify the path for mysqld_safe.

You should see messages indicating MySQL server started successfully.

8. Check the file /data2/ibdata/ibdata2 appears, and is the right size.

If you are planning to use your system for Active Performance Monitoring, best practice is to store performance data in a separate database. This improves performance.