It Table optimization 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. necessary to help certain tables run more efficiently. Optimization happens automatically, but you might need to adjust the schedule to make sure it does not conflict with users working in the system.
Two types of system tables undergo scheduled optimization: tables are usually optimized:
- Internal reverse index tables used to support full-text search
- Commonly Certain commonly-used system tables tables
Optimizing Index Tables
The optimization settings for the first type of table are located within in the corresponding knowledgebaseproject, 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, index table optimization requests are placed in the queue to run every second Saturday night at 2 AM a.m. 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 between 10 p.m. till and 5 a.m. System table optimization runs every fourth Saturday.
...
| Info | ||
|---|---|---|
| ||
If a table is locked for optimization, the log will show shows a message like the followingthis:
During this time any other processes, such as record deletion will be , are locked. If the process takes longer than the default timeout value of 150 seconds, it will fail fails with a LockWaitException. If it can get access to the system resources before the timeout, the process will can 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. |
Disable Optimization
To turn table optimization off, set the Revindex Optimization Period value to something like "0-0".
...
Disabling optimization means that the system will not doesn't initiate any new optimizations, but any optimizations that are currently running will can be completed.
Manual Optimization
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 can perform table 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:
...