Table Optimization Scheduling
It is often necessary to control the start time for table optimizations, to prevent situations where administrative processes can be affected due to the table locking that happens during optimization.
Two types of system tables undergo scheduled optimization:
- Internal reverse index tables used to support full-text search
- Commonly used system tables
Optimizing Index Tables
The optimization settings for the first type of table are located within the corresponding knowledgebase, and calculated using the Revindex Optimization Period variable for the knowledgebase with the KB timezone. The optimization of these tables does not affect the work of other projects and does not block the work of users. The only inconvenience is a temporary performance reduction.
By default table optimization requests are placed in the queue to run every second Saturday night at 2 AM in the knowledgebase timezone.
Optimizing System Tables
For system tables the Revindex Optimization Period variable in the admin console is used to set the start time. For instance, the default value is "22-05", meaning that optimization can start from 10 p.m. till 5 a.m. System table optimization runs every fourth Saturday.
Additionally, the system must be in an idle state, which the system defines as whether there has been any user activity in the past 5 minutes. If there was no idle time during this entire period, the optimization is postponed till the next optimization range. This kind of optimization blocks the work of users and therefore should preferably be done at maintenance server time.
If a table is locked for optimization, the log will show a message like the following:
server.log.2018-11-20-07:2018-11-20 07:13:20,988 WARN [earch.utils.FTSSQLMonitor] Long-term query (2812 sec): optimize table swhistorylog
During this time any other processes such as record deletion will be locked. If the process takes longer than the default timeout value of 150 seconds it will fail with a LockWaitException. If it can get access to the system resources before the timeout, the process will be performed correctly.
The timeout value can be modified by setting the innodb_lock_wait_timeout variable in MySQL: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout.
To turn table optimization off, set the Revindex Optimization Period value to something like "0-0".
Alternately, you can create a file in the tmp directory of AL_HOME, with the name
Disabling optimization means that the system will not initiate any new optimizations, but any optimizations that are currently running will be completed.
If you have disabled optimization, or if you need to run optimization manually at any time, there are two possible methods:
- In AL_HOME/bin, the optimize_tables utility will perform optimization when run.
- The following SQL stored procedure runs on all tables under 5gb in size, which should be safe from the MYSQL lock timeout and offers more flexibility in terms of customization than the utility: