1.3 Creating and Maintaining an SQL Database

Backing Up the Database

Be sure to back up (dump) the database before performing any maintenance. If on a VM, you can alternatively back up the VM or take a snapshot.

Optimizing Tables

Like hard disks, database tables' indexes can become fragmented resulting in poor performance. Optimizing tables can dramatically improve archive job and deletion job performance.

At the command prompt, type: mysqlcheck -o -u root -p retain

The command above optimizes all Retain tables at once. However, many are static and would not necessarily be subject to fragmentation issues.

Paste the following query into the MySQL prompt after logging into MySQL. This optimizes the tables one at a time.

These are the Retain 3.x tables you might consider optimizing:

OPTIMIZE TABLE retain.Audit;
OPTIMIZE TABLE retain.s_AddressBookCache;
OPTIMIZE TABLE retain.t_abook;
OPTIMIZE TABLE retain.t_document;
OPTIMIZE TABLE retain.t_dsref;
OPTIMIZE TABLE retain.t_message;
OPTIMIZE TABLE retain.t_message_attachments;
OPTIMIZE TABLE retain.t_message_properties;
OPTIMIZE TABLE retain.t_message_recipients;
OPTIMIZE TABLE retain.t_recipient;

These are the Retain 2.x tables you might consider optimizing if you are planning on migrating to Retain 3.x in the next few days or weeks:

OPTIMIZE TABLE retain.Audit;
OPTIMIZE TABLE retain.Document;
OPTIMIZE TABLE retain.Email;
OPTIMIZE TABLE retain.t_Node;
OPTIMIZE TABLE retain.t_attachment;
OPTIMIZE TABLE retain.t_msg_properties;
OPTIMIZE TABLE retain.t_recp_properties;
OPTIMIZE TABLE retain.t_recipients;

You can check on the progress of the queries by opening a separate terminal window and launching the following command:

watch -n 1 "mysqladmin -u root -p[password] processlist"

(note: no space between "-p" and your mysql root user password!)

For more detailed information and other mysqlcheck options, you may want to research this on the Internet. For your convenience, here are a couple of helpful articles:

4.5.3. mysqlcheck - A Table Maintenance Program

MySQL Back to Basics: Analyze, Check, Optimize, and Repair (written in 2008)

MySQL Reference Manual on getting a dump of a corrupted database This is sometimes the only option for recovering data from a corrupted INNODB database.

Memory Optimization

Memory for your innoDB can be configured in /etc/my.cnf (Linux) or on Windows, in the [drive]:\Program Files\MySQL\MySQL Server 5.x/my.ini file (where "x" is the MySQL version).

You'll find the following section in your my.cnf and there are identical lines in the my.ini on Windows although they may not be in this same order. The settings you may want to consider modifying are in red. This is followed by indented explanations in blue and are additional comments made for this article - those additional comments are not found in the my.cnf or my.ini.

Be sure to stop tomcat Starting and Stopping the Retain Server before working with MySQL.

# Uncomment the following if you are using InnoDB tables. If it does not exist, create it. innodb_file_per_table

The "innodb_file_per_table" is not included in the default my.cnf or my.ini and has to be typed in manually. It causes MySQL to create an .idx file for every table in the database. The benefits of this is up for debate. Some swear by it because they feel it eliminates contention for a single file containing all the tables (ibdata1); however, others have written that it is fast at first and then bogs down. We have found that, for Retain databases, it seems to positively impact performance. Do your own research and determine what is best for your system.

#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 4G

This allocates buffer memory for MySQL into which it can load table and index data. Obviously, accessing RAM is faster than disk, so the more memory you can give for the buffer pool, the more MySQL can load into the buffer. MySQL uses memory for other things, so if you have 6G to play with, you probably do not want the buffer pool to be larger than 4G. You'll have to experiment on your own system.

A helpful tool that is free is called mysqltuner.pl. It is a perl script that runs against your database to help you tune it's memory, security, etc. To get it, create a directory on your Linux server and change to that directory. Then type: wget mysqltuner.pl. Once it is down donwloading (takes 1 second), type: chmod +x *.pl so that it can be executed; then, execute it by typing: ./mysqltuner.pl.

innodb_additional_mem_pool_size = 204M

From what we've seen, this should be set to 5% of the buffer pool size. But, again, do your research.

# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 1024M

This affects the ib_logfile0 and ib_logfile1 files in your MySQL data directory. These files hold statements/transactions. The maximum size these can be set to (regardless of your buffer pool size) is 1024M (or 1G, totaling 2 GB for all log files together) for MySQL 5.5 and 255 G (total for all log files together can be up to 512 GB) for 5.6. For more information check out the MySQL website here. There is also some discussion on what values you should set the log files to (see https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/. The maximum size these can be set to is 1024M.

#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

To enable any one of those settings, remove the hash mark ("#") preceding it. The following is an example for a typical system that has 16G of total RAM, where 2G is allocated for the Linux OS and 8G to Tomcat. The amount allocated to Tomcat versus the database varies per customer. A system where a lot of queries/searches are made in Retain for items, or that runs multiple workers on the Retain Server itself, allocates more memory to Tomcat, like in this example; otherwise, a customer might want to favor MySQL a bit and give Tomcat a little less.

Changes to the my.cnf do not take affect until after MySQL is restarted. Again, if you changed the log file size setting, you must follow the steps listed above or MySQL fails to load.

Once MySQL loads successfully, you can start tomcat Starting and Stopping the Retain Server.