Over time, RT tables may become fragmented into numerous disk chunks and/or contaminated with deleted, yet unpurged data, affecting search performance. In these cases, optimization is necessary. Essentially, the optimization process combines pairs of disk chunks, removing documents that were previously deleted using DELETE statements.

Beginning with Manticore 4, this process occurs automatically by default. However, you can also use the following commands to manually initiate table compaction.

OPTIMIZE TABLE table_name [OPTION opt_name = opt_value [,...]]

OPTIMIZE TABLE rt;

OPTIMIZE TABLE rt OPTION cutoff=4;

OPTIMIZE TABLE rt OPTION sync=1;

Optimization can be a lengthy and I/O-intensive process. To minimize the impact, all actual merge work is executed serially in a special background thread, and the OPTIMIZE statement simply adds a job to its queue. The optimization thread can be I/O-throttled, and you can control the maximum number of I/Os per second and the maximum I/O size with the rt_merge_iops and rt_merge_maxiosize directives, respectively.

During optimization, the RT table being optimized remains online and available for both searching and updates nearly all the time. It is locked for a very brief period when a pair of disk chunks is successfully merged, allowing for the renaming of old and new files and updating the table header.

As long as auto_optimize is not disabled, tables are optimized automatically.

If you are experiencing unexpected SSTs or want tables across all nodes of the cluster to be binary identical, you need to:

1. Disable auto_optimize.
2. Manually optimize tables:

   On one of the nodes, drop the table from the cluster:

   ‹›

   • SQL

   SQL

   📋

   ⚙

   ALTER CLUSTER mycluster DROP myindex;

   Optimize the table:

   ‹›

   • SQL

   SQL

   📋

   ⚙

   OPTIMIZE TABLE myindex;

   Add back the table to the cluster:

   ‹›

   • SQL

   SQL

   📋

   ⚙

   ALTER CLUSTER mycluster ADD myindex;

   When the table is added back, the new files created by the optimization process will be replicated to the other nodes in the cluster. Any local changes made to the table on other nodes will be lost.

Table data modifications (inserts, replaces, deletes, updates) should either:

1. Be postponed, or
2. Be directed to the node where the optimization process is running.

Note that while the table is out of the cluster, insert/replace/delete/update commands should refer to it without the cluster name prefix (for SQL statements or the cluster property in case of an HTTP JSON request), otherwise they will fail. Once the table is added back to the cluster, you must resume write operations on the table and include the cluster name prefix again, or they will fail.

Search operations are available as usual during the process on any of the nodes.

Manticore provides isolation during the flushing and merging process of a real-time table to prevent any changes from affecting running queries.

For example, during table compaction, a pair of disk chunks are merged and a new chunk is produced. At one point, a new version of the table is created with the new chunk replacing the original pair. This is done seamlessly so that a long-running query using the original chunks will continue to see the old version of the table, while a new query will see the new version with the resulting merged chunk.

The same applies to flushing a RAM chunk, where suitable RAM segments are merged into a new disk chunk and the participated RAM chunk segments are abandoned. During this operation, Manticore provides isolation for queries that started before the operation began.

Furthermore, these operations are transparent for replaces and updates. If you update an attribute in a document that belongs to a disk chunk being merged with another one, the update will be applied to both that chunk and the resulting merged chunk. If you delete a document during a merge, it will be deleted in the original chunk and also in the resulting merged chunk, which will either have the document marked as deleted or have no such document at all if the deletion happened early in the merging process.

"Freezing" a table is useful when you want to make a physical copy or backup. It marks the table's files as frozen and shows where they are stored. After freezing, you can safely copy these files elsewhere. You can still add new documents to a frozen table until it reaches rt_mem_limit, but that new data stays in memory and is not written to disk until the table is unfrozen. If the table exceeds rt_mem_limit, all changes pause until it is unfrozen. If the daemon stops unexpectedly, any unsaved data is restored from the binlog.

"Locking" a table is useful for logical backups. It doesn't stop internal maintenance tasks, such as disk chunk merging operations or writing a RAM chunk to disk. It only blocks write operations. This means you can’t insert, replace, or update data in a locked table. This is helpful for tools like mysqldump, because locking ensures the data stays logically consistent. For example, without a lock, if you replace a document during a dump, the old version might already be in the dump and the new version might also appear later, both with the same document ID. Locking a table prevents that situation.

