MySQL Database Maintenance for Best Performance

As a recommended best practice, customers should periodically optimize the Retain tables in order to achieve optimum query and update performance.  This can have a big impact on the performance of archive jobs, deletion jobs, etc.

It is also a good idea to optimize the memory settings for MySQL.

DISCLAIMER:

This knowledgebase (KB) article is provided for informational purposes only and as a courtesy service to you, our customer. GWAVA Technical Support does not have any database administration (DBA) expertise, nor does it provide DBA services or support. GWAVA is not responsible for the results of implementing any of the concepts contained in this KB article. Implementation of any of the concepts suggested in this KB article shall be done entirely at your own and sole risk, and GWAVA does not provide any kind of warranties whatsoever resulting from your decision of implementing any of the KB article’s concepts. It is up to you to do any research and to ensure yourself that any implementation and setup of any of the KB article’s concepts in your database system is correctly and properly executed. It is imperative that you have backups of your database system and storage directory before making any implementation. If you don’t have any DBA expertise, you should consult with a DBA expert before any implementation of the KB article’s concepts.  Under no circumstances, shall GWAVA, or any of its employees, be liable, in contract, tort, delict or otherwise, whether negligence is provable or not, for any direct, indirect, incidental, special, punitive, consequential or other damages, loss, cost or liability whatsoever that would result from or are related to the implementation of any of the concepts suggested in the KB article.

To the extent permitted by applicable law, GWAVA shall not be liable to you for any special, consequential, direct, indirect or similar damages, including any loss of data, arising out from migrating any type of messages, attachments, database, metadata in your Retain system to another server and/or location.

Since GWAVA Support does NOT provide DBA services or support, the following information is given as helpful bread crumbs, and it is up to each System Admin or DBA to research this information.  The following information has been gleaned partly from various support engineer experiences, and mostly from searching the internet.

As always, it is recommended to first backup (dump) the database before performing any maintenance.  If on a VM, you can alternatively back up the VM or take a snapshot.

Optimize Tables

Like hard disks, database tables' indices can become fragmented resulting in poor performance.  We've seen dramatic improvements in archive job and deletion job performance for customers who have optimized their retain tables.

From a command line, type:  mysqlcheck -o -u root -p retain

• "-o" instructs it to optimize all tables in the database.
• "-u root" specifies the user "root".
• "-p" will cause it to prompt for the retain database password.
• "retain" instructs it to run against the "retain" database (or whatever you named it during installation - "retain" is the default).

WARNING:  This should be done during a maintenance window, as it locks the tables and can take hours to complete; and for larger databases, possibly days.  We haven't received feedback from any customer on this, but a 50G database took hours.  This, of course, is more of a function of the number of records in the tables (not the database size), the power of the underlying hardware, and RAM.  The more innodb_buffer_pool_size you can give to MySQL, the more tablespace it can load into RAM.

The aforementioned will do ALL Retain tables; however, many are static and would not necessarily be subject to fragmentation issues; thus, you can paste the following query into the MySQL prompt after logging into MySQL.  It will optimize the tables one by one.

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!)

NOTE:  Retain uses the innoDB engine in MySQL, which does not support the optimize option; however, by running this option, it will automatically perform a re-create action on the tables, which will eliminate the fragmentation.  There might be better ways to accomplish this, so this information is supplied to point you in the right direction.

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 5.5 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, for Windows, in the [drive]:\Program Files\MySQL\MySQL Server 5.x/my.ini (where  "x" is the MySQL version).  This article only mentions a few items.  A customer will want to do its own research on these and other settings.

You'll find the following section in your my.cnf and there will be 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 will be 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 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 will be using 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.

IMPORTANT NOTE:  If you change this setting, it would be a good idea to flush the existing logs (ib_logfile0 and ib_logfile1).  To do this, take the following steps:
a)  Log into MySQL:  mysql -u root -p

b)  From the MySQL prompt, type:  SET GLOBAL innodb_fast_shutdown=0;

Then type "quit" to log out of MySQL.

c)  Shut down MySQL:  rcmysql stop

d)  Rename the ib_logfile0 and ib_logfile1 files.

g)  Start up MySQL: rcmysql start

h)  If MySQL starts up successfully, you can delete the ib_logfile* files.
If you do not delete or move the exisiting ib_logfile0 and ib_logfile1 files from the MySQL data directory when changing the logfile size, MySQL will not load.

innodb_log_buffer_size = 8M

The log buffer allows transactions to run without having to write the log to disk before the transactions commit.  If the transactions are large, then making the log buffer larger saves disk I/O.

There is a status called "innodb_log_waits" in MySQL.  This shows the number of times a wait was required for it to be flushed before continuing.  If you have any waits, then the buffer size is too small.

Here is the query you can run on that status:  SHOW GLOBAL STATUS LIKE 'innodb_log_waits';

#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 will vary per customer.  A system where a lot of queries/searches are made in Retain for items or runs multiple workers on the Retain Server itself will want to lean towards more memory to Tomcat, like in this example; otherwise, a customer may want to favor MySQL a bit and give Tomcat a little less. 

Changes to the my.cnf will 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 will fail to load.

Once MySQL loads successfully, you can start up Tomcat.

This article was originally published in the GWAVA knowledgebase as article ID 2221.