3.8 Create a new database for Retain
3.8.13 MySQL
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.
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 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 Starting and Stopping 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: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 tomcat Starting and Stopping Tomcat.