FREEZE tbl1[, tbl2, ...]

FREEZE t;

+-------------------+---------------------------------+
| file              | normalized                      |
+-------------------+---------------------------------+
| data/t/t.0.spa    | /work/anytest/data/t/t.0.spa    |
| data/t/t.0.spd    | /work/anytest/data/t/t.0.spd    |
| data/t/t.0.spds   | /work/anytest/data/t/t.0.spds   |
| data/t/t.0.spe    | /work/anytest/data/t/t.0.spe    |
| data/t/t.0.sph    | /work/anytest/data/t/t.0.sph    |
| data/t/t.0.sphi   | /work/anytest/data/t/t.0.sphi   |
| data/t/t.0.spi    | /work/anytest/data/t/t.0.spi    |
| data/t/t.0.spm    | /work/anytest/data/t/t.0.spm    |
| data/t/t.0.spp    | /work/anytest/data/t/t.0.spp    |
| data/t/t.0.spt    | /work/anytest/data/t/t.0.spt    |
| data/t/t.meta     | /work/anytest/data/t/t.meta     |
| data/t/t.ram      | /work/anytest/data/t/t.ram      |
| data/t/t.settings | /work/anytest/data/t/t.settings |
+-------------------+---------------------------------+
13 rows in set (0.01 sec)

The file column indicates the table's file paths within the data_dir of the running instance. The normalized column displays the absolute paths for the same files. To back up a table, simply copy the provided files without additional preparation.

When a table is frozen, you cannot execute UPDATE queries; they will wait until the table is unfrozen.

Also, DELETE and REPLACE queries have some restrictions while the table is frozen:

• If DELETE affects a document in the current RAM chunk - it is permitted.
• If DELETE impacts a document in a disk chunk but was previously deleted - it is allowed.
• If DELETE would alter an actual disk chunk - it will wait until the table is unfrozen.

Manually FLUSHing a RAM chunk of a frozen table will report 'success', but no real saving will occur.

DROP/TRUNCATE of a frozen table is allowed since these operations are not implicit. We assume that if you truncate or drop a table, you don't need it backed up; therefore, it should not have been frozen initially.

INSERTing into a frozen table is supported but limited: new data will be stored in RAM (as usual) until rt_mem_limit is reached; then, new insertions will wait until the table is unfrozen.

If you shut down the daemon with a frozen table, it will act as if it experienced a dirty shutdown (e.g., kill -9): newly inserted data will not be saved in the RAM-chunk on disk, and upon restart, it will be restored from a binary log (if any) or lost (if binary logging is disabled).

UNFREEZE tbl1[, tbl2, ...]

UNFREEZE tbl;

SHOW TABLE `foo` STATUS LIKE 'locked';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| locked        | 2     |
+---------------+-------+
1 row in set (0,00 sec)

lock tables tbl1 read[, tbl2 read, ...]

--------------
LOCK TABLES tbl READ
--------------
Query OK, 0 rows affected (0.000 sec)
--------------
LOCK TABLES tbl READ, tbl2 WRITE
--------------
Query OK, 0 rows affected, 1 warning (0.000 sec)
--------------
SHOW WARNINGS
--------------
+---------+------+--------------------------------+
| Level   | Code | Message                        |
+---------+------+--------------------------------+
| warning | 1000 | Write lock is not implemented. |
+---------+------+--------------------------------+
1 row in set (0.000 sec)

UNLOCK TABLES;

SHOW LOCKS;

+-----------+------+-----------+-----------------+
| Type      | Name | Lock Type | Additional Info |
+-----------+------+-----------+-----------------+
| rt        | a    | read      | Count: 1        |
| percolate | bar  | freeze    | Count: 3        |
| rt        | foo  | freeze    | Count: 2        |
+-----------+------+-----------+-----------------+
3 rows in set (0,01 sec)

FLUSH ATTRIBUTES

The FLUSH ATTRIBUTES command flushes all in-memory attribute updates in all the active disk tables to disk. It returns a tag that identifies the result on-disk state (which is basically a number of actual disk attribute saves performed since the server startup).

mysql> UPDATE testindex SET channel_id=1107025 WHERE id=1;
Query OK, 1 row affected (0.04 sec)
mysql> FLUSH ATTRIBUTES;
+------+
| tag  |
+------+
|    1 |
+------+
1 row in set (0.19 sec